Obvious reason Postgres Users can't read a table?

Question

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?

Answer 1

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;

Answer 2

I would try the following steps:

1. Can they connect to the database?
2. Do they have usage permission on the schema?
3. Do they have select permission on the table?
4. Are you using row security?
5. Are you using column security? If they only have select permission on column foo and you try select * that's an error
6. What is their search_path? If it's foo,public and they have access to public.mytable but not foo.mytable that's an error. Try the fully qualified name of the table ($schemaName.tableName)