Re: Select works only when connected from login postgres

> So is the 9.4 instance the production/live database?

Essentially, but it's not heavily used: this is me messing around on a dev box.

> So what happens when you specify the port in your psql connection, eg:
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434

With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
other two complain like so:

  psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5434"?


On Sat, Dec 3, 2016 at 9:11 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 12/03/2016 07:38 PM, Joseph Brenner wrote:
>>
>> Our story thus far: I've now got three different pg installations, with
>> three
>> servers running simultaneously:
>>
>> ps ax | egrep postgres | egrep '\-D'
>>   748 ?        S      0:04 /usr/lib/postgresql/9.4/bin/postgres -D
>> /var/lib/postgresql/9.4/main -c
>> config_file=/etc/postgresql/9.4/main/postgresql.co
>> 23019 pts/1    S      0:01 /usr/local/pgsql/bin/postgres -D
>> /usr/local/pgsql/data
>> 27352 ?        S      0:00 /usr/lib/postgresql/9.6/bin/postgres -D
>> /var/lib/postgresql/9.6/main -c
>> config_file=/etc/postgresql/9.6/main/postgresql.co
>>
>> The 9.4 version presumably is using the standard default port 5432.
>
>
> So is the 9.4 instance the production/live database?
>
>> The 9.6 /usr/local version was compiled to use port 5433.
>> The other 9.6 version I just installed from apt.postgresql.org,
>> which according to the installation messages used port 5434
>> (automatically grabbing the next unused port, I gather: pretty
>> slick).
>>
>> This is what I mean by "failing silently", I get no output from
>> the select, no error message inside of psql, nothing in the error
>> logs, *but* psql doesn't terminate:
>>
>>   doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
>>   psql (9.6.1)
>>   Type "help" for help.
>>
>>   doom=# select 'hello' as world;
>>   doom=#
>
>
> So what happens when you specify the port in your psql connection, eg:
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>
>>
>> Nothing else gives me any output either: \l, \du, etc.
>>
>>>> The only thing unusual about the steps that I followed was I built
>>>> with port 5433 (rather than 5432) as the default,
>>
>>
>>> This is not as simple as it might look; the default port is actually
>>> wired into libpq.so, not psql itself.  And on most brands of Linuxen,
>>> it's not that easy to get a program to link to a non-default copy of
>>> a shared library if there's a copy in /usr/lib.  However, if you were
>>> connecting to the wrong port number, I'd still not expect that it
>>> just dies without saying anything.
>>
>>
>> Well, I've been presuming that the INSTALL file knows what
>> it's talking about in describing configure options:
>>
>>   --with-pgport=NUMBER
>>           Set "NUMBER" as the default port number for server and
>>           clients. The default is 5432. The port can always be
>>           changed later on, but if you specify it here then both
>>           server and clients will have the same default compiled in,
>>           which can be very convenient.
>
>
> Generally it is just easier/safer to just change the port in
> postgresql.conf. That is what the Debian packaging does when it sets up
> multiple Postgres instances.
>
>
>>
>>> ... maybe psql is crashing
>>> because it's linking to an ABI-incompatible libpq.  You should try
>>> "ldd" on the psql executable and see if it's resolving the libpq
>>> dependency to the copy you intended.
>>
>>
>> Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
>>   /usr/local/pgsql/lib/libpq.so.5
>>
>> ldd /usr/local/pgsql/bin/psql
>>     linux-vdso.so.1 (0x00007fff033e2000)
>>     libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x00007f2c34e8f000)
>>     libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
>> (0x00007f2c34c45000)
>>     libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f2c34944000)
>>     libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f2c34599000)
>>     libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
>> (0x00007f2c34338000)
>>     libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
>> (0x00007f2c33f3c000)
>>     libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
>> (0x00007f2c33d1f000)
>>     libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5
>> (0x00007f2c33af5000)
>>     /lib64/ld-linux-x86-64.so.2 (0x00007f2c350bc000)
>>     libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f2c338f1000)
>>
>> This seems a bit peculiar though, the binary packages are both
>> configured to use the same, unversioned libpq?
>>
>> ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq
>>     libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5
>> (0x00007fe9db2ea000)
>>
>> ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
>>     libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5
>> (0x00007fa7337ec000)
>>
>> On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Joseph Brenner <doomvox@gmail.com> writes:
>>>>
>>>> I'm trying to get a new build of 9.6.1 working on Debian
>>>> stable and I'm seeing some odd behavior where things work
>>>> correctly if I run psql when logged in as user 'postgres',
>>>> but if I'm logged-in as user 'doom' (my usual login), I don't
>>>> seem to have any select privileges.  Even this fails
>>>> silently:
>>>
>>>
>>>>   select 'world' as hello;
>>>
>>>
>>> Um, define "fails silently"?  Do you get a command prompt from
>>> psql?  What does the interaction look like *exactly*?  If psql
>>> just returns to the shell command prompt, maybe it's giving a
>>> nonzero exit code? (try "echo $?" afterwards)
>>>
>>> [ and later... ]
>>>
>>>> The only thing unusual about the steps that I followed was I built
>>>> with port 5433 (rather than 5432) as the default,
>>>
>>>
>>> This is not as simple as it might look; the default port is actually
>>> wired into libpq.so, not psql itself.  And on most brands of Linuxen,
>>> it's not that easy to get a program to link to a non-default copy of
>>> a shared library if there's a copy in /usr/lib.  However, if you were
>>> connecting to the wrong port number, I'd still not expect that it
>>> just dies without saying anything.
>>>
>>> Hmm ... a different take on that is that maybe psql is crashing
>>> because it's linking to an ABI-incompatible libpq.  You should try
>>> "ldd" on the psql executable and see if it's resolving the libpq
>>> dependency to the copy you intended.
>>>
>>>                         regards, tom lane
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com