-
Notifications
You must be signed in to change notification settings - Fork 2
/
exampleScript-verbose.sql
96 lines (74 loc) · 5.74 KB
/
exampleScript-verbose.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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- BEGIN: org.company
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- POLICY NAME: Custom Policy: org.company
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- REMOVE EXISTING TABLE GRANTS
revoke all privileges on table org.company from public;
revoke all privileges on table org.company from app_super_admin;
revoke all privileges on table org.company from app_admin;
revoke all privileges on table org.company from app_user;
revoke all privileges on table org.company from app_anonymous;
revoke all privileges on table org.company from app_demon;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- ENABLE ROW LEVEL SECURITY
alter table org.company enable row level security;
create policy rls_special_app_admin_policy_that_will_make_things_confusing_org_company
on org.company
as permissive
for insert
to app_admin
using ( owner_id = viewer_id() )
;
create policy rls_is_the_best_s_org_company
on org.company
as permissive
for all
to app_user
using ( owner_id = this-can-change-in-settings.viewer_id() )
with check something_special
;
create policy rls_something_else_org_company
on org.company
as restrictive
for all
to app_user
using (another_function())
;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE NEW TABLE GRANTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_super_admin
grant
delete
on table org.company
to app_super_admin;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_user
grant
select ,
insert ( id, external_id, licenses, company_type, name, updated_at, location_id ), -- excluded columns: created_at
update ( external_id, licenses, company_type, name, updated_at, location_id ), -- excluded columns: id, created_at
on table org.company
to app_user;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- IMPLIED TABLE GRANTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_super_admin
--IMPLIED: grant select on table org.company to app_super_admin;
--IMPLIED: grant insert on table org.company to app_super_admin;
--IMPLIED: grant update on table org.company to app_super_admin;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_admin
--IMPLIED: grant select on table org.company to app_admin;
--IMPLIED: grant insert on table org.company to app_admin;
--IMPLIED: grant update on table org.company to app_admin;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- DENIED TABLE GRANTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_admin
--DENIED: grant delete on table org.company to app_admin;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_user
--DENIED: grant delete on table org.company to app_user;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_anonymous
--DENIED: grant select on table org.company to app_anonymous;
--DENIED: grant insert on table org.company to app_anonymous;
--DENIED: grant update on table org.company to app_anonymous;
--DENIED: grant delete on table org.company to app_anonymous;
------------------------------------------------------------------------------------------------------------------------------------------------------------------- app_demon
--DENIED: grant select on table org.company to app_demon;
--DENIED: grant insert on table org.company to app_demon;
--DENIED: grant update on table org.company to app_demon;
--DENIED: grant delete on table org.company to app_demon;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- END: org.company
--==