0

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 
2
  • Seen this?
    – mustaccio
    Commented Jul 13, 2022 at 16:24
  • @mustaccio Yes I have but I need the altered privileges for other roles that have been assigned by owner_1 to still exist after owner_1 is dropped. I.e. I want to ensure that owner_role is the owner of any altered privileges or other objects that are blocking the user dropping.
    – Kajsa
    Commented Jul 14, 2022 at 9:47

2 Answers 2

0

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;
    
4
  • But I need the manager_role to have those default privileges. And I also don't know exactly what other objects are affected. This is just an MVE, the original system is managed by alembic and has gone through multiple revisions.
    – Kajsa
    Commented Jul 14, 2022 at 8:15
  • But those default privileges only apply to tables created by owner_1. If you need them, you cannot get rid of the role. Commented Jul 14, 2022 at 10:37
  • So the tables have been reassigned ownership to owner_role. Even if I drop owner_1 the tables will still exist as they are now owned by owner_role.
    – Kajsa
    Commented Jul 14, 2022 at 13:20
  • Right.Then you don't need the default privileges for owner_1 any more and can safely do what I suggest. Commented Jul 14, 2022 at 15:27
0

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.

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.