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 ``` What I did wrong?

Accepted Answer

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.

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

Solution

Problem

Related problems