Reassigning objects and dropping users

After assigning permissions and restricting access, it can happen that users will be dropped from the system. Unsurprisingly, the commands to do that are the DROP ROLE and DROP USER commands:

test=# h DROP ROLE
Command: DROP ROLE
Description: remove a database role
Syntax:
DROP ROLE [ IF EXISTS ] name [, ...]

URL: https://www.postgresql.org/docs/12/sql-droprole.html

Let's give it a try. The following listing shows how this works:

test=# DROP ROLE joe; 
ERROR: role "joe" cannot be dropped because some objects depend on it DETAIL: target of policy joe_pol_3 on table t_person target of policy joe_pol_2 on table t_person target of policy joe_pol_1 on table t_person privileges for table t_person owner of table t_user owner of sequence t_user_id_seq owner of default privileges on new relations belonging to role joe in schema public owner of table t_useful

PostgreSQL will issue error messages, because a user can only be removed if everything has been taken away from them. This makes sense for the following reason: just suppose that somebody owns a table. What should PostgreSQL do with that table? Somebody has to own them.

To reassign tables from one user to the next, consider taking a look at the REASSIGN clause:

test=# h REASSIGN 
Command: REASSIGN OWNED
Description: change the ownership of database objects owned by a database role
Syntax:
REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
TO { new_role | CURRENT_USER | SESSION_USER }

URL: https://www.postgresql.org/docs/12/sql-reassign-owned.html

The syntax is, again, quite simple, and helps to simplify the process of handing over. Here is an example:

test=# REASSIGN OWNED  BY joe TO postgres;  
REASSIGN OWNED 

So, let's try to drop the joe role again:

test=# DROP ROLE joe; 
ERROR:  role "joe" cannot be dropped because some objects depend on it 
DETAIL:  target of policy joe_pol_3 on table t_person target of policy joe_pol_2 on table t_person 
target of policy joe_pol_1 on table t_person privileges for table t_person 
owner of default privileges on new relations belonging to role joe in schema public 

As we can see, the list of problems has been reduced significantly. What we can do now is resolve all of those problems one after the other, and then drop the role. There is no shortcut that I am aware of. The only way to make this more efficient is to make sure that as few permissions as possible are assigned to real people. Try to abstract as much as you can into roles, which, in turn, can be used by many people. If individual permissions are not assigned to real people, things tend to be easier in general.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset