11

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?

2
  • 4
    rds_superuser is not the same thing as a PostgreSQL superuser. This is fundamentally a RDS question, not a plain PostgreSQL question, so you should tag it appropriately. People who only know the community version of PostgreSQL probably can't help you.
    – jjanes
    Jul 16, 2018 at 13:29
  • 3
    It worked for me. sysadmintips.com/services/databases/…
    – RBB
    Jul 9, 2019 at 10:02

4 Answers 4

9

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
2
  • Nice. Was necessary to specify a password for the role; e.g. ALTER ROLE change_owner WITH PASSWORD 'change'; Applied to RDS 10.14 serverless.
    – bvj
    Dec 11, 2021 at 5:25
  • BTW, the solution solved the dilemma of transferring ownership where the login account is also the owner.
    – bvj
    Dec 11, 2021 at 7:30
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
1

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

2
  • 1
    I get this error even while logged in as the postgres superuser...
    – Cerin
    Aug 18, 2021 at 20:59
  • 3
    @Cerin the most likely explanation to this is, that you are using Postgres on a Cloud provider, for example AWS RDS. They do not grant the actual "superuser" privileges to the users they create, but instead, a trimmed down "rds_superuser" role that can do most, but not all of the actual Postgres "superuser".
    – Overbryd
    Feb 28, 2022 at 9:11
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.