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/)

default-config-postgres-updated.sql

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