1  -- SPDX-FileCopyrightText: 2023 LakeSoul Contributors
  2  --
  3  -- SPDX-License-Identifier: Apache-2.0
  4  
  5  ALTER TABLE namespace
  6      ENABLE ROW LEVEL SECURITY;
  7  CREATE INDEX CONCURRENTLY IF NOT EXISTS namespace_domain_index ON namespace (domain);
  8  DO
  9  $$
 10      BEGIN
 11          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'namespace') THEN
 12              -- admins/users can read namespaces in the domains they belong to and the public domain
 13              CREATE POLICY read_policy ON namespace AS PERMISSIVE FOR SELECT USING (
 14                          domain = 'public'
 15                      OR domain = 'lake-public'
 16                      OR domain = current_user
 17                      OR domain IN (SELECT rolname
 18                                    FROM pg_roles
 19                                    WHERE pg_has_role(current_user, oid, 'member')));
 20  
 21              -- only admin role in workspace is allowed to create/drop namespace
 22              CREATE POLICY update_policy ON namespace AS PERMISSIVE FOR UPDATE
 23                  USING (concat(domain, '_admins') IN (SELECT rolname
 24                                                       FROM pg_roles
 25                                                       WHERE pg_has_role(current_user, oid, 'member')));
 26              CREATE POLICY insert_policy ON namespace AS PERMISSIVE FOR INSERT
 27                  WITH CHECK (concat(domain, '_admins') IN (SELECT rolname
 28                                                       FROM pg_roles
 29                                                       WHERE pg_has_role(current_user, oid, 'member')));
 30              CREATE POLICY delete_policy ON namespace AS PERMISSIVE FOR DELETE
 31                  USING (concat(domain, '_admins') IN (SELECT rolname
 32                                                       FROM pg_roles
 33                                                       WHERE pg_has_role(current_user, oid, 'member')));
 34          END IF;
 35      END
 36  $$;
 37  
 38  ALTER TABLE table_info
 39      ENABLE ROW LEVEL SECURITY;
 40  CREATE INDEX CONCURRENTLY IF NOT EXISTS table_info_domain_index ON table_info (domain);
 41  DO
 42  $$
 43      BEGIN
 44          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'table_info') THEN
 45              CREATE POLICY domain_only_policy ON table_info
 46                  USING (
 47                          domain = 'public'
 48                      OR domain = 'lake-public'
 49                      OR domain = current_user
 50                      OR domain IN (SELECT rolname
 51                                    FROM pg_roles
 52                                    WHERE pg_has_role(current_user, oid, 'member')));
 53          END IF;
 54      END
 55  $$;
 56  
 57  ALTER TABLE table_path_id
 58      ENABLE ROW LEVEL SECURITY;
 59  CREATE INDEX CONCURRENTLY IF NOT EXISTS table_path_id_domain_index ON table_path_id (domain);
 60  DO
 61  $$
 62      BEGIN
 63          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'table_path_id') THEN
 64              CREATE POLICY domain_only_policy ON table_path_id
 65                  USING (
 66                          domain = 'public'
 67                      OR domain = 'lake-public'
 68                      OR domain = current_user
 69                      OR domain IN (SELECT rolname
 70                                    FROM pg_roles
 71                                    WHERE pg_has_role(current_user, oid, 'member')));
 72          END IF;
 73      END
 74  $$;
 75  
 76  ALTER TABLE table_name_id
 77      ENABLE ROW LEVEL SECURITY;
 78  CREATE INDEX CONCURRENTLY IF NOT EXISTS table_name_id_domain_index ON table_name_id (domain);
 79  DO
 80  $$
 81      BEGIN
 82          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'table_name_id') THEN
 83              CREATE POLICY domain_only_policy ON table_name_id
 84                  USING (
 85                          domain = 'public'
 86                      OR domain = 'lake-public'
 87                      OR domain = current_user
 88                      OR domain IN (SELECT rolname
 89                                    FROM pg_roles
 90                                    WHERE pg_has_role(current_user, oid, 'member')));
 91          END IF;
 92      END
 93  $$;
 94  
 95  ALTER TABLE data_commit_info
 96      ENABLE ROW LEVEL SECURITY;
 97  CREATE INDEX CONCURRENTLY IF NOT EXISTS data_commit_info_domain_index ON data_commit_info (domain);
 98  DO
 99  $$
100      BEGIN
101          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'data_commit_info') THEN
102              CREATE POLICY domain_only_policy ON data_commit_info
103                  USING (
104                          domain = 'public'
105                      OR domain = 'lake-public'
106                      OR domain = current_user
107                      OR domain IN (SELECT rolname
108                                    FROM pg_roles
109                                    WHERE pg_has_role(current_user, oid, 'member')));
110          END IF;
111      END
112  $$;
113  
114  ALTER TABLE partition_info
115      ENABLE ROW LEVEL SECURITY;
116  CREATE INDEX CONCURRENTLY IF NOT EXISTS partition_info_domain_index ON partition_info (domain);
117  DO
118  $$
119      BEGIN
120          IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'partition_info') THEN
121              CREATE POLICY domain_only_policy ON partition_info
122                  USING (
123                          domain = 'public'
124                      OR domain = 'lake-public'
125                      OR domain = current_user
126                      OR domain IN (SELECT rolname
127                                    FROM pg_roles
128                                    WHERE pg_has_role(current_user, oid, 'member')));
129          END IF;
130      END
131  $$;
132  
133  
134  
135  -- create table if not exists casbin_rule
136  -- (
137  --     id    integer default nextval('casbin_sequence'::regclass) not null
138  --     primary key,
139  --     ptype varchar(100)                                         not null,
140  --     v0    varchar(100),
141  --     v1    varchar(100),
142  --     v2    varchar(100),
143  --     v3    varchar(100),
144  --     v4    varchar(100),
145  --     v5    varchar(100)
146  -- );
147  DO
148  $$
149      BEGIN
150          IF (select count(*) from information_schema.tables where table_name = 'casbin_rule') = 0
151          THEN
152              CREATE SEQUENCE IF NOT EXISTS CASBIN_SEQUENCE START 1;
153              CREATE TABLE IF NOT EXISTS casbin_rule
154              (
155                  id    int          NOT NULL PRIMARY KEY default nextval('CASBIN_SEQUENCE'::regclass),
156                  ptype VARCHAR(100) NOT NULL,
157                  v0    VARCHAR(100),
158                  v1    VARCHAR(100),
159                  v2    VARCHAR(100),
160                  v3    VARCHAR(100),
161                  v4    VARCHAR(100),
162                  v5    VARCHAR(100)
163              );
164              CREATE POLICY read_policy ON casbin_rule FOR SELECT
165                  USING (
166                      (ptype = 'g' AND v0 = current_user AND v1 IN (SELECT rolname
167                                                                    FROM pg_roles
168                                                                    WHERE pg_has_role(current_user, oid, 'member')))
169                      OR
170                      (ptype = 'p' AND v0 IN (SELECT rolname
171                                              FROM pg_roles
172                                              WHERE pg_has_role(current_user, oid, 'member'))));
173              CREATE POLICY update_policy ON casbin_rule FOR UPDATE
174                  USING (
175                      (ptype = 'g' AND v0 = current_user
176                          AND concat(v1, '_admins') IN (SELECT rolname
177                                                        FROM pg_roles
178                                                        WHERE pg_has_role(current_user, oid, 'member')))
179                      OR
180                      (ptype = 'p'
181                          AND concat(v0, '_admins') IN (SELECT rolname
182                                                        FROM pg_roles
183                                                        WHERE pg_has_role(current_user, oid, 'member'))));
184              CREATE POLICY insert_policy ON casbin_rule FOR INSERT
185                  WITH CHECK (
186                      (ptype = 'g' AND v0 = current_user
187                          AND concat(v1, '_admins') IN (SELECT rolname
188                                                        FROM pg_roles
189                                                        WHERE pg_has_role(current_user, oid, 'member')))
190                      OR
191                      (ptype = 'p'
192                          AND concat(v0, '_admins') IN (SELECT rolname
193                                                        FROM pg_roles
194                                                        WHERE pg_has_role(current_user, oid, 'member'))));
195              CREATE POLICY delete_policy ON casbin_rule FOR DELETE
196                  USING (
197                      (ptype = 'g' AND v0 = current_user
198                          AND concat(v1, '_admins') IN (SELECT rolname
199                                                        FROM pg_roles
200                                                        WHERE pg_has_role(current_user, oid, 'member')))
201                      OR
202                      (ptype = 'p'
203                          AND concat(v0, '_admins') IN (SELECT rolname
204                                                        FROM pg_roles
205                                                        WHERE pg_has_role(current_user, oid, 'member'))));
206          END IF;
207      END
208  $$;
209  
210  CREATE INDEX CONCURRENTLY IF NOT EXISTS casbin_rule_ptype_index ON casbin_rule (ptype);
211  CREATE INDEX CONCURRENTLY IF NOT EXISTS casbin_rule_v0_index ON casbin_rule (v0);
212  CREATE INDEX CONCURRENTLY IF NOT EXISTS casbin_rule_v1_index ON casbin_rule (v1);
213  
214  
215  
216  INSERT INTO global_config
217  VALUES ('lakesoul.authz.enabled', 'true')
218  ON CONFLICT (key) DO UPDATE SET value = 'true';
219  
220  INSERT INTO global_config
221  VALUES ('lakesoul.authz.casbin.model', '[request_definition]
222  r = sub, dom, obj, act
223  
224  [policy_definition]
225  p = sub, dom, obj, act
226  
227  [role_definition]
228  g = _, _, _
229  
230  [policy_effect]
231  e = some(where (p.eft == allow))
232  
233  [matchers]
234  m = g(r.sub, p.sub, r.dom) && r.dom == p.dom && r.obj == p.obj && r.act == p.act')
235  ON CONFLICT (key) DO UPDATE SET value = '[request_definition]
236  r = sub, dom, obj, act
237  
238  [policy_definition]
239  p = sub, dom, obj, act
240  
241  [role_definition]
242  g = _, _, _
243  
244  [policy_effect]
245  e = some(where (p.eft == allow))
246  
247  [matchers]
248  m = g(r.sub, p.sub, r.dom) && r.dom == p.dom && r.obj == p.obj && r.act == p.act';
249  
250  DO
251  $$
252      BEGIN
253          IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'base_user_role') THEN
254              CREATE ROLE base_super_admin_role WITH BYPASSRLS CREATEROLE;
255              CREATE ROLE base_admin_role;
256              CREATE ROLE base_user_role;
257          END IF;
258      END
259  $$;
260  
261  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO base_super_admin_role;
262  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO base_admin_role;
263  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO base_user_role;
264  
265  -- admins are not allowed to modify global_config table
266  REVOKE INSERT, UPDATE, DELETE ON TABLE global_config FROM base_admin_role;
267  
268  -- users are not allowed to modify global_config, namespace and casbin_rule table
269  REVOKE INSERT, UPDATE, DELETE ON TABLE namespace FROM base_user_role;
270  REVOKE INSERT, UPDATE, DELETE ON TABLE global_config FROM base_user_role;
271  REVOKE INSERT, UPDATE, DELETE ON TABLE casbin_rule FROM base_user_role;
272  
273  ALTER TABLE table_info ADD creator TEXT default current_user;
274  ALTER TABLE namespace ADD creator TEXT default current_user;