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!