gpt4 book ai didi

sql - 我可以在逻辑复制中使用 .pgpass 吗?

转载 作者:行者123 更新时间:2023-11-29 12:41:00 29 4
gpt4 key购买 nike

我正在使用逻辑复制。我做了如下订阅。

=# CREATE SUBSCRIPTION mysub CONNECTION 'host=xxx.xxx.xxx.xxx port=5432 
user=postgres dbname=mydb password=<password>' PUBLICATION mypub;
NOTICE: created replication slot "mysub" on publisher
CREATE SUBSCRIPTION

但我想知道我是否可以使用 .pgpass 文件来提供密码。当然,我试过了。但它失败了,如下所示。

=# CREATE SUBSCRIPTION mysub CONNECTION 'host=xxx.xxx.xxx.xxx port=5432 
user=postgres dbname=mydb' PUBLICATION mypub;
ERROR: could not connect to the publisher: fe_sendauth: no password supplied

[我的.pgpass]

localhost:5432:postgres:postgres:<password>
localhost:5432:mydb:postgres:<password>
xxx.xxx.xxx.xxx:5432:mydb:postgres:<password>

这个 .pgpass 文件适用于 pgAgent。

我可以使用 .pgpass 文件进行逻辑复制吗?或者我应该在 CREATE 语句中写下我的密码吗?如果在 CREATE 命令中写入密码是唯一的答案,那么它安全吗?

最佳答案

https://www.postgresql.org/docs/10/static/sql-createsubscription.html

CONNECTION 'conninfo' The connection string to the publisher. For details see

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING

passfile

Specifies the name of the file used to store passwords (see Section 33.15). Defaults to ~/.pgpass

所以是的 - 它应该有效。让我们模拟一下。首先我特意用了bad passfile看看有没有反射(reflect)error:

t=# CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost port=5433 passfile=/tmp/p user=vao dbname=t' PUBLICATION mypub;
ERROR: could not connect to the publisher: fe_sendauth: no password supplied

不,不是,但检查日志确实:

-bash-4.2$ tail /pg/d10/log/postgresql-Tue.log | grep WARN | tail -n 1
WARNING: password file "/tmp/p" has group or world access; permissions should be u=rw (0600) or less

好的,尝试使用默认值:

t=# CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost port=5433 user=vao dbname=t' PUBLICATION mypub;
ERROR: could not connect to the publisher: fe_sendauth: no password supplied

而且这次连警告都没有!所以检查 chmod:

-bash-4.2$ ll ~/.pgpass
-r-------- 1 postgres postgres 1227 May 15 15:00 /home/vao/.pgpass

看起来不错,但是啊哈 - 此连接没有线路,因为下面要求输入密码:

-bash-4.2$ psql -h localhost -p 5433 -U vao t
Password for user vao:

所以:

echo '*:*:*:vao:blah' > ~/.pgpass
-bash-4.2$ psql -h localhost -p 5433 -U vao t
psql: FATAL: password authentication failed for user "vao"
password retrieved from file "/var/lib/pgsql93/.pgpass"

啊哈 - 现在它使用了它,所以回到 SQL:

t=# CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost port=5433 user=vao dbname=t' PUBLICATION mypub;
ERROR: could not connect to the publisher: FATAL: password authentication failed for user "vao"
password retrieved from file "/var/lib/pgsql93/.pgpass"

是的,您可以使用指定的和默认的 pgpassword 文件进行逻辑复制订阅

关于sql - 我可以在逻辑复制中使用 .pgpass 吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50236286/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com