#!/bin/bash

cat <<HERE
DROP OWNED BY ${PROJECT_NAME}_ddl CASCADE;
DROP OWNED BY ${PROJECT_NAME}_config CASCADE;
DROP OWNED BY ${PROJECT_NAME}_delete CASCADE;
DROP OWNED BY ${PROJECT_NAME}_write CASCADE;
DROP OWNED BY ${PROJECT_NAME}_read CASCADE;

\c ${PROJECT_NAME}
DROP OWNED BY ${PROJECT_NAME}_ddl CASCADE;
DROP OWNED BY ${PROJECT_NAME}_config CASCADE;
DROP OWNED BY ${PROJECT_NAME}_delete CASCADE;
DROP OWNED BY ${PROJECT_NAME}_write CASCADE;
DROP OWNED BY ${PROJECT_NAME}_read CASCADE;

DROP SCHEMA data;
DROP SCHEMA func;

DROP USER ${PROJECT_NAME}_ddl;
DROP USER ${PROJECT_NAME}_config;
DROP USER ${PROJECT_NAME}_delete;
DROP USER ${PROJECT_NAME}_write;
DROP USER ${PROJECT_NAME}_read;

CREATE SCHEMA data;
CREATE SCHEMA func;

SET SEARCH_PATH = "data", "func";

CREATE USER ${PROJECT_NAME}_ddl nocreatedb nocreaterole noinherit password '${DATABASE_DDL_PASSWORD}';
CREATE USER ${PROJECT_NAME}_config nosuperuser nocreatedb nocreaterole noinherit password '${DATABASE_CONFIG_PASSWORD}';
CREATE USER ${PROJECT_NAME}_delete nosuperuser nocreatedb nocreaterole noinherit password '${DATABASE_DELETE_PASSWORD}';
CREATE USER ${PROJECT_NAME}_write nosuperuser nocreatedb nocreaterole noinherit password '${DATABASE_WRITE_PASSWORD}';
CREATE USER ${PROJECT_NAME}_read nosuperuser nocreatedb nocreaterole noinherit password '${DATABASE_READ_PASSWORD}';

GRANT ${PROJECT_NAME}_ddl to avnadmin;
ALTER DATABASE ${PROJECT_NAME} OWNER TO ${PROJECT_NAME}_ddl;

ALTER ROLE ${PROJECT_NAME}_ddl SET SEARCH_PATH = "data", "func";
ALTER ROLE ${PROJECT_NAME}_config SET SEARCH_PATH = "data", "func";
ALTER ROLE ${PROJECT_NAME}_delete SET SEARCH_PATH = "data", "func";
ALTER ROLE ${PROJECT_NAME}_write SET SEARCH_PATH = "data", "func";
ALTER ROLE ${PROJECT_NAME}_read SET SEARCH_PATH = "data", "func";

REVOKE ALL ON SCHEMA data,func FROM ${PROJECT_NAME}_ddl;
REVOKE ALL ON SCHEMA data,func FROM ${PROJECT_NAME}_config;
REVOKE ALL ON SCHEMA data,func FROM ${PROJECT_NAME}_delete;
REVOKE ALL ON SCHEMA data,func FROM ${PROJECT_NAME}_write;
REVOKE ALL ON SCHEMA data,func FROM ${PROJECT_NAME}_read;

REVOKE ALL PRIVILEGES ON DATABASE ${PROJECT_NAME} FROM ${PROJECT_NAME}_ddl;
REVOKE ALL PRIVILEGES ON DATABASE ${PROJECT_NAME} FROM ${PROJECT_NAME}_config;
REVOKE ALL PRIVILEGES ON DATABASE ${PROJECT_NAME} FROM ${PROJECT_NAME}_delete;
REVOKE ALL PRIVILEGES ON DATABASE ${PROJECT_NAME} FROM ${PROJECT_NAME}_write;
REVOKE ALL PRIVILEGES ON DATABASE ${PROJECT_NAME} FROM ${PROJECT_NAME}_read;

GRANT CONNECT ON DATABASE ${PROJECT_NAME} TO ${PROJECT_NAME}_config;
GRANT USAGE ON SCHEMA data TO ${PROJECT_NAME}_config;
GRANT USAGE ON SCHEMA func TO ${PROJECT_NAME}_config;
GRANT CONNECT ON DATABASE ${PROJECT_NAME} TO ${PROJECT_NAME}_read;
GRANT USAGE ON SCHEMA data TO ${PROJECT_NAME}_read;
GRANT USAGE ON SCHEMA func TO ${PROJECT_NAME}_read;
GRANT CONNECT ON DATABASE ${PROJECT_NAME} TO ${PROJECT_NAME}_write;
GRANT USAGE ON SCHEMA data TO ${PROJECT_NAME}_write;
GRANT USAGE ON SCHEMA func TO ${PROJECT_NAME}_write;
GRANT CONNECT ON DATABASE ${PROJECT_NAME} TO ${PROJECT_NAME}_delete;
GRANT USAGE ON SCHEMA data TO ${PROJECT_NAME}_delete;
GRANT USAGE ON SCHEMA func TO ${PROJECT_NAME}_delete;
GRANT CONNECT ON DATABASE ${PROJECT_NAME} TO ${PROJECT_NAME}_ddl;
GRANT USAGE ON SCHEMA data TO ${PROJECT_NAME}_ddl;
GRANT USAGE ON SCHEMA func TO ${PROJECT_NAME}_ddl;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA func TO ${PROJECT_NAME}_ddl;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA func TO ${PROJECT_NAME}_config;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA func TO ${PROJECT_NAME}_write;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA func TO ${PROJECT_NAME}_read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA func TO ${PROJECT_NAME}_delete;

GRANT ALL ON SCHEMA data,func TO ${PROJECT_NAME}_ddl;

GRANT ALL ON ALL TABLES IN SCHEMA data TO ${PROJECT_NAME}_ddl;

GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA data TO ${PROJECT_NAME}_delete;
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA data TO ${PROJECT_NAME}_write;
GRANT SELECT ON ALL TABLES IN SCHEMA data TO ${PROJECT_NAME}_read;

ALTER DEFAULT PRIVILEGES FOR USER avnadmin IN SCHEMA data
GRANT ALL ON TABLES TO ${PROJECT_NAME}_ddl;

ALTER DEFAULT PRIVILEGES FOR USER avnadmin IN SCHEMA data
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO ${PROJECT_NAME}_delete;

ALTER DEFAULT PRIVILEGES FOR USER avnadmin IN SCHEMA data
GRANT SELECT, UPDATE, INSERT ON TABLES TO ${PROJECT_NAME}_write;

ALTER DEFAULT PRIVILEGES FOR USER avnadmin IN SCHEMA data
GRANT SELECT ON TABLES TO ${PROJECT_NAME}_read;


ALTER DEFAULT PRIVILEGES FOR USER ${PROJECT_NAME}_ddl IN SCHEMA data
GRANT ALL ON TABLES TO ${PROJECT_NAME}_ddl;

ALTER DEFAULT PRIVILEGES FOR USER ${PROJECT_NAME}_ddl IN SCHEMA data
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO ${PROJECT_NAME}_delete;

ALTER DEFAULT PRIVILEGES FOR USER ${PROJECT_NAME}_ddl IN SCHEMA data
GRANT SELECT, UPDATE, INSERT ON TABLES TO ${PROJECT_NAME}_write;

ALTER DEFAULT PRIVILEGES FOR USER ${PROJECT_NAME}_ddl IN SCHEMA data
GRANT SELECT ON TABLES TO ${PROJECT_NAME}_read;

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA func;

HERE