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

6.1 创建test100_tbs的表空间，在这个表空间中创建数据库testdb100

openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/opt/huawei/tmp" at port "26000".
openGauss=#

openGauss=#  CREATE TABLESPACE test100_tbs RELATIVE LOCATION 'tablespace/test100_tbs';
CREATE TABLESPACE
openGauss=#  CREATE DATABASE testdb100 WITH TABLESPACE = test100_tbs;
CREATE DATABASE
openGauss=#

openGauss=# \db
              List of tablespaces
     Name     | Owner |        Location
--------------+-------+-------------------------
 test100_tbs  | omm   | tablespace/test100_tbs
 test_tbs22   | user1 | tablespace/test_tbs22
(8 rows)

openGauss=# \l
                              List of databases
   Name    | Owner  | Encoding |  Collate   |   Ctype    | Access privileges
-----------+--------+----------+------------+------------+-------------------
 testdb100 | omm    | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 testdb2   | user1  | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 zyfdb     | omm    | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 zzzdb     | user1  | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
(17 rows)

6.2 使用create user创建用户user100，登录数据库testdb100，创建测试表t1和t2

 CREATE USER user100 IDENTIFIED BY 'kunpeng@1234';
 GRANT ALL ON DATABASE testdb100 TO user100;

openGauss=# \c testdb100 user100
Password for user user100:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb100" as user "user100".
testdb100=>
testdb100=> show search_path;
  search_path
----------------
 "$user",public
(1 row)
testdb100=> create table t1(id int);
ERROR:  permission denied for schema public   >>>>>>> 为什么这里没有权限
DETAIL:  N/A
testdb100=>
testdb100=> create schema  sch_100 ;                  >>>>> 创建 模式
CREATE SCHEMA
testdb100=> create table sch_100.t1 (id int);
CREATE TABLE
testdb100=> \dt
No relations found.
testdb100=> select * from sch_100.t1;
 id
----
(0 rows)

testdb100=> set search_path to  "$user",sch_100;
SET
testdb100=> show search_path;
   search_path
------------------
 "$user", sch_100
(1 row)

testdb100=> \dt
                          List of relations
 Schema  | Name | Type  |  Owner  |             Storage
---------+------+-------+---------+----------------------------------
 sch_100 | t1   | table | user100 | {orientation=row,compression=no}
(1 row)

testdb100=> select * from t1;
 id
----
(0 rows)
testdb100=> set search_path to  "$user",sch_100;
SET
testdb100=> create table t2 (id int);
CREATE TABLE
testdb100=> \dt
                          List of relations
 Schema  | Name | Type  |  Owner  |             Storage
---------+------+-------+---------+----------------------------------
 sch_100 | t1   | table | user100 | {orientation=row,compression=no}
 sch_100 | t2   | table | user100 | {orientation=row,compression=no}
(2 rows)

6.3 使用create role创建角色role100，登录数据库testdb100

CREATE ROLE role100 IDENTIFIED BY 'kunpeng@1234';

testdb100=>  CREATE ROLE role100 IDENTIFIED BY 'kunpeng@1234';
ERROR:  Permission denied to create role.
testdb100=> \conninfo
You are connected to database "testdb100" as user "user100" via socket in "/opt/huawei/tmp" at port "26000".
testdb100=>


[omm@node1 ~]$ gsql -p 26000 -d postgres -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# CREATE ROLE role100 IDENTIFIED BY 'kunpeng@1234';
CREATE ROLE
openGauss=#   GRANT ALL ON DATABASE testdb100 TO role100;


[omm@node1 ~]$ gsql -p 26000 -d testdb100 -U role100 -r
Password for user role100:
gsql: FATAL:  role "role100" is not permitted to login
[omm@node1 ~]$


alter user role100 LOGIN;
openGauss=# alter user role100 LOGIN;
ALTER ROLE
openGauss=# \c testdb100 role100
Password for user role100:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb100" as user "role100".
testdb100=>
testdb100=> \dt
No relations found.
testdb100=> \d
No relations found.
testdb100=>

----  不知道为什么  role100 发现不了 schema  sch_100下面的表
testdb100=> \c testdb100 role100
Password for user role100:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb100" as user "role100".
testdb100=>
testdb100=> set search_path to  "$user",sch_100;
SET
testdb100=> \dt
No relations found.
testdb100=> \dtd
No relations found.
testdb100=> \d
No relations found.
testdb100=>
testdb100=> select * from sch_100.t1;
ERROR:  permission denied for schema sch_100
LINE 1: select * from sch_100.t1;
                      ^
DETAIL:  N/A

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

openGauss=# drop user user100;
ERROR:  role "user100" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb100
3 objects in database testdb100
openGauss=#

##在 testdb100 下显示的更清楚
testdb100=# drop user user100;
ERROR:  role "user100" cannot be dropped because some objects depend on it
DETAIL:  
owner of table sch_100.t2
owner of table sch_100.t1
owner of schema sch_100
privileges for database testdb100
1 object in database postgres
testdb100=#
testdb100=# drop table sch_100.t1;
DROP TABLE
testdb100=# set search_path to  "$user",sch_100;
SET
testdb100=# \d
                          List of relations
 Schema  | Name | Type  |  Owner  |             Storage
---------+------+-------+---------+----------------------------------
 sch_100 | t2   | table | user100 | {orientation=row,compression=no}
(1 row)
testdb100=# alter table sch_100.t2  owner to role100;
ALTER TABLE
testdb100=# \d
                          List of relations
 Schema  | Name | Type  |  Owner  |             Storage
---------+------+-------+---------+----------------------------------
 sch_100 | t2   | table | role100 | {orientation=row,compression=no}
(1 row)                   ^^^^^^^


testdb100=# drop user user100;
ERROR:  role "user100" cannot be dropped because some objects depend on it
DETAIL:  
owner of schema sch_100
privileges for database testdb100
1 object in database postgres
testdb100=# alter schema sch_100 owner to role100;
ALTER SCHEMA
testdb100=# alter database testdb100 owner to role100;
ALTER DATABASE
testdb100=# drop user user100;
ERROR:  role "user100" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb100
1 object in database postgres
testdb100=#


openGauss=# drop schema user100;
DROP SCHEMA
openGauss=#

openGauss=# drop schema user100;
DROP SCHEMA
openGauss=# drop user user100;
ERROR:  role "user100" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb100  
openGauss=#   REVOKE ALL ON DATABASE testdb100 FROM user100;              <<<<<<<<<<<<<<<<
testdb100=#   REVOKE ALL ON DATABASE testdb100 FROM user100;              <<<<<<<<<<<
REVOKE
testdb100=# drop user user100;
DROP ROLE
testdb100=#

6.5 最后删除role100

testdb100=# drop role role100;
ERROR:  role "role100" cannot be dropped because some objects depend on it
DETAIL:  owner of database testdb100
owner of schema sch_100
owner of table t2
testdb100=#

testdb100=# drop table sch_100.t2;
DROP TABLE
testdb100=# drop role role100;
ERROR:  role "role100" cannot be dropped because some objects depend on it
DETAIL:  owner of database testdb100
owner of schema sch_100
testdb100=#
testdb100=# alter database  testdb100 owner to user1;
ALTER DATABASE
 
testdb100=# drop role role100;
ERROR:  role "role100" cannot be dropped because some objects depend on it
DETAIL:  owner of schema sch_100
testdb100=#
testdb100=#  REVOKE ALL ON DATABASE testdb100 FROM role100;   

testdb100=# drop role role100;
ERROR:  role "role100" cannot be dropped because some objects depend on it
DETAIL:  owner of schema sch_100
testdb100=#
testdb100=# REVOKE ALL ON schema sch_100 from role100;
REVOKE
testdb100=# alter schema sch_100 to user1;
ERROR:  syntax error at or near "to"
LINE 1: alter schema sch_100 to user1;
                             ^
testdb100=# alter schema sch_100 owner to user1;
ALTER SCHEMA
testdb100=# \dn
     List of schemas
      Name       | Owner
-----------------+-------
 sch_100         | user1

(12 rows)
testdb100=# drop role role100;
DROP ROLE
testdb100=#