PostgreSQL – Why Grant Update on Sequence

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)

GRANT'ing SELECT (or USAGE) on the sequence is not sufficient if it's contained in a schema for which the user has no permission. I believe that's the case because your schema named public is not public. If it was, it would have permissions that would look like that:

test=> \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 

as opposed to the lack of access privileges shown in the question. This is also consistent with the fact that the same commands work in you other instances: presumably the schema public of these other databases is the original, not a dropped/recreated-differently version or with its permissions removed.

As possible solutions, consider doing, as the owner of the schema:

 GRANT ALL ON SCHEMA public TO public;

or the more limited

 GRANT ALL ON SCHEMA public TO new_role;

or the even more limited

 GRANT USAGE ON SCHEMA public TO new_role;