4.1 第4天 | openGauss中一个数据库可以被多个用户访问

Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.


root@modb:~#
root@modb:~#
root@modb:~#
root@modb:~# su - omm
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=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

omm=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)

omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

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
(4 rows)

omm=# create tablespace music_tbs relative location 'tablespace/test_ts1';
CREATE TABLESPACE

^
omm=# create database musicdb with tablespace=music_tbs;
CREATE DATABASE
omm=# create user user1 identified by 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user2 identified by 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 identified by 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user user1 sysadmin;
ALTER ROLE
omm=# alter user user2 sysadmin;
ALTER ROLE
omm=# alter user user3 sysadmin;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}

omm=# \c musicdb user1
Password for user user1:
musicdb=> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".

musicdb=> create table t1 (col1 char(20));
CREATE TABLE

musicdb=> insert into t1 values('hello kunpeng 1');
INSERT 0 1
musicdb=> select * from t1;
col1
----------------------
hello kunpeng 1
(1 row)

musicdb=> \c music_db user2
Password for user user2:
FATAL: database "music_db" does not exist
Previous connection kept
musicdb=> \c musicdb user2
Password for user user2:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user2".

^
musicdb=> create table t2 (col1 char(20));
musicdb=> CREATE TABLE

musicdb=> insert into t2 values('hello kunpeng 2');
INSERT 0 1

musicdb=> select * from t2;
col1
----------------------
hello kunpeng 2
(1 row)

musicdb=> \c music_db user3
Password for user user3:
FATAL: database "music_db" does not exist
Previous connection kept
musicdb=> \c musicdb user3
Password for user user3:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user3".
musicdb=> create table t3 (col1 char(20));
CREATE TABLE
musicdb=> insert into t3 values ('hello kunpeng3 ');
INSERT 0 1
musicdb=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
musicdb=> public | t1 | table | user1 | {orientation=row,compression=no}
public | t2 | table | user2 | {orientation=row,compression=no}
public | t3 | table | user3 | {orientation=row,compression=no}
(3 rows)

musicdb=>
\dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
public | t1 | table | user1 | {orientation=row,compression=no}
public | t2 | table | user2 | {orientation=row,compression=no}
public | t3 | table | user3 | {orientation=row,compression=no}
(3 rows)

musicdb=> create database musicdb2 with tablespace = music_tbs;
CREATE DATABASE
musicdb=> \c musicdb2 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".
musicdb2=> create table products1 (product_id integer);
CREATE TABLE
musicdb2=> drop table products1 ;
DROP TABLE

musicdb2=> \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}

ct_name char(20),cateory (char30));
^
musicdb2=> create table products1 (product_id integer,product_name char(20),cateory char(30));
CREATE TABLE

musicdb2(> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user1 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}


musicdb2(> \db
List of tablespaces
Name | Owner | Location
------------+-------+---------------------
music_tbs | omm | tablespace/test_ts1
pg_default | omm |
pg_global | omm |
(3 rows)

musicdb2(> insert into products1 values(1520,'olymus camera','electrncs'）;
musicdb2(> select * from products1;
musicdb2(> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

musicdb2(> insert into products1 values(1520,'olympus camera' ,'electrncs');
musicdb2(> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

musicdb2(> insert into products1 values (1520,'olympus camera' ,'electrncs');
musicdb2(> insert into products1 values(1502,'olympus camera' ,'electrncs');
musicdb2(> insert into products1 values(1502,'olympus camera','electrncs');
musicdb2(> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

musicdb2(> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
musicdb | omm | UTF8 | C | C |
musicdb2 | user3 | UTF8 | C | C |
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
(6 rows)

musicdb2(> \c musicdb2 user1
Password for user user1:
FATAL: Invalid username/password,login denied.
Previous connection kept
musicdb2(> \c musicdb2 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".

musicdb2(> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products1 | table | user1 | {orientation=row,compression=no}
(1 row)



musicdb2(> \q
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=# drop database musicdb2;
DROP DATABASE
omm=# create database musicdb2 with tablespace= music_tbs;
CREATE DATABASE
omm=# \c musicdb2 user1
Password for user user1:
FATAL: Invalid username/password,login denied.
Previous connection kept

omm=# create table products1 (product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
omm=# insert into products1 values(1502,'olympus camera' ,'electrncs');
INSERT 0 1

omm=# insert into products1 values(1601,'lamaze,toys');
INSERT 0 1
omm=# insert into products1 values(1700,'wait interface','books');
INSERT 0 1
omm=# insert into products1 values(1666,'harry potter','toys');
INSERT 0 1
omm=# \c musicdb2 user2
Password for user user2:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user2".

musicdb2=> create table products2 (product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products2 values(1502,'olympus camera' ,'electrncs');
INSERT 0 1
musicdb2=> insert into products2 values(1601,'lamaze,toys');
INSERT 0 1
musicdb2=> insert into products2 values(1700,'wait interface','books');
INSERT 0 1
musicdb2=> insert into products2 values(1666,'harry potter','toys');
INSERT 0 1
musicdb2=>
musicdb2=>
musicdb2=> \c musicdb2 user3
Password for user user3:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user3".
musicdb2=> create table products3 (product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products3 values(1502,'olympus camera' ,'electrncs');
INSERT 0 1
musicdb2=> insert into products3 values(1601,'lamaze,toys');
INSERT 0 1
musicdb2=> insert into products3 values(1700,'wait interface','books');
INSERT 0 1
musicdb2=> insert into products3 values(1666,'harry potter','toys');
INSERT 0 1
musicdb2=>
musicdb2=>
musicdb2=>
musicdb2=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products2 | table | user2 | {orientation=row,compression=no}
public | products3 | table | user3 | {orientation=row,compression=no}
(2 rows)

musicdb2=> \
Invalid command \. Try \? for help.
musicdb2=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products2 | table | user2 | {orientation=row,compression=no}
public | products3 | table | user3 | {orientation=row,compression=no}
(2 rows)

musicdb2=> \c musicdb2 user1
Password for user user1:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".
musicdb2=> create table products1 (product_id INTEGER,product_name char(20),category char(30));
CREATE TABLE
musicdb2=> insert into products1 values(1502,'olympus camera' ,'electrncs');
INSERT 0 1
musicdb2=> insert into products1 values(1601,'lamaze,toys');
INSERT 0 1
musicdb2=> insert into products1 values(1700,'wait interface','books');
INSERT 0 1
musicdb2=> insert into products1 values(1666,'harry potter','toys');
INSERT 0 1
musicdb2=>
musicdb2=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-----------+-------+-------+----------------------------------
public | products1 | table | user1 | {orientation=row,compression=no}
public | products2 | table | user2 | {orientation=row,compression=no}
public | products3 | table | user3 | {orientation=row,compression=no}
(3 rows)

musicdb2=>