Skip to content

Instantly share code, notes, and snippets.

@anilgulecha
Last active August 17, 2022 07:35
Show Gist options
  • Save anilgulecha/99ea03e5b00b5ea90650c616d8cb2487 to your computer and use it in GitHub Desktop.
Save anilgulecha/99ea03e5b00b5ea90650c616d8cb2487 to your computer and use it in GitHub Desktop.
-- 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;
-- 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}';
#!/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}"
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment