1

I have been googling this for a few months now and I am confounded why I have not been able to get a solid answer on this.

Goal: Grant several roles SELECT, INSERT, UPDATE, DELETE permissions.

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

I run this SQL script with no errors returned, but when I attempt to run a simple

SELECT * FROM schema1.new_table_name

I get a permission denied for relation returned.

Do you have any rules of thumb or recommended "further reading" for how to handle and understand Postgres permission and hierarchy setup for users?

I setup a role hierarchy that doesn't seem to be respected and now GRANT scripts don't seem to be working either

1

Ok, so my broader question still stands (and will likely build that out in its own forum post) but for now, I'll answer the immediate problem here.

My GRANT query was syntactically incorrect and that was producing the error.

My query:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1.new_table_name" TO "role1";

Should really be:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

(I was missing closing Quote marks after the schema name, and opening quote marks before the table name)

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.