Initialise auth. and permissions in postgreSQL dbs

postgres-init.md

UNIX-Style postgreSQL Initialisation

Introduction

This script aims at easily initialising access rights for users in postgreSQL databases in a UNIX:y way. The script will read a configuration file where fields are separated by the colon (:) sign (e.g. analogical to /etc/password). The file is able to express and map rwx-style permissions onto GRANT-style privileges with the database and its schema/tables, thus simplifying initialisation of the authorisations, but also use of users created in other parts of a cloud system. The script is meant to be run at initialisation time by the "super-user" and is able to create the databases on demand. In real scenarios, the database itself might have been initialised through running a .sql file containing a number of SQL clauses for creating tables, triggers, etc.

Options

The script itself takes a few internal options, any other dash-led option will be passed further to psql at each execution, thus making it easy to pass further authentication- or connection- oriented options. A double-dash should mark the end of the option list and all arguments after the double-dash will be path to access files, which will be used for user creation and access rights definitions.

The options specific to the script are the following:

• --psql to give the exact path to the psql binary
• --verbose to turn on verbosity in the script

Provided a file called access.cfg in the format described below, and a password stored in the PGPASSWORD environment variable (for example), the following command would create users and give them access rights as of the file at a remote PostgreSQL instance. This example is knowned to be unsafe but exists solely for the sake of examplifying options passing. --verbose is captured and used by the script, while -h is blindly passed to psql.

postgres-init.sh --verbose -h postgres.example.com -- access.cfg

Configuration

Syntax

The syntax of the access rights files is as described below. Empty lines will be ignored. Lines starting with a hash-sign (#) will be ignored. Files MUST end with an empty line, otherwise the last line will not be parsed. Apart from empty or commenting lines, lines should contain 5 fields separated by colon-signs. These are described in order below:

1. List of databases, separated by commas, that the user will have access to. The script will attempt to create the databases if they do not already exist.

2. Name of user to be created

3. Password for user.

4. Access specification within the databases and for the coming (next field) tables/schemas. These specifications are a combination, in any order, of the letters r, w and x where r stands for read, w for writes and x for execute and these are mapped onto various postgreSQL privileges such as the ability to SELECT (read), INSERT (write), etc. Existing access rights for that user are cleared prior to applying the ones associated to the combination or r, w and x, unless the letter + also is present. When + is specified, no clearance will be applied. This can be used to give, for example, reading rights to a schema, but writing rights to another.

5. The comma separated list of schemas or tables that the permissions above apply to. When tables are specified, they should be fully-qualified, i.e. designated with the name of the schema, followed by a dot, followed by the name of the table.

Note that PostgreSQL itself uses similar, but different, conventions when summarising the rights of roles onto tables, schemas and databases from command-lines commands like \dp. The conventions used in this script are at a coarser-grain and an over-simplification of the rights available in PostgreSQL. If you want more control over roles and permissions, you should probably look into using a set of GRANT clauses.

Example

Provided a database called db with two schemas for storing raw and processed data called raw_data and processed_data, the following examplifies the creation of a number of users with varying access rights on these schemas.

# The user called pushed will be used when writing data into the incoming
# database for raw data.
db:push:s8gB2aw99PCLtU76GjcvCfJ8:wx:raw_data

# The user called api will be used when serving processed data to remote clients
db:api:umhgMpZcutQBZVMTswv9k29B:r:processed_data

# The user called transform might be used to collect raw data and push this data
# into the processed_data schema. Note the use of the "+" sign to incrementally
# define varying access rights on the two schemas.
db:transform:4wSs5Zt6bHPDyZ68WeTCh55D:rx:raw_data
db:transform::+wx:processed_data

# Backup user is able to access both schemas in the database
db:backup:hqm5ZYQ9Q3mbpTrLYnaZMMYF:r:raw_data,processed_data

LICENSE

Copyright (c) <2018>, <Emmanuel Frécon>
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this
   list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright notice,
   this list of conditions and the following disclaimer in the documentation
   and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

postgres-init.sh

#!/bin/sh

###
# This script aims at easily initialising access rights for users in postgreSQL
# databases in a UNIX:y way. The script itself takes a few internal options, any
# other dash-led option will be passed further to psql at each execution, thus
# making it easy to pass further authentication- or connection- oriented
# options. A double-dash should mark the end of the option list and all
# arguments after the double-dash will be path to access files, which will be
# used for user creation and access rights definitions.
#
# The options specific to the script are the following:
# --psql       to give the exact path to the psql binary
# --verbose    to turn on verbosity.
#
# The syntax of the access rights files is described in the manual.
####

# Eat all options so we can pass them to influx when calling it, all remaining
# stuff should be file names.
PSQL="psql"
OPTS=""
VERBOSE=0
while [ $# -gt 0 ]; do
    case "$1" in
        --)
            # End of options, everything that follows will be filenames
            shift
            break
            ;;
        --psql)
            # Capture separate path to binary, just in case
            PSQL="$2"
            shift 2
            ;;
        --verbose)
            VERBOSE=1
            shift
            ;;
        *)
            # All other options are supposed to take an argument (which isn't
            # entirely true), store them and arrange to capture them for further
            # invokation of influx.
            OPTS="${OPTS} $1 $2"
            shift 2
            ;;
    esac
done

verbose() {
  if [ "$VERBOSE" = "1" ]; then printf "%s\n" "$*" 1>&2; fi
}

warn() {
  printf "%s\n" "$*" 1>&2
}

callpsql() {
    verbose "pSQL (${OPTS}): $1"
    ${PSQL} ${OPTS} -t -X -q -c "$1"
}


for fname in "$@"; do
    DBS=""
    # See http://mywiki.wooledge.org/BashFAQ/024 for the loop...
    while IFS= read -r line; do
        line=$(echo "${line}" | sed '/^[[:space:]]*$/d' | sed '/^[[:space:]]*#/d')
        if [ -n "${line}" ]; then
            dbs=$(echo "${line}"|cut -d: -f1)
            user=$(echo "${line}"|cut -d: -f2)
            pass=$(echo "${line}"|cut -d: -f3)
            access=$(echo "${line}"|cut -d: -f4)
            tables=$(echo "${line}"|cut -d: -f5)

            # Create, amend the user and take care of admin privieges early.
            user=$(echo "${user}" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')

            if [ -n "${user}" ]; then
                # Create the user if it does not already exist.
                already_user=$(callpsql "\du" | cut -d"|" -f1 | grep ${user})
                if [ -z "${already_user}" ]; then
                    if [ -z "${pass}" ]; then
                        pass=$(strings /dev/urandom | grep -o '[[:alnum:]]' | head -n 16 | tr -d '\n')
                        warn "Generated password '$pass' for $user"
                    fi

                    admin=$(echo "${access}" | grep x)
                    # Check privileges (represented by an x)
                    if [ -z "${admin}" ]; then
                        verbose "Creating user ${user}"
                        callpsql "CREATE USER ${user} PASSWORD '${pass}';"
                    else
                        verbose "Creating administrator ${user}"
                        callpsql "CREATE USER ${user} PASSWORD '${pass}' CREATEDB CREATEROLE;"
                    fi
                else
                    if [ -n "${pass}" ]; then
                        verbose "Amending password for ${user}"
                        callpsql "ALTER USER ${user} PASSWORD '${pass}';"
                    fi
                    admin=$(echo "${access}" | grep x)
                    already=$(callpsql "\du" | grep ${user} | grep "Create ")
                    if [ -z "${admin}" -a -n "${already}" ]; then
                        verbose "Revoking privileges for ${user}"
                        callpsql "ALTER USER ${user} NOCREATEDB NOCREATEROLE;"
                    fi
                    if [ -n "${admin}" -a -z "${already}" ]; then
                        verbose "Granting privileges for ${user}"
                        callpsql "ALTER USER ${user} CREATEDB CREATEROLE;"
                    fi
                fi

                # Parse the list of databases, separated by comas and create them if
                # necessary (we are supposed to be the super-user)
                for db in $(echo "${dbs}" | tr "," "\n"); do
                    db=$(echo "${db}" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
                    if [ -n "${db}" ]; then
                        # Create the database if it does not already exist.
                        already=$(callpsql "\l" | cut -d"|" -f1 | grep ${db})
                        if [ -z "${already}" ]; then
                            verbose "Creating database ${db}" 
                            callpsql "CREATE DATABASE ${db};"
                        else
                            verbose "Database ${db} already exists"
                        fi

                        # Keep record of databases specified in file
                        already=$(echo ${DBS}|grep ${db})
                        if [ -z "${already}" ]; then
                            DBS="${DBS} ${db}"
                        fi
                    fi
                done

                # Decide access for user.
                read=$(echo "${access}" | grep r)
                write=$(echo "${access}" | grep w)
                exec=$(echo "${access}" | grep x)
                continue=$(echo "${access}" | grep "+")
                ACCESS=""
                if [ -n "${read}" ] && [ -n "${write}" ] && [ -n "${exec}" ]; then
                    ACCESS="ALL PRIVILEGES"
                else
                    if [ -n "${read}" ]; then
                        ACCESS="SELECT, ${ACCESS}"
                    fi
                    if [ -n "${write}" ]; then
                        ACCESS="INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ${ACCESS}"
                    fi
                    if [ -n "${exec}" ]; then
                        ACCESS="TRIGGER, ${ACCESS}"
                    fi
                    ACCESS=$(echo "${ACCESS}" | sed -e 's/,[[:space:]]*$//')
                fi

                # Give access to the databases to the user
                for db in $(echo "${dbs}" | tr "," "\n"); do
                    db=$(echo "${db}" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
                    if [ -n "${db}" ]; then
                        if [ -n "${already_user}" ] && [ -z "${continue}" ]; then
                            verbose "Cleaning up user privileges for ${user}!"
                            callpsql "REVOKE ALL PRIVILEGES ON DATABASE ${db} FROM ${user};"
                        fi
                        callpsql "GRANT CONNECT, TEMPORARY ON DATABASE ${db} to ${user};"
                    fi
                done

                for tbl in $(echo "${tables}" | tr "," "\n"); do
                    table=$(echo "${tbl}"|grep "\.")
                    if [ -z "$table" ]; then
                        if [ -n "${exec}" ]; then
                            callpsql "GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ${tbl} TO ${user};"
                        fi
                        # Grant access to all current tables and usage on the schema
                        callpsql "GRANT ${ACCESS} ON ALL TABLES IN SCHEMA ${tbl} TO ${user};"
                        callpsql "GRANT USAGE ON SCHEMA ${tbl} TO ${user};"
                        # Grant access to future tables in the same schema
                        callpsql "ALTER DEFAULT PRIVILEGES IN SCHEMA ${tbl} GRANT ${ACCESS} ON TABLES TO ${user};"
                    else
                        if [ -n "${exec}" ]; then
                            schema=$(echo "${tbl}"|cut -d. -f1)
                            callpsql "GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ${schema} TO ${user};"
                        fi
                        callpsql "GRANT ${ACCESS} ON TABLE ${tbl} TO ${user};"
                    fi
                done
            fi
        fi
    done < ${fname}
done