华为openGauss高斯数据库 - gsql用法

目录

1.1 gsql的语法

1.2 gsql常用选项

1.2.1  最常用的必要选项

1.2.2 -r选项

1.2.3 -E选项

1.2.4 -t选项

1.2.5 -A选项

1.2.6 -v选项

1.2.7 -c选项

1.2.8 -f选项

1.2.9 -q选项

1.3 gsql的元命令

1.3.1 l命令

1.3.2 du命令和dg命令

1.3.3 db命令

1.3.4 dn命令

1.3.5 d命令

1.3.6 dt命令

1.3.7 di命令

1.3.8 dv命令

1.3.9 ds命令

1.3.10 df命令

1.3.11 d TableName命令

1.3.12 di IndexName命令

1.3.13 dx命令

1.3.14 x命令

1.3.15 timing命令

1.3.16 h命令

1.3.17 ?命令

1.3.18 ! os_command命令

1.3.19 o  fileName命令

1.3.20 i  file.sql命令

1.3.21 conninfo命令

1.3.22 c[onnect] [DBNAME]命令

1.3.23 echo [string]命令

1.3.24 q命令和快捷键ctrl+d

1.4 gsql初始化文件.gsqlrc

2.客户端安全

2.1配置客户端接入认证

2.2客户端验证


1.1 gsql的语法

gsql --help

[omm@test ~]$ gsql --help

gsql is the FusionInsight LibrA interactive terminal.

Usage:

  gsql [OPTION]... [DBNAME [USERNAME]]

General options:

  -c, --command=COMMAND    run only single command (SQL or internal) and exit

  -d, --dbname=DBNAME      database name to connect to (default: "omm")

  -f, --file=FILENAME      execute commands from file, then exit

  -l, --list               list available databases, then exit

  -v, --set=, --variable=NAME=VALUE

                           set gsql variable NAME to VALUE

  -V, --version            output version information, then exit

  -X, --no-gsqlrc          do not read startup file (~/.gsqlrc)

  -1 ("one"), --single-transaction

                           execute command file as a single transaction

  -?, --help               show this help, then exit

Input and output options:

  -a, --echo-all           echo all input from script

  -e, --echo-queries       echo commands sent to server

  -E, --echo-hidden        display queries that internal commands generate

  -k, --with-key=KEY       the key for decrypting the encrypted file

  -L, --log-file=FILENAME  send session log to file

  -m, --maintenance        can connect to cluster during 2-pc transaction recovery

  -n, --no-libedit        disable enhanced command line editing (libedit)

  -o, --output=FILENAME    send query results to file (or |pipe)

  -q, --quiet              run quietly (no messages, only query output)

  -s, --single-step        single-step mode (confirm each query)

  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:

  -A, --no-align           unaligned table output mode

  -F, --field-separator=STRING

                           set field separator (default: "|")

  -H, --html               HTML table output mode

  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see pset command)

  -R, --record-separator=STRING

                           set record separator (default: newline)

  -r                         if this parameter is set,use libedit

  -t, --tuples-only        print rows only

  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)

  -x, --expanded           turn on expanded table output

  -z, --field-separator-zero

                           set field separator to zero byte

  -0, --record-separator-zero

                           set record separator to zero byte

Connection options:

  -h, --host=HOSTNAME      database server host or socket directory (default: "/opt/gaussdbi/tmp")

  -p, --port=PORT          database server port (default: "5432")

  -U, --username=USERNAME  database user name (default: "omm")

  -W, --password=PASSWORD  the password of specified database user

For more information, type "?" (for internal commands) or "help" (for SQL

commands) from within gsql, or consult the gsql section in the FusionInsight LibrA

documentation.

Report bugs to <pgsql-bugs@postgresql.org>.

[omm@test ~]$

1.2 gsql常用选项

1.2.1  最常用的必要选项

使用gsql命令,用数据库用户student,连接到openGauss数据库管理系统下的studentdb数据库:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=>

其中的选项说明如下:

-d选项: 指定gsql客户端连接的数据库

-h选项: 指定gsql客户端连接的服务器IP

-U选项: 指定gsql客户端连接数据库的用户名

-p选项: 指定gsql客户端连接的服务器端口号

-W选项: 指定gsql客户端连接的用户密码

1.2.2 -r选项

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql命令的-r选项,第一个作用是当我们在gsql中执行了很多语句后,如果我们想重新执行之前执行过的语句,可以使用上箭头和下箭头,向前和向后翻阅之前执行过的命令和SQL语句。

执行下面的语句:使用上箭头可以提供很多便利性。

SELECT * FROM instructor WHERE salary=90000;

studentdb=> SELECT * FROM instructor WHERE salary=90000;

  id   | dept_name | name |  salary 

----------+------------------+---------+----------

12121  | Finance    | Wu   | 90000.00

(1 row)

studentdb=>

1.2.3 -E选项

-E选项会让gsql客户端程序再执行元命令的时候,显示其对应的SQL语句。

使用Linux用户omm,打开一个Linux终端窗口,执行gsql的元命令l,该命令的作用是显示当前系统有哪些数据库

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -E

l

q

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -E

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> l

********* QUERY **********                  -E选项会显示gsql客户端的元命令对应的SQL语句

SELECT d.datname as "Name",

       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

       d.datcollate as "Collate",

       d.datctype as "Ctype",

       pg_catalog.array_to_string(d.datacl, E'n') AS "Access privileges"

FROM pg_catalog.pg_database d

ORDER BY 1;

**************************

                          List of databases

   Name    | Owner | Encoding  | Collate | Ctype | Access privileges

-----------+-------+-----------+---------+-------+-------------------

 postgres  | omm   | SQL_ASCII | C       | C     |

 studentdb | omm   | SQL_ASCII | C       | C     |

 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

(4 rows)

studentdb=> q

[omm@test ~]$

如果不使用-E选项,同样执行上面的命令系列,结果如下:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

l

q

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> l

                          List of databases

   Name    | Owner | Encoding  | Collate | Ctype | Access privileges

-----------+-------+-----------+---------+-------+-------------------

 postgres  | omm   | SQL_ASCII | C       | C     |

 studentdb | omm   | SQL_ASCII | C       | C     |

 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

(4 rows)

studentdb=> q

[omm@test ~]$

可以看出,如果没有-E选项,不会显示元命令l对应的SQL查询语句。

1.2.4 -t选项

-t选项会让gsql客户端程序在执行SQL查询语句的时候,返回的结果不显示列名及返回结果的行数

使用Linux用户omm,打开一个Linux终端窗口,执行如下的命令:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -t

select * from instructor where salary=80000;

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -t

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> select * from instructor where salary=80000;

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng.   | Kim   | 80000.00

studentdb=> q

[omm@test ~]$

1.2.5 -A选项

-A选项会让gsql客户端程序在执行SQL查询语句的时候,不对齐显示查询返回的行数据,如图6-7所示(列不是对齐的!)。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -A

select * from instructor where salary=80000;

q

图6-8 -A选项表示不对齐显式

可以将-A和-t两个选项同时应用,表示不对齐显示,也不显示列名和返回行数

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -At

select * from instructor where salary=80000;

q

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r -At

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> select * from instructor where salary=80000;

76543|Finance|Singh|80000.00

98345|Elec. Eng.|Kim|80000.00

studentdb=> q

[omm@test ~]$

1.2.6 -v选项

-v选项会让gsql客户端程序在命令行中设置gsql环境变量

如果我们想在命令行中告诉gsql启动后关闭自动提交,设置为手动事务提交,可以执行如下命令:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

-v AUTOCOMMIT=off -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

> -v AUTOCOMMIT=off -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=>

1.2.7 -c选项

-c选项会让gsql客户端程序直接在命令行中运行SQL语句,示例如下:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "select * from instructor where salary=80000"

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "select * from instructor where salary=80000"

  id   | dept_name  | name  |  salary 

----------+--------------------+------------+----------

 76543 | Finance     | Singh   | 80000.00

 98345 | Elec. Eng.    | Kim    | 80000.00

(2 rows)

[omm@test ~]$

1.2.8 -f选项

-f选项会让gsql客户端程序直接在命令行中直接运行SQL脚本文件

首先用omm用户,生成一个测试用的SQL语句脚本:

cat > test.sql<<EOF

select * from instructor where salary=80000;

EOF

[omm@test ~]$ cat > test.sql<<EOF

> select * from instructor where salary=80000;

> EOF

[omm@test ~]$

然后使用gsql客户端程序在命令行中直接执行刚刚创建的SQL语句脚本:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -f test.sql

rm test.sql

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -f test.sql

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng.   | Kim  | 80000.00

(2 rows)

total time: 0  ms

[omm@test ~]$ rm test.sql

[omm@test ~]$

1.2.9 -q选项

-q选项会让gsql客户端程序以安静的方式运行,只显示查询结果

首先用omm用户,执行下面的gsql命令(使用了-q选项),创建test表,然后再次执行gsql命令(不使用-q选项),删除刚刚创建的test表,接着再次执行gsql命令(不使用-q选项),重新创建test表,最后又一次执行gsql命令(使用了-q选项),删除刚刚创建的test表:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "create table test(col char)" -q

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "drop table test"

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "create table test(col char)"

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "drop table test" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "create table test(col char)" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "drop table test"

DROP TABLE

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "create table test(col char)"

CREATE TABLE

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "drop table test" -q

[omm@test script]$

我们发现使用了-q选项的gsql没有显示任何信息。

执行下面的命令,进行SQL查询,我们发现虽然有-q选项,但是会显示查询结果。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

     -c "select * from instructor where salary=80000" -q

[omm@test script]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020

>      -c "select * from instructor where salary=80000" -q

  id   | dept_name  | name  |  salary 

----------+---------------------+----------+----------

 76543 | Finance     | Singh  | 80000.00

 98345 | Elec. Eng.    | Kim   | 80000.00

(2 rows)

[omm@test script]$

1.3 gsql的元命令

本节的测试均使用下面的命令登录到openGauss数据库:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=>

1.3.1 l命令

元命令l的作用是显示openGauss数据库集簇中,目前有哪些数据库

l

studentdb=> l

                          List of databases

   Name    | Owner | Encoding  | Collate | Ctype | Access privileges

-----------+-------+-----------+---------+-------+-------------------

 postgres  | omm   | SQL_ASCII | C       | C     |

 studentdb | omm   | SQL_ASCII | C       | C     |

 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +

          |        |          |         |      | omm=CTc/omm

 template1 | omm   | SQL_ASCII | C        | C    | =c/omm           +

          |        |          |         |      | omm=CTc/omm

(4 rows)

studentdb=>

1.3.2 du命令和dg命令

元命令dg命令与元命令du命令的作用类似,都是显示openGauss数据库集簇中,目前有哪些用户和角色

du

dg

studentdb=> du

                                         List of roles

 Role name |                               Attributes                               | Member of

-----------+------------------------------------------------------------------------+-----------

 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {}

 student   | Sysadmin                                                               | {}

studentdb=> dg

                                         List of roles

 Role name |                               Attributes                               | Member of

-----------+------------------------------------------------------------------------+-----------

 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {}

 student   | Sysadmin                                                               | {}

studentdb=>

1.3.3 db命令

元命令db的作用是显示openGauss数据库集簇中,目前有哪些表空间

db

studentdb=> db

             List of tablespaces

    Name    | Owner |        Location       

---------------------+-----------+------------------------

 pg_default   | omm  |

 pg_global    | omm  |

 student_ts   | omm  | tablespace/student_ts1

(3 rows)

studentdb=>

1.3.4 dn命令

元命令dn的作用是显示当前数据库,有哪些数据库模式

dn

studentdb=> dn

 List of schemas

   Name | Owner

--------------+------------

 cstore   | omm

 dbe_perf | omm

 public   | omm

 snapshot | omm

(4 rows)

studentdb=>

1.3.5 d命令

元命令d的作用是显示当前数据库下的所有的数据库对象(相当于命令dtvsE,这里E表示外部表):

d

                     List of relations

Schema |    Name    | Type  |  Owner  |             Storage             

-----------+---------------------+----------+----------------+----------------------------------

 public | advisor       | table  | student   | {orientation=row,compression=no}

 public | classroom     | table  | student   | {orientation=row,compression=no}

 public | course        | table  | student   | {orientation=row,compression=no}

 public | department   | table  | student   | {orientation=row,compression=no}

 public | instructor     | table  | student   | {orientation=row,compression=no}

 public | prereq        | table  | student   | {orientation=row,compression=no}

 public | section        | table  | student   | {orientation=row,compression=no}

 public | student       | table  | student   | {orientation=row,compression=no}

 public | takes         | table  | student   | {orientation=row,compression=no}

 public | teaches       | table  | student   | {orientation=row,compression=no}

 public | time_slot      | table  | student   | {orientation=row,compression=no}

(11 rows)

studentdb=>

1.3.6 dt命令

元命令dt的作用是显示数据库中所有的表

dt

studentdb=> dt

                            List of relations

 Schema |    Name    | Type  |  Owner  |             Storage             

--------+------------+-------+---------+----------------------------------

 public | advisor    | table | student | {orientation=row,compression=no}

 public | classroom  | table | student | {orientation=row,compression=no}

 public | course     | table | student | {orientation=row,compression=no}

 public | department | table | student | {orientation=row,compression=no}

 public | instructor | table | student | {orientation=row,compression=no}

 public | prereq     | table | student | {orientation=row,compression=no}

 public | section    | table | student | {orientation=row,compression=no}

 public | student    | table | student | {orientation=row,compression=no}

 public | takes      | table | student | {orientation=row,compression=no}

 public | teaches    | table | student | {orientation=row,compression=no}

 public | time_slot  | table | student | {orientation=row,compression=no}

(11 rows)

studentdb=>

元命令dt+的作用是以扩展的方式,显示数据库中所有的表:

dt+

studentdb=> dt+

                                          List of relations

 Schema |    Name    | Type  |  Owner  |    Size    |             Storage              | Description

--------+------------+-------+---------+------------+----------------------------------+-------------

 public | advisor    | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | classroom  | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | course     | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | department | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | instructor | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | prereq     | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | section    | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | student    | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | takes      | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | teaches    | table | student | 8192 bytes | {orientation=row,compression=no} |

 public | time_slot  | table | student | 8192 bytes | {orientation=row,compression=no} |

(11 rows)

studentdb=>

后面增加一个+号,表示显示更多的信息。

1.3.7 di命令

元命令di的作用是查看数据库中索引的信息

di

studentdb=> di

                         List of relations

 Schema |      Name       | Type  |  Owner  |   Table    | Storage

--------+-----------------+-------+---------+------------+---------

 public | advisor_pkey    | index | student | advisor    |

 public | classroom_pkey  | index | student | classroom  |

 public | course_pkey     | index | student | course     |

 public | department_pkey | index | student | department |

 public | instructor_pkey | index | student | instructor |

 public | prereq_pkey     | index | student | prereq     |

 public | section_pkey    | index | student | section    |

 public | student_pkey    | index | student | student    |

 public | takes_pkey      | index | student | takes      |

 public | teaches_pkey    | index | student | teaches    |

 public | time_slot_pkey  | index | student | time_slot  |

(11 rows)

studentdb=>

1.3.8 dv命令

元命令di的作用是查看数据库中索引的信息。

测试数据集目前暂时没有视图,因此首先创建一个视图:

create or replace view faculty as

    select ID, name, dept_name

    from instructor;

studentdb=> create or replace view faculty as

studentdb->     select ID, name, dept_name

studentdb->     from instructor;

CREATE VIEW

studentdb=>

执行gsql元命令dv,查询当前数据库下有哪些视图

dv

studentdb=> dv

              List of relations

 Schema |  Name   | Type |  Owner  | Storage

-------------+-------------+------+---------+---------

 public | faculty | view | student |

(1 row)

studentdb=>

删除刚刚创建的视图:

drop view faculty;

studentdb=> drop view faculty;

DROP VIEW

studentdb=>

1.3.9 ds命令

元命令ds的作用是查看数据库中序列的信息

测试数据集目前暂时没有序列,因此首先创建一个表,其两列都是序列,创建该表会自动创建2个序列:

DROP TABLE IF EXISTS test;

create table test(id serial primary key,testnum serial);

studentdb=> DROP TABLE IF EXISTS test;

NOTICE:  table "test" does not exist, skipping

DROP TABLE

studentdb=> create table test(id serial primary key,testnum serial);

NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"

NOTICE:  CREATE TABLE will create implicit sequence "test_testnum_seq" for serial column "test.testnum"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"

CREATE TABLE

studentdb=>

执行gsql元命令ds,查询当前数据库下有哪些序列:

ds

studentdb=> ds

                    List of relations

 Schema |       Name       |   Type   |  Owner  | Storage

--------+------------------+----------+---------+---------

 public | test_id_seq      | sequence | student |

 public | test_testnum_seq | sequence | student |

(2 rows)

studentdb=>

删除刚刚创建的测试表,序列也同时被删除,使用ds命令已经查不到有任何序列了:

DROP TABLE IF EXISTS test;

ds

studentdb=> DROP TABLE IF EXISTS test;

DROP TABLE

studentdb=> ds

No relations found.

studentdb=>

1.3.10 df命令

元命令df的作用是查看数据库中关于存储函数的信息

因为目前数据库中暂时没有任何函数,首先执行下面的语句,创建一个测试用的函数:

CREATE OR REPLACE FUNCTION myfunction(s INT)

RETURN INT

AS

BEGIN

        IF(s>0) THEN

           RETURN 1;

        ELSEIF(s<0) THEN

           RETURN -1;

        ELSE

           RETURN 0;

        END IF;

END

/

studentdb=> CREATE OR REPLACE FUNCTION myfunction(s INT)

studentdb-> RETURN INT

studentdb-> AS

studentdb$> BEGIN

studentdb$>         IF(s>0) THEN

studentdb$>            RETURN 1;

studentdb$>         ELSEIF(s<0) THEN

studentdb$>            RETURN -1;

studentdb$>         ELSE

studentdb$>            RETURN 0;

studentdb$>         END IF;

studentdb$> END

studentdb$> /

CREATE FUNCTION

studentdb=>

然后执行元命令df,查看当前数据库下有什么函数:

df

studentdb=> df

                                        List of functions

 Schema |    Name    | Result data type | Argument data types |  Type  | fencedmode | propackage

-------------+---------------------+-----------------------+------------------------------+-------------+------------------+------------------

 public  | myfunction   | integer        | s integer           | normal  | f          | f

(1 row)

studentdb=>

删除用于测试的函数:

drop function myfunction;

df

studentdb=> drop function myfunction;

DROP FUNCTION

studentdb=> df

                                    List of functions

 Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage

-------------+---------+------------------------+-----------------------------+--------+-------------------+---------------

(0 rows)

studentdb=>

1.3.11 d TableName命令

元命令d TableName的作用是查看某个表的信息

执行下面的命令,查看表instructor的信息:

d instructor

studentdb=> d instructor

           Table "public.instructor"

  Column   |         Type          | Modifiers

-----------+-----------------------+-----------

 id        | character varying(5)  | not null

 dept_name | character varying(20) |

 name      | character varying(20) | not null

 salary    | numeric(8,2)          |

Indexes:

    "instructor_pkey" PRIMARY KEY, btree (id) TABLESPACE student_ts

Foreign-key constraints:

    "fk_sys_c0011280" FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON UPDATE RESTRICT ON DELETE RESTRICT

Referenced by:

    TABLE "teaches" CONSTRAINT "fk_sys_c0011287" FOREIGN KEY (id) REFERENCES instructor(id) ON UPDATE RESTRICT ON DELETE RESTRICT

    TABLE "advisor" CONSTRAINT "fk_sys_c0011297" FOREIGN KEY (id) REFERENCES instructor(id) ON UPDATE RESTRICT ON DELETE RESTRICT

studentdb=>

可以看到,显示表的信息包括表的列名及数据类型、索引、外键、以及被哪个表引用。如果表不在数据库默认的表空间,还将显示表所在的表空间。这一点可以做个测试:

CREATE TABLESPACE test_ts RELATIVE LOCATION 'tablespace/test_ts1';

DROP TABLE IF EXISTS test;

CREATE TABLE test(col1 smallint) TABLESPACE test_ts;

d test

drop table test;

drop tablespace test_ts;

studentdb=> CREATE TABLESPACE test_ts RELATIVE LOCATION 'tablespace/test_ts1';

CREATE TABLESPACE

studentdb=> DROP TABLE IF EXISTS test;

NOTICE:  table "test" does not exist, skipping

DROP TABLE

studentdb=> CREATE TABLE test(col1 smallint) TABLESPACE test_ts;

CREATE TABLE

studentdb=> d test

      Table "public.test"

 Column |   Type   | Modifiers

--------+----------+-----------

 col1   | smallint |

Tablespace: "test_ts"      (因为表test不在数据库的默认表空间中,因此显示表test所在的表空间)

studentdb=> drop table test;

DROP TABLE

studentdb=> drop tablespace test_ts;

DROP TABLESPACE

studentdb=>

1.3.12 di IndexName命令

元命令di IndexName的作用是查看某个索引的信息

执行下面的元命令,查看当前数据库下有哪些索引:

di

studentdb=> di

                         List of relations

 Schema |      Name       | Type  |  Owner  |   Table    | Storage

--------+-----------------+-------+---------+------------+---------

 public | advisor_pkey    | index | student | advisor    |

 public | classroom_pkey  | index | student | classroom  |

 public | course_pkey     | index | student | course     |

 public | department_pkey | index | student | department |

 public | instructor_pkey  | index | student | instructor   |

 public | prereq_pkey     | index | student | prereq     |

 public | section_pkey    | index | student | section    |

 public | student_pkey    | index | student | student    |

 public | takes_pkey      | index | student | takes      |

 public | teaches_pkey    | index | student | teaches    |

 public | time_slot_pkey  | index | student | time_slot  |

(11 rows)

studentdb=>

执行下面的元命令,显示索引instructor_pkey的详细信息:

di instructor_pkey

studentdb=> di instructor_pkey

                         List of relations

 Schema |      Name       | Type  |  Owner  |   Table    | Storage

-------------+-----------------------------+-------+---------+------------+---------

 public | instructor_pkey | index | student | instructor |

(1 row)

studentdb=>

1.3.13 dx命令

元命令dx的作用是查看已安装的扩展程序信息

dx

studentdb=> dx

                     List of installed extensions

  Name   | Version |   Schema   |             Description            

----------------+------------+---------------------+-------------------------------------------------

 mot_fdw  | 1.0    | pg_catalog    | foreign-data wrapper for MOT access

 plpgsql    | 1.0    | pg_catalog    | PL/pgSQL procedural language

(2 rows)

studentdb=>

1.3.14 x命令

元命令x的语法: x [ on | off | auto ]

元命令dx的作用是设置语句的输出模式。默认情况下记录上按行的方式来显示的。如果执行元命令x on,则显示将按每条记录每列的方式来显示。这种方式在有些情况下很有用。

DROP TABLE IF EXISTS test;

create table test(id int,name varchar(20));

insert into test values(1,'zqf'),(2,'zfz');

select * from test;

x on

select * from test;

x off

DROP TABLE IF EXISTS test;

studentdb=> DROP TABLE IF EXISTS test;

DROP TABLE

studentdb=> create table test(id int,name varchar(20));

CREATE TABLE

studentdb=> insert into test values(1,'zqf'),(2,'zfz');

INSERT 0 2

studentdb=> select * from test;      默认情况下,查询语句的显示方式为行方式

 id | name

----+------

  1 | zqf

  2 | zfz

(2 rows)

studentdb=> x on                 修改显示方式为列方式

Expanded display is on.

studentdb=> select * from test;

-[ RECORD 1 ]

id   | 1

name | zqf

-[ RECORD 2 ]

id   | 2

name | zfz

studentdb=> x off                修改显示方式为行方式

Expanded display is off.

studentdb=> DROP TABLE IF EXISTS test;

DROP TABLE

studentdb=>

1.3.15 timing命令

元命令timing的语法: timing [ on | off ]

元命令timing的作用是,如果设置为on,将显示SQL语句的执行时间

select * from instructor where salary=80000;

timing on

select * from instructor where salary=80000;

timing off

studentdb=> select * from instructor where salary=80000;

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

studentdb=> timing on

Timing is on.

studentdb=> select * from instructor where salary=80000;

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

Time: 0.436 ms

studentdb=> timing off

Timing is off.

studentdb=>

1.3.16 h命令

元命令h的作用是获取SQL语句的帮助。例如我们想获取update语句的帮助信息:

h update

studentdb=> h update

Command:     UPDATE

Description: update rows of a table

Syntax:

UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]

    SET {column_name = { expression | DEFAULT } |

          ( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }

          }[, ...]

    [ FROM from_list] [ WHERE condition ]

    [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }];

studentdb=>

想获取insert语句的帮助信息:

h insert

studentdb=> h insert

Command:     INSERT

Description: create new rows in a table

Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]

INSERT INTO table_name [ ( column_name [, ...] ) ]

    { DEFAULT VALUES | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] | query }

    [ ON DUPLICATE KEY UPDATE { column_name = { expression | DEFAULT } } [, ...] ]

    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

studentdb=>

1.3.17 ?命令

元命令?的作用是获取gsql元命令的帮助

?

studentdb=> ?

General

  copyright             show FusionInsight LibrA usage and distribution terms

  g [FILE] or ;         execute query (and send results to file or |pipe)

  h(help) [NAME]              help on syntax of SQL commands, * for all commands

  parallel [on [num]|off] toggle status of execute (currently off)

  q                     quit gsql

Query Buffer

  e [FILE] [LINE]       edit the query buffer (or file) with external editor

  ef [FUNCNAME [LINE]]  edit function definition with external editor

  p                     show the contents of the query buffer

  r                     reset (clear) the query buffer

  w FILE                write query buffer to file

Input/Output

  copy ...              perform SQL COPY with data stream to the client host

  echo [STRING]         write string to standard output

  i FILE                execute commands from file

  i+ FILE KEY           execute commands from encrypted file

  ir FILE               as i, but relative to location of current script

  ir+ FILE KEY          as i+, but relative to location of current script

  o [FILE]              send all query results to file or |pipe

  qecho [STRING]        write string to query output stream (see o)

Informational

  (options: S = show system objects, + = additional detail)

  d[S+]                 list tables, views, and sequences

  d[S+]  NAME           describe table, view, sequence, or index

  da[S]  [PATTERN]      list aggregates

  db[+]  [PATTERN]      list tablespaces

  dc[S+] [PATTERN]      list conversions

  dC[+]  [PATTERN]      list casts

  dd[S]  [PATTERN]      show object descriptions not displayed elsewhere

  ddp    [PATTERN]      list default privileges

  dD[S+] [PATTERN]      list domains

  ded[+] [PATTERN]      list data sources

  det[+] [PATTERN]      list foreign tables

  des[+] [PATTERN]      list foreign servers

  deu[+] [PATTERN]      list user mappings

  dew[+] [PATTERN]      list foreign-data wrappers

  df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions

  dF[+]  [PATTERN]      list text search configurations

  dFd[+] [PATTERN]      list text search dictionaries

  dFp[+] [PATTERN]      list text search parsers

  dFt[+] [PATTERN]      list text search templates

  dg[+]  [PATTERN]      list roles

  di[S+] [PATTERN]      list indexes

  dl                    list large objects, same as lo_list

  dL[S+] [PATTERN]      list procedural languages

  dm[S+] [PATTERN]      list materialized views

  dn[S+] [PATTERN]      list schemas

  do[S]  [PATTERN]      list operators

  dO[S+] [PATTERN]      list collations

  dp     [PATTERN]      list table, view, and sequence access privileges

  drds [PATRN1 [PATRN2]] list per-database role settings

  ds[S+] [PATTERN]      list sequences

  dt[S+] [PATTERN]      list tables

  dT[S+] [PATTERN]      list data types

  du[+]  [PATTERN]      list roles

  dv[S+] [PATTERN]      list views

  dE[S+] [PATTERN]      list foreign tables

  dx[+]  [PATTERN]      list extensions

  l[+]                  list all databases

  sf[+] FUNCNAME        show a function's definition

  z      [PATTERN]      same as dp

Formatting

  a                     toggle between unaligned and aligned output mode

  C [STRING]            set table title, or unset if none

  f [STRING]            show or set field separator for unaligned query output

  H                     toggle HTML output mode (currently off)

  pset NAME [VALUE]     set table output option

                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|

                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})

  t [on|off]            show only rows (currently off)

  T [STRING]            set HTML <table> tag attributes, or unset if none

  x [on|off|auto]       toggle expanded output (currently off)

Connection

  c[onnect] [DBNAME|- USER|- HOST|- PORT|-]

                         connect to new database (currently "studentdb")

  encoding [ENCODING]   show or set client encoding

  conninfo              display information about current connection

Operating System

  cd [DIR]              change the current working directory

  setenv NAME [VALUE]   set or unset environment variable

  timing [on|off]       toggle timing of commands (currently off)

  ! [COMMAND]           execute command in shell or start interactive shell

Variables

  prompt [TEXT] NAME    prompt user to set internal variable

  set [NAME [VALUE]]    set internal variable, or list all if no parameters

  unset NAME            unset (delete) internal variable

Large Objects

  lo_export LOBOID FILE

  lo_import FILE [COMMENT]

  lo_list

  lo_unlink LOBOID      large object operations

studentdb=>

1.3.18 ! os_command命令

元命令! os_command的作用是在gsql中执行操作系统命令。例如:

! ls -l

studentdb=> ! ls -l

total 20

-rw------- 1 omm dbgrp  7545 Nov  1 16:08 create_db_tables.sql

-rw------- 1 omm dbgrp 10590 Nov  1 16:08 load_data.sql

studentdb=>

1.3.19 o  fileName命令

元命令o  fileName的作用是重定向输出到文件fileName。例如:

select * from instructor where salary=80000;

o myoutputfile

select * from instructor where salary=80000;

! cat myoutputfile

studentdb=> select * from instructor where salary=80000;

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

studentdb=> o myoutputfile

studentdb=> select * from instructor where salary=80000;

studentdb=> ! cat myoutputfile

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

studentdb=>

从上面的测试可以看出,o myoutputfile将输出重定向到文件myoutputfile中,而不是把查询结果显示在终端上。

1.3.20 i  file.sql命令

元命令i  file.sql的作用是在交互式gsql中,执行文件file.sql中的SQL语句。下面是一个示例。

用Linux用户omm,打开一个终端,执行下面的命令,生成一个测试用的SQL语句脚本:

cat > test.sql<<EOF

select * from instructor where salary=80000;

EOF

[omm@test ~]$ cat > test.sql<<EOF

> select * from instructor where salary=80000;

> EOF

[omm@test ~]$

登录到openGauss:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=>

执行下面的命令,运行sql语句脚本文件:

i test.sql

studentdb=> i test.sql

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng.   | Kim  | 80000.00

(2 rows)

studentdb=>

1.3.21 conninfo命令

元命令conninfo的作用是在gsql中,显示会话的连接信息

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

conninfo

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> conninfo

You are connected to database "studentdb" as user "student" on host "192.168.100.91" at port "26000".

studentdb=>

1.3.22 c[onnect] [DBNAME]命令

元命令 c[onnect] [DBNAME]的作用是在gsql中,切换连接的数据库

使用Linux用户omm,执行下面的命令和语句,进行测试:

gsql -d postgres -p 26000 -r

CREATE TABLESPACE test_ts RELATIVE LOCATION 'tablespace/test_ts1';

CREATE DATABASE testdb  WITH TABLESPACE = test_ts;

-- 切换连接到数据库testdb

c testdb

-- 切换连接到数据库studentdb

connect studentdb

DROP DATABASE testdb;

DROP TABLESPACE test_ts;

q

[omm@test ~]$ gsql -d postgres -p 26000 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type "help" for help.

postgres=# CREATE TABLESPACE test_ts RELATIVE LOCATION 'tablespace/test_ts1';

CREATE TABLESPACE

postgres=# CREATE DATABASE testdb  WITH TABLESPACE = test_ts;

CREATE DATABASE

postgres=# -- 切换连接到数据库testdb

postgres=# c testdb

Non-SSL connection (SSL connection is recommended when requiring high-security)

You are now connected to database "testdb" as user "omm".

testdb=# -- 切换连接到数据库studentdb

testdb=# connect studentdb

Non-SSL connection (SSL connection is recommended when requiring high-security)

You are now connected to database "studentdb" as user "omm".

studentdb=# DROP DATABASE testdb;

DROP DATABASE

studentdb=# DROP TABLESPACE test_ts;

DROP TABLESPACE

studentdb=# q

[omm@test ~]$

1.3.23 echo [string]命令

元命令echo [string]的作用是在标准输出上显式信息

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

echo Hello,openGauss from Huawei!

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> echo Hello,openGauss from Huawei!

Hello,openGauss from Huawei!

studentdb=>

1.3.24 q命令和快捷键ctrl+d

元命令q的作用是退出gsql。也可以使用快捷键ctrl+d来退出gsql。

q

studentdb=> q

[omm@test ~]$

1.4 gsql初始化文件.gsqlrc

环境变量PSQLRC用来设置.gsqlrc文件的目录位置。在Linux的终端上,使用用户omm执行下面的命令来设置PSQLRC环境变量:

export PLSQLRC=PathYourWantItToBe

如果没有设置PSQLRC环境变量,那么将默认读取Linux用户主目录下的.gsqlrc文件。

下面进行测试。首先不设置.gsqlrc文件,在gsql中运行下面的SQL语句:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

select * from instructor where salary=80000;

q

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> select * from instructor where salary=80000;

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

                               默认情况下不显示查询语句的执行时间

studentdb=> q

[omm@test ~]$

我们看到执行完SQL语句,不会显式该SQL语句的执行时间。

接下来在linux用户的主目录下创建如下的.gsqlrc文件:

cat >~/.gsqlrc<<EOF

timing on

EOF

[omm@test ~]$ cat >~/.gsqlrc<<EOF

> timing on             设置gsql在执行完一条查询语句后,显示查询语句的执行时间

> EOF

[omm@test ~]$

重新登录到openGauss数据库,并执行同样的SQL语句:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

select * from instructor where salary=80000;

q

rm ~/.gsqlrc

[omm@test ~]$ gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

Timing is on.      已经通过.gsqlrc文件设置了Timing变量为on,作用是显示查询语句的执行时机

gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:00:59 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

studentdb=> select * from instructor where salary=80000;

  id   | dept_name  | name  |  salary 

-------+------------+-------+----------

 76543 | Finance    | Singh | 80000.00

 98345 | Elec. Eng. | Kim   | 80000.00

(2 rows)

Time: 0.687 ms              执行完一条查询语句后,显示查询语句的执行时间

studentdb=> q

[omm@test ~]$ rm ~/.gsqlrc

[omm@test ~]$

以上的实验表明,gsql命令启动时,默认情况下,会自动运行主目录下的初始化文件.gsqlrc中的设置命令。

2.客户端安全

2.1配置客户端接入认证

参考:官方文档:07 开发者指南-5.1 客户端接入认证-5.1.1 配置客户端接入认证

实验:

  1. 以操作系统用户omm登录数据库主节点。
  2. 配置客户端认证方式,允许客户端以“jack”用户连接到本机,此处远程连接禁止使用“omm”用户(即数据库初始化用户)。

例如,下面示例中配置允许IP地址为192.168.0.1的客户端访问本机。

gs_guc set -N all -I all -h "host all jack 192.168.0.1/32 sha256"

postgres=# l

                          List of databases

   Name    | Owner | Encoding  | Collate | Ctype | Access privileges

-----------+-------+-----------+---------+-------+-------------------

 mydb      | omm   | GBK       | C       | C     |

 postgres  | omm   | SQL_ASCII | C       | C     |

 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +

           |       |           |         |       | omm=CTc/omm

 testdb    | jack  | SQL_ASCII | C       | C     |

 testdb1   | omm   | SQL_ASCII | C       | C     |

 testdb2   | omm   | SQL_ASCII | C       | C     |

 testdb3   | omm   | SQL_ASCII | C       | C     |

(8 rows)

[omm@node1 ~]$ gs_guc set -N all -I all -h "host all all 192.168.0.1/32 sha256"

Begin to perform the total nodes: 1.

Popen count is 1, Popen success count is 1, Popen failure count is 0.

Begin to perform gs_guc for datanodes.

Command count is 1, Command success count is 1, Command failure count is 0.

Total instances: 1. Failed instances: 0.

ALL: Success to perform gs_guc!

[omm@node1 ~]$ pg_om -t status

bash: pg_om: command not found...

[omm@node1 ~]$ gs_om -t status

-----------------------------------------------------------------------

cluster_name    : dbCluster

cluster_state   : Normal

redistributing  : No

-----------------------------------------------------------------------

1)、如果为了支持MD5和sha256,需要修改password_encryption_type参数:

 gs_guc reload -N all -I all -c "password_encryption_type=1"

默认是password_encryption_type=2

  1. 、postgresql.conf

# - Connection Settings -

listen_addresses = '192.168.0.11'               # what IP address(es) to listen on;

                                        # comma-separated list of addresses;

                                        # defaults to 'localhost'; use '*' for all

                                        # (change requires restart)

local_bind_address = '192.168.0.11'

port = 26000                            # (change requires restart)

2.2客户端验证

 

 

说明:

可以正常登录。

● 使用“jack”用户前,需先本地连接数据库,并在数据库中使用如下语句建立“jack”用

户:

postgres=# CREATE USER jack PASSWORD 'Test@123';

● -N all表示openGauss的所有主机。

● -I all表示主机的所有实例。

● -h表示指定需要在“pg_hba.conf”增加的语句。

● all表示允许客户端连接到任意的数据库。

● jack表示连接数据库的用户。

● 192.168.0.1/32表示只允许IP地址为192.168.0.1的主机连接。此处的IP地址不能为openGauss内 的IP,在使用过程中,请根据用户的网络进行配置修改。32表示子网掩码为1的位数,即

255.255.255.255

● sha256表示连接时jack用户的密码使用sha256算法加密。

这条命令在数据库主节点实例对应的“pg_hba.conf”文件中添加了一条规则,用于对

连接数据库主节点的客户端进行鉴定。

“pg_hba.conf”文件中的每条记录可以是下面四种格式之一,四种格式的参数说明请

参见5.1.2 配置文件参考。

local DATABASE USER METHOD [OPTIONS]

host DATABASE USER ADDRESS METHOD [OPTIONS]

hostssl DATABASE USER ADDRESS METHOD [OPTIONS]

hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]

因为认证时系统是为每个连接请求顺序检查“pg_hba.conf”里的记录的,所以这些记

录的顺序是非常关键的。

说明

在配置“pg_hba.conf”文件时,请依据通讯需求按照格式内容从上至下配置记录,优先级高的

需求需要配置在前面。openGauss和扩容配置的IP优先级最高,用户手动配置的IP请放在这二者

之后,如果已经进行的客户配置和扩容节点的IP在同一网段,请在扩容前删除,扩容成功后再进

行配置。

因此对于认证规则的配置建议如下:

● 靠前的记录有比较严格的连接参数和比较弱的认证方法。

● 靠后的记录有比较宽松的连接参数和比较强的认证方法。

说明

● 一个用户要想成功连接到特定的数据库,不仅需要通过pg_hba.conf中的规则检查,还必须

要有该数据库上的CONNECT权限。如果希望控制某些用户只能连接到指定数据库,赋予/撤 销CONNECT权限通常比在pg_hba.conf中设置规则更为简单。

● 对应openGauss外部客户端连接,trust为不安全的认证方式,请将认证方式设置为sha256。

【其他】

如果想要在服务器上,

[omm@node1 dn]$ gsql -d testdb  -p 26000 -U jack -h 192.168.0.11 -r

Password for user jack:

gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr  )

SSL connection (cipher: DHE-RSA-AES128-GCM-SHA256, bits: 128)

Type "help" for help.

testdb=>

需要修改cd /opt/huawei/install/data/dn

vim pg_hba.conf

host    testdb    jack    192.168.0.11/32    sha256

华为技术有限公司图册_360百科