Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement