Permissioned Template Based PG Database Creation

commands.sql

---------------------------
-- SERVER LEVEL COMMANDS --
---------------------------

-- Create a new template database (we could alter template1 as well).
CREATE DATABASE "template2";

-- This avoids possibly droping the template table
UPDATE pg_database SET datistemplate = true WHERE datname='template2';

-- Revoke CONNECT on template2.public from default permission set
REVOKE ALL ON SCHEMA public FROM public;




----------------
-- NEW APP DB --
----------------
-- Create a new group for the application
CREATE ROLE "app-group";

-- Create a new user for DDL
CREATE USER "app-ddl" IN ROLE "app-group" PASSWORD 'x' CREATEDB INHERIT;

-- Create a new user for application
CREATE USER "app-user" IN ROLE "app-group" PASSWORD 'x' INHERIT;

-- Create a non-user for application
CREATE USER "non-app-user" PASSWORD 'x';

-- Create a new application database
CREATE DATABASE "app-db" OWNER "app-group" TEMPLATE "template2";

-- REVOKE connect on the DATABASE
REVOKE CONNECT ON DATABASE "app-db" FROM PUBLIC;
GRANT CONNECT ON DATABASE "app-db" TO "app-ddl", "app-user";