Postgresql – Postgres 10.5: Granting full schema access to a user that isn’t the owner

amazon-rdspermissionspostgresql

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.

Best Answer

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.

Related Solutions

PostgreSQL – Grant Access to All Tables for a User

First, you have to be able to connect to the database in order to run queries. This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

The REVOKE is necessary because

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

As Jack Douglas pointed out, the above only gives the privileges for the already existing tables. To achieve the same for future tables, you have to define default privileges:

ALTER DEFAULT PRIVILEGES 
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Here, some_role is a role that creates the tables, while user_name is the one who gets the privileges. Defining this, you have to be logged in as some_role or a member of it.

And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) - here it is the USAGE privilege that you need.

PostgreSQL – Why Schema Owner Differs from Database Owner

A new database is created by cloning an existing one. If you don't specify which one to clone, PostgreSQL clones the hidden-by-default database template1. All objects in template1 are owned by user postgres.

So you're not the owner of any of the objects in the database at the time it's cloned from template0 or template1; you only own the database its self.

New objects are created with your ownership because you created them. Existing objects retain their existing ownership.

See \dn * in psql to see the namespaces defined, \dp *.* to see table/view/sequence privileges, \df+ * to see function definitions with owners, \dT * to see data types, etc. You'll see that in a new blank DB they're all owned by user postgres.

Best Answer

Related Solutions

PostgreSQL – Grant Access to All Tables for a User

PostgreSQL – Why Schema Owner Differs from Database Owner

Related Question