mypgsetup

gistfile1.txt

CREATE ROLE role__readonly  WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE role__readwrite WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE role__migrate   WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE DATABASE appdb WITH
  OWNER = postgres
  ENCODING = 'UTF8'
  LC_COLLATE = 'C'
  LC_CTYPE = 'C'
  TABLESPACE = pg_default
  template = template0
  CONNECTION LIMIT = -1;

\connect appdb;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;

CREATE SCHEMA app;
CREATE SCHEMA migration;

GRANT CONNECT ON DATABASE appdb TO role__migrate;
GRANT USAGE, CREATE ON SCHEMA app TO role__migrate;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role__migrate;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE ON SEQUENCES TO role__migrate;
GRANT USAGE, CREATE ON SCHEMA migration TO role__migrate;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role__migrate;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration GRANT USAGE ON SEQUENCES TO role__migrate;

GRANT CONNECT ON DATABASE appdb TO role__readonly;
GRANT USAGE ON SCHEMA app TO role__readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE role__migrate IN SCHEMA app GRANT SELECT ON TABLES TO role__readonly;

GRANT CONNECT ON DATABASE appdb TO role__readwrite;
GRANT USAGE ON SCHEMA app TO role__readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE role__migrate IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role__readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE role__migrate IN SCHEMA app GRANT USAGE ON SEQUENCES TO role__readwrite;

CREATE USER user__rpt WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'shhh';
GRANT role__readonly TO user__rpt;
CREATE USER user__app WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'shhh';
GRANT role__readwrite TO user__app;
CREATE USER user__mig WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'shhh';
GRANT role__migrate TO user__mig;