Postgres 10.5: Granting full schema access to a user that isn't the owner

Question

So the underlying problem I'm having is trying to migrate my on premise Postgres 10.5 instance to RDS. RDS does NOT give end users the SuperUser role so I'm hitting tons of permissions issues when trying to import from a pg_dump.

For example, logged in as user postgres - this is the user created when I created the RDS instance. What amazon calls the "root user":

CREATE SCHEMA f_site;
GRANT ALL ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA f_site TO postgres;
GRANT ALL PRIVILEGES ON DATABASE mydb to postgres;

Those all get set without error. Now, the owner changes:

ALTER SCHEMA f_site OWNER TO schemaadmin;

Great, however if I try to immediately run another grant, just to test my permissions, still logged in as postgres user, same session:

GRANT ALL PRIVILEGES ON SCHEMA f_site TO postgres;
ERROR:  permission denied for schema f_site

Basically I am trying to create a 'pseudo-superuser' by giving the postgres user full grants on everything it creates, then transfering ownership of that object to another role.

This is because the very first lines in my pgdump backup are essentially creating schema and setting schema owners, then later (thousands of lines later) making changes/additions to it. Normally with a superuser this wouldn't be a problem.

Thanks for any advice.

Answer 1

Ok figured this out after doing a few more searches.

Essentially you need to grant same role that eventually will own the object to the user you're using.

So in my case: postgres is the user that I want to be my "pseudo-superuser" and schemaadmin is the user role that will own the objects.

GRANT schemaadmin TO postgres;

After that, everything that schemaadmin owns, postgres can modify.

Answer 2

You need to grant schema usage to user.

GRANT USAGE ON SCHEMA "schema_name" TO role;