Why am I getting permission denied on an ownership change?

Question

My current user is pronto

mediapop_staging=> select current_user;
 current_user
--------------
 pronto
(1 row)

This user a standard super user created on AWS RDS.

mediapop_staging=> \du pronto
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 pronto    | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

But I'm getting this:

mediapop_staging=> REASSIGN OWNED BY pronto TO mediapop_staging;
ERROR:  permission denied to reassign objects

Why? How can I resolve it?

Answer 1

Thanks to @Spike for comment, for solution which worked for me (source)

This assumes that objects are reassigned from role admin to role new_admin.

1.Create a new role:

mydb=> CREATE ROLE change_owner LOGIN;
CREATE ROLE

2.Make both the old owner and the new owner roles members of the newly created role:

mydb=> GRANT admin TO change_owner;
GRANT ROLE
mydb=> GRANT new_admin TO change_owner;
GRANT ROLE

3.Logout from psql and login using the new role:

$ psql -U change_owner mydb
mydb=>

4.Execute the reassignment:

mydb=> REASSIGN OWNED BY admin TO new_admin
REASSIGN OWNED

Answer 2

A variation of the great answer by @OtoShavadze:

mydb=> GRANT admin TO <current_user>;
GRANT ROLE
mydb=> GRANT new_admin TO <current_user>;
GRANT ROLE
mydb=> REASSIGN OWNED BY admin TO new_admin;
REASSIGN OWNED

This removes the need to logout and back in as a different role.

Then you can clean up with:

mydb=> REVOKE admin FROM <current_user>;
REVOKE ROLE
mydb=> REVOKE new_admin FROM <current_user>;
REVOKE ROLE

Answer 3

There are some unintuitive permissions requirements when using REASSIGN without a superuser. See this other post on SO where I answered the same question.

Answer 4

There are 2 ways to run REASSIGN OWNED and DROP OWNED. The 1st way is with a superuser and the 2nd way is with a normal user.

<The 1st way>:

For example, first, log in to apple database with any superuser e.g., postgres. *My answer explains how to create a superuser. and my answer explains how to make a user a superuser:

psql -U postgres apple

Finally, you can run REASSIGN OWNED and DROP OWNED as shown below:

REASSIGN OWNED BY david TO peter

Or:

REASSIGN OWNED BY peter TO david

Or:

DROP OWNED BY david;

Or:

DROP OWNED BY peter;

<The 2nd way>:

For example, first, log in to any database e.g., postgres database with any superuser e.g., postgres as shown below:

psql -U postgres

Then, create the user john as shown below:

CREATE ROLE john WITH LOGIN PASSWORD 'banana';

Then, grant the users david and peter to the user john, exit as shown below:

GRANT david TO john;
GRANT peter TO john;
\q

Then, log in to apple database with the user john as shown below:

psql -U john apple

Finally, you can run REASSIGN OWNED and DROP OWNED as shown below:

REASSIGN OWNED BY david TO peter

Or:

REASSIGN OWNED BY peter TO david

Or:

DROP OWNED BY david;

Or:

DROP OWNED BY peter;