A base setup for new self-managed postgres databases. See related guide (https://tightlycoupled.io/my-goto-postgres-configuration-for-web-services/). Also, please make sure to change all the passwords from `secret` to something suitable. !! Update !! see this gist for a config that works for self-managed and RDS databases: https://gist.github.co…

default-config-postgres.sql

CREATE ROLE owner 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;

CREATE DATABASE exampledb WITH OWNER owner ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

\c exampledb
REVOKE ALL ON DATABASE exampledb FROM PUBLIC;
DROP SCHEMA public;

SET ROLE owner;
CREATE SCHEMA exampledb;
RESET ROLE;

-- 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;

-- 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;