报错fe_sendauth: invalid authentication request from server: AUTH_REQ_SASL_CONT without AUTH_REQ_SASL
👉 本文共约898个字，系统预计阅读时间或需4分钟。
现象
使用psql客户端远程连接openGauss数据库，报错：
1 2 | [root@docker35 ~]# psql -d postgres -U lhr3 -h 192.168.66.35 -p25432 psql.bin: fe_sendauth: invalid authentication request from server: AUTH_REQ_SASL_CONT without AUTH_REQ_SASL |
如果使用gsql客户端远程连接openGauss数据库，报错：
1 2 3 4 | [omm@lhropengauss ~]$ gsql -d postgres -U 'lhr3' -h 172.17.0.6 -p5432 Password for user lhr3: gsql: FATAL: Invalid username/password,login denied. FATAL: Invalid username/password,login denied. |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
当然，密码肯定没有输错，所以，这里很奇怪。
解决
1 2 | -- 使用md5和sha256，否则不能登录 alter system set password_encryption_type=1; |
然后，新建用户就可以登陆了，不能使用系统用户，系统用户omm不支持远程登陆。
注意
1、在openGauss中，用户密码存储在系统表pg_authid中，为防止用户密码泄露，openGauss对用户密码进行加密存储，所采用的加密算法由配置参数password_encryption_type决定。
- 当参数password_encryption_type设置为0时，表示采用md5方式对密码加密。MD5加密算法安全性低，存在安全风险，不建议使用。
- 当参数password_encryption_type设置为1时，表示采用sha256和md5方式对密码加密。MD5加密算法安全性低，存在安全风险，不建议使用。
- 当参数password_encryption_type设置为2时，表示采用sha256方式对密码加密，为默认配置。
- 当参数password_encryption_type设置为3时，表示采用sm3方式对密码加密。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | C:\Users\lhrxxt>psql -U lhr -h 192.168.66.35 -p 25432 -d postgres Password for user lhr: psql (14.0, server 9.2.4) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=> select version(); version ------------------------------------------------------------------------------------------------------------------------------------------------------ (openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit (1 row) postgres=> select * from pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolmonitoradmin | roloperatoradmin | rolpolicyadmin ------------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+-----------------+------------------+---------------- gs_role_copy_files | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f gs_role_signal_backend | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f gs_role_tablespace | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f gs_role_replication | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f gs_role_account_lock | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f gs_role_pldebugger | f | t | f | f | f | f | f | f | f | -1 | | | | default_pool | f | 0 | | n | 0 | | | | f | f | f omm | t | t | t | t | t | t | t | t | t | -1 | sha2561d90d6387d532d9b7b017c6a38f416bcd4aa9378752ed491bff3237776dada868693784dc66481f4772b2823b68117cb423db08f4225490d16d74f35a41821eddf2dc3d17c5baa532c7bad9411afb2609a7145597c0aa4299dd10036647333d8ecdfecefade | | | default_pool | t | 0 | | n | 0 | | | | t | t | t lhr | f | t | f | f | f | t | f | f | t | -1 | sha256f1def11cbb6d6d90d0d1a548dd85f0ac4bd0ea179ff4b4b47b657e1fe870927604c24eaad4da75c30a6053b22ea3d545e788434e520c73017db731eebefc4d10a8139d220fb94a8038ba558ae7ac9f9d3e2651957c04e2cba2452be1ddd74c80md53e5c401ee2b9f28db1bb075b1b99e0adecdfecefade | | | default_pool | f | 0 | | n | | | | | f | f | f (8 rows) postgres=> select * from pg_settings where name like '%password%'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline --------------------------+---------+------+--------------------------------------------------------------+--------------------------------------------------------+-----------------------------------------------------------------------------------------------+---------+---------+--------------------+---------+---------+----------+----------+-----------+-------------------------------------+------------ modify_initial_password | on | | Connections and Authentication / Security and Authentication | modify the initial password of the initial user. | | sighup | bool | configuration file | | | | off | on | /opengauss/data/db1/postgresql.conf | 102 password_effect_time | 90 | | Connections and Authentication / Security and Authentication | password effective time. | | sighup | real | default | 0 | 999 | | 90 | 90 | | password_encryption_type | 1 | | Connections and Authentication / Security and Authentication | The encryption method of password. | This controls the encryption type of the password. A value of 2 uses the system default. | sighup | integer | configuration file | 0 | 3 | | 2 | 1 | /opengauss/data/db1/postgresql.conf | 108 password_lock_time | 1 | d | Connections and Authentication / Security and Authentication | password lock time | | sighup | real | default | 0 | 365 | | 1 | 1 | | password_max_length | 32 | | Connections and Authentication / Security and Authentication | max length of password. | | sighup | integer | default | 6 | 999 | | 32 | 32 | | password_min_digital | 0 | | Connections and Authentication / Security and Authentication | min number of digital character in password. | | sighup | integer | default | 0 | 999 | | 0 | 0 | | password_min_length | 8 | | Connections and Authentication / Security and Authentication | min length of password. | | sighup | integer | default | 6 | 999 | | 8 | 8 | | password_min_lowercase | 0 | | Connections and Authentication / Security and Authentication | min number of lower character in password. | | sighup | integer | default | 0 | 999 | | 0 | 0 | | password_min_special | 0 | | Connections and Authentication / Security and Authentication | min number of special character in password. | | sighup | integer | default | 0 | 999 | | 0 | 0 | | password_min_uppercase | 0 | | Connections and Authentication / Security and Authentication | min number of upper character in password. | | sighup | integer | default | 0 | 999 | | 0 | 0 | | password_notify_time | 7 | | Connections and Authentication / Security and Authentication | password deadline notice time. | | sighup | integer | default | 0 | 999 | | 7 | 7 | | password_policy | 0 | | Connections and Authentication / Security and Authentication | The password complexity-policy of the database system. | This controls the complexity-policy of database system. A value of 1 uses the system default. | sighup | integer | configuration file | 0 | 1 | | 1 | 0 | /opengauss/data/db1/postgresql.conf | 103 password_reuse_max | 0 | | Connections and Authentication / Security and Authentication | max times password can reuse. | | sighup | integer | default | 0 | 1000 | | 0 | 0 | | password_reuse_time | 60 | | Connections and Authentication / Security and Authentication | max days password can reuse. | | sighup | real | default | 0 | 3650 | | 60 | 60 | | (14 rows) |
2、在PostgreSQL中，用户密码也是存储在系统表pg_authid中，也有参数password_encryption来控制加密算法，默认为MD5算法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 64321 Password for user postgres: psql (14.0, server 13.4 (Debian 13.4-4.pgdg110+1)) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 13.4 (Debian 13.4-4.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) postgres=# select * from pg_settings where name like '%password%'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart ---------------------+---------+------+-------------------------------------------------+-------------------------------------------------+------------+---------+---------+---------+---------+---------+---------------------+----------+-----------+------------+------------+----------------- password_encryption | md5 | | Connections and Authentication / Authentication | Chooses the algorithm for encrypting passwords. | | user | enum | default | | | {md5,scram-sha-256} | md5 | md5 | | | f (1 row) postgres=# select * from pg_authid; oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil ------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+--------------- 3373 | pg_monitor | f | t | f | f | f | f | f | -1 | | 3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 | | 3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 | | 3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | | 4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 | | 4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 | | 4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 | | 4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 | | 10 | postgres | t | t | t | t | t | t | t | -1 | md5da3edeb741de62d06ab73785ed222494 | (9 rows) postgres=# show password_encryption; password_encryption --------------------- md5 (1 row) |
