Basic Postgres Permission Groups

create_permission_groups.sql

-- Create a groups to assign permissions to
CREATE GROUP main;
CREATE GROUP read_only;

-- Revoke default permission settings
REVOKE ALL ON DATABASE <database_name> FROM PUBLIC, main, read_only;

REVOKE ALL ON SCHEMA public FROM PUBLIC, main, read_only;

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC, main, read_only;

-- Grant basic permissions
GRANT USAGE ON SCHEMA public TO main, read_only;

GRANT CONNECT
ON DATABASE <database_name>
TO main, read_only;

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO main, read_only;

GRANT INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO main;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO main;

-- Alter default privileges for new tables added to this schema
ALTER DEFAULT PRIVILEGES 
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO main;

ALTER DEFAULT PRIVILEGES 
IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

ALTER DEFAULT PRIVILEGES 
IN SCHEMA public 
GRANT USAGE ON SEQUENCES TO main;


-- Create CRUD user
CREATE USER <username>
IN GROUP main
ENCRYPTED PASSWORD <password>;

-- Create read only user
CREATE USER <username>
IN GROUP read_only
ENCRYPTED PASSWORD <password>;