PostgreSQL Readonly User

Rational

When you create a user in postgresql, even with only select only permission, your user has a lot more privileges.

Solution

Connect with root user to the desired database you will be giving permissions for:
mydb=# psql -h 10.10.10.10 -U postgres mydb
Now lets create the user:
mydb=# create user readonly with encrypted password 'readonly';
Revoke everything so we can explicitly allow what we need:
mydb=# revoke all on schema public from readonly;

Grant only what you need.

First give connection privilege to the actual database:
mydb=# GRANT CONNECT ON DATABASE mydb TO readonly;

Then grant usage and select:
mydb=# GRANT USAGE,SELECT ON SCHEMA public TO readonly;
mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
If you want to grant access to the new tables in the future automatically, you have to alter default:
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

Fin

Thats all!

Fuente: parask.me/blog/postgresql-readonly-user


Posted

in

by

Tags: