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(dot)klaver(at)aklaver(dot)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(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