openGauss每日一练第5天

今日目标：openGauss体系结构——使用一个用户访问多个数据库

1.创建用户&查看用户&查看表

1.1创建用户

CREATE USER user_name [ [ WITH ] option [ … ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { ‘password’ [EXPIRED] | DISABLE };

option子句用于设置权限及属性等信息。

{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {PERSISTENCE | PERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

1.2查看用户

\du

1.3 查看表

\dt

2.课后作业

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

omm-# create tablespace music_tbs relative location 'tablespace/test'; 
ERROR:  syntax error at or near "stty"
LINE 1: stty cols 138 
        ^
omm=# create tablespace music_tbs relative location 'tablespace/test';
CREATE TABLESPACE
omm=# create database musicdb10 with tablespace te

omm=# create database musicdb10 with tablespace music_tbs ;
CREATE DATABASE
omm=# create user user10 sysadmin identified by 'gauss@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \db
         List of tablespaces
    Name    | Owner |    Location     
------------+-------+-----------------
omm=#  music_tbs  | omm   | tablespace/test
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)
omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 musicdb10 | omm   | 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
(5 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 | {}
 user10    | Sysadmin                                                                                                         | {}

omm=#

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

omm=# \c postgres user10
Password for user user10: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "user10".
openGauss=> create table postgres_test (id integer, name char(50));
CREATE TABLE
openGauss=> insert into postgres_test values(1,'postgres-db-test');
INSERT 0 1
openGauss=> select * from postgres_test ;
 id |                        name                        
----+----------------------------------------------------
  1 | postgres-db-test                                  
(1 row)
 
openGauss=> \dt
                             List of relations
 Schema |     Name      | Type  | Owner  |             Storage              
--------+---------------+-------+--------+----------------------------------
 public | postgres_test | table | user10 | {orientation=row,compression=no}
(1 row)

openGauss=>

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

openGauss=> \c omm user10
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=> create table omm_test(col1 char(30));
CREATE TABLE
omm=> insert into  omm_test values ('hello opengauss ! omm-test');
INSERT 0 1
omm=> select * from omm_test ;
              col1              
--------------------------------
 hello opengauss ! omm-test    
(1 row)

omm=> \dt
                           List of relations
 Schema |   Name   | Type  | Owner  |             Storage              
--------+----------+-------+--------+----------------------------------
 user10 | omm_test | table | user10 | {orientation=row,compression=no}
(1 row)

omm=> 

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

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

musicdb10=> create table musicdb_test(col char(30));
CREATE TABLE
musicdb10=> insert into musicdb_test values ('hellow musicdb-test!');
INSERT 0 1
musicdb10=> select * from musicdb_test ;
              col               
--------------------------------
 hellow musicdb-test!          
(1 row)

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


musicdb10=>