openGauss每日一练第19天| 用户和角色管理

学习目标

掌握openGauss的用户和角色管理。

课程作业

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

omm=# CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10_tbs';
CREATE TABLESPACE
omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;
CREATE DATABASE
omm=# 
omm=# \db
            List of tablespaces
    Name    | Owner |       Location        
------------+-------+-----------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 test10_tbs | omm   | tablespace/test10_tbs
(3 rows)

omm=# 

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

omm=#  CREATE USER user10 IDENTIFIED BY 'user10@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#  GRANT ALL ON DATABASE testdb10 TO user10;
GRANTomm=#   ALTER USER user10 SYSADMIN;
omm=# ALTER ROLE
omm=# \q
omm@modb:~$ gsql -d testdb10 -U user10   -W user10@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.

testdb10=>  CREATE TABLE t1(col int);CREATE TABLE
testdb10=>  CREATE TABLE t2(col int);
CREATE TABLE
testdb10=> 
testdb10=> \dt
                         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)

testdb10=> 

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

omm@modb:~$ gsql -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.

omm=#   CREATE ROLE role10 IDENTIFIED BY 'role10@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#   GRANT ALL ON DATABASE testdb10 TO role10;
GRANT
omm=#   alter user role10 LOGIN;
ALTER ROLE
omm=# \q
omm@modb:~$  gsql -d testdb10   -U role10   -W role10@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.

testdb10=> 

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

testdb10=# \dt
                         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)

testdb10=# drop table t1;
DROP TABLE
testdb10=# \dt
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t2   | table | user10 | {orientation=row,compression=no}
(1 row)

testdb10=# alter database testdb10 owner to role10;
ALTER DATABASE
testdb10=# alter tablespace test10_tbs owner to role10;
ALTER TABLESPACE
testdb10=# 
testdb10=# alter table t2 owner to role10;
testdb10=# ALTER TABLE

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

testdb10=# 

--s回收权限，删除用户omm=#   REVOKE ALL ON DATABASE testdb10 FROM user10;
REVOKE
omm=# drop user user10;
DROP ROLE

5、最后删除role10

omm=# alter database testdb10 owner to omm;
ALTER DATABASE
omm=# alter tablespace test10_tbs owner to omm;
ALTER TABLESPACE
omm=# 
omm=# REVOKE ALL ON DATABASE testdb10 FROM role10;
REVOKE
omm=# 
omm=# drop role role10;
DROP ROLE
omm=# \db
            List of tablespaces
    Name    | Owner |       Location        
------------+-------+-----------------------
 pg_default | omm   | 
 pg_global  | omm   | 
 test10_tbs | omm   | tablespace/test10_tbs
(3 rows)

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 testdb10  | omm   | UTF8     | C       | C     | =Tc/omm
(5 rows)

omm=#