Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 05:30 PM, John Iliffe wrote:

On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:



So the issue is in PHP via Apache using the socket, because if I
remember right you used localhost in the Apache/PHP combination and it
worked, correct?


Yes.

I think there is some confusion here, might be on my part, I don't know.

There is a network connection from 192.168.1.10 to 192.168.1.6 to Apache
and then there should be a connection from Apache on using localhost (or
127.0.0.1) to Postgresql.  So shouldn't that be sufficient?  Other than the
original error on my part, coding the server's external address
(192.168.1.6) in the db_connect() call which is now fixed, shouldn't the
pg_hba host address  line be 127.0.0.1/32 ?


Yes, Apache is connecting to Postgres on the same machine so localhost 
should be sufficient for IP purposes.


Not sure that it applies here, but what does ifconfig show?



Anyhow, that is working properly now.  The domain socket doesn't have an
explicit address (for 'local') as it is on the current machine as far as I
understand.

Am I correct?


Correct. The issue is then why psql and stand alone PHP can see the 
domain socket while PHP through Apache does not. Not sure why that is at 
the moment. Hmm, had a thought. What user is Apache running as and does 
that user have permissions on the socket file(s)?




John



Report back.


Based on the reference that Joe sent earlier, I do have a second
domain socket on /var/pgsql but the problem is how do I get PHP to
look there? There isn't any config file for mod_php and php-fpm has
one but the location of the domain socket is the default -
/tmp/.s...

I don't think this is the problem if this list unless someone
happens to know the solution.  If not, then thank you for all the
work, and especially for the promptness of the responses.   I'm not
at all sure that I could have figured this out by myself.

John





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[rob stone]

On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote:
> 
> > 
> > You have Apache, PHP, and Postgres all running on your LAN at
> > 192.168.1.6.
> > 
> > 
> > You are on 192.168.1.10.
> > 
> > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to
> run
> > that script?
> 
> testfcgi.php is in the document root of the default named virtual
> server.
> 

O.K.

So in sites-available, your conf file just tells Apache to run
testfcgi.php and nothing else?
There is no resolution required by obtaining the IP address from
/etc/hosts.

Are you able to put some trigger_error messages into testfcgi.php in
order to make sure Apache is running the correct program?

Cheers,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 20:07:01 Adrian Klaver wrote:
> On 04/09/2017 03:27 PM, rob stone wrote:
> > Hello John,
> > 
> > Just saw this message.
> > 
> >> Still set to the default:
> >> 
> >> #listen_addresses = 'localhost' # what IP address(es) to
> >> listen on;
> >> 
> >> # comma-separated list of
> >> 
> >> addresses;
> >> 
> >> # defaults to 'localhost';
> >> 
> >> use '*'
> >> for all
> >> 
> >> # (change requires restart)
> >> 
> >> #port = 5432# (change requires restart)
> >> 
> >> I did change the Unix domain socket directories:
> >> 
> >> #unix_socket_directories = '/tmp'   # comma-separated list of
> >> directories
> >> unix_socket_directories = '/tmp,/var/pgsql' # *changed from
> >> default
> >> #
> > 
> > Your set-up has Apache, PHP and Postgres all running from the same
> > machine.
> > So as far as running from there goes, it is "localhost".
> 
> The issue is not localhost, it is the case when John is trying to
> connect without a host specifier and therefore is trying to reach the
> Unix socket.
> 
> > There is no requirement to traverse a network. It is all on the same
> > physical machine.
> > 
> > Alter your postgresql.conf file and remove the hash so that:-
> > 
> > listen_addresses = 'localhost'
> > 
> > is explicitly defined. Alter pg_hba.conf so that localhost is declared
> > and let's see what happens.
> 
> It will be the same effect, the commented line is just showing that the
> default is 'localhost'. Though, John if you do decide to do this
> remember to restart the server to have the change take effect.

Yes, I learned that lesson years ago!  

I have been restarting both Apache and Postgresql and closed the browser on 
every change, and have rebooted a few times too, to ensure that all buffers 
are not serving stale images.  It only takes a minute and keeps a lot of 
egg off my face!

> 
> > Cheers,
> > Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:
> On 04/09/2017 02:35 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:
> >> On 04/09/2017 02:00 PM, John Iliffe wrote:
> >>> On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
>  Remember host != local
>  
>  host is for IP connections
>  
>  local is for socket connections
> >>> 
> >>> Yes, I had forgotten that for the moment.  I have the following line
> >>> in the 'local' section of the pg_hba.conf file:
> >>> 
> >>> local   all all
> >>> password
> >>> 
> >>> and this in the 'host' section
> >>> 
> >>> hostall all 127.0.0.1/32
> >>> password
> >>> 
> >>> and at the moment I can connect using this:
> >>> 
> >>> $db_handle = pg_connect('dbname=yrarc host=localhost port=5432
> >>> user=yrcro password=yrreadonly');
> >>> 
> >>> but NOT using this:
> >>> 
> >>> $db_handle = pg_connect('dbname=yrarc user=yrcro
> >>> password=yrreadonly');
> >>> 
> >>> so I have a problem with the domain sockets.
> >> 
> >> I don't think it has been asked and for the sake of completeness,
> >> what do you have listen_addresses set to in postgresql.conf?
> > 
> > Still set to the default:
> > 
> > #listen_addresses = 'localhost' # what IP address(es) to
> > listen on;
> > 
> > # comma-separated list of
> 
> Well that would explain why connecting via 192.1.168.x would not work,
> Postgres is only listening on the loopback interface:
> 
> https://www.postgresql.org/docs/9.6/static/runtime-config-connection.htm
> l "listen_addresses (string)
> 
>  Specifies the TCP/IP address(es) on which the server is to listen
> for connections from client applications. The value takes the form of a
> comma-separated list of host names and/or numeric IP addresses. The
> special entry * corresponds to all available IP interfaces. The entry
> 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening
> for all IPv6 addresses. If the list is empty, the server does not listen
> on any IP interface at all, in which case only Unix-domain sockets can
> be used to connect to it. The default value is localhost, which allows
> only local TCP/IP "loopback" connections to be made. While client
> authentication (Chapter 20) allows fine-grained control over who can
> access the server, listen_addresses controls which interfaces accept
> connection attempts, which can help prevent repeated malicious
> connection requests on insecure network interfaces. This parameter can
> only be set at server start.
> "
> 
> > addresses;
> > 
> > # defaults to 'localhost'; use
> > '*'
> > 
> > for all
> > 
> > # (change requires restart)
> > 
> > #port = 5432# (change requires restart)
> > 
> > I did change the Unix domain socket directories:
> > 
> > #unix_socket_directories = '/tmp'   # comma-separated list of
> > directories
> > unix_socket_directories = '/tmp,/var/pgsql' # *changed from
> > default #
> > 
> >> To solve this is going to require starting as close to the Postgres
> >> server as possible and use a consistent connection string between
> >> psql and your PHP code. For the time being I would leave the Apache
> >> server out of the loop as well as your workstation(as much as
> >> possible).
> >> 
> >> So:
> >> 
> >> 1) Log into the machine with the Postgres server.
> >> 
> >> 2) Using psql:
> >> 
> >> psql 'dbname=yrarc user=yrcro password=yrreadonly'
> > 
> > worked, no problem.  Connected to the database and allowed me to
> > select anything as expected.
> > 
> >> 3) Using a standalone PHP script:
> >> 
> >> $db_handle = pg_connect('dbname=yrarc user=yrcro
> >> password=yrreadonly')
> > 
> > Worked perfectly as a standalone PHP programme.  Connected and
> > retrieved a record from the database.
> 
> So the issue is in PHP via Apache using the socket, because if I
> remember right you used localhost in the Apache/PHP combination and it
> worked, correct?

Yes.

I think there is some confusion here, might be on my part, I don't know.

There is a network connection from 192.168.1.10 to 192.168.1.6 to Apache 
and then there should be a connection from Apache on using localhost (or 
127.0.0.1) to Postgresql.  So shouldn't that be sufficient?  Other than the 
original error on my part, coding the server's external address 
(192.168.1.6) in the db_connect() call which is now fixed, shouldn't the 
pg_hba host address  line be 127.0.0.1/32 ?  

Anyhow, that is working properly now.  The domain socket doesn't have an 
explicit address (for 'local') as it is on the current machine as far as I 
understand.  

Am I correct?

John
> 
> >> Report back.
> >> 
> >>> Based on the reference that Joe sent earlier, I do have a second
> >>> domain socket on /var/pgsql but the problem is how do I get PHP to
> >>> 

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 18:27:49 rob stone wrote:
Hi Rob:

Thanks for chiming in.

> Hello John,
> 
> Just saw this message.
> 
> > Still set to the default:
> > 
> > #listen_addresses = 'localhost' # what IP address(es) to
> > listen on;
> > # comma-separated list of 
> > addresses;
> > # defaults to 'localhost';
> > use '*' 
> > for all
> > # (change requires restart)
> > #port = 5432# (change requires restart)
> > 
> > I did change the Unix domain socket directories:
> > 
> > #unix_socket_directories = '/tmp'   # comma-separated list of 
> > directories
> > unix_socket_directories = '/tmp,/var/pgsql' # *changed from
> > default
> > # 
> 
> Your set-up has Apache, PHP and Postgres all running from the same
> machine.
> So as far as running from there goes, it is "localhost".
> There is no requirement to traverse a network. It is all on the same
> physical machine.
> 
> Alter your postgresql.conf file and remove the hash so that:-
> 
> listen_addresses = 'localhost'
> 
> is explicitly defined. Alter pg_hba.conf so that localhost is declared
> and let's see what happens.
Same as before; the TCP connection works as expected but the Unix domain 
socket reports 

---
Sun Apr 09 20:07:02.915744 2017] [php7:warn] [pid 27755:tid 
140325136111360] [client 192.168.1.10:36749] PHP Warning:  pg_connect(): 
Unable to connect to PostgreSQL server: could not connect to server: No 
such file or directory\n\tIs the server running locally and 
accepting\n\tconnections on Unix domain socket 
/tmp/.s.PGSQL.5432? in /httpd/iliffe/testfcgi.php on line 133
---

> 
> Cheers,
> Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 03:27 PM, rob stone wrote:

Hello John,

Just saw this message.




Still set to the default:

#listen_addresses = 'localhost' # what IP address(es) to
listen on;
# comma-separated list of
addresses;
# defaults to 'localhost';
use '*'
for all
# (change requires restart)
#port = 5432# (change requires restart)

I did change the Unix domain socket directories:

#unix_socket_directories = '/tmp'   # comma-separated list of
directories
unix_socket_directories = '/tmp,/var/pgsql' # *changed from
default
#




Your set-up has Apache, PHP and Postgres all running from the same
machine.
So as far as running from there goes, it is "localhost".


The issue is not localhost, it is the case when John is trying to 
connect without a host specifier and therefore is trying to reach the 
Unix socket.



There is no requirement to traverse a network. It is all on the same
physical machine.

Alter your postgresql.conf file and remove the hash so that:-

listen_addresses = 'localhost'

is explicitly defined. Alter pg_hba.conf so that localhost is declared
and let's see what happens.


It will be the same effect, the commented line is just showing that the 
default is 'localhost'. Though, John if you do decide to do this 
remember to restart the server to have the change take effect.




Cheers,
Rob




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 02:35 PM, John Iliffe wrote:

On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:

On 04/09/2017 02:00 PM, John Iliffe wrote:

On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:

Remember host != local

host is for IP connections

local is for socket connections


Yes, I had forgotten that for the moment.  I have the following line
in the 'local' section of the pg_hba.conf file:

local   all all
password

and this in the 'host' section

hostall all 127.0.0.1/32
password

and at the moment I can connect using this:

$db_handle = pg_connect('dbname=yrarc host=localhost port=5432
user=yrcro password=yrreadonly');

but NOT using this:

$db_handle = pg_connect('dbname=yrarc user=yrcro
password=yrreadonly');

so I have a problem with the domain sockets.


I don't think it has been asked and for the sake of completeness, what
do you have listen_addresses set to in postgresql.conf?


Still set to the default:

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of


Well that would explain why connecting via 192.1.168.x would not work, 
Postgres is only listening on the loopback interface:


https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html
"listen_addresses (string)

Specifies the TCP/IP address(es) on which the server is to listen 
for connections from client applications. The value takes the form of a 
comma-separated list of host names and/or numeric IP addresses. The 
special entry * corresponds to all available IP interfaces. The entry 
0.0.0.0 allows listening for all IPv4 addresses and :: allows listening 
for all IPv6 addresses. If the list is empty, the server does not listen 
on any IP interface at all, in which case only Unix-domain sockets can 
be used to connect to it. The default value is localhost, which allows 
only local TCP/IP "loopback" connections to be made. While client 
authentication (Chapter 20) allows fine-grained control over who can 
access the server, listen_addresses controls which interfaces accept 
connection attempts, which can help prevent repeated malicious 
connection requests on insecure network interfaces. This parameter can 
only be set at server start.

"


addresses;
# defaults to 'localhost'; use '*'
for all
# (change requires restart)
#port = 5432# (change requires restart)

I did change the Unix domain socket directories:

#unix_socket_directories = '/tmp'   # comma-separated list of
directories
unix_socket_directories = '/tmp,/var/pgsql' # *changed from default
#



To solve this is going to require starting as close to the Postgres
server as possible and use a consistent connection string between psql
and your PHP code. For the time being I would leave the Apache server
out of the loop as well as your workstation(as much as possible).

So:

1) Log into the machine with the Postgres server.

2) Using psql:

psql 'dbname=yrarc user=yrcro password=yrreadonly'

worked, no problem.  Connected to the database and allowed me to select
anything as expected.



3) Using a standalone PHP script:

$db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')


Worked perfectly as a standalone PHP programme.  Connected and retrieved a
record from the database.


So the issue is in PHP via Apache using the socket, because if I 
remember right you used localhost in the Apache/PHP combination and it 
worked, correct?





Report back.


Based on the reference that Joe sent earlier, I do have a second
domain socket on /var/pgsql but the problem is how do I get PHP to
look there? There isn't any config file for mod_php and php-fpm has
one but the location of the domain socket is the default -
/tmp/.s...

I don't think this is the problem if this list unless someone happens
to know the solution.  If not, then thank you for all the work, and
especially for the promptness of the responses.   I'm not at all sure
that I could have figured this out by myself.

John





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 17:39:30 rob stone wrote:
> Hello John,
> 
> > > 
> > >
> > > > PHP and Postgresql are both running on same box.  It does have
> > 
> > two
> > 
> > > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme
> > 
> > is
> > 
> > > > available on either.  The reference to 192.168.1.10 is the client
> > > > machine, in this case my workstation, which is 192.168.1.10.
> > >
> > > 
> > >
> > > Here is the error message you said came from the box running PHP:
> > > >> > --
> > > >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> > > >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> > > >> > pg_connect(): Unable to connect to PostgreSQL server: could
> > 
> > not
> > 
> > > >> > connect to server: Connection refused\n\tIs the server running
> > 
> > on
> > 
> > > >> > host 192.168.1.6 and accepting\n\tTCP/IP
> > 
> > connections on
> > 
> > > >> > port 5432? in
> > > >> > /httpd/iliffe/testfcgi.php on line 132
> > > >> > ---
> > >
> > > 
> 
> This is a tad confusing to me.
> 
> You have Apache, PHP, and Postgres all running on your LAN at
> 192.168.1.6.
> 
> 
> You are on 192.168.1.10.
> 
> Your NOT doing "php -f testfcgi.php", so how does Apache "know" to run
> that script?

testfcgi.php is in the document root of the default named virtual server.
> 
> Cheers,
> Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[rob stone]

Hello John,

Just saw this message.


> 
> Still set to the default:
> 
> #listen_addresses = 'localhost' # what IP address(es) to
> listen on;
>     # comma-separated list of 
> addresses;
>     # defaults to 'localhost';
> use '*' 
> for all
>     # (change requires restart)
> #port = 5432    # (change requires restart)
> 
> I did change the Unix domain socket directories:
> 
> #unix_socket_directories = '/tmp'   # comma-separated list of 
> directories
> unix_socket_directories = '/tmp,/var/pgsql' # *changed from
> default
> # 
> 
> 

Your set-up has Apache, PHP and Postgres all running from the same
machine.
So as far as running from there goes, it is "localhost".
There is no requirement to traverse a network. It is all on the same
physical machine.

Alter your postgresql.conf file and remove the hash so that:-

listen_addresses = 'localhost'

is explicitly defined. Alter pg_hba.conf so that localhost is declared
and let's see what happens.

Cheers,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[rob stone]

Hello John,


> > 
> > > PHP and Postgresql are both running on same box.  It does have
> two
> > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme
> is
> > > available on either.  The reference to 192.168.1.10 is the client
> > > machine, in this case my workstation, which is 192.168.1.10.
> > 
> > Here is the error message you said came from the box running PHP:
> > >> > --
> > >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> > >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> > >> > pg_connect(): Unable to connect to PostgreSQL server: could
> not
> > >> > connect to server: Connection refused\n\tIs the server running
> on
> > >> > host 192.168.1.6 and accepting\n\tTCP/IP
> connections on
> > >> > port 5432? in
> > >> > /httpd/iliffe/testfcgi.php on line 132
> > >> > ---
> > 
> 

This is a tad confusing to me.

You have Apache, PHP, and Postgres all running on your LAN at
192.168.1.6.


You are on 192.168.1.10.

Your NOT doing "php -f testfcgi.php", so how does Apache "know" to run
that script?

Cheers,
Rob






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 17:02:47 Adrian Klaver wrote:
> On 04/09/2017 02:00 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> >> Remember host != local
> >> 
> >> host is for IP connections
> >> 
> >> local is for socket connections
> > 
> > Yes, I had forgotten that for the moment.  I have the following line
> > in the 'local' section of the pg_hba.conf file:
> > 
> > local   all all
> > password
> > 
> > and this in the 'host' section
> > 
> > hostall all 127.0.0.1/32   
> > password
> > 
> > and at the moment I can connect using this:
> > 
> > $db_handle = pg_connect('dbname=yrarc host=localhost port=5432
> > user=yrcro password=yrreadonly');
> > 
> > but NOT using this:
> > 
> > $db_handle = pg_connect('dbname=yrarc user=yrcro
> > password=yrreadonly');
> > 
> > so I have a problem with the domain sockets.
> 
> I don't think it has been asked and for the sake of completeness, what
> do you have listen_addresses set to in postgresql.conf?

Still set to the default:

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of 
addresses;
# defaults to 'localhost'; use '*' 
for all
# (change requires restart)
#port = 5432# (change requires restart)

I did change the Unix domain socket directories:

#unix_socket_directories = '/tmp'   # comma-separated list of 
directories
unix_socket_directories = '/tmp,/var/pgsql' # *changed from default
# 

> 
> To solve this is going to require starting as close to the Postgres
> server as possible and use a consistent connection string between psql
> and your PHP code. For the time being I would leave the Apache server
> out of the loop as well as your workstation(as much as possible).
> 
> So:
> 
> 1) Log into the machine with the Postgres server.
> 
> 2) Using psql:
> 
> psql 'dbname=yrarc user=yrcro password=yrreadonly'
worked, no problem.  Connected to the database and allowed me to select 
anything as expected.

> 
> 3) Using a standalone PHP script:
> 
> $db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')
> 
Worked perfectly as a standalone PHP programme.  Connected and retrieved a 
record from the database.

> Report back.
> 
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there? There isn't any config file for mod_php and php-fpm has
> > one but the location of the domain socket is the default -
> > /tmp/.s...
> > 
> > I don't think this is the problem if this list unless someone happens
> > to know the solution.  If not, then thank you for all the work, and
> > especially for the promptness of the responses.   I'm not at all sure
> > that I could have figured this out by myself.
> > 
> > John


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Are multiple array_aggs going to be in the same order?

[Guyren Howe]

If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the ARRAY_AGGs 
be guaranteed to have entries in the same (ie corresponding) order?

eg

SELECT
u.name,
ARRAY_AGG(o.order_date) AS order_dates,
ARRAY_AGG(o.order_total) AS order_totals
FROM
user u JOIN
orders o USING (user_id)
GROUP BY
u.user_id

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 02:00 PM, John Iliffe wrote:

On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:



Remember host != local

host is for IP connections

local is for socket connections


Yes, I had forgotten that for the moment.  I have the following line in the
'local' section of the pg_hba.conf file:

local   all all password

and this in the 'host' section

hostall all 127.0.0.1/32password

and at the moment I can connect using this:

$db_handle = pg_connect('dbname=yrarc host=localhost port=5432 user=yrcro
password=yrreadonly');

but NOT using this:

$db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly');

so I have a problem with the domain sockets.


I don't think it has been asked and for the sake of completeness, what 
do you have listen_addresses set to in postgresql.conf?


To solve this is going to require starting as close to the Postgres 
server as possible and use a consistent connection string between psql 
and your PHP code. For the time being I would leave the Apache server 
out of the loop as well as your workstation(as much as possible).


So:

1) Log into the machine with the Postgres server.

2) Using psql:

psql 'dbname=yrarc user=yrcro password=yrreadonly'

3) Using a standalone PHP script:

$db_handle = pg_connect('dbname=yrarc user=yrcro password=yrreadonly')

Report back.




Based on the reference that Joe sent earlier, I do have a second domain
socket on /var/pgsql but the problem is how do I get PHP to look there?
There isn't any config file for mod_php and php-fpm has one but the location
of the domain socket is the default - /tmp/.s...

I don't think this is the problem if this list unless someone happens to
know the solution.  If not, then thank you for all the work, and especially
for the promptness of the responses.   I'm not at all sure that I could
have figured this out by myself.

John






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 15:38:10 Adrian Klaver wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> >> On 04/09/2017 11:33 AM, John Iliffe wrote:
> >>> On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
>  On 04/08/2017 01:23 PM, John Iliffe wrote:
> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >> So what if you change the connection to use -h localhost?
> > 
> > Can you please expand on that request?  I'm not sure where you
> > want me to put that directive.  I'm using the mod_php module in
> > Apache.
>  
>  See the second example here:
>  
>  http://php.net/manual/en/function.pg-connect.php
>  
>  8<-
>  $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
>  // connect to a database named "mary" on "localhost" at port "5432"
>  8<-
>  
>  That will try to use a tcp connection on localhost instead of a
>  unix socket.
> >>> 
> >>> Thanks Joe.  I Changed the pg_connect line in the script to:
> >>> 
> >>> --
> >>> 
> >>>   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >>> 
> >>> user= password=xx');
> >>> ---
> >>> 
> >>> Even though "localhost" is in the /etc/hosts file the lookup failed
> >>> to
> >> 
> >>> resolve so I provided the full IP address.  The error from Apache is:
> >> You have some very odd issues with your machine...
> > 
> > No, Fedora/Red Hat has made a mess!  I just installed everything
> > (APache, Postgresql, PHP, OpenSSL from source as I always have over a
> > period of more than 15 years.  This is the first time I have run into
> > this sort of problem and it seems to be related to systemctl's unit
> > files.
> > 
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system. Very nice on theory but nothing on the details or "What is
> > required?/How do I do it?"
> > 
> > In a small company like mine, we are all multi-tasked and having a
> > specialist for everything, like a system programmer skilled in Linux,
> > is just not on.  Things have to work right out of the box.  Server
> > administration, system operations, etc, is just not my core skill,
> > and I depend heavily on the open source community for help (like you
> > at the moment).
> > 
> >>> --
> >>> [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> >>> 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> >>> pg_connect(): Unable to connect to PostgreSQL server: could not
> >>> connect to server: Connection refused\n\tIs the server running on
> >>> host 192.168.1.6 and accepting\n\tTCP/IP connections on
> >>> port 5432? in
> >>> /httpd/iliffe/testfcgi.php on line 132
> >>> ---
> >>> 
> >>> PHP does not show anything in its log.
> >>> 
>  Another question I don't believe has been asked is, what does your
>  pg_hba.conf look like?
> >>> 
> >>> Note here that I have deleted a number of production users and the
> >>> associated databases from the file shown below for security reasons.
> >>> The user marked "XXX" has a real name but isn't the one we are
> >>> using to connect to the database, so the active line should be the
> >>> "local all all password" line.  The UID being used to connect IS in
> >>> the password list and PSQL can still connect OK.  The yrarc database
> >>> does exist and contains several tables.
> >>> 
> >>> --
> >>> # TYPE  DATABASEUSERADDRESS
> >>> METHOD
> > 
> > i> >
> > 
> >>> # "local" is for Unix domain socket connections only
> >>> #local   all all md5
> >>> 
> >>> local   yrarc  XXX 
> >>> trust local   all all
> >>> password #local   all all
> >>> 
> >>> trust # IPv4 local connections:
> >>> hostall all 127.0.0.1/32   
> >>> trust -
> >> 
> >> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> >> surprising that cannot connect. You need something like:
> >> 
> >> # only allow connections from one host using tcp
> >> hostall all 192.168.1.6/32md5
> >> 
> >> - or maybe -
> >> 
> >> # only allow connections from same subnet using tcp
> >> hostall all 192.168.1.0/24md5
> > 
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it.  I did try it though
> > and set it to "trust" to avoid any problems with permissions in
> > Postgresql.   I then stopped and restarted both Postgres and Apache
> > and still get the same 

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 15:41:12 Joe Conway wrote:
> On 04/09/2017 12:37 PM, John Iliffe wrote:
> >> > Thanks Joe.  I Changed the pg_connect line in the script to:
> >> >   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> >> > 
> >> > user= password=xx');
> > 
> > If I may be permitted a rant at this point, the Fedora documentation
> > is almost useless for SELinux and much of the underlying operating
> > system.
> 
> Maybe so, but as I said earlier if you are in permissive, then blaming
> any of these issues on selinux is wrong -- selinux does no enforcement
> in permissive.
> 
> >> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> >> surprising that cannot connect. You need something like:
> >> 
> >> # only allow connections from one host using tcp
> >> hostall all 192.168.1.6/32md5
> >> 
> >> - or maybe -
> >> 
> >> # only allow connections from same subnet using tcp
> >> hostall all 192.168.1.0/24md5
> > 
> > I don't think I should need that since httpd/mod_php is on the same
> > machine so should be 127.0.0.1 should cover it.
> 
> No, not if your connection string is
> 
>  'dbname=yrarc host=192.168.1.6 port=5432 user= password=xx'

I'm not sure why "localhost" didn't work on the first pass; I just rebooted, 
changed the connection string to:  

$db_handle = pg_connect('dbname=yrarc host=localhost port=5432 
user= password=xxx');

and got a connection.  So, it looks like I can connect using TCP on the 
loopback.   Still doesn't work on the Unix domain socket though. 

> 
> as you said it was. In this case you must have one of the two pg_hba
> lines above or something more broad. With that connection string you are
> not connecting to localhost, you are connecting to 192.168.1.6 and there
> MUST be a pg_hba line to support it.
> 
> > "trust" to avoid any problems with permissions in Postgresql.
> 
> Trust has nothing whatsoever to do with the problems you have shown us
> so far.

No, I agree with you, but I just didn't want to open another can of worms.  
One of the problems here is too many changes and too many variables.  Now 
that I can connect I changed it back to "password" and everything still 
works.  
> 
> > PHP and Postgresql are both running on same box.  It does have two
> > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is
> > available on either.  The reference to 192.168.1.10 is the client
> > machine, in this case my workstation, which is 192.168.1.10.
> 
> Here is the error message you said came from the box running PHP:
> >> > --
> >> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> >> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
> >> > pg_connect(): Unable to connect to PostgreSQL server: could not
> >> > connect to server: Connection refused\n\tIs the server running on
> >> > host 192.168.1.6 and accepting\n\tTCP/IP connections on
> >> > port 5432? in
> >> > /httpd/iliffe/testfcgi.php on line 132
> >> > ---
> 
No, the workstation doesn't have PHP installed on it.  

This message is coming from Apache's error log on 192.168.1.6.  Apache logs 
the source address of the client (ie my workstation at 192.168.1.10) and 
then follows with the PHP error message "PHP Warning: pg_connect(): Unable 
to connect to PostgreSQL server: could not connect to server..."

> Now, if your workstation is 192.168.1.10, then I can only conclude that
> you are running PHP on your workstation and *not* on the box with
> Postgres which you say uses 192.168.1.6 and 192.168.1.7.
> 
> Joe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[Joe Conway]

On 04/09/2017 12:37 PM, John Iliffe wrote:
>> > Thanks Joe.  I Changed the pg_connect line in the script to:
>> >   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
>> > user= password=xx');

> If I may be permitted a rant at this point, the Fedora documentation is 
> almost useless for SELinux and much of the underlying operating system. 

Maybe so, but as I said earlier if you are in permissive, then blaming
any of these issues on selinux is wrong -- selinux does no enforcement
in permissive.

>> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
>> surprising that cannot connect. You need something like:
>> 
>> # only allow connections from one host using tcp
>> hostall all 192.168.1.6/32md5
>> 
>> - or maybe -
>> 
>> # only allow connections from same subnet using tcp
>> hostall all 192.168.1.0/24md5
>> 
> I don't think I should need that since httpd/mod_php is on the same machine 
> so should be 127.0.0.1 should cover it.

No, not if your connection string is

 'dbname=yrarc host=192.168.1.6 port=5432 user= password=xx'

as you said it was. In this case you must have one of the two pg_hba
lines above or something more broad. With that connection string you are
not connecting to localhost, you are connecting to 192.168.1.6 and there
MUST be a pg_hba line to support it.

> "trust" to avoid any problems with permissions in Postgresql.

Trust has nothing whatsoever to do with the problems you have shown us
so far.

> PHP and Postgresql are both running on same box.  It does have two 
> interfaces, 192.168.1.6 and 192.168.1.7, and the test programme is 
> available on either.  The reference to 192.168.1.10 is the client machine, 
> in this case my workstation, which is 192.168.1.10.

Here is the error message you said came from the box running PHP:

>> > --
>> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
>> > 139671464015616] [client 192.168.1.10:59260] PHP Warning:
>> > pg_connect(): Unable to connect to PostgreSQL server: could not
>> > connect to server: Connection refused\n\tIs the server running on
>> > host 192.168.1.6 and accepting\n\tTCP/IP connections on
>> > port 5432? in
>> > /httpd/iliffe/testfcgi.php on line 132
>> > ---

Now, if your workstation is 192.168.1.10, then I can only conclude that
you are running PHP on your workstation and *not* on the box with
Postgres which you say uses 192.168.1.6 and 192.168.1.7.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 12:37 PM, John Iliffe wrote:

On Sunday 09 April 2017 14:34:01 Joe Conway wrote:

On 04/09/2017 11:33 AM, John Iliffe wrote:

On Saturday 08 April 2017 18:10:35 Joe Conway wrote:

On 04/08/2017 01:23 PM, John Iliffe wrote:

On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:

So what if you change the connection to use -h localhost?


Can you please expand on that request?  I'm not sure where you want
me to put that directive.  I'm using the mod_php module in Apache.


See the second example here:

http://php.net/manual/en/function.pg-connect.php

8<-
$dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
// connect to a database named "mary" on "localhost" at port "5432"
8<-

That will try to use a tcp connection on localhost instead of a unix
socket.


Thanks Joe.  I Changed the pg_connect line in the script to:

--

  $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432

user= password=xx');
---

Even though "localhost" is in the /etc/hosts file the lookup failed to



resolve so I provided the full IP address.  The error from Apache is:

You have some very odd issues with your machine...


No, Fedora/Red Hat has made a mess!  I just installed everything (APache,
Postgresql, PHP, OpenSSL from source as I always have over a period of more
than 15 years.  This is the first time I have run into this sort of problem
and it seems to be related to systemctl's unit files.

If I may be permitted a rant at this point, the Fedora documentation is
almost useless for SELinux and much of the underlying operating system.
Very nice on theory but nothing on the details or "What is required?/How do
I do it?"

In a small company like mine, we are all multi-tasked and having a
specialist for everything, like a system programmer skilled in Linux, is
just not on.  Things have to work right out of the box.  Server
administration, system operations, etc, is just not my core skill, and I
depend heavily on the open source community for help (like you at the
moment).


--
[Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
139671464015616] [client 192.168.1.10:59260] PHP Warning:
pg_connect(): Unable to connect to PostgreSQL server: could not
connect to server: Connection refused\n\tIs the server running on
host 192.168.1.6 and accepting\n\tTCP/IP connections on
port 5432? in
/httpd/iliffe/testfcgi.php on line 132
---

PHP does not show anything in its log.


Another question I don't believe has been asked is, what does your
pg_hba.conf look like?


Note here that I have deleted a number of production users and the
associated databases from the file shown below for security reasons.
The user marked "XXX" has a real name but isn't the one we are
using to connect to the database, so the active line should be the
"local all all password" line.  The UID being used to connect IS in
the password list and PSQL can still connect OK.  The yrarc database
does exist and contains several tables.

--
# TYPE  DATABASEUSERADDRESS METHOD

i> >

# "local" is for Unix domain socket connections only
#local   all all md5

local   yrarc  XXX  trust
local   all all
password #local   all all
trust # IPv4 local connections:
hostall all 127.0.0.1/32trust
-


You have no pg_hba.conf rule for host=192.168.1.6 so it is not
surprising that cannot connect. You need something like:

# only allow connections from one host using tcp
hostall all 192.168.1.6/32md5

- or maybe -

# only allow connections from same subnet using tcp
hostall all 192.168.1.0/24md5


I don't think I should need that since httpd/mod_php is on the same machine
so should be 127.0.0.1 should cover it.  I did try it though and set it to
"trust" to avoid any problems with permissions in Postgresql.   I then
stopped and restarted both Postgres and Apache and still get the same error
in the log from pg_connect asking whether the socket is available.


Remember host != local

host is for IP connections

local is for socket connections

So if you set up a host line pointing to 127.0.0.1 and connected without 
using host in the connection string the 127.0.0.1 host line will not be 
used, instead the first matching local line will. Furthermore in your 
examples when you did connect using host= you used an IP that was not 
127.0.0.1, so the connection would not use the 127.0.0.1 line anyway. 
That is why I made the suggestion to use host=localhost or if you want 
host=127.0.0.1 .





I just noticed in the message above "client 

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 15:05:18 Adrian Klaver wrote:
> On 04/09/2017 11:33 AM, John Iliffe wrote:
> > On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >>> On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
>  So what if you change the connection to use -h localhost?
> >>> 
> >>> Can you please expand on that request?  I'm not sure where you want
> >>> me to put that directive.  I'm using the mod_php module in Apache.
> >> 
> >> See the second example here:
> >> 
> >> http://php.net/manual/en/function.pg-connect.php
> >> 
> >> 8<-
> >> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >> // connect to a database named "mary" on "localhost" at port "5432"
> >> 8<-
> >> 
> >> That will try to use a tcp connection on localhost instead of a unix
> >> socket.
> > 
> > Thanks Joe.  I Changed the pg_connect line in the script to:
> > 
> > --
> > 
> >   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> > 
> > user= password=xx');
> > ---
> 
> So is this on the machine that has the Postgres server?

Yes, the machine has two addresses, 192.168.1.6 and 192.168.1.7

> 
> If not change to that machine and use host=localhost, otherwise just
> change to host=localhost.
> 
> > Even though "localhost" is in the /etc/hosts file the lookup failed to
> 
> > resolve so I provided the full IP address.  The error from Apache is:
> Can you show us the /etc/host file?

--
127.0.0.1   localhost localhost.localdomain localhost4 
localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 
localhost6.localdomain6

192.168.1.1 default

--
I added the blank lines above because the mail client had run them 
together.  The real file doesn't have any blank lines in it between the host 
definitions.

192.168.1.1 is the default Internet gateway.  This file was generated by the 
Fedora installation programme.

John



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 14:34:01 Joe Conway wrote:
> On 04/09/2017 11:33 AM, John Iliffe wrote:
> > On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> >> On 04/08/2017 01:23 PM, John Iliffe wrote:
> >> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >> >> So what if you change the connection to use -h localhost?
> >> > 
> >> > Can you please expand on that request?  I'm not sure where you want
> >> > me to put that directive.  I'm using the mod_php module in Apache.
> >> 
> >> See the second example here:
> >> 
> >> http://php.net/manual/en/function.pg-connect.php
> >> 
> >> 8<-
> >> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> >> // connect to a database named "mary" on "localhost" at port "5432"
> >> 8<-
> >> 
> >> That will try to use a tcp connection on localhost instead of a unix
> >> socket.
> > 
> > Thanks Joe.  I Changed the pg_connect line in the script to:
> > 
> > --
> > 
> >   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
> > 
> > user= password=xx');
> > ---
> > 
> > Even though "localhost" is in the /etc/hosts file the lookup failed to
> 
> > resolve so I provided the full IP address.  The error from Apache is:
> You have some very odd issues with your machine...
> 
No, Fedora/Red Hat has made a mess!  I just installed everything (APache, 
Postgresql, PHP, OpenSSL from source as I always have over a period of more 
than 15 years.  This is the first time I have run into this sort of problem 
and it seems to be related to systemctl's unit files.

If I may be permitted a rant at this point, the Fedora documentation is 
almost useless for SELinux and much of the underlying operating system. 
Very nice on theory but nothing on the details or "What is required?/How do 
I do it?" 

In a small company like mine, we are all multi-tasked and having a 
specialist for everything, like a system programmer skilled in Linux, is 
just not on.  Things have to work right out of the box.  Server 
administration, system operations, etc, is just not my core skill, and I 
depend heavily on the open source community for help (like you at the 
moment).  

> > --
> > [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid
> > 139671464015616] [client 192.168.1.10:59260] PHP Warning: 
> > pg_connect(): Unable to connect to PostgreSQL server: could not
> > connect to server: Connection refused\n\tIs the server running on
> > host 192.168.1.6 and accepting\n\tTCP/IP connections on
> > port 5432? in
> > /httpd/iliffe/testfcgi.php on line 132
> > ---
> > 
> > PHP does not show anything in its log.
> > 
> >> Another question I don't believe has been asked is, what does your
> >> pg_hba.conf look like?
> > 
> > Note here that I have deleted a number of production users and the
> > associated databases from the file shown below for security reasons. 
> > The user marked "XXX" has a real name but isn't the one we are
> > using to connect to the database, so the active line should be the
> > "local all all password" line.  The UID being used to connect IS in
> > the password list and PSQL can still connect OK.  The yrarc database
> > does exist and contains several tables.
> > 
> > --
> > # TYPE  DATABASEUSERADDRESS METHOD
i> > 
> > # "local" is for Unix domain socket connections only
> > #local   all all md5
> > 
> > local   yrarc  XXX  trust
> > local   all all
> > password #local   all all
> > trust # IPv4 local connections:
> > hostall all 127.0.0.1/32trust
> > -
> 
> You have no pg_hba.conf rule for host=192.168.1.6 so it is not
> surprising that cannot connect. You need something like:
> 
> # only allow connections from one host using tcp
> hostall all 192.168.1.6/32md5
> 
> - or maybe -
> 
> # only allow connections from same subnet using tcp
> hostall all 192.168.1.0/24md5
> 
I don't think I should need that since httpd/mod_php is on the same machine 
so should be 127.0.0.1 should cover it.  I did try it though and set it to 
"trust" to avoid any problems with permissions in Postgresql.   I then 
stopped and restarted both Postgres and Apache and still get the same error 
in the log from pg_connect asking whether the socket is available.

> I just noticed in the message above "client 192.168.1.10". I thought you
> had said earlier that PHP was running on the same box as Postgres? So
> that box uses both 192.168.1.6 and 192.168.1.10 on two different
> interfaces?

PHP and Postgresql 

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Saturday 08 April 2017 11:20:29 Joe Conway wrote:
> On 04/08/2017 06:31 AM, John Iliffe wrote:
> > On Saturday 08 April 2017 00:10:14 Adrian Klaver wrote:
> >> On 04/07/2017 07:45 PM, Joe Conway wrote:
> >> > On 04/07/2017 05:35 PM, Adrian Klaver wrote:
> >> >> On 04/07/2017 05:03 PM, John Iliffe wrote:
> >> > Running on Fedora 25 with SELinux in PERMISSIVE mode.  The
> >> > audit log shows no hits on Postgresql.
> >> >>> 
> >> >>> My going in position was/still is, that this is a SELinux
> >> >>> security problem
> >> >>> but I am finding SELinux to be the most opaque and badly
> >> >>> documented software
> >> >>> that I have ever had to deal with, which is why it is running in
> >> >>> permissive
> >> >>> mode at the moment.
> >> >> 
> >> >> Well what I know about SELinux would fit in the navel of a
> >> >> flea(tip of the hat to David Niven), so I can not be of much help
> >> >> there. The reason I am returned this thread to the list, there
> >> >> are folks that do understand it.
> >> > 
> >> > If SELinux is running in permissive I don't see how it could be at
> >> > fault for your issue. Did you verify that (getenforce)?
> >> > 
> >> >>> --
> >> >>> [Fri Apr 07 17:03:28.597101 2017] [php7:warn] [pid 1797:tid
> >> >>> 140599445419776] [client 192.168.1.10:45127] PHP Warning:
> >> >>> pg_connect(): Unable to connect to PostgreSQL server: could not
> >> >>> connect to server: No such file or directory\n\tIs the server
> >> >>> running locally and
> >> >>> accepting\n\tconnections on Unix domain socket
> >> >>> /tmp/.s.PGSQL.5432? in /httpd/iliffe/testfcgi.php on
> >> >>> line 121 
> >> > 
> >> > This might be a silly question, but is PHP running on the same
> >> > server as Postgres?
> >> 
> >> To add to this, previously you mentioned:
> >> 
> >> "Also, using the on board firewall (firewalld) to provide a secondary
> >> domain where the actual business processes run. "
> >> 
> >> What exactly does that mean?
> > 
> > There is something rather odd here.
> > 
> > getenforce shows the mode as permissive, which is what I think it is.
> 
> If getenforce shows you are in permissive, then selinux is not your
> problem, full stop.
> 
> > BUT, this morning's logwatch report shows:
> >  *** Denials ***
> >  
> > system_u system_u (tcp_socket): 1 times
> 
> selinux will continue to log denials in permissive -- this is useful to
> determine what would have been blocked by selinux had it been in
> enforcing, which in turn gives you a chance to fix those issues before
> turning on enforcing.
> 
> For more detail on the selinux logs look in /var/log/audit/audit.log
> 
> You definitely have something odd going on though. As you said
> elsewhere, using a Unix domain socket connection the firewall should
> not get involved either.
> 
> Seems like the issue is related to PHP somehow. For example, see:
> http://serverfault.com/questions/641329/cannot-connect-to-postgresql-uni
> x-domain-socket
In a way, probably yes, but I still can't figure it out.  The systemctl unit 
file DOES have the line PrivateTmp=true and changing it to 'false' didn't 
accomplish anything.

So I did the whole routine, created the /var/pgsql directory, changed the 
postgresql.conf Unix domain socket line to create two sockets, restarted 
and checked that both sockets do exist (yes) and then retried the 
connection.  Even stopped and restarted Apache in case something there was 
required but still no luck.  

I checked in the PHP directories and there doesn't seem to be any config file 
that applies to mod_php and in the php-fpm config file there is no reference 
to postgres.  That suggests to me that there is no way to point the 
connection request to the new socket file location.

So, any ideas as to where to go next?

FYI, psql still works OK.

And, thanks again for your patience!

John


> 
> Joe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/09/2017 11:33 AM, John Iliffe wrote:

On Saturday 08 April 2017 18:10:35 Joe Conway wrote:

On 04/08/2017 01:23 PM, John Iliffe wrote:

On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:

So what if you change the connection to use -h localhost?


Can you please expand on that request?  I'm not sure where you want me
to put that directive.  I'm using the mod_php module in Apache.


See the second example here:

http://php.net/manual/en/function.pg-connect.php

8<-
$dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
// connect to a database named "mary" on "localhost" at port "5432"
8<-

That will try to use a tcp connection on localhost instead of a unix
socket.


Thanks Joe.  I Changed the pg_connect line in the script to:

--
  $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432
user= password=xx');
---


So is this on the machine that has the Postgres server?

If not change to that machine and use host=localhost, otherwise just 
change to host=localhost.




Even though "localhost" is in the /etc/hosts file the lookup failed to
resolve so I provided the full IP address.  The error from Apache is:


Can you show us the /etc/host file?



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to connect to Postgresql

[Joe Conway]

On 04/09/2017 11:33 AM, John Iliffe wrote:
> On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
>> On 04/08/2017 01:23 PM, John Iliffe wrote:
>> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
>> >> So what if you change the connection to use -h localhost?
>> > 
>> > Can you please expand on that request?  I'm not sure where you want me
>> > to put that directive.  I'm using the mod_php module in Apache.
>> 
>> See the second example here:
>> 
>> http://php.net/manual/en/function.pg-connect.php
>> 
>> 8<-
>> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
>> // connect to a database named "mary" on "localhost" at port "5432"
>> 8<-
>> 
>> That will try to use a tcp connection on localhost instead of a unix
>> socket.
>> 
> Thanks Joe.  I Changed the pg_connect line in the script to:
> 
> --
>   $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432 
> user= password=xx');
> ---
> 
> Even though "localhost" is in the /etc/hosts file the lookup failed to 
> resolve so I provided the full IP address.  The error from Apache is:


You have some very odd issues with your machine...

> --
> [Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid 
> 139671464015616] [client 192.168.1.10:59260] PHP Warning:  pg_connect(): 
> Unable to connect to PostgreSQL server: could not connect to server: 
> Connection refused\n\tIs the server running on host 192.168.1.6 
> and accepting\n\tTCP/IP connections on port 5432? in 
> /httpd/iliffe/testfcgi.php on line 132
> ---
> 
> PHP does not show anything in its log.
> 
>> Another question I don't believe has been asked is, what does your
>> pg_hba.conf look like?
> 
> Note here that I have deleted a number of production users and the 
> associated databases from the file shown below for security reasons.  The 
> user marked "XXX" has a real name but isn't the one we are using to 
> connect to the database, so the active line should be the "local all all 
> password" line.  The UID being used to connect IS in the password list and 
> PSQL can still connect OK.  The yrarc database does exist and contains 
> several tables.
> 
> --
> # TYPE  DATABASEUSERADDRESS METHOD
> 
> # "local" is for Unix domain socket connections only
> #local   all all md5
> 
> local   yrarc  XXX  trust
> local   all all password
> #local   all all trust
> # IPv4 local connections:
> hostall all 127.0.0.1/32trust
> -

You have no pg_hba.conf rule for host=192.168.1.6 so it is not
surprising that cannot connect. You need something like:

# only allow connections from one host using tcp
hostall all 192.168.1.6/32md5

- or maybe -

# only allow connections from same subnet using tcp
hostall all 192.168.1.0/24md5

I just noticed in the message above "client 192.168.1.10". I thought you
had said earlier that PHP was running on the same box as Postgres? So
that box uses both 192.168.1.6 and 192.168.1.10 on two different interfaces?

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Saturday 08 April 2017 18:10:35 Joe Conway wrote:
> On 04/08/2017 01:23 PM, John Iliffe wrote:
> > On Saturday 08 April 2017 09:38:07 Adrian Klaver wrote:
> >> So what if you change the connection to use -h localhost?
> > 
> > Can you please expand on that request?  I'm not sure where you want me
> > to put that directive.  I'm using the mod_php module in Apache.
> 
> See the second example here:
> 
> http://php.net/manual/en/function.pg-connect.php
> 
> 8<-
> $dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
> // connect to a database named "mary" on "localhost" at port "5432"
> 8<-
> 
> That will try to use a tcp connection on localhost instead of a unix
> socket.
> 
Thanks Joe.  I Changed the pg_connect line in the script to:

--
  $db_handle = pg_connect('dbname=yrarc host=192.168.1.6 port=5432 
user= password=xx');
---

Even though "localhost" is in the /etc/hosts file the lookup failed to 
resolve so I provided the full IP address.  The error from Apache is:

--
[Sun Apr 09 14:08:16.178126 2017] [php7:warn] [pid 24917:tid 
139671464015616] [client 192.168.1.10:59260] PHP Warning:  pg_connect(): 
Unable to connect to PostgreSQL server: could not connect to server: 
Connection refused\n\tIs the server running on host 192.168.1.6 
and accepting\n\tTCP/IP connections on port 5432? in 
/httpd/iliffe/testfcgi.php on line 132
---

PHP does not show anything in its log.

> Another question I don't believe has been asked is, what does your
> pg_hba.conf look like?

Note here that I have deleted a number of production users and the 
associated databases from the file shown below for security reasons.  The 
user marked "XXX" has a real name but isn't the one we are using to 
connect to the database, so the active line should be the "local all all 
password" line.  The UID being used to connect IS in the password list and 
PSQL can still connect OK.  The yrarc database does exist and contains 
several tables.

--
# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
#local   all all md5

local   yrarc  XXX  trust
local   all all password
#local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
-

> 
> Joe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Aggregate query on large tables

[Adrian Klaver]

On 04/09/2017 08:05 AM, Job wrote:

Hi,

i have a table with about 400 millions of rows and i need to build some 
aggregate function for reporting.
I noticed that query performances are slowing down, even though indexes are 
present.

Query is simple (i make an example, my table is in italian language):
select a,sum(b) from table where a=x and c=y group by a

a is a varchar
b is an integer

x and y are two field i use for filter results.

I tried to create different indexes to try to speed up performance:
index1 (a)
index2 (c)
index3 (a,c)

I noticed, with query planner, that the mulfi-field index is not used.
Postgresql 9.6.1 still use scan without indexes.


At some point doing a scan over a table is better then using an index, 
as index use incurs a lookup cost. Unfortunately there is no way to 
determine what is actually happening in your case without more information.


So:

1) Table schema(language does not matter, the names are just tags).

2) The actual query run through EXPLAIN ANALYZE.



I obtain significant improvements only if i create a materialized view with 
aggregated data.

But i would like to avoid - if possible - creating (and mantaining) the 
materialized view.

Than you!
/F




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Aggregate query on large tables

[Bill Moran]

On Sun, 9 Apr 2017 17:05:56 +0200
Job  wrote:

> Hi,
> 
> i have a table with about 400 millions of rows and i need to build some 
> aggregate function for reporting.
> I noticed that query performances are slowing down, even though indexes are 
> present.
> 
> Query is simple (i make an example, my table is in italian language):
> select a,sum(b) from table where a=x and c=y group by a
> 
> a is a varchar
> b is an integer
> 
> x and y are two field i use for filter results.
> 
> I tried to create different indexes to try to speed up performance:
> index1 (a)
> index2 (c)
> index3 (a,c)
> 
> I noticed, with query planner, that the mulfi-field index is not used.
> Postgresql 9.6.1 still use scan without indexes.
> 
> I obtain significant improvements only if i create a materialized view with 
> aggregated data.

It helps a lot if you provide EXPLAIN output for questions like this. Also,
definitions of what you consider "fast" and "slow" are helpful, as everyone
seems to have a different opinion on what those words mean.

However, my guess is that your WHERE condition isn't significantly restrictive
to make use of the index worth the time. If you'll be fetching a significant
percentage of the rows anyway, using the index would actually slow things
down.

You _might_ get better performance if you create an index on (a,c,b) which
would allow the query to run without ever needing to access the actual
table; but I'm just speculating.

In my experience, queries like these rarely benefit from filter indexes,
because most of the time involved is in the grouping and aggregate processing,
and the index does nothing to help with that. But, again, without EXPLAIN
output I'm only speculating.

-- 
Bill Moran 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Aggregate query on large tables

[Job]

Hi,

i have a table with about 400 millions of rows and i need to build some 
aggregate function for reporting.
I noticed that query performances are slowing down, even though indexes are 
present.

Query is simple (i make an example, my table is in italian language):
select a,sum(b) from table where a=x and c=y group by a

a is a varchar
b is an integer

x and y are two field i use for filter results.

I tried to create different indexes to try to speed up performance:
index1 (a)
index2 (c)
index3 (a,c)

I noticed, with query planner, that the mulfi-field index is not used.
Postgresql 9.6.1 still use scan without indexes.

I obtain significant improvements only if i create a materialized view with 
aggregated data.

But i would like to avoid - if possible - creating (and mantaining) the 
materialized view.

Than you!
/F

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] TimeScaleDB -- Open Source Time Series Database Released (www.i-programmer.info);

[Nicolas Paris]

Le 09 avril 2017 à 05:31, Steve Petrie, P.Eng. écrivait :
> Warm Greetings To pgsql-general@postgresql.org
> 
> (I am a very newbie user of PG for a pretty trivial PHP / SQL web app. Been
> lurking with great admiration for a long time, on the
> pgsql-general@postgresql.org discussion list channel.)
> 
> I subscribe to a usefully wide-ranging but tightly edited source of
> tech-related news:
> 
> www.i-programmer.info
> 
> * * *
> * * *
> 
> Here is a link to an interesting recent i-programmer article titled "Open
> Source Time Series Database Released":
> 
> http://www.i-programmer.info/news/84/10648.html
> 
> And here are selected snippets quoted from this i-programmer web article
> about the TimeScaleDB open source project :
> 
> "A new, open-source time series database built with the Postgres engine has
> been released. TimeScaleDB is currently available in a single-node version,
> and is optimized for fast ingest and complex queries.
> 
> "The developers say that it offers advantages because unlike traditional
> RDBMS, TimescaleDB it scales-out horizontally across multiple servers; while
> unlike NoSQL databases, it natively supports all of SQL
> 

Thanks for the work around timeseries databases !

No mention of horizontal sharding mecanisms in the paper. Can you
provide more details ?



> ...
> 
> The developers say they were unwilling to make the trade-off between the
> horizontally scalability of NoSQL and the query power of relational
> databases:
> 
> "We needed something that offered both, so we built it".
> ...
> 
> "The SQL support comes courtesy of the PostgreSQL engine, and includes
> features such as secondary indices, JOINs, and window functions. TimescaleDB
> acts and appears as though it is just a PostgreSQL database: You connect to
> the database as if it's PostgreSQL, and you can administer the database as
> if it's PostgreSQL. Any tools and libraries that connect with PostgreSQL
> will automatically work with TimescaleDB.
> 
> "The developers say TimescaleDB offers advantages over straight PostgreSQL
> because PostgreSQL does not scale well to the volume of data that most
> time-series applications produce, especially when running on a single
> server. They say that in particular, vanilla PostgreSQL has poor write
> performance for large tables, and this problem only becomes worse over time
> as data volume grows linearly in time. These problems emerge when table
> indexes can no longer fit in memory, as each insert will translate to many
> disk fetches to swap in portions of the indexes' B-Trees.
> 
> * * *
> * * *
> 
> Curious to learn if any seriously PG-knowledgeable list participants have
> thoughts on this TimeScaleDB project ??
> 
> Would there be merit in considering porting some TimeScaleDB functionality
> into standard Postgres, as a response to NoSQL "competition"  ??
> 
> Best Regards,
> 
> Steve
> 
> * * *
> 
> Steve Petrie, P.Eng.
> 
> http://aspetrie.net
> Oakville, Ontario, Canada
> (905) 847-3253
> apet...@aspetrie.net
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Nullsif ? PG 9.1

[Patrick B]

2017-04-09 20:18 GMT+12:00 Patrick B :

> Hi guys,
>
> I've got this select:
>  SELECT split_part(n.node_full_path::text, '/'::text, 8)::integer AS id,
>
> However, not always I will get the 8th field, and because of that, i may
> get no data somethings.
>
> Example:
> /filesuser/client/27801123/attachment/4510/main
> /filesuser/client//attachment/21314134/file/12312312312
> <(231)%20231-2312>/small/photo.jpg
>
> Note that, sometimes it only goes until the 7th splitted_part, not always
> i will get the 8th.
>
> How can I tell the select, if no 8th field is found, then returns null?
>
> Thanks!
> Patrick
>


Sorry guys.. Got it!
https://www.postgresql.org/docs/9.1/static/functions-conditional.html

[GENERAL] Nullsif ? PG 9.1

[Patrick B]

Hi guys,

I've got this select:
 SELECT split_part(n.node_full_path::text, '/'::text, 8)::integer AS id,

However, not always I will get the 8th field, and because of that, i may
get no data somethings.

Example:
/filesuser/client/27801123/attachment/4510/main
/filesuser/client//attachment/21314134/file/12312312312/small/photo.jpg

Note that, sometimes it only goes until the 7th splitted_part, not always i
will get the 8th.

How can I tell the select, if no 8th field is found, then returns null?

Thanks!
Patrick