-
Notifications
You must be signed in to change notification settings - Fork 29
/
12_1_roles.sql
67 lines (61 loc) · 3.84 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
------------------------------------------
/* GRANT on schemas - once per database */
------------------------------------------
/* Viewer */
GRANT USAGE ON SCHEMA qgep_od TO qgep_viewer;
GRANT USAGE ON SCHEMA qgep_sys TO qgep_viewer;
GRANT USAGE ON SCHEMA qgep_vl TO qgep_viewer;
GRANT USAGE ON SCHEMA qgep_network TO qgep_viewer;
GRANT USAGE ON SCHEMA qgep_import TO qgep_viewer;
GRANT USAGE ON SCHEMA qgep_swmm TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_od TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_sys TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_vl TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_network TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_import TO qgep_viewer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA qgep_swmm TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_od TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_sys TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_vl TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_network TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_import TO qgep_viewer;
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA qgep_swmm TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_od GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_sys GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_vl GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_network GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_import GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_swmm GRANT SELECT, REFERENCES, TRIGGER ON TABLES TO qgep_viewer;
/* User */
GRANT ALL ON SCHEMA qgep_od TO qgep_user;
GRANT ALL ON ALL TABLES IN SCHEMA qgep_od TO qgep_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA qgep_od TO qgep_user;
GRANT ALL ON ALL TABLES IN SCHEMA qgep_network TO qgep_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_od GRANT ALL ON TABLES TO qgep_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_od GRANT ALL ON SEQUENCES TO qgep_user;
GRANT ALL ON ALL TABLES IN SCHEMA qgep_import TO qgep_user;
DO $$ BEGIN EXECUTE 'GRANT CREATE ON DATABASE ' || (SELECT current_database()) || ' TO "qgep_user"'; END $$; -- required for ili2pg imports/exports
/* Manager */
GRANT ALL ON SCHEMA qgep_vl TO qgep_manager;
GRANT ALL ON ALL TABLES IN SCHEMA qgep_vl TO qgep_manager;
GRANT ALL ON ALL SEQUENCES IN SCHEMA qgep_vl TO qgep_manager;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_vl GRANT ALL ON TABLES TO qgep_manager;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_vl GRANT ALL ON SEQUENCES TO qgep_manager;
/* SysAdmin */
GRANT ALL ON SCHEMA qgep_sys TO qgep_sysadmin;
GRANT ALL ON ALL TABLES IN SCHEMA qgep_sys TO qgep_sysadmin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA qgep_sys TO qgep_sysadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_sys GRANT ALL ON TABLES TO qgep_sysadmin;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_sys GRANT ALL ON SEQUENCES TO qgep_sysadmin;
/*
-- Revoke
REVOKE ALL ON SCHEMA qgep_od FROM qgep_viewer;
REVOKE ALL ON SCHEMA qgep_sys FROM qgep_viewer;
REVOKE ALL ON SCHEMA qgep_vl FROM qgep_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA qgep_od FROM qgep_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA qgep_sys FROM qgep_viewer;
REVOKE ALL ON ALL TABLES IN SCHEMA qgep_vl FROM qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_od REVOKE ALL ON TABLES FROM qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_sys REVOKE ALL ON TABLES FROM qgep_viewer;
ALTER DEFAULT PRIVILEGES IN SCHEMA qgep_vl REVOKE ALL ON TABLES FROM qgep_viewer;
*/