create-postgres-db-and-schema.sql

CREATE USER [database owner name] WITH CREATEROLE ENCRYTPED PASSWORD '[database owner's password]';
CREATE DATABASE [database name] OWNER [database owner name];

-- connect to [database name]
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE [database name] FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- log in as [database owner name] to [database name] db
CREATE SCHEMA [schema name];
ALTER USER [database owner name] SET SEARCH_PATH TO [schema name];

CREATE ROLE [read/write application group name] NOINHERIT;
GRANT CONNECT ON DATABASE [database name] TO [read/write application group name];
GRANT USAGE ON SCHEMA [schema name] TO [read/write application group name];
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA [schema name] TO [read/write application group name];
GRANT USAGE ON ALL SEQUENCES IN SCHEMA [schema name] TO [read/write application group name];
ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO [read/write application group name];
ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT USAGE ON SEQUENCES TO [read/write application group name];

CREATE USER [read/write application user] WITH ENCRYPTED PASSWORD '[read/write application user password]' IN ROLE [read/write application group name];
ALTER USER [read/write application user] SET SEARCH_PATH TO [schema name];