background
In multi-tenant scenarios or other scenarios, it is often necessary to actively clean up some users. This article will introduce how to quickly delete a user (role) under PostgreSQL.
Specific methods
Generally, you can delete this user by directly executing drop role xxx; But many times, errors will be reported because the user has dependencies.
Permission dependency
postgres=# create role test with login; CREATE ROLE postgres=# grant all on database postgres to test; GRANT postgres=# drop role test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: privileges for database postgres
It can be seen that because we have given the permissions of the database postgres to the test user, an error will be reported when deleting it directly. Faced with this situation, we need to first revoke all permissions of role, as follows:
postgres=# revoke all on database postgres from test; REVOKE postgres=# drop role test; DROP ROLE
Note: All permissions of all database objects (tables, views, SEQUENCE) in which the user has permissions in all databases must be recycled before the user can be deleted.
Object dependency
postgres=# create role test with login; CREATE ROLE postgres=# \c - test You are now connected to database "postgres" as user "test". postgres=> create table test (id int); CREATE TABLE postgres=# \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# drop role test; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: owner of table test
It can be seen that because the test user is the owner of the test table, an error owner of table test was reported when deleting. If you do not need to retain the object, you need to delete the dependent object first. If you need to keep the object, you should assign owner to someone else before deleting it, as follows:
postgres=# alter table test OWNER TO postgres; ALTER TABLE postgres=# drop role test; DROP ROLE
Note: You need to delete all database objects (tables, views, SEQUENCE) that have owner permissions in all databases or execute alter xx owner to in order to delete the user.
A more awesome method
If the owner's database object is not preserved
postgres=# REASSIGN OWNED BY test TO postgres; REASSIGN OWNED postgres=# DROP OWNED BY test; DROP OWNED postgres=# drop role test; DROP ROLE
If the owner's database object is retained
postgres=# REASSIGN OWNED BY test TO postgres; REASSIGN OWNED postgres=# drop role test; DROP ROLE
Note: REASSIGN OWNED requires that the role (or subset) to which the executor belongs must contain test and postgres or superuser. In addition, the above statement must be executed on all involved databases to delete the user.
Supplement: PostgreSQL database creation/deletion
Method 1 - System Commands
sudo su - postgres #Switch to postgres user (system user)createdb weichen #Create a databasepsql #Direct access to the database (by default, enter the local postgres database)\l --View the database list :q --Exit list page \q --Exit the client dropdb weichen #Delete the database
Method 2 - psql command line
sudo -u postgres psql #Login clientcreate database weichen; --Create a database create database sz owner postgres; --Create a database select oid,datname from pg_database; --View the database list drop database weichen; --Delete the database drop database sz; --Delete the database
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.