From 6bd24af2ffbea91db1b10a5d5258980ce2068c7f Mon Sep 17 00:00:00 2001
From: sadbeast <sadbeast@sadbeast.com>
Date: Tue, 16 Jul 2024 18:16:29 -0700
Subject: let's go

---
 src/db/database.sql | 64 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 64 insertions(+)
 create mode 100644 src/db/database.sql

(limited to 'src/db/database.sql')

diff --git a/src/db/database.sql b/src/db/database.sql
new file mode 100644
index 0000000..91ca755
--- /dev/null
+++ b/src/db/database.sql
@@ -0,0 +1,64 @@
+\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;
-- 
cgit v1.2.3