postgresql 没有主机的 pg_hba.conf 条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1406025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
no pg_hba.conf entry for host
提问by
I get following error when I try to connect using DBI
当我尝试使用 DBI 连接时出现以下错误
DBI connect('database=chaosLRdb;host=192.168.0.1;port=5433','postgres',...)
failed: FATAL: no pg_hba.conf entry for host "192.168.0.1", user "postgres", database "chaosLRdb", SSL off
Here is my pg_hba.conf file:
这是我的 pg_hba.conf 文件：
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
host all postgres 127.0.0.1/32 trust
host all postgres 192.168.0.1/32 trust
host all all 192.168.0.1/32 trust
host all all 192.168.0.1/128 trust
host all all 192.168.0.1/32 md5
host chaosLRdb postgres 192.168.0.1/32 md5
local all all 192.168.0.1/32 trust
My perl code is
我的 perl 代码是
#!/usr/bin/perl-w
use DBI;
use FileHandle;
print "Start connecting to the DB...\n";
@ary = DBI->available_drivers(true);
%drivers = DBI->installed_drivers();
my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433", "postgres", "chaos123");
May I know what i miss here?
我可以知道我在这里想念什么吗？
回答by Gray
In your pg_hba.conf file, I see some incorrect and confusing lines:
在您的 pg_hba.conf 文件中，我看到一些不正确且令人困惑的行：
# fine, this allows all dbs, all users, to be trusted from 192.168.0.1/32
# not recommend because of the lax permissions
host all all 192.168.0.1/32 trust
# wrong, /128 is an invalid netmask for ipv4, this line should be removed
host all all 192.168.0.1/128 trust
# this conflicts with the first line
# it says that that the password should be md5 and not plaintext
# I think the first line should be removed
host all all 192.168.0.1/32 md5
# this is fine except is it unnecessary because of the previous line
# which allows any user and any database to connect with md5 password
host chaosLRdb postgres 192.168.0.1/32 md5
# wrong, on local lines, an IP cannot be specified
# remove the 4th column
local all all 192.168.0.1/32 trust
I suspect that if you md5'd the password, this might work if you trim the lines. To get the md5 you can use perl or the following shell script:
我怀疑如果你输入了密码，如果你修剪线条，这可能会起作用。要获取 md5，您可以使用 perl 或以下 shell 脚本：
echo -n 'chaos123' | md5sum
> d6766c33ba6cf0bb249b37151b068f10 -
So then your connect line would like something like:
那么你的连接线会像这样：
my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433",
"chaosuser", "d6766c33ba6cf0bb249b37151b068f10");
For more information, here's the documentation of postgres 8.X's pg_hba.conf file.
有关更多信息，请参阅postgres 8.X 的 pg_hba.conf 文件的文档。
回答by Hasan Tuna Oru?
If you can change this line:
如果您可以更改此行：
host all all 192.168.0.1/32 md5
With this:
有了这个：
host all all all md5
You can see if this solves the problem.
你可以看看这是否解决了问题。
But another consideration is your postgresql port(5432) is very open to password attacks with hackers (maybe they can brute force the password). You can change your postgresql port 5432 to '33333' or another value, so they can't know this configuration.
但另一个考虑是你的 postgresql 端口（5432）对黑客的密码攻击非常开放（也许他们可以暴力破解密码）。您可以将 postgresql 端口 5432 更改为“33333”或其他值，这样他们就无法知道此配置。
回答by adam
Your postgres server configuration seems correct
您的 postgres 服务器配置似乎正确
host all all 127.0.0.1/32 md5
host all all 192.168.0.1/32 trust
Test this by creating a specific user for that database
通过为该数据库创建特定用户来测试这一点
createuser -a -d -W -U postgres chaosuser
Then adjust your perl script to use the newly created user
然后调整您的 perl 脚本以使用新创建的用户
my $dbh = DBI->connect("DBI:PgPP:database=chaosLRdb;host=192.168.0.1;port=5433", "chaosuser", "chaos123");
回答by uma
To resolved this problem, you can try this.
要解决这个问题，你可以试试这个。
first you have find out your pg_hba.conf and write :
首先你要找到你的 pg_hba.conf 并写下：
local all all md5
after that restart pg server:
之后重新启动 pg 服务器：
postgresql restart
or
或者
sudo /etc/init.d/postgresql restart
回答by Guy Lowe
For those who are getting this error in DBeaver the solution was found hereat line:
对于那些在 DBeaver 中遇到此错误的人，可以在此处找到解决方案：
@lcustodio on the SSL page, set SSL mode: require and either leave the SSL Factory blank or use the org.postgresql.ssl.NonValidatingFactory
@lcustodio 在 SSL 页面上，设置 SSL 模式：要求并将 SSL 工厂留空或使用 org.postgresql.ssl.NonValidatingFactory
Under Network -> SSL tab I checked the Use SLL checkbox and set Advance -> SSL Mode = require and it now works.
在 Network -> SSL 选项卡下，我选中了 Use SLL 复选框并设置 Advance -> SSL Mode = require，它现在可以工作了。
回答by Yauhen
For those who have the similar problem trying to connect to local db and trying likecon = psycopg2.connect(database="my_db", user="my_name", password="admin"), try to pass the additional parameter, so the following saved me a day:con = psycopg2.connect(database="my_db", user="my_name", password="admin", host="localhost")
对于那些尝试连接到本地数据库并尝试类似问题的人con = psycopg2.connect(database="my_db", user="my_name", password="admin")，请尝试传递附加参数，因此以下内容为我节省了一天：con = psycopg2.connect(database="my_db", user="my_name", password="admin", host="localhost")
回答by Chirag Agrawal
To resolve this problem, you can try this.
要解决这个问题，你可以试试这个。
first, you have found out your pg_hba.conf by:
首先，您通过以下方式找到了 pg_hba.conf：
cd /etc/postgresql/9.5/main from your root directory
cd /etc/postgresql/9.5/main 从你的根目录
and open file using
并使用打开文件
sudo nano pg_hba.conf
then add this line:
然后添加这一行：
local all all md5
to your pg_hba.conf and then restart by using the command:
到您的 pg_hba.conf，然后使用以下命令重新启动：
sudo service postgresql restart
回答by Sobhan
in my case i just changed spring.postgresql.jdbc.urlthat contained IPV4, i changed it to 127.0.0.1
就我而言，我只是更改了包含 IPV4 的spring.postgresql.jdbc.url，我将其更改为 127.0.0.1
回答by shiddu mageppa
If you are getting an error like the one below:
如果您收到如下错误：
OperationalError: FATAL: no pg_hba.conf entry for host "your ipv6",
user "username", database "postgres", SSL off
then add an entry like the following, with your mac address.
然后使用您的 mac 地址添加如下所示的条目。
host all all [your ipv6]/128 md5
回答by Florin D
BTW, in my case it was that I needed to specify the user/pwd in the url, not as independent properties, they were ignored and my OS user was used to connect
顺便说一句，在我的情况下，我需要在 url 中指定用户/密码，而不是作为独立的属性，它们被忽略了，我的操作系统用户被用来连接
My config is in a WebSphere 8.5.5 server.xml file
我的配置位于 WebSphere 8.5.5 server.xml 文件中
<dataSource
jndiName="jdbc/tableauPostgreSQL"
type="javax.sql.ConnectionPoolDataSource">
<jdbcDriver
javax.sql.ConnectionPoolDataSource="org.postgresql.ds.PGConnectionPoolDataSource"
javax.sql.DataSource="org.postgresql.ds.PGPoolingDataSource"
libraryRef="PostgreSqlJdbcLib"/>
<properties
url="jdbc:postgresql://server:port/mydb?user=fred&password=secret"/>
</dataSource>
This would not work and was getting the error:
这行不通，并且出现错误：
<properties
user="fred"
password="secret"
url="jdbc:postgresql://server:port/mydb"/>
