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

by

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.

More Related Contents:

  1. Why the “Group BY” by month didn’t work? [closed]
  2. Postgres: INSERT if does not exist already
  3. Truncating all tables in a Postgres database
  4. How can I drop all the tables in a PostgreSQL database?
  5. How to change PostgreSQL user password?
  6. How to get the count of current month Sunday’s in psql?
  7. quick random row selection in Postgres
  8. Restore a postgres backup file using the command line?
  9. Running PostgreSQL in memory only
  10. How to close idle connections in PostgreSQL automatically?
  11. Refresh a materialized view automatically using a rule or notify
  12. postgresql – replace all instances of a string within text field
  13. Is it possible to have autoincrement number without it being an ID?
  14. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  15. How do I login and authenticate to Postgresql after a fresh install?
  16. Postgres query optimization (forcing an index scan)
  17. psql: FATAL: database “” does not exist
  18. Difference between timestamps with/without time zone in PostgreSQL
  19. Omitting the double quote to do query on PostgreSQL
  20. Storing Images in PostgreSQL
  21. Psql could not connect to server: No such file or directory, 5432 error?
  22. Strange behaviour in Postgresql
  23. In PostgreSQL, how to insert data with COPY command?
  24. Cursor based records in PostgreSQL
  25. How to add column if not exists on PostgreSQL?
  26. Maximum characters in labels (table names, columns etc)
  27. Docker – Postgres and pgAdmin 4 : Connection refused
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment