So, the underlying problem that I'm having is trying to migrate my local instance of Postgres 10.5 to RDS. RDS DOES NOT give end users the
Super user paper so I'm having a lot of permissions problems when trying to import from a pg_dump.
For example, you have logged in as a user.
postgres – this is the user created when I created the RDS instance. What Amazon calls the "root user":
CREATE SCHEME f_site; GRANT EVERYTHING IN SCHEME f_site TO postgres; GRANT ALL PRIVILEGES IN SCHEMA f_site TO postgres; GRANT ALL THE PRIVILEGES IN ALL THE TABLES OF THE SCHEME f_site TO postgres; GRANT ALL THE PRIVILEGES IN ALL THE SEQUENCES IN SCHEME f_site TO postgres; GRANT ALL PRIVILEGES IN DATABASE mydb to postgres;
Those all put themselves without error. Now, the owner changes:
ALTER SCHEMA f_site OWNER TO schemaadmin;
Great, however, if I try to immediately execute another grant, just to test my permissions, I'm still registered as
postgres user, same session:
GRANT ALL PRIVILEGES IN SCHEMA f_site TO postgres; ERROR: permission denied for the f_site scheme
Basically I'm trying to create a pseudo-superuser & # 39; giving the
postgres the user grants all the grants to everything he creates, and then transfers ownership of that object to another role.
This is because the first lines in my pgdump backup are essentially the creation of schemas and the configuration of schema owners, and then (thousands of lines later) make changes / additions. Normally, with a superuser, this would not be a problem.
Thanks for any advice.