0

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? – Neil McGuigan Mar 25 '17 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 '17 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 '17 at 17:45
  • Did you run grant usage on schema $name to $role? – Neil McGuigan Mar 26 '17 at 19:02
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 at 2:39
1

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 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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