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
Best Answer
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)