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.