efrecon/ postgres-init.md

## postgres-init.md

      
    Raw
  

               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:


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.


Name of user to be created


Password for user.


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.


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
	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.
	#!/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