Skip to content

Instantly share code, notes, and snippets.

@paydro
Last active September 14, 2023 16:24
Show Gist options
  • Save paydro/8a47986d222b0d2c8bdad8d44e495d96 to your computer and use it in GitHub Desktop.
Save paydro/8a47986d222b0d2c8bdad8d44e495d96 to your computer and use it in GitHub Desktop.
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…
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment