无法通过 PHP 连接到 Postgres，但可以从不同机器上的命令行和 PgAdmin 连接 [英] Unable to connect to Postgres via PHP but can connect from command line and PgAdmin on different machine
问题描述
我进行了快速搜索(大约 30 分钟)并尝试了一些，但似乎没有任何效果.另请注意，我不是 Linux 专家(我可以做最基本的东西、简单的安装、配置等)，所以我的一些配置可能显然是错误的，但我只是没有看到！(随意更正以下任何配置)
I've had a quick search around (about 30 minutes) and tried a few bits, but nothing seems to work. Also please note I'm no Linux expert (I can do most basic stuff, simple installs, configurations etc) so some of the config I have may be obviously wrong, but I just don't see it! (feel free to correct any of the configs below)
我在 Red Hat Enterprise Linux Server 7.1 (Maipo) 机器上有一个正在运行的 PostgreSQL 9.3 实例.它还运行 SELinux 和 IPTables.
I have a running instance of PostgreSQL 9.3 on a Red Hat Enterprise Linux Server release 7.1 (Maipo) box. It's also running SELinux and IPTables.
# sample configuration for iptables service
# you can edit this manually or use system-config-firewall
# please do not ask us to add additional ports/services to this default configuration
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -p tcp --dport 443 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
PostgreSQL pg_hba.cong(删除所有评论)
# TYPE DATABASE USER ADDRESS METHOD
local all all ident
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host all all 0.0.0.0/0 md5
postgresql.conf(只改了监听地址)
listen_addresses = '*'
设置新用户
$ sudo -u postgres /usr/pgsql-9.3/bin/createuser -s "pgadmin"
$ sudo -u postgres /usr/pgsql-9.3/bin/createuser "webuser"
$ sudo -u postgres psql
postgres=# ALTER ROLE "pgadmin" WITH PASSWORD 'weakpassword';
ALTER ROLE
postgres=# ALTER ROLE "webuser" WITH PASSWORD 'anotherweakpassword';
ALTER ROLE
postgres=# \q
测试连接
psql -U [pgadmin|webuser] -h [localhost|127.0.0.1|hostname] -W postgres
Password for user [pgadmin|webuser]: [weakpassword|anotherweakpassword]
psql (9.3.7)
Type "help" for help.
postgres=# \q
如您所见，我在命令行上测试了 127.0.0.1、localhost 和主机名，以确保我可以通过两个不同的帐户使用所有三个标识符进行连接.
As you can see I tested 127.0.0.1, localhost and the hostname on the command line to make sure I could connect use all three identifiers with both different accounts.
我还使用 Windows 设备中的 PgAdmin 进行连接，它使用两个用户的主机名和 IP 地址进行连接.
I've also connected using PgAdmin from my windows box, and it connects using the hostname and ip address using both users.
当我尝试通过 Apache 从 PHP 连接时出现问题(如果我在命令行上运行相同的脚本，则不会发生)
The problem comes when I try to connect from PHP via Apache (it doesn't happen if I run the same script on the command line)
<?php
error_reporting( E_ALL );
ini_set('display_errors', '1');
$conn1 = pg_connect("host='localhost' port='5432' user='pgadmin' password='weakpassword' dbname='postgres'");
$conn2 = pg_connect("host='127.0.0.1' port='5432' user='pgadmin' password='weakpassword' dbname='postgres'");
$conn3 = pg_connect("host='localhost' port='5432' user='webuser' password='anotherweakpassword' dbname='postgres'");
$conn4 = pg_connect("host='127.0.0.1' port='5432' user='webuser' password='anotherweakpassword' dbname='postgres'");
$status1 = pg_connection_status( $conn1 );
$status2 = pg_connection_status( $conn2 );
$status3 = pg_connection_status( $conn3 );
$status4 = pg_connection_status( $conn4 );
# Check connection
if (
$status1 === false || $status1 === PGSQL_CONNECTION_BAD ||
$status2 === false || $status2 === PGSQL_CONNECTION_BAD ||
$status3 === false || $status3 === PGSQL_CONNECTION_BAD ||
$status4 === false || $status4 === PGSQL_CONNECTION_BAD
)
{
throw new Exception("I'm broken");
}
# Do a query
$res1 = pg_query( $conn1, "SELECT * FROM pg_type LIMIT 1" );
$res2 = pg_query( $conn2, "SELECT * FROM pg_type LIMIT 1" );
$res3 = pg_query( $conn3, "SELECT * FROM pg_type LIMIT 1" );
$res4 = pg_query( $conn4, "SELECT * FROM pg_type LIMIT 1" );
# Test one result.
$row1 = pg_fetch_row($res1);
$row2 = pg_fetch_row($res2);
$row3 = pg_fetch_row($res3);
$row4 = pg_fetch_row($res4);
echo $row1[0] . "\n";
echo $row2[0] . "\n";
echo $row3[0] . "\n";
echo $row4[0] . "\n";
在命令行上，我得到以下输出(如预期)
On the command line I get the following output (as expected)
bool
bool
bool
bool
但在浏览器中我得到以下内容
But in the browser I get the following
Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Permission denied Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? in /var/www/html/test.php on line 6
Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? in /var/www/html/test.php on line 7
Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Permission denied Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? in /var/www/html/test.php on line 8
Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? in /var/www/html/test.php on line 9
Fatal error: Uncaught exception 'Exception' with message 'I'm broken' in /var/www/html/test.php:25 Stack trace: #0 {main} thrown in /var/www/html/test.php on line 25
我有一种感觉，这与 IPTables 出于某种原因在通过 Apache 时不允许连接有关，但我很难过(我敢打赌这很简单)
I've got a feeling it's something to do with IPTables not allowing the connect when coming through Apache for some reason, but I'm stumped (I bet it's stupidly simple)
我认为这涵盖了一切......
I think that covers everything...
帮助我 Stack Overflow，你是我唯一的希望！
Help me Stack Overflow, you're my only hope!
推荐答案
OK... Answered... 是 SELinux 的问题.需要运行以下....
OK... Answered... Was a problem with SELinux. Needed to run the following....
setsebool -P httpd_can_network_connect_db on
此外，如果您需要检查 SELinux 是否导致问题，可以使用以下命令将其关闭
Also if you need to check if SELinux is causing issues it can be turned off with the following
setenforce 0
然后一旦完成
setenforce 1
无论如何，完成了……继续！
Anyways, done... onwards!
这篇关于无法通过 PHP 连接到 Postgres，但可以从不同机器上的命令行和 PgAdmin 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
