· 6 years ago · Aug 23, 2019, 03:26 AM
1#!/bin/bash
2
3set -e
4set -u
5#
6# GROUP: min user access...
7# - can connect to database
8# - can access schema
9# - can read schema tables
10#
11# SERVICE: inherits GROUP plus..
12# - can insert to any schema table
13#
14# DEVELS: inherits GROUP + SERVICE plus...
15# - can create new schema objects
16#
17
18
19
20##
21## tablespace > db > schema : {table 1, table 2, ...}
22##
23
24_DBDATA='/var/lib/postgresql/data'
25
26_TSPATH='/tmp/fastssd' # path on disk for tspace
27_TSNAME='docker'
28
29_DBNAME='predictions'
30_SCHEMA='model'
31
32_ROLE_GROUP='cortex' # container for all user-roles
33_DEF_PASSWD='default'
34
35_ROLE_ADMIN='cxadmin' # highest level admin devs
36_ROLE_DEVEL='hostdev' # developers w/cli access from host
37_ROLE_SERVS='service' # read/write access for internal services
38
39_TABLE_MAIN='mlresult' # predictions.model.result
40_TABLE_META='mlmeta' # predictions.model.release
41
42mkdir -p $_TSPATH
43mkdir -p $_DBDATA
44
45# Set these environmental variables to override them,
46# but they have safe defaults.
47
48export PGDATA=${_DBDATA-/var/lib/postgresql/data}
49export POSTGRES_DB=${_DBNAME-postgres}
50export POSTGRES_USER=${_ROLE_GROUP-postgres}
51export POSTGRES_PASSWORD=${_DEF_PASSWD-postgres}
52
53
54##
55## SET PSQL STATEMENT STEPS
56##
57_DROP=$(cat <<EOF
58 DROP DATABASE IF EXISTS "$_DBNAME";
59 DROP SCHEMA IF EXISTS "$_SCHEMA";
60 DROP TABLESPACE IF EXISTS "$_TSNAME";
61 DROP ROLE IF EXISTS "$_ROLE_ADMIN";
62 DROP ROLE IF EXISTS "$_ROLE_DEVEL";
63 DROP ROLE IF EXISTS "$_ROLE_SERVS";
64 DROP ROLE IF EXISTS "$_ROLE_GROUP";
65EOF
66)
67
68_CREATE_ROLES=$(cat <<EOF
69 CREATE ROLE "$_ROLE_ADMIN"
70 SUPERUSER
71 NOLOGIN
72 NOINHERIT;
73
74 CREATE ROLE "$_ROLE_DEVEL"
75 CREATEDB CREATEROLE
76 NOLOGIN
77 INHERIT;
78
79 CREATE ROLE "$_ROLE_SERVS"
80 NOLOGIN
81 INHERIT;
82
83 CREATE ROLE "$_ROLE_GROUP"
84 NOLOGIN
85 INHERIT;
86EOF
87)
88
89_CREATE_DB=$(cat <<EOF
90 CREATE DATABASE $_DBNAME
91 WITH ENCODING='UTF8'
92 OWNER $_ROLE_ADMIN;
93EOF
94)
95
96_INIT_SCHEMA=$(cat <<EOF
97 CREATE SCHEMA $_SCHEMA AUTHORIZATION $_ROLE_ADMIN
98 CREATE TABLE $_TABLE_MAIN (
99 session_id integer NOT NULL,
100 model_id integer NOT NULL,
101 procedure_type varchar(50) NOT NULL,
102 prediction_utc TIMESTAMP NOT NULL
103 )
104 CREATE TABLE $_TABLE_META (
105 model_id integer NOT NULL,
106 released_utc TIMESTAMP NOT NULL,
107 ml_sig REAL,
108 ml_r2 REAL,
109 ml_bci REAL,
110 ml_fci REAL
111 );
112EOF
113)
114
115# CREATE TABLESPACE $_TSNAME
116# OWNER $_ROLE_ADMIN
117# LOCATION '$_TSPATH';
118#
119# SET default_tablespace = $_TSNAME;
120
121_GRANT_PRIVS=$(cat <<EOF
122 GRANT CONNECT
123 ON DATABASE $_DBNAME
124 TO $_ROLE_GROUP;
125
126 GRANT USAGE
127 ON SCHEMA $_SCHEMA
128 TO $_ROLE_GROUP;
129
130 GRANT SELECT
131 ON ALL TABLES IN SCHEMA $_SCHEMA
132 TO $_ROLE_GROUP;
133
134 GRANT INSERT
135 ON ALL TABLES IN SCHEMA $_SCHEMA
136 TO $_ROLE_SERVS;
137
138 GRANT CREATE
139 ON SCHEMA $_SCHEMA
140 TO $_ROLE_DEVEL;
141EOF
142)
143
144
145
146_ALTER=$(cat <<EOF
147 ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
148 GRANT SELECT
149 ON TABLES
150 TO $_ROLE_GROUP;
151
152 ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
153 GRANT INSERT
154 ON TABLES
155 TO $_ROLE_SERVS;
156
157 ALTER DEFAULT PRIVILEGES IN SCHEMA $_SCHEMA
158 GRANT ALL PRIVILEGES
159 ON TABLES
160 TO $_ROLE_DEVEL;
161EOF
162)
163
164_INHERIT=$(cat <<EOF
165 GRANT $_ROLE_GROUP
166 TO $_ROLE_SERVS;
167
168 GRANT $_ROLE_SERVS
169 TO $_ROLE_DEVEL;
170
171 GRANT $_ROLE_DEVEL
172 TO $_ROLE_ADMIN;
173EOF
174)
175
176
177psql <<SQL
178$_CREATE_ROLES
179$_CREATE_DB
180$_INIT_SCHEMA
181$_GRANT_PRIVS
182$_ALTER
183$_INHERIT
184SQL