Re: Select works only when connected from login postgres - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Select works only when connected from login postgres |
Date | |
Msg-id | 4eaaf43f-36ae-fe8a-562b-5ef914bed467@aklaver.com Whole thread Raw |
In response to | Re: Select works only when connected from login postgres (Joseph Brenner <doomvox@gmail.com>) |
Responses |
Re: Select works only when connected from login postgres
|
List | pgsql-general |
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
pgsql-general by date: