Skip to content

Instantly share code, notes, and snippets.

@guedressel
Last active September 24, 2021 19:31
Show Gist options
  • Save guedressel/93b875885604d31007fe9b05a8d19939 to your computer and use it in GitHub Desktop.
Save guedressel/93b875885604d31007fe9b05a8d19939 to your computer and use it in GitHub Desktop.
Boilerplate DCL statements handy for PostgreSQL databases
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment