PostgreSQL運維—DROP ROLE

語法

DROP ROLE — remove a database role

DROP ROLE [ IF EXISTS ] name [, ...]

描述

DROP ROLE removes the specified role(s). To drop a superuser role, you must be a superuser yourself; to drop non-superuser roles, you must have CREATEROLE privilege.

postgres=# create user u1 with superuser;
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# create user u3;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 lxs       | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        | Superuser                                                  | {}
 u2        |                                                            | {}
 u3        |                                                            | {}

postgres=# \c - u2
You are now connected to database "postgres" as user "u2".
postgres=> drop user u1;
ERROR:  permission denied to drop role
postgres=> drop user u3;
ERROR:  permission denied to drop role
postgres=# alter user u2 createrole;
ALTER ROLE
postgres=# drop user u3;
DROP ROLE
postgres=# alter user u2 superuser;
ALTER ROLE
postgres=# drop user u1;
DROP ROLE
postgres=# \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop user u2;
DROP ROLE
postgres=# 

A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so.

postgres=# create user u1;
CREATE ROLE
postgres=# create table t1(id int);
CREATE TABLE
postgres=# grant select on t1 to u1;
GRANT
postgres=# drop user u1;
ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table t1
postgres=# 

Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the role has been granted on other objects. The REASSIGN OWNED and DROP OWNED commands can be useful for this purpose;

postgres=# \du 
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 lxs       | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        |                                                            | {}

postgres=# reassign owned by u1 to lxs;
REASSIGN OWNED
postgres=# reassign owned by u1 to lxs;
REASSIGN OWNED
postgres=# drop owned by u1;
DROP OWNED
postgres=# drop user u1;
DROP ROLE
postgres=# 

However, it is not necessary to remove role memberships involving the role; DROP ROLE automatically revokes any memberships of the target role in other roles, and of other roles in the target role. The other roles are not dropped nor otherwise affected.

參數

• IF EXISTS

  Do not throw an error if the role does not exist. A notice is issued in this case.

  postgres=# drop user if;
  ERROR:  role "if" does not exist
  postgres=# drop user if exists u1;
  NOTICE:  role "u1" does not exist, skipping
  DROP ROLE
  postgres=# 
  

• name

  The name of the role to remove.

  postgres=# create user u1;
  CREATE ROLE
  postgres=# drop user u1;
  DROP ROLE
  postgres=# 
  

注解

PostgreSQL includes a program dropuserthat has the same functionality as this command (in fact, it calls this command) but can be run from the command shell.

postgres=# create user u1;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 lxs       | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        |                                                            | {}

postgres=# \q
[postgres@lyp ~]$ dropuser u1;
[postgres@lyp ~]$ psql
psql (14.1)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 lxs       | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# 

兼容性

The SQL standard defines DROP ROLE, but it allows only one role to be dropped at a time, and it specifies different privilege requirements than PostgreSQL uses.

SQL 標準定義了DROP ROLE， 但是它只允許一次刪除一個角色并且它指定了和 PostgreSQL不同的特權需求。