Skip to content

Instantly share code, notes, and snippets.

@house9
Last active May 23, 2018 21:07
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save house9/798d2b61ce5c2beb5a225daf5f4b1572 to your computer and use it in GitHub Desktop.
--------------------------------------------
-- login as superuser
-- use sample_database
-- do not allow users to create tables by default
REVOKE CREATE ON SCHEMA public FROM public;
-- application user
CREATE USER tablecreator;
ALTER USER tablecreator WITH ENCRYPTED PASSWORD 'password';
-- allow application user to create database objects (tables, etc...)
GRANT CREATE ON SCHEMA public TO tablecreator;
GRANT ALL PRIVILEGES ON DATABASE sample_database to tablecreator;
-- read-only report user
CREATE USER reportuser;
ALTER USER reportuser WITH ENCRYPTED PASSWORD 'password';
-- login as tablecreator
-- read-only access for reportuser
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reportuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reportuser;
ALTER DEFAULT PRIVILEGES FOR ROLE tablecreator IN SCHEMA public GRANT SELECT ON TABLES TO reportuser;
-- https://dba.stackexchange.com/questions/35316/why-is-a-new-user-allowed-to-create-a-table
---------------------------------------------------------------------
--- REVOKE
-- login as tablecreator
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM reportuser;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM reportuser;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM reportuser;
ALTER DEFAULT PRIVILEGES FOR ROLE tablecreator IN SCHEMA public REVOKE SELECT ON TABLES FROM reportuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM reportuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON SEQUENCES FROM reportuser;
-- login as superuser
DROP USER reportuser;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment