Postgres Access Control
Jump to navigation
Jump to search
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; \dn
PG Bouncer
https://www.pgbouncer.org/usage.html
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