Boilerplate DCL statements handy for PostgreSQL databases

postgres-privileges-cheat-sheet

CREATE DATABASE test OWNER postgres;
# connect to db test and stay connected for rest of statements in this file.
\c test

# prevent unauthorized access
REVOKE ALL ON DATABASE test FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

# create "groups" (read: ROLES)
CREATE ROLE read NOLOGIN NOINHERIT;
CREATE ROLE readwrite NOLOGIN NOINHERIT;

# add "user" to "group" (read: assign ROLE to ROLE)
GRANT read TO someone;
GRANT readwrite TO someoneelse;

# Set up grants for read group:
# assumption: owner of db test is user postgres
GRANT CONNECT ON DATABASE test TO read;
-- Existing objects
GRANT USAGE ON SCHEMA public TO read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO read;
-- New objects
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO read;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON SEQUENCES TO read;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO read;

# Set up grants for readwrite group:
# assumption: owner of db test is user postgres
GRANT CONNECT ON DATABASE test TO readwrite;
-- Existing objects
GRANT ALL PRIVILEGES ON SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO readwrite;
-- New objects
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO readwrite;