readonly-postgres-users.sql

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