Re: Select works only when connected from login postgres

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.
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=#

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.

> ... 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