openGauss每日一练第5天--openGauss中一个用户访问多个数据库

本次课程学习的是openGauss中一个用户可以被多个数据库：
课后练习：
1、创建数据库musicdb10，创建用户user10，赋予sysadmin权限

mm=# 
omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb10  WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# CREATE USER user10 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user10 SYSADMIN;
ALTER ROLE
omm=#

2、用户user10访问数据库postgres，创建一个表并插入数据
切换到postgres数据库，使用的是系统层的命令来完成的,使用元命令\conninfo来确认当前的连接信息，确保使用的用户是目标用户。

omm@modb:~$ gsql -d postgres -U user10 -W kunpeng@1234
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.
openGauss=>\conninfo                                   
You are connected to database "postgres" as user "user10" via socket in "/tmp" at port "5432".
openGauss=> 
openGauss=> create table t1(t1 int,v1 char(10));
insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');
select * from t1;CREATE TABLE
openGauss=> INSERT 0 3
openGauss=> 
 t1 |     v1     
----+------------
  1 | aaa       
  2 | bbb       
  3 | ccc       
(3 rows)

openGauss=> 
penGauss=> 
openGauss=> \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user10 | {orientation=row,compression=no}
(1 row)

3.用户user10访问数据库omm，创建一个表并插入数据
这里的连接方式使用数据库层的连接

openGauss=> \c omm
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "user10".
omm=> 
omm=> \conninfo
You are connected to database "omm" as user "user10" via socket in "/tmp" at port "5432".
omm=> 
omm=> create table t1(t1 int,v1 char(10));
CREATE TABLE
omm=> \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 user10 | t1   | table | user10 | {orientation=row,compression=no}
(1 row)
omm=> insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');
select * from t1;INSERT 0 3
omm=> 
omm=>  t1 |     v1     
----+------------
  1 | aaa       
  2 | bbb       
  3 | ccc       
(3 rows)
omm=> 

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

omm@modb:~$ gsql -d musicdb10 -U user10 -W kunpeng@1234
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.

musicdb10=> \conninfo
You are connected to database "musicdb10" as user "user10" via socket in "/tmp" at port "5432".
musicdb10=> \d
No relations found.
musicdb10=> create table t1(t1 int,v1 char(10));
insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');
select * from t1;CREATE TABLE
musicdb10=> INSERT 0 3
musicdb10=> 
 t1 |     v1     
----+------------
  1 | aaa       
  2 | bbb       
  3 | ccc       
(3 rows)

musicdb10=> 
musicdb10=> 
musicdb10=> \d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user10 | {orientation=row,compression=no}
(1 row)

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)

musicdb10=> 

总结：
1、访问数据库可以用系统层和数据库层两种方式连接；
2、普通用户赋予sysadmin权限后，可以对所有的库进行操作。
疑问点：
1、连接omm库创建的表默认schema是user10 ，连接postgres和自创建库创建表，默认都是在public下。