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?

  • 1
    Can they connect to the database? Can they use other objects in the schema? What is their search_path? Mar 25, 2017 at 6:48
  • @NeilMcGuigan right now I limited it "select" for one specific object in a schema as a test. Where can I find where the role's search_path is listed?
    – GPP
    Mar 25, 2017 at 17:43
  • @NeilMcGuigan and to answer your earlier question, yes the user can connect to the database, but they cannot read from any objects in the schema, even the object specific granted access to the Role the user inherits from
    – GPP
    Mar 26, 2017 at 17:45
  • Did you run grant usage on schema $name to $role? Mar 26, 2017 at 19:02

2 Answers 2

  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;
  • Thanks so much for the suggestion! I think what we ended up learning is that the database tables do not inherit from the existing Postgres roles & permission schema, so we ended up creating a SQL trigger with these GRANTs upon a table's creation stackoverflow.com/questions/19309416/…
    – GPP
    May 6, 2019 at 2:39
  • thanks I'd forgotten to do the GRANT USAGE ON SCHEMA Aug 16, 2022 at 14:09

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)
  • Thanks so much for the suggestion! I meant to respond to this a while back. While this was a great pointer, that didn't resolve the issue. Our workaround was creating a SQL trigger that ensured the table was shared with all other postgres roles upon table creation, similar to this suggestion here: stackoverflow.com/questions/19309416/…
    – GPP
    May 6, 2019 at 2:38
  • 1
    It would be nice if someone could publish the queries to get the answers to these questions.
    – Bart Jonk
    Aug 18, 2021 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.