postgres group role setup

create-pg-user-and-db.sh

#!/bin/bash

##########################################################################################
#
# create-pg-user-and-database
#
# The context here is a typical Django web app. If you have a single application instance
# then it is straightforward to create a database user with a randomised password, and a
# database which is owned by this user:
#
#     $ createdb -O user db
#
# Then 'user' has all the privileges required to both create tables and SELECT, UPDATE etc.
#
# But if there are multiple app instances, each with their own settings, and if you are
# connecting to the DB as the same user, then any changes to 'user's password will mean
# updating the settings of each instance. Better to create a role and separate per-instance
# users belonging to this role. This wasn't straightforward to work out, so here is the detail.
#
# create a role without the LOGIN privilege:
#     % CREATE ROLE myapp
#
# create a role with LOGIN privilege (a user) who inherits the privileges of any roles granted:
#     % CREATE ROLE user1 LOGIN INHERIT PASSWORD secret   
#
# (or similar with CREATE USER or createuser)
#
# grant the role to the user
#     % GRANT myapp TO user1
#
# create a database owned by the *role*
#     $ createdb -O myapp dbname
#
#
# The problem then arises that, although user1 may be able to create tables in 'dbname'
# by virtue of belonging to role 'myapp', those table are then owned by user1, and a
# unprivileged user2 cannot by default SELECT etc., even though the two users share a role.
# One solution is to run a 'REASSIGN OWNED BY' statement after table
# creation - with psql this would be:
#
#    $ python manage.py syncdb --database=dbname
#    $ psql -d dbname -c "REASSIGN OWNED BY user1 TO myapp"
#
#############################################################################################

function create_pg_role {
    #params: rolename
    exists=$(psql -tqc "select COUNT(1) from pg_roles where rolname='$1'" | sed "s/\s//g")
    if [ $exists = 0 ]; then
        echo "creating $1";
        psql -c "CREATE ROLE \"$1\"";
    fi
}

function create_pg_user {
    # params: username, password, rolename
    exists=$(psql -tqc "SELECT count(1) FROM pg_catalog.pg_user WHERE usename = '$1'");
    if [ $exists = 0 ]; then
        psql -c "CREATE ROLE \"$1\" LOGIN INHERIT PASSWORD '$2'";
        psql -c "GRANT \"$3\" TO \"$1\"";
    else
        psql -c "ALTER ROLE \"$1\" WITH PASSWORD '$2'";
    fi
}

function create_pg_database {
    # params: database, rolename
    exists=$(psql -lqt | cut -d \| -f 1 | grep -w $1 | wc -l);
    if [ $exists = 0 ]; then
        createdb -O "$2" "$1";
    fi
}

set -e

create_pg_role "myapplication"
create_pg_user "user1" "secret1" "myapplication"
create_pg_user "user2" "secret2" "myapplication"
create_pg_database "mydatabase" "myapplication"