generated from teksi/template
-
Notifications
You must be signed in to change notification settings - Fork 0
/
12_1_roles.sql
67 lines (52 loc) · 3.05 KB
/
12_1_roles.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
------------------------------------------
/* GRANT on schemas - once per database */
------------------------------------------
/* Viewer */
GRANT USAGE ON SCHEMA tdh_od TO tdh_viewer;
GRANT USAGE ON SCHEMA tdh_sys TO tdh_viewer;
GRANT USAGE ON SCHEMA tdh_vl TO tdh_viewer;
GRANT USAGE ON SCHEMA tdh_cfg TO tdh_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tdh_od TO tdh_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tdh_sys TO tdh_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tdh_vl TO tdh_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tdh_cfg TO tdh_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA tdh_od TO tdh_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA tdh_sys TO tdh_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA tdh_vl TO tdh_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA tdh_cfg TO tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_od GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_sys GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_vl GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_cfg GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO tdh_viewer;
/* User */
GRANT ALL ON SCHEMA tdh_od TO tdh_user;
GRANT ALL ON ALL TABLES IN SCHEMA tdh_od TO tdh_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA tdh_od TO tdh_user;
GRANT ALL ON ALL TABLES IN SCHEMA tdh_cfg TO tdh_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_od GRANT ALL ON TABLES TO tdh_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_od GRANT ALL ON SEQUENCES TO tdh_user;
DO $$ BEGIN EXECUTE 'GRANT CREATE ON DATABASE ' || (SELECT current_database()) || ' TO "tdh_user"'; END $$; -- required for ili2pg imports/exports
/* Manager */
GRANT ALL ON SCHEMA tdh_vl TO tdh_manager;
GRANT ALL ON ALL TABLES IN SCHEMA tdh_vl TO tdh_manager;
GRANT ALL ON ALL SEQUENCES IN SCHEMA tdh_vl TO tdh_manager;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_vl GRANT ALL ON TABLES TO tdh_manager;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_vl GRANT ALL ON SEQUENCES TO tdh_manager;
/* SysAdmin */
GRANT ALL ON SCHEMA tdh_sys TO tdh_sysadmin;
GRANT ALL ON ALL TABLES IN SCHEMA tdh_sys TO tdh_sysadmin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA tdh_sys TO tdh_sysadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_sys GRANT ALL ON TABLES TO tdh_sysadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_sys GRANT ALL ON SEQUENCES TO tdh_sysadmin;
/*
-- Revoke
REVOKE ALL ON SCHEMA tdh_od FROM tdh_viewer;
REVOKE ALL ON SCHEMA tdh_sys FROM tdh_viewer;
REVOKE ALL ON SCHEMA tdh_vl FROM tdh_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA tdh_od FROM tdh_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA tdh_sys FROM tdh_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA tdh_vl FROM tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_od REVOKE ALL ON TABLES FROM tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_sys REVOKE ALL ON TABLES FROM tdh_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA tdh_vl REVOKE ALL ON TABLES FROM tdh_viewer;
*/