Re: Select works only when connected from login postgres

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