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(at)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Joseph Brenner <doomvox(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com