概述

本文档记录openGauss 3.0.0数据库每日一练第5天课程作业，学习openGauss体系结构，使用一个用户访问多个数据库。

课程练习

创建数据库musicdb10，创建用户user10，赋予sysadmin权限

[omm@ogauss1 ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm@local:/opt/huawei/tmp [postgres]=#create database musicdb10;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb10
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb10" as user "omm".
omm@local:/opt/huawei/tmp [musicdb10]=#\dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(10 rows)

omm@local:/opt/huawei/tmp [musicdb10]=#create user user10 with sysadmin identified by 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [musicdb10]=#\dn
     List of schemas
      Name       | Owner  
-----------------+--------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user10          | user10   <========================
(11 rows)

omm@local:/opt/huawei/tmp [musicdb10]=#

在对应数据库中创建用户，默认会创建对应schema。

用户user10访问数据库postgres，创建一个表并插入数据

[omm@ogauss1 ~]$ gsql -d postgres -p 15400 -U user10 -W zs@123456 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

user10@local:/opt/huawei/tmp [postgres]=>create table day5_post(id int,name char(10));
CREATE TABLE
user10@local:/opt/huawei/tmp [postgres]=>insert into day5_post values(1,'postgres');
INSERT 0 1
user10@local:/opt/huawei/tmp [postgres]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner  |             Storage              
--------+-----------+-------+--------+----------------------------------
 public | day5_post | table | user10 | {orientation=row,compression=no}
(1 row)

user10@local:/opt/huawei/tmp [postgres]=>select * from day5_post;
 id |    name    
----+------------
  1 | postgres  
(1 row)

user10@local:/opt/huawei/tmp [postgres]=>
user10@local:/opt/huawei/tmp [postgres]=>\dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 test1           | test1
(11 rows)

在postgres库中没有对应的user10的schema，表会创建到public中。

用户user10访问数据库zsdba，创建一个表并插入数据

[omm@ogauss1 ~]$ gsql -d zsdba -p 15400 -U user10 -W zs@123456 -r 
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

user10@local:/opt/huawei/tmp [zsdba]=>create table day5_zsdba(id int,name char(10));
CREATE TABLE
user10@local:/opt/huawei/tmp [zsdba]=>insert into day5_zsdba values(1,'zsdba');
INSERT 0 1
user10@local:/opt/huawei/tmp [zsdba]=>\d
                            List of relations
 Schema |    Name    | Type  | Owner  |             Storage              
--------+------------+-------+--------+----------------------------------
 public | day5_zsdba | table | user10 | {orientation=row,compression=no}
 public | t          | table | omm    | {orientation=row,compression=no}
(2 rows)

user10@local:/opt/huawei/tmp [zsdba]=>select * from day5_zsdba;
 id |    name    
----+------------
  1 | zsdba     
(1 row)

user10@local:/opt/huawei/tmp [zsdba]=>\dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user1           | user1
 user2           | user2
 user3           | user3
(13 rows)

在zsdba库中没有对应的user10的schema，表会创建到public中。

用户user10访问数据库musicdb10，创建一个表并插入数据

[omm@ogauss1 ~]$ gsql -d musicdb10 -p 15400 -U user10 -W zs@123456 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

user10@local:/opt/huawei/tmp [musicdb10]=>create table day5_musicdb10(id int,name char(10));
CREATE TABLE
user10@local:/opt/huawei/tmp [musicdb10]=>insert into day5_musicdb10 values(1,'musicdb10');
INSERT 0 1
user10@local:/opt/huawei/tmp [musicdb10]=>\d
                              List of relations
 Schema |      Name      | Type  | Owner  |             Storage              
--------+----------------+-------+--------+----------------------------------
 user10 | day5_musicdb10 | table | user10 | {orientation=row,compression=no}
(1 row)

user10@local:/opt/huawei/tmp [musicdb10]=>select * from day5_musicdb10;
 id |    name    
----+------------
  1 | musicdb10 
(1 row)

user10@local:/opt/huawei/tmp [musicdb10]=>\dn
     List of schemas
      Name       | Owner  
-----------------+--------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user10          | user10
(11 rows)

在musicdb10库中有对应的user10的schema，表会创建到user10的schema中。