PostgreSQL roles

postgresql_roles.sql

\z

-- Full access
GRANT ALL privileges ON DATABASE db TO admin_user;
GRANT ALL ON schema public TO admin_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;

-- Read-only access
CREATE ROLE readonly;

-- grant access to all existing tables
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

-- grant access to all table which will be created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

-- create user and grant role to this user
CREATE USER b_readonly WITH PASSWORD 'reAdOnLy123';
GRANT readonly TO b_readonly;