Postgres Access Control

Setup test database and tables

CREATE DATABASE test_database;
\c test_database

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;

Users

unode=# create role someuser;
CREATE ROLE
unode=# create role someadmin;
CREATE ROLE
unode=# ALTER ROLE someadmin WITH PASSWORD 'test';
ALTER ROLE
unode=# ALTER ROLE someuser WITH PASSWORD 'test';
ALTER ROLE
unode=# ALTER ROLE someuser WITH login;
ALTER ROLE
unode=# ALTER ROLE someadmin WITH login SUPERUSER;
ALTER ROLE

Refs

• https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql
• https://www.techonthenet.com/postgresql/grant_revoke.php
• https://chartio.com/resources/tutorials/how-to-change-a-user-to-superuser-in-postgresql/