-
Notifications
You must be signed in to change notification settings - Fork 0
/
rls_test.sql
165 lines (128 loc) · 4 KB
/
rls_test.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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
select current_user
set role appGooAdmin
create table ag_sys.rls_test (
id int primary key default nextval('ag_sys.ag_seq'),
name text,
description text,
age int,
dob date)
drop table ag_sys.rls_test
truncate table ag_sys.rls_test
insert into ag_sys.rls_test values (1, 'stephen', 'dad', 50, '05-mar-1968'),
(2, 'sansanee', 'mum', 36, '30-jun-1982'), (3, 'jonathan', 'son', 2, '8-Nov-2016')
alter table ag_sys.rls_test enable row level security
select * from ag_sys.rls_test
set role appgooadmin
create table ag_sys.ag_data_security_rules (
ds_rule_id int not null primary key default nextval('ag_sys.ag_seq'),
table_name text,
rule_SQL text)
create table ag_sys.ag_data_security_rule_columns (
ds_rule_column_id int not null primary key default nextval('ag_sys.ag_seq'),
ds_column_name text,
ds_operator text,
ds_text_array text[],
ds_value text)
truncate table ag_sys.ag_data_security_rule_columns
insert into ag_sys.ag_data_security_rules values(1, 'ag_sys.rls_test', null)
insert into ag_sys.ag_data_security_rule_columns values(1, 'name', 'like', null::text[], '%e%')
DO $$
DECLARE
rls_sql1 text := '';
rls_sql2 text := '';
i integer := 0;
r record;
BEGIN
select 'create policy p' || ds_rule_id || ' ON ' || table_name || ' FOR SELECT TO r123 USING '
into rls_sql1
from ag_sys.ag_data_security_rules;
select '(' || ds_column_name || ' ' || ds_operator || ' ''' || ds_value || ''')'
into rls_sql2
from ag_sys.ag_data_security_rule_columns;
EXECUTE rls_sql1 || rls_sql2;
END$$;
drop policy p1 on ag_sys.rls_test;
create role r123;
select * from ag_sys.rls_test
GRANT ALL ON SCHEMA ag_sys TO r123;
GRANT ALL ON ag_sys.rls_test to r123
set role r123
select current_user
set role appgooadmin
alter role r123 with noinherit
GRANT ALL ON SCHEMA ag_sys TO appGooSudo;
select * from pg_roles where rolname = 'r123'
select pg_has_role('appgooadmin', 'appgoosudo', 'USAGE')
create user appGooAdmin IN ROLE appGooSudo;
alter role appGooAdmin WITH CREATEROLE;
create user "123"
set role "123"
select current_user::int
set role "root"
set role postgres
set role appgooadmin
select ('x'='x')
select (current_user = '123')
select current_user
DO $$
DECLARE
rolint int := 123;
roltxt text := '123';
BEGIN
execute 'set role "123"';
if current_user = rolint::name then
execute 'set role root';
else
execute 'set role postgres';
end if;
--perform 'select current_user';
END$$;
create table ag_sys.my_test (
user_id int, user_name name)
insert into ag_sys.my_test values (123, 123)
insert into ag_sys.my_test (user_name) values('test')
select * from ag_sys.my_test
set role "123"
set role root
GRANT ALL ON SCHEMA ag_sys TO "u124";
grant all on ag_sys.my_test to "u124"
select current_user
alter table ag_sys.my_test enable row level security;
create policy p123 on ag_sys.my_test FOR SELECT TO "123" using (user_name like '%123%')
drop policy if exists p123 on ag_sys.my_test
select pg_has_role('123', 'USAGE')
set role root;
drop table ag_sys.my_test;
create table ag_sys.my_test (
user_id name not null primary key default nextval('ag_sys.ag_seq'), user_name name)
create user u124 WITH INHERIT IN ROLE "123";
set role "u124"
select * from pg_auth_members
select * from pg_roles
WITH RECURSIVE cte AS (
SELECT oid, rolname FROM pg_roles WHERE rolname = 'u123'
UNION ALL
SELECT m.roleid, 'member of'
FROM cte
JOIN pg_auth_members m ON m.member = cte.oid
)
SELECT rolname FROM cte;
select app_role.rolname AS APP_ROLE, app_user.rolname AS APP_USER
from pg_auth_members m, pg_roles app_role, pg_roles app_user
where m.roleid = app_role.oid
and m.member = app_user.oid
select false::boolean AS has_rls
UNION ALL
select COALESCE(true, false)::boolean AS has_rls
from (
select true::boolean
from pg_catalog.pg_class t, pg_catalog.pg_policy p
where t.oid = p.polrelid
and COALESCE(t.relrowsecurity, false)::boolean
and lower(t.relname) = lower('_events')
and t.relkind in('r', 'p')
LIMIT 1) x
ORDER BY has_rls DESC
LIMIT 1;
select * from pg_class
ALTER TABLE _events DISABLE ROW LEVEL SECURITY;