Postgres Access Control - Revision history

Busk at 13:48, 30 April 2023

2023-04-30T13:48:12Z

2023-04-30T13:46:39Z

2021-01-17T01:54:06Z

2021-01-17T00:27:56Z

2021-01-17T00:26:41Z

2021-01-16T23:44:56Z

2021-01-16T23:21:25Z

2021-01-16T14:46:23Z

Created page with "https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql"

New page

https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql

@@ Line 1: / Line 1: @@
 # Guide
 - https://satoricyber.com/postgres-security/postgres-row-level-security/
 # Setup test database and tables

← Older revision		Revision as of 13:46, 30 April 2023
Line 1:		Line 1:
		+	# Guide
		+	- https://satoricyber.com/postgres-security/postgres-row-level-security/
		+
	# Setup test database and tables		# Setup test database and tables
	```		```

@@ Line 41: / Line 41: @@
 ALTER USER postgres SET search_path = tester, public;
 SELECT * from information_schema.schemata;
 ```

@@ Line 42: / Line 42: @@
 SELECT * from information_schema.schemata;
 ```
 # Users

@@ Line 41: / Line 41: @@
 ALTER USER postgres SET search_path = tester, public;
 SELECT * from information_schema.schemata;
 ```
@@ Line 46: / Line 62: @@
 - https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
 - https://www.techonthenet.com/postgresql/grant_revoke.php

← Older revision		Revision as of 23:44, 16 January 2021
Line 1:		Line 1:
−	~~https://www.enterprisedb.com/postgres-tutorials/how-implement-column-~~and~~-row-level-security-postgresql~~	+	# Setup test database and tables
		+	```
		+	CREATE DATABASE test_database;
		+	\c test_database

−	https://www.techonthenet.com/postgresql/grant_revoke.php	+	CREATE TABLE contacts(
		+	name TEXT NOT NULL,
		+	rls_role text DEFAULT current_user
		+	);
		+	create role joe1;
		+	create role joe2;
		+	GRANT SELECT, INSERT, UPDATE, DELETE ON contacts to joe1,joe2;
		+	ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
		+	CREATE POLICY public_contacts_rls_policy ON public.contacts USING (rls_role=current_user);
		+
		+
		+
		+	set role joe1
		+	insert into contacts (name) values ('joe1 1');
		+	insert into contacts (name) values ('joe1 2');
		+	select * from contacts;
		+
		+	set role joe2
		+	insert into contacts (name) values ('joe2 1');
		+	insert into contacts (name) values ('joe2 2');
		+	select * from contacts;
		+	update contacts set name = 'joe2 1 updated' where name ='joe2 1'
		+	delete * from contacts;
		+	```
		+
		+	# Disable temporarily
		+	```
		+	ALTER TABLE contacts DISABLE ROW LEVEL SECURITY;
		+	... do stuff
		+	ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
		+	```
		+
		+	# Use different schema then default public
		+	```
		+	CREATE SCHEMA test_schema;
		+	ALTER USER postgres SET search_path = tester, public;
		+	SELECT * from information_schema.schemata;
		+	```
		+
		+	# Refs
		+	- https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
		+	- https://www.techonthenet.com/postgresql/grant_revoke.php

← Older revision		Revision as of 23:21, 16 January 2021
Line 1:		Line 1:
	https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql		https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
		+
		+	https://www.techonthenet.com/postgresql/grant_revoke.php