Skip to content

Instantly share code, notes, and snippets.

@paydro
Last active August 7, 2020 14:19
Show Gist options
  • Save paydro/dd0be658da6ce0f142fa24b534472bea to your computer and use it in GitHub Desktop.
Save paydro/dd0be658da6ce0f142fa24b534472bea to your computer and use it in GitHub Desktop.
A postgres configuration for RDS and self-managed postgres databases. Based on my guide (https://tightlycoupled.io/goto-postgres-configuration-for-rds-and-self-managed-postgres/)
-- Copy/paste this file or execute with `psql -f thisfile.sql`
CREATE ROLE owner CREATEDB LOGIN ENCRYPTED PASSWORD 'secret' CONNECTION LIMIT 3;
ALTER ROLE owner SET statement_timeout = 20000;
ALTER ROLE owner SET lock_timeout = 3000;
ALTER ROLE owner SET idle_in_transaction_session_timeout = 3000; -- v9.6+
CREATE ROLE readwrite_users NOLOGIN;
CREATE ROLE readonly_users NOLOGIN;
-- We need to be the owner user before creating the database, otherwise it's
-- owned by the rds_superuser.
SET ROLE owner;
CREATE DATABASE exampledb WITH OWNER owner ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
\c exampledb
-- We're the rds_superuser here -- \c resets the settings set with `SET`.
-- But, this allows us to remove the public schema that is always added by
-- default.
DROP SCHEMA public;
-- Now we can switch to owner and proceed
SET ROLE owner;
REVOKE ALL ON DATABASE exampledb FROM PUBLIC;
CREATE SCHEMA exampledb;
-- search_paths
ALTER ROLE owner SET search_path TO exampledb;
SET search_path TO exampledb;
-- Privileges and GRANTS
GRANT CONNECT ON DATABASE exampledb TO readwrite_users;
GRANT TEMPORARY ON DATABASE exampledb TO readwrite_users;
GRANT CONNECT ON DATABASE exampledb TO readonly_users;
GRANT TEMPORARY ON DATABASE exampledb TO readonly_users;
-- Not needed, but being explicit is nice.
GRANT CREATE, USAGE ON SCHEMA exampledb TO owner;
GRANT USAGE ON SCHEMA exampledb TO readwrite_users;
GRANT USAGE ON SCHEMA exampledb TO readonly_users;
-- readwrite_users
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES
TO readwrite_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT USAGE, SELECT, UPDATE
ON SEQUENCES
TO readwrite_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT EXECUTE
ON FUNCTIONS
TO readwrite_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT USAGE
ON TYPES
TO readwrite_users;
-- readonly_users
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT SELECT
ON TABLES
TO readonly_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT USAGE, SELECT
ON SEQUENCES
TO readonly_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT EXECUTE
ON FUNCTIONS
TO readonly_users;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
IN SCHEMA exampledb
GRANT USAGE
ON TYPES
TO readonly_users;
-- Revoke PUBLIC -- some of this is redundant, but being explicit is good.
-- see PUBLIC defaults: https://www.postgresql.org/docs/12/ddl-priv.html#PRIVILEGES-SUMMARY-TABLE
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
REVOKE ALL PRIVILEGES
ON TABLES
FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
REVOKE ALL PRIVILEGES
ON SEQUENCES
FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
REVOKE ALL PRIVILEGES
ON FUNCTIONS
FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
REVOKE ALL PRIVILEGES
ON TYPES
FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
FOR ROLE owner
REVOKE ALL PRIVILEGES
ON SCHEMAS
FROM PUBLIC;
-- only rds_superuser can create users
RESET ROLE;
-- Create app user (readwrite)
CREATE ROLE app WITH
LOGIN
ENCRYPTED PASSWORD 'secret'
CONNECTION LIMIT 90
IN ROLE readwrite_users;
ALTER ROLE app SET statement_timeout = 1000;
ALTER ROLE app SET lock_timeout = 750;
-- v9.6+
ALTER ROLE app SET idle_in_transaction_session_timeout = 1000;
ALTER ROLE app SET search_path = exampledb;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment