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

User · Answer

You should execute the next query   GRANT ALL ON TABLE mytable TO myuser    Or if your error is in a view then maybe the table does not have permission  so you should execute the next query   GRANT ALL ON TABLE tbm grupo TO myuser

User · Answer

Try to add  GRANT USAGE ON SCHEMA public to readonly    You probably were not aware that one needs to have the requisite permissions to a schema  in order to use objects in the schema

User · 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

User · Answer

make sure your user has attributes on its role  for example    postgres    du                              List of roles  Role name                     Attributes                     Member of  ----------- ------------------------------------------------ -----------  flux                                                             postgres    Superuser  Create role  Create DB  Replication        after performing the following command   postgres   ALTER ROLE flux WITH Superuser  ALTER ROLE postgres    du                              List of roles  Role name                     Attributes                     Member of  ----------- ------------------------------------------------ -----------  flux        Superuser                                           postgres    Superuser  Create role  Create DB  Replication        it fixed the problem   see tutorial for roles and stuff here  https   www digitalocean com community tutorials how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2

User · Answer

This worked for me  Check the current role you are logged into by using  SELECT CURRENT USER  SESSION USER  Note  It must match with Owner of the schema  Schema               Name               Type        Owner -------- -------- ------- ---------- If the owner is different  then give all the grants to the current user role from the admin role by   GRANT  ROLE OWNER  to  CURRENT ROLENAME   Then try to execute the query  it will give the output as it has access to all the relations now

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

Examples related to postgresql