如何在POSTGRESQL服务器中使用基于SCRAM-SHA-256的TLS配置用户身份验证?

clj7thdc  于 2023-06-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(211)

我创建了一个PostgreSQL 13.1集群,其中有2个备用和1个主。
在这里,我使用scram-sha-256作为客户端的身份验证模式,其中我的集群配置了TLS。
当我启动PostgreSQL服务器时,我得到了一个连续的fatal日志。日志为:

UTC [181] FATAL:  expected SASL response, got message type 88

postgresql.conf

wal_level = replica
max_wal_senders = 90
wal_keep_size = 64
wal_log_hints = on
hot_standby = on
password_encryption = scram-sha-256
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=<application_name> host=<host_name> password=<Postgres_password sslmode=verify-full sslrootcert=<path_to_ca_cert>/ca.crt'
promote_trigger_file = '/run_scripts/tmp/pg-failover-trigger'

...
<some other basic config>
...

pg_hba.conf

#TYPE      DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local      all             all                                     trust
# IPv4 local connections:
host         all             all             127.0.0.1/32            trust
# IPv6 local connections:
host         all             all             ::1/128                 scram-sha-256
local        replication     all                                     scram-sha-256
host         replication     all             127.0.0.1/32            scram-sha-256
host         replication     all             ::1/128                 scram-sha-256
host         all             all             0.0.0.0/0               scram-sha-256
host         replication     postgres        0.0.0.0/0               scram-sha-256
host         all             all             ::/0                    scram-sha-256
host         replication     postgres        ::/0                    scram-sha-256

可以为tls配置集群添加scram-sha-256吗?如果是的话,我该怎么做呢?
这是我作为客户端连接的方式。

$ export PGPASSWORD=<POSTGRES_PASSWORD>
$ export PGUSER=<POSTGRES_USER>
$ psql -w -h <hostname>

psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

我没有看到任何错误从我的客户端网站,但得到连续的致命日志。

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/pv/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
sh: locale: not found
2021-03-03 07:43:56.823 UTC [35] WARNING:  no usable system locales were found
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/pv/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2021-03-03 07:44:03.835 GMT [42] LOG:  skipping missing configuration file "/etc/config/user.conf"
2021-03-03 07:44:03.836 GMT [42] LOG:  skipping missing configuration file "/etc/config/user.conf"
2021-03-03 07:44:03.996 UTC [42] LOG:  starting PostgreSQL 13.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
2021-03-03 07:44:03.996 UTC [42] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-03-03 07:44:03.996 UTC [42] LOG:  listening on IPv6 address "::", port 5432
2021-03-03 07:44:04.073 UTC [42] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-03-03 07:44:04.187 UTC [43] LOG:  database system was shut down at 2021-03-03 07:43:59 UTC
2021-03-03 07:44:04.237 UTC [44] FATAL:  the database system is starting up
2021-03-03 07:44:04.281 UTC [42] LOG:  database system is ready to accept connections
 done
server started
ALTER ROLE

/run_scripts/role/start.sh: ignoring /var/initdb/*

waiting for server to shut down...2021-03-03 07:44:04.387 UTC [42] LOG:  received fast shutdown request
.2021-03-03 07:44:04.478 UTC [42] LOG:  aborting any active transactions
2021-03-03 07:44:04.480 UTC [42] LOG:  background worker "logical replication launcher" (PID 51) exited with exit code 1
2021-03-03 07:44:04.482 UTC [45] LOG:  shutting down
2021-03-03 07:44:05.169 UTC [56] FATAL:  the database system is shutting down
.2021-03-03 07:44:05.702 UTC [57] FATAL:  the database system is shutting down
2021-03-03 07:44:05.806 UTC [58] FATAL:  the database system is shutting down
2021-03-03 07:44:06.217 UTC [59] FATAL:  the database system is shutting down
..2021-03-03 07:44:07.648 UTC [60] FATAL:  the database system is shutting down
2021-03-03 07:44:07.853 UTC [61] FATAL:  the database system is shutting down
2021-03-03 07:44:08.163 UTC [42] LOG:  database system is shut down
 done
server stopped
2021-03-03 07:44:08.208 GMT [25] LOG:  skipping missing configuration file "/etc/config/user.conf"
2021-03-03 07:44:08.208 GMT [25] LOG:  skipping missing configuration file "/etc/config/user.conf"
2021-03-03 07:44:08.487 UTC [25] LOG:  starting PostgreSQL 13.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
2021-03-03 07:44:08.487 UTC [25] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-03-03 07:44:08.487 UTC [25] LOG:  listening on IPv6 address "::", port 5432
2021-03-03 07:44:08.688 UTC [25] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-03-03 07:44:08.895 UTC [67] LOG:  database system was shut down at 2021-03-03 07:44:07 UTC
2021-03-03 07:44:08.905 UTC [68] FATAL:  the database system is starting up
2021-03-03 07:44:08.945 UTC [69] FATAL:  the database system is starting up
2021-03-03 07:44:08.946 UTC [70] FATAL:  the database system is starting up
2021-03-03 07:44:08.991 UTC [25] LOG:  database system is ready to accept connections
2021-03-03 07:44:15.990 UTC [94] FATAL:  expected SASL response, got message type 88
2021-03-03 07:44:16.011 UTC [95] FATAL:  expected SASL response, got message type 88
2021-03-03 07:44:30.197 UTC [108] FATAL:  expected SASL response, got message type 88
2021-03-03 07:44:31.324 UTC [110] FATAL:  expected SASL response, got message type 88
2021-03-03 07:44:57.571 UTC [114] FATAL:  expected SASL response, got message type 88
2021-03-03 07:44:57.729 UTC [115] FATAL:  expected SASL response, got message type 88
2021-03-03 07:45:07.982 UTC [127] FATAL:  expected SASL response, got message type 88
2021-03-03 07:45:08.020 UTC [128] FATAL:  expected SASL response, got message type 88
2021-03-03 07:45:18.135 UTC [140] FATAL:  expected SASL response, got message type 88
2021-03-03 07:45:18.173 UTC [141] FATAL:  expected SASL response, got message type 88
3pvhb19x

3pvhb19x1#

问题出在我的go-client上,我试图用它来连接我的Postgres服务器。当我尝试使用https://github.com/lib/pq客户端包而不是https://github.com/go-pg/pg时,问题得到了解决。

相关问题