patternsqlMinor
Why am I getting permission denied on an ownership change?
Viewed 0 times
whypermissiondeniedgettingchangeownership
Problem
My current user is
This user a standard super user created on AWS RDS.
But I'm getting this:
Why? How can I resolve it?
prontomediapop_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 objectsWhy? 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
1.Create a new role:
2.Make both the old owner and the new owner roles members of the newly created role:
3.Logout from psql and login using the new role:
4.Execute the reassignment:
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 ROLE2.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 ROLE3.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 OWNEDCode Snippets
mydb=> CREATE ROLE change_owner LOGIN;
CREATE ROLEmydb=> 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 OWNEDContext
StackExchange Database Administrators Q#212279, answer score: 9
Revisions (0)
No revisions yet.