HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Why am I getting permission denied on an ownership change?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whypermissiondeniedgettingchangeownership

Problem

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?

Solution

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

Code Snippets

mydb=> CREATE ROLE change_owner LOGIN;
CREATE ROLE
mydb=> GRANT admin TO change_owner;
GRANT ROLE
mydb=> GRANT new_admin TO change_owner;
GRANT ROLE
$ psql -U change_owner mydb
mydb=>
mydb=> REASSIGN OWNED BY admin TO new_admin
REASSIGN OWNED

Context

StackExchange Database Administrators Q#212279, answer score: 9

Revisions (0)

No revisions yet.