postsetup.sql

-- to be run as main postgres user
-- remove default public permissions and only allow migration user, on the app db

-- public (lower case) is for the schema public.
-- PUBLIC (upper case) means "for all users".
\c {}
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO {}_usermigrate;
ALTER USER {}_usermigrate WITH NOCREATEDB;

presetup.sql

-- to be run as main postgres rile
-- clear any existing db

-- debug lines (so you can make changes quickly on local)
-- DROP DATABASE {};
-- REVOKE CREATE ON SCHEMA public from {}_usermigrate;
-- DROP ROLE {}_user;
-- DROP ROLE {}_userro;
-- DROP ROLE {}_usermigrate;
-- end debug lines

CREATE ROLE {}_usermigrate WITH LOGIN CREATEDB CREATEROLE;
ALTER ROLE {}_usermigrate WITH ENCRYPTED PASSWORD '{pass}';

run.sh

#!/bin/bash

# this script generates postgres template sql scripts so you can create users with least necessary privileges.
# 1 database for app, 3 privileges users: superadmin, regular and readonly.

# Simple setup your APPNAME below, and run the script. For now the postgres host is localhost.

# Overall strategy.
# 1. as the main db user (postgres) create a app database, and the app database superadmin.
# 2. as the app superadmin, create other users, and grant them necessary permissions.
# 3. as main db user (postgres), revoke all create privileges except for app superadmin.

# further resources:
# https://www.jujens.eu/posts/en/2021/Mar/10/db-user-migrations/
# https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/


# Setup variables
APPNAME=app1
DEBUG=false
APPMIGRATEPASS=`tr -dc A-Za-z0-9 </dev/urandom | head -c 16`
APPUSERPASS=`tr -dc A-Za-z0-9 </dev/urandom | head -c 16`
APPUSERROPASS=`tr -dc A-Za-z0-9 </dev/urandom | head -c 16`

# Generate app sql files.
echo "=============== Generating presetup, setup and postsetup file"
sed -e "s/{}/${APPNAME}/g" -e "s/{pass}/${APPMIGRATEPASS}/g" presetup.sql > ${APPNAME}_presetup.sql
sed -e "s/{}/${APPNAME}/g" -e "s/{passuser}/${APPUSERPASS}/g" -e "s/{passro}/${APPUSERROPASS}/g" setup.sql > ${APPNAME}_setup.sql
sed -e "s/{}/${APPNAME}/g" postsetup.sql > ${APPNAME}_postsetup.sql

# Run the sql files.
## presetup
echo "=============== Running presetup (as user postgres): and creating main (migration capable) user"
psql -d postgres -U postgres -h localhost -p 5432 `[ $DEBUG = true ] && echo -n "-a"` -f ${APPNAME}_presetup.sql
echo "Created user $APPNAME_usermigrate (migration permissions)"

## setup
echo "=============== Running setup (as user $APPNAME_usermigrate): and creating database and regular user"
psql "postgresql://${APPNAME}_usermigrate:${APPMIGRATEPASS}@localhost:5432/postgres" `[ $DEBUG = true ] && echo -n "-a"` -f ${APPNAME}_setup.sql
#PGPASSWORD="$APPMIGRATEPASS" PGUSER= psql -d postgres -U "$APPNAME_usermigrate" -h localhost -p 5432 -a -f ${APPNAME}_setup.sql
echo "Created db $APPNAME and user $APPNAME_user (regular permissions)"

## postsetup
echo "=============== Running postsetup (as user postgres), and revoking unecessary create table access"
psql -d postgres -U postgres -h localhost -p 5432 `[ $DEBUG = true ] && echo -n "-a"` -f ${APPNAME}_postsetup.sql

echo All Done. Please copy below credentials:
echo "Database          : $APPNAME"
echo "Admin user        : ${APPNAME}_usermigrate (has all permissions)"
echo "Admin password    : ${APPMIGRATEPASS}"
echo "Normal user       : ${APPNAME}_user (has regular permissions)"
echo "Normal password   : ${APPUSERPASS}"
echo "Readonly user     : ${APPNAME}_userro (has read-only permissions)"
echo "Readonly password : ${APPUSERROPASS}"

setup.sql

-- to be run as the app role.

--create and swithc to database
CREATE DATABASE {};
\c {};

-- create regular and ro users
CREATE ROLE {}_user WITH LOGIN;
ALTER ROLE {}_user WITH ENCRYPTED PASSWORD '{passuser}';

CREATE ROLE {}_userro WITH LOGIN;
ALTER ROLE {}_userro WITH ENCRYPTED PASSWORD '{passro}';

-- setup permissions for regular user;
-- Allow our user to use Select, insert, update, delete and SEQUENCES.
-- It's required to insert data with auto-incrementing primary keys for instance.
-- it also cannot privilege escalate - block option.
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES
    IN SCHEMA public
    TO {}_user;
ALTER DEFAULT PRIVILEGES
    GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLES
    TO {}_user;
GRANT USAGE, SELECT ON ALL SEQUENCES 
    IN SCHEMA public
    TO {}_user;
ALTER DEFAULT PRIVILEGES
    GRANT USAGE, SELECT
    ON SEQUENCES
    TO {}_user;
REVOKE GRANT OPTION
    FOR ALL PRIVILEGES
    ON ALL TABLES
    IN SCHEMA public
    FROM {}_user;
ALTER DEFAULT PRIVILEGES
    REVOKE GRANT OPTION
    FOR ALL PRIVILEGES
    ON TABLES
    FROM {}_user;

-- setup permissions for regular user (only select)
GRANT SELECT
    ON ALL TABLES
    IN SCHEMA public
    TO {}_userro;
ALTER DEFAULT PRIVILEGES
    GRANT SELECT
    ON TABLES
    TO {}_userro;