学习目标

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

课程作业

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

openGauss=# CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10_tbs1';
CREATE TABLESPACE
openGauss=# CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;
CREATE DATABASE

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

openGauss=#  CREATE USER user10 IDENTIFIED BY 'user54321!';
CREATE ROLE
openGauss=#  GRANT ALL ON DATABASE testdb10 TO user1;
GRANT
openGauss=# \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=>

testdb10=# \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=> create table t1(name varchar);
CREATE TABLE
testdb10=> create table t2(name varchar);
CREATE TABLE

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

testdb10=# create role rol10  identified by 'rol1054321!';
CREATE ROLE
testdb10=# GRANT ALL ON DATABASE testdb10 to rol10;
GRANT

--role 是无法登陆的 ，需要授权login
testdb10=# \c testdb10 rol10
Password for user rol10:
FATAL:  role "rol10" is not permitted to login
Previous connection kept
testdb10=# alter user rol10 login;
ALTER ROLE
testdb10=# \c testdb10 rol10
Password for user rol10:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb10" as user "rol10".
testdb10=>

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

estdb10=# alter tablespace test10_tbs owner to rol10;
ALTER TABLESPACE
testdb10=# drop table t1;
DROP TABLE
testdb10=# alter table t2 owner to rol10;
ALTER TABLE
testdb10=# drop user test10;
ERROR:  role "test10" does not exist
testdb10=# drop user user10;
ERROR:  role "user10" cannot be dropped because some objects depend on it
DETAIL:  1 object in database postgres

5、最后删除role10

testdb10=# drop role rol10;
ERROR:  role "rol10" cannot be dropped because some objects depend on it
DETAIL:  owner of table t2
owner of tablespace test10_tbs
owner of database testdb10
testdb10=# alter tablespace test10_tbs owner to omm
testdb10-# ;
ALTER TABLESPACE
testdb10=#  alter database testdb10 owner to omm
;
ALTER DATABASE
testdb10=# drop role rol10;
ERROR:  role "rol10" cannot be dropped because some objects depend on it
DETAIL:  owner of table t2
testdb10=# alter table t2 owner to omm;
ALTER TABLE
testdb10=# drop role rol10;
DROP ROLE