dbo_init.sh

#!/bin/bash

set -e
set -u
#
# GROUP: min user access...
#   - can connect to database
#   - can access schema 
#   - can read schema tables
#
# SERVICE: inherits GROUP plus..
#   - can insert to any schema table 
#
# DEVELS: inherits GROUP + SERVICE plus...
#   - can create new schema objects 
# 



##
## tablespace > db > schema : {table 1, table 2, ...}
##

_DBDATA='/var/lib/postgresql/data'

_TSPATH='/tmp/fastssd' # path on disk for tspace
_TSNAME='docker'

_DBNAME='predictions'
_SCHEMA='model'

_ROLE_GROUP='cortex'  # container for all user-roles
_DEF_PASSWD='default'

_ROLE_ADMIN='cxadmin' # highest level admin devs
_ROLE_DEVEL='hostdev' # developers w/cli access from host
_ROLE_SERVS='service' # read/write access for internal services

_TABLE_MAIN='mlresult'   # predictions.model.result
_TABLE_META='mlmeta'  # predictions.model.release

mkdir -p $_TSPATH
mkdir -p $_DBDATA

# Set these environmental variables to override them,
# but they have safe defaults.

export PGDATA=${_DBDATA-/var/lib/postgresql/data}
export POSTGRES_DB=${_DBNAME-postgres}
export POSTGRES_USER=${_ROLE_GROUP-postgres}
export POSTGRES_PASSWORD=${_DEF_PASSWD-postgres}


##
## SET PSQL STATEMENT STEPS
##
_DROP=$(cat <<EOF
  DROP DATABASE IF EXISTS "$_DBNAME";
  DROP SCHEMA IF EXISTS "$_SCHEMA";
  DROP TABLESPACE IF EXISTS "$_TSNAME";
  DROP ROLE IF EXISTS "$_ROLE_ADMIN";
  DROP ROLE IF EXISTS "$_ROLE_DEVEL";
  DROP ROLE IF EXISTS "$_ROLE_SERVS";
  DROP ROLE IF EXISTS "$_ROLE_GROUP";
EOF
)

_CREATE_ROLES=$(cat <<EOF
  CREATE ROLE "$_ROLE_ADMIN" 
    SUPERUSER 
    NOLOGIN 
    NOINHERIT;

  CREATE ROLE "$_ROLE_DEVEL" 
    CREATEDB CREATEROLE
    NOLOGIN 
    INHERIT;

  CREATE ROLE "$_ROLE_SERVS"
    NOLOGIN 
    INHERIT;

  CREATE ROLE "$_ROLE_GROUP" 
    NOLOGIN 
    INHERIT;
EOF
)

_CREATE_DB=$(cat <<EOF
  CREATE DATABASE $_DBNAME
    WITH ENCODING='UTF8'
    OWNER $_ROLE_ADMIN;
EOF
)

_INIT_SCHEMA=$(cat <<EOF
  CREATE SCHEMA $_SCHEMA AUTHORIZATION $_ROLE_ADMIN
    CREATE TABLE $_TABLE_MAIN (
      session_id integer NOT NULL,
      model_id integer NOT NULL,
      procedure_type varchar(50) NOT NULL,
      prediction_utc TIMESTAMP NOT NULL
    )
    CREATE TABLE $_TABLE_META (
      model_id integer NOT NULL,
      released_utc TIMESTAMP NOT NULL,
      ml_sig REAL,
      ml_r2 REAL,
      ml_bci REAL,
      ml_fci REAL
    );
EOF
)

#  CREATE TABLESPACE $_TSNAME
#    OWNER $_ROLE_ADMIN
#    LOCATION '$_TSPATH';
#   
#  SET default_tablespace = $_TSNAME;

_GRANT_PRIVS=$(cat <<EOF
  GRANT CONNECT 
    ON DATABASE $_DBNAME
    TO $_ROLE_GROUP;

  GRANT USAGE 
    ON SCHEMA $_SCHEMA
    TO $_ROLE_GROUP;

  GRANT SELECT 
    ON ALL TABLES IN SCHEMA $_SCHEMA
    TO $_ROLE_GROUP;

  GRANT INSERT
    ON ALL TABLES IN SCHEMA $_SCHEMA
    TO $_ROLE_SERVS;

  GRANT CREATE
    ON SCHEMA $_SCHEMA
    TO $_ROLE_DEVEL;
EOF
)



_ALTER=$(cat <<EOF
  ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
    GRANT SELECT
      ON TABLES
      TO $_ROLE_GROUP;

  ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
    GRANT INSERT
      ON TABLES
      TO $_ROLE_SERVS;

  ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
    GRANT ALL PRIVILEGES
      ON TABLES
      TO $_ROLE_DEVEL;
EOF
)

_INHERIT=$(cat <<EOF
  GRANT $_ROLE_GROUP 
    TO $_ROLE_SERVS;

  GRANT $_ROLE_SERVS 
    TO $_ROLE_DEVEL;

  GRANT $_ROLE_DEVEL 
    TO $_ROLE_ADMIN;
EOF
)


psql <<SQL
$_CREATE_ROLES
$_CREATE_DB
$_INIT_SCHEMA
$_GRANT_PRIVS
$_ALTER
$_INHERIT
SQL