Reassign ownership and drop old owner in PostgreSQL

All we need is an easy explanation of the problem, so here it is.

I have got a PSQL 13.6 database that was originally created by owner_1, and has been populated over time. For external reasons, I now need to periodically change the owner account of the database and drop the old user accounts. When I try to achieve this I get blocked because there are objects that depend on it.

I do not want to risk loosing privileges on the manager_role or any cascading dependencies there might be.
How can I find and transfer the objects that are still owned by owner_1?

MWE:

  1. As postgres user: Create owner_1.
CREATE USER owner_1 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_1';
  1. As owner_1: Set up the database and objects.
CREATE DATABASE test_db;
\c test_db
CREATE ROLE manager_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO manager_role;
CREATE USER manager_1 WITH ENCRYPTED PASSWORD 'manager_password_1' IN ROLE manager_role;
  1. As owner_1: Create an owner_role, transfer ownership, and create new user in that role.
CREATE ROLE owner_role WITH NOLOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
GRANT USAGE, CREATE ON SCHEMA public TO owner_role;
GRANT owner_role TO "owner_1";
REASSIGN OWNED BY owner_1 TO owner_role;

CREATE USER owner_2 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_2' IN ROLE owner_role;

You should now have these user definitions

\du
                                       List of roles
  Role name   |                         Attributes                         |   Member of    
--------------+------------------------------------------------------------+----------------
 manager_1    |                                                            | {manager_role}
 manager_role | Cannot login                                               | {}
 owner_1      | Create role, Create DB                                     | {owner_role}
 owner_2      | Create role, Create DB                                     | {owner_role}
 owner_role   | Create role, Create DB, Cannot login                       | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  1. As owner_2: Drop the old user.
DROP USER owner_1;

This last command will fail with the error:

ERROR:  role "owner_1" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role owner_1 in schema public

EDIT
Executing DROP OWNED BY owner_1; from the owner_2 user fails with an error:

test_db=> DROP OWNED BY owner_1;
ERROR:  permission denied to drop objects 

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You have two options:

  • explicitly remove the default privileges:

    ALTER DEFAULT PRIVILEGES FOR ROLE owner_1 IN SCHEMA public
       REVOKE ALL ON TABLES FROM manager_role;
    
  • remove all objects and privileges the role owns:

    DROP OWNED BY owner_1;
    

Method 2

I have found a workaround, which will result in the desired state.
Instead of creating the owner_role, granting it to owner_1, reassigning the ownership etc. I can convert owner_1 into owner_role which means I no longer have to drop owner_1 and can still create a new owner user which can then be changed periodically.

MWE:

Perform step 1 and 2 as above.

  1. As owner_1: Create a new user in the owner_1 role
CREATE USER owner_2 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_2' IN ROLE owner_role;
  1. As owner_2: Convert owner_1 into owner_2
ALTER ROLE owner_1 RENAME TO owner_role;
ALTER ROLE owner_role WITH NOLOGIN;

Now owner_2 can be used to create owner_3 when the account needs to be changed next and as long as owner_2 always runs commands as owner_role (SET ROLE owner_role;) there should be no trouble dropping owner_2.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply