postgresql - ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

Question

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

The readonly user can connect, see the tables but when it tries to do a simple select it gets:

ERROR: permission denied for relation mytable
SQL state: 42501

This is happening on PostgreSQL 9.1

What I did wrong?

Answer 1

Here is the complete solution for PostgreSQL 9+, updated recently.

CREATE USER readonly  WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repeat code below for each database:

GRANT CONNECT ON DATABASE foo to readonly;
c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly; 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Thanks to https://jamie.curle.io/creating-a-read-only-user-in-postgres/ for several important aspects

If anyone find shorter code, and preferably one that is able to perform this for all existing databases, extra kudos.