openGauss每日一练第19天 | openGauss逻辑结构：用户和角色管理

使用create user创建的用户与使用create role创建的用户的区别在于，前者可以直接连接登录数据库，而使用create role创建的用户不能直接登录到数据库。必须添加LOGIN权限后，才能登录到数据库管理系统。
删除用户，首先需要将用户拥有的数据库对象转移或者删除。

1、创建test10_tbs的表空间，在这个表空间中创建数据库testdb10

su - omm
gsql -r

--创建test10_tbs的表空间
omm=# create tablespace test10_tbs relative location 'tablespace/test10_tbs1';
CREATE TABLESPACE

--创建数据库testdb10，并指定数据库表空间是test10_tbs
omm=# create database testdb10 with tablespace test10_tbs;
CREATE DATABASE

2、使用create user创建用户user10，登录数据库testdb10，创建测试表t1和t2

--创建用户user10
omm=# create user user10 identified by 'Kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

--授予user10用户sysadmin角色
omm=# alter user user10 sysadmin;
ALTER ROLE

或者把数据库testdb10权限授予给用户user10
omm=# grant all on database testdb10 to user10;
GRANT

--使用user10连接数据库testdb10
omm=# \c testdb10 user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "user10".

--查看当前连接会话所处位置
testdb10=> select current_user,current_database(),current_schema;
 current_user | current_database | current_schema 
--------------+------------------+----------------
 user10       | testdb10         | public
(1 row)

testdb10=> create table t1(id int,ename varchar(50));
CREATE TABLE
testdb10=> create table t2(id int,ename varchar(50));
CREATE TABLE
testdb10=> 

testdb10=> \d t1
             Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 ename  | character varying(50) | 

testdb10=> \d t2
             Table "public.t2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 ename  | character varying(50) | 

3、使用create role创建角色role10，登录数据库testdb10

testdb10=> create role role10 identified by 'Kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

testdb10=> grant all on database testdb10 to role10;
GRANT

--这里可以看到create user 和 create role的区别了，使用create role创建的用户，是不允许登录的
omm@modb:~$ gsql -d testdb10 -U role10 -W 'Kunpeng@1234' -r
gsql: FATAL:  role "role10" is not permitted to login
omm@modb:~$ 

--查看role10，可以看到role10不能够登录的
omm=# \du role10
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 role10    | Cannot login | {}

4、将表t1直接删除，将前面创建的表空间和数据库、表t2转给role10，删除用户user10

omm=# alter user role10 login;
ALTER ROLE

omm=# \du role10
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 role10    |            | {}

omm=# \q

--授予role10登录权限后，role10是可以连接数据库登录了
omm@modb:~$ gsql -d testdb10 -U role10 -W 'Kunpeng@1234' -r 
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


gsql -r

omm=# \c testdb10  
testdb10=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "omm".

testdb10=# \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user10 | {orientation=row,compression=no}
 public | t2   | table | user10 | {orientation=row,compression=no}
(2 rows)

--user10包含对象，不能删除，需要先删除user10下对象，再来删除用户user10
testdb10=# drop user user10;
ERROR:  role "user10" cannot be dropped because some objects depend on it
DETAIL:  owner of table t2
owner of table t1
privileges for database testdb10
1 object in database omm

--删除表t1
testdb10=# drop table t1;
DROP TABLE
testdb10=# 

--把表t2的owner修改成role10
testdb10=# alter table t2 owner to role10;
ALTER TABLE

testdb10=# \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role10 | {orientation=row,compression=no}
(1 row)


--通过\l+查看是否有user10相关
testdb10=# \l+
                                                          List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace |   
             Description                 
-----------+-------+----------+---------+-------+-------------------+-------+------------+---
-----------------------------------------
 omm       | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | 
 postgres  | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | de
fault administrative connection database
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | de
fault template for new databases
           |       |          |         |       | omm=CTc/omm       |       |            | 
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | un
modifiable empty database
           |       |          |         |       | omm=CTc/omm       |       |            | 
 testdb10  | omm   | UTF8     | C       | C     | =Tc/omm          +| 12 MB | test10_tbs | 
           |       |          |         |       | user10=APm/omm   +|       |            | 
           |       |          |         |       | role10=CTc/omm   +|       |            | 
           |       |          |         |       | role10=APm/omm    |       |            | 
(5 rows)

           |       |          |         |       | omm=CTc/omm      +|       |            | 
           |       |          |         |       | user10=CTc/omm   +|       |            | 

--把user10权限收回
testdb10=# revoke all on database testdb10 from user10;
REVOKE

----把user10权限收回
testdb10=# revoke all on database omm from user10;
REVOKE

--退出连接testdb10数据库，登录到omm数据库
testdb10=# \c omm
omm=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".

--删除user10用户，发现可以删除了
omm=# drop user user10;
DROP ROLE

5、最后删除role10

--同理要删除role10，需要把涉及role10相关的都要先移除，再来删除role10才可以
omm=# drop user role10;
ERROR:  role "role10" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb10
1 object in database testdb10
omm=# 
omm=# \l+
                                                          List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace |   
             Description                 
-----------+-------+----------+---------+-------+-------------------+-------+------------+---
-----------------------------------------
 omm       | omm   | UTF8     | C       | C     | =Tc/omm          +| 12 MB | pg_default | 
           |       |          |         |       | omm=CTc/omm       |       |            | 
 postgres  | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | de
fault administrative connection database
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | de
fault template for new databases
           |       |          |         |       | omm=CTc/omm       |       |            | 
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | un
modifiable empty database
           |       |          |         |       | omm=CTc/omm       |       |            | 
 testdb10  | omm   | UTF8     | C       | C     | =Tc/omm          +| 12 MB | test10_tbs | 

           |       |          |         |       | role10=CTc/omm   +|       |            | 
           |       |          |         |       | role10=APm/omm    |       |            | 
(5 rows)
           |       |          |         |       | omm=CTc/omm      +|       |            | 
omm=# revoke all on database omm from role10;
REVOKE
omm=# revoke all on database testdb10 from role10;
REVOKE

omm=# \c testdb10
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "omm".testdb10=# \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | role10 | {orientation=row,compression=no}
(1 row)

testdb10=# drop table t2;
DROP TABLE

testdb10=# drop user role10;
DROP ROLE

可以看出不能删除用户user10的原因是：

DETAIL:

owner of table t2
owner of table t1
privileges for database testdb10
1 object in database omm

1）用户user10拥有数据库testdb10

2）用户user10拥有表空间对象test10_tbs。

3）用户user10对数据库testdb具有权限。

--通过revoke回收权限
revoke all on database testdb10 from user10;
revoke all on database omm from user10;

4）用户user2在数据库testdb中有两个对象。

--比如删除表t1、t2
drop table t1;
drop table t2;

或者修改表owner
alter table t1 owner to role10;

解决思路：

就是把涉及用户user10相关的，都要去除掉，最后才能删除用户。