\set rw_name :database_name '_rw'
\set ro_name :database_name '_ro'
\set password_role :database_name '_password'

DROP SCHEMA IF EXISTS :schema CASCADE;
DROP DATABASE IF EXISTS :database_name;

-- Uncomment for non-development environments
-- DROP ROLE IF EXISTS :owner;
-- CREATE ROLE :owner WITH LOGIN PASSWORD :'owner';

DROP ROLE IF EXISTS :password_role;
CREATE ROLE :password_role; 
ALTER ROLE :password_role SET search_path TO :schema, pgsodium, public;

GRANT ddl TO :owner;

DROP ROLE IF EXISTS :rw_name;
CREATE ROLE :rw_name WITH LOGIN PASSWORD :'rw_name';

GRANT dml TO :rw_name;

DROP ROLE IF EXISTS :ro_name;
CREATE ROLE :ro_name WITH LOGIN PASSWORD :'ro_name';

GRANT read_only TO :ro_name;

ALTER ROLE :owner SET search_path TO :schema, public;
ALTER ROLE :rw_name SET search_path TO :schema, pgsodium, public;
ALTER ROLE :ro_name SET search_path TO :schema, public;

CREATE DATABASE :database_name OWNER :owner;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON DATABASE :database_name FROM PUBLIC;

GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, read_only;

\c :database_name
CREATE EXTENSION pgsodium;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA pgsodium TO dml;

SET ROLE :owner;

CREATE SCHEMA :schema AUTHORIZATION :schema;

GRANT USAGE, CREATE ON SCHEMA :schema TO ddl;

GRANT USAGE ON SCHEMA :schema TO dml, read_only;

REVOKE ALL ON DATABASE :database_name FROM PUBLIC;

GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, read_only;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT USAGE, SELECT ON SEQUENCES TO dml, read_only;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT UPDATE ON SEQUENCES TO dml;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT EXECUTE ON ROUTINES TO dml, read_only;