Obvious reason Postgres Users can’t read a table

permissionsread-only-databaserole

Situation: a PG user (non superuser, inherits from parent role) that is a member of a Role/Group cannot read from specific tables even though these Object Privileges have been specified:

  • DBName – Connect
  • SELECT – true
  • INSERT – true
  • Delete – true
  • UPDATE – true

I can't figure out what the tablename_tablename_id_seq object does– there is a 1:1 relationship between all my DB's tables and sequences but not sure how this impacts permissioning.

I tried clicking the checkbox to True (In Navicat) for "Usage" but the user still cannot read from the specified table.

I have attempted to edit these permissions in PG Admin 3&4, Navicat, and Postico…any idea where I am getting stuck?

Best Answer

  1. Connect to the respective database by \c DATABASE_NAME
  2. It should have the Connect permission: GRANT CONNECT ON DATABASE DATABASE_NAME TO USER_NAME;
  3. Grant the permission to use the schema GRANT USAGE ON SCHEMA public TO USER_NAME;
  4. Grant permission to select on all the tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER_NAME;