Small PostgreSQL script to quickly initialize an isolated (i.e. with private schema) database for a small project with a minimal administration layout (to prevent abusing default superuser)

default_db_init

-- Courtesy of Erwin Brandstetter
-- https://dba.stackexchange.com/a/117661
--
-- new_db   = new solitary database
-- new_sch  = private schema for new database
-- db_admin = db_mng privileges + create/drop tables privileges
-- db_mng   = db_user privileges + insert/update/delete privileges
-- db_user  = select privilege only
--
-- psql -U postgres

CREATE USER db_admin WITH ENCRYPTED PASSWORD 'pw1';
CREATE USER db_mng WITH ENCRYPTED PASSWORD 'pw2';
CREATE USER db_user WITH ENCRYPTED PASSWORD 'pw3';

GRANT db_user TO db_mng;
GRANT db_mng TO db_admin;

CREATE DATABASE new_db;
REVOKE ALL ON DATABASE new_db FROM public; -- remove default public schema privileges from new_db

GRANT CONNECT ON DATABASE new_db TO db_user; -- others inherit

-- \connect new_db
CREATE SCHEMA new_sch AUTHORIZATION db_admin;

SET search_path = new_sch;

ALTER ROLE db_admin IN DATABASE new_db 
  SET search_path = new_sch; -- not inherited
ALTER ROLE db_mng IN DATABASE new_db
  SET search_path = new_sch;
ALTER ROLE db_user IN DATABASE new_db
  SET search_path = new_sch;
  
GRANT USAGE ON SCHEMA new_sch TO db_user;
GRANT CREATE ON SCHEMA new_sch TO db_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE db_admin
  GRANT SELECT ON TABLES TO db_user;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin
  GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO db_mng;
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_mng;
  
-- Optional
ALTER TABLE preexisting_table OWNER TO db_admin;