Friday, July 24, 2020

Create readonly user in Postgres


CREATE USER testuser WITH PASSWORD 'somepassword';
GRANT CONNECT ON DATABASE dbtest TO testuser;

//remove all permission, only superuser can perform db actions
REVOKE ALL ON schema public FROM public;
//remove all table permission from user
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM testuser;
//grant select permission to user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO testuser;