Select works only when connected from login postgres

On 12/03/2016 12:08 PM, Joseph Brenner wrote:

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;

But if run logged in as 'postgres', all is well:

sudo su - postgres
/usr/local/pgsql/bin/psql --dbname=doom --username=doom
doom=# select 'world' as hello;
select 'world' as hello;
hello
-------
world

Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).

I've got my pg_hba.conf setup to use "trust" for all users:

# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust

Looking at how the program files are installed, I see they're all
owned by 'root' with group 'staff':

ls -la /usr/local/pgsql/bin/psql
-rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql

On speculation, I added doom to the staff group and reloaded
pg, but that didn't help. I see that the data files are all
owned by postgres, but I don't think that's unusual (or else
I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
privs to the data files):

drwx------ 1 postgres postgres 42 Nov 26 16:14 base

I'm running out of ideas for things to check. Any suggestions?

Some more info, for completeness sake:

\du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------+-----------
doom | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

\l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
doom | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
...

Did you receive the previous suggestions?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, and sorry about the re-post. I thought my original message was
hung-up in moderation, so I was doing an unsub/resub fandango to get
email addresses to match.

On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 12/03/2016 12:08 PM, Joseph Brenner wrote:

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;

But if run logged in as 'postgres', all is well:

sudo su - postgres
/usr/local/pgsql/bin/psql --dbname=doom --username=doom
doom=# select 'world' as hello;
select 'world' as hello;
hello
-------
world

Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).

I've got my pg_hba.conf setup to use "trust" for all users:

# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust

Looking at how the program files are installed, I see they're all
owned by 'root' with group 'staff':

ls -la /usr/local/pgsql/bin/psql
-rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql

On speculation, I added doom to the staff group and reloaded
pg, but that didn't help. I see that the data files are all
owned by postgres, but I don't think that's unusual (or else
I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
privs to the data files):

drwx------ 1 postgres postgres 42 Nov 26 16:14 base

I'm running out of ideas for things to check. Any suggestions?

Some more info, for completeness sake:

\du
List of roles
Role name | Attributes
| Member of

-----------+------------------------------------------------------------+-----------
doom | Superuser, Create role, Create DB |
{}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
{}

\l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges

-----------+----------+----------+-------------+-------------+-----------------------
doom | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
...

Did you receive the previous suggestions?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Saturday, December 3, 2016, Joseph Brenner <doomvox@gmail.com> wrote:

doom=# select 'hello' as world;
doom=#

Nothing else gives me any output either: \l, \du, etc.

For kicks, how about \echo or \! Something that doesn't need a server to
work.

David J,

For kicks, how about \echo or \! Something that doesn't need a server to work.

Sure: those do work.

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=# \echo 'yo'
yo
doom=# \! ls -lad p*
drwxr-xr-x 1 doom doom 12 Nov 16 12:29 perl5

On Sat, Dec 3, 2016 at 7:48 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Saturday, December 3, 2016, Joseph Brenner <doomvox@gmail.com> wrote:

doom=# select 'hello' as world;
doom=#

Nothing else gives me any output either: \l, \du, etc.

For kicks, how about \echo or \! Something that doesn't need a server to
work.

David J,

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@aklaver.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@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/03/2016 09:38 PM, Joseph Brenner wrote:

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"?

Alright how about?:

/usr/bin/psql --dbname=doom --username=doom -p 5434

My suspicion is that when you did the source build you got some cross
contamination of libraries.

If it where me I would get rid of the instance that you built from
source, assuming that there is nothing important on it.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yeah, I get the sense I need to simplify the situation, if only to
eliminate some distractions from the story.

This morning I was thinking I want to play around with pg_hba.conf
settings some more, but after that I'll do some pg_dumps and
uninstalls and see how things behave.

Oh, trying:

/usr/bin/psql --dbname=doom --username=doom -p 543x

As login 'doom' the connection happens only for port 5432,
as login 'postgres' the connection fails differently for port 5432:

psql: FATAL: Peer authentication failed for user "doom"

(Like I said, I need to play with pg_hba.conf a bit.)

But that actually surprises me, I would've expected it'd be on
port 5434, because that's the most recent binary install, not the
original 9.4 version:

/usr/bin/psql -V
psql (PostgreSQL) 9.6.1

/etc/postgresql/9.6/main/postgresql.conf
port = 5434 # (change requires restart)

/etc/postgresql/9.4/main/postgresql.conf
port = 5432 # (change requires restart)

On Sun, Dec 4, 2016 at 7:06 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/03/2016 09:38 PM, Joseph Brenner wrote:

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"?

Alright how about?:

/usr/bin/psql --dbname=doom --username=doom -p 5434

My suspicion is that when you did the source build you got some cross
contamination of libraries.

If it where me I would get rid of the instance that you built from source,
assuming that there is nothing important on it.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:

My suspicion is that when you did the source build you got some cross
contamination of libraries.

That's the best theory I can come up with either, although libpq's
APIs haven't really changed in any non-backwards-compatible fashion
in years. I could imagine a newer psql flat-out crashing because it
tries to call some libpq function that doesn't exist in an older
libpq, but that's not what we're seeing here. It's really weird.

A couple of tests that might help narrow things down:

1. In the server configuration, enable log_connections,
log_disconnections, and log_statement = 'all'. Does anything show up in
the log when you connect with the broken psql and issue commands?

2. If you issue commands that span multiple lines --- unclosed left
parens, multiline string literals, missing semicolons --- does the
psql prompt change to match?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Joseph Brenner <doomvox@gmail.com> writes:

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"?

What this probably indicates is that the other two installations are
configured to put their socket files someplace else than /tmp, perhaps
/var/run/postgresql. Connecting to them and issuing "show
unix_socket_directories" would tell the tale.

You can persuade a psql to connect to a socket in a nondefault directory
by giving the directory name as host, eg

psql --host=/var/run/postgresql -p 5434

It would be interesting to try all nine combinations of the psql's
supplied by your various installations and the servers, just to confirm
which ones behave normally and which don't. Of course, the other two
would have to be told --host=/tmp to talk to the handbuilt server.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Okay: I think I'm closing in on the trouble. I didn't used to
have a ~/.psqlrc file, but recently I experimented with
creating one. When I have a .psqlrc file containing the magic
incantaion

\pset pager off

*Then* everything works. All three of my extant postgresql
installations work correctly whether connected to with unix
login doom or postgres.

Our story thus far:

I've got three postgresql installations running on a Debian
stable machine:

o version 9.4, a binary package from Debian stable
(using port 5432),
o a build of 9.6.1 from scratch (using port 5433),
o an installation of 9.6.1 from a binary pgdb
package, (using port 5434).

I've been seeing some odd behavior where a psql connection will
work fine if connected to as *unix login* 'postgres', but not
always if with unix login 'doom', it which case even the
simplest selects can fail silently, without any messages in the
log or on the screen to explain why.

Tom Lane suggested I might try connecting all three of my psql
clients to all three of the servers (by juggling the port and host
options).

Since I was going to conduct at least 9 experiments (with two
logins each), I decided to script it, but before that I made an
effort to clean things up and make sure all three installations
were exactly parallel setups: all needed a user 'doom' with
superuser privileges, all needed a 'doom' database which was
owned by 'doom', all have a pg_hba.conf with auth-method trust,
and so on. I also added additional logging settings (as
suggested by Tom) to all three the postgresql.conf files.

There were some other small things I changed, such as making
all the log files "chmod a+r" so the script would be able to
read them and echo newly added messages...

And I created a ~/.psqlrc file, though I didn't expect it
to have any effect on my new trial runs using the --command
feature, e.g.:

/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

When I got the script cleaned up and working, I found that all
9 connections worked, for both logins: something I'd done has
fixed the problem (or alternately, the problem has "gone away
on it's own").

For example, now when connecting to my local build (without
bothering to specifying port & host):

doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom

This works now (as does \du, \l, etc):

select 'hello' as world;
world
-------
hello

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

\pset pager off

I thought it might be just the fact that it was non-empty, and
tried a few other settings without any luck. If I have that
line in my ~/.psqlrc, then this probe returns the expected result:

/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"

If I delete that line, then the select fails silently again.

Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed, e.g.

Pager usage is off.

Or in the logs:

2016-12-05 16:17:04 PST [18517-3] doom@doom LOG: statement: set
client_encoding to 'unicode'

Because I also had this line:

\encoding unicode

On Sun, Dec 4, 2016 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joseph Brenner <doomvox@gmail.com> writes:

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"?

What this probably indicates is that the other two installations are
configured to put their socket files someplace else than /tmp, perhaps
/var/run/postgresql. Connecting to them and issuing "show
unix_socket_directories" would tell the tale.

You can persuade a psql to connect to a socket in a nondefault directory
by giving the directory name as host, eg

psql --host=/var/run/postgresql -p 5434

It would be interesting to try all nine combinations of the psql's
supplied by your various installations and the servers, just to confirm
which ones behave normally and which don't. Of course, the other two
would have to be told --host=/tmp to talk to the handbuilt server.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Dec 5, 2016 at 6:13 PM, Joseph Brenner <doomvox@gmail.com> wrote:

Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed

​New behavior in 9.6 - you shouldn't be seeing this in your 9.4 test setup
unless you are​ using the 9.6 psql to connect to the 9.4 server (its a
client-specific behavior).

David J.

On 12/05/2016 05:13 PM, Joseph Brenner wrote:

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

\pset pager off

So what does:

env | grep PAGER

show?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Joseph Brenner <doomvox@gmail.com> writes:

Okay: I think I'm closing in on the trouble. I didn't used to
have a ~/.psqlrc file, but recently I experimented with
creating one. When I have a .psqlrc file containing the magic
incantaion

\pset pager off

*Then* everything works.

Ah! So, most likely, there is something wrong with the local installation
of "more", or whatever the environment variable PAGER is set to. If you
say "more somefile", does it behave reasonably? Check "echo $PAGER"
as well.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

So what does:

env | grep PAGER

show?

Nothing. I have no PAGER settting (I don't normally use one).

On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/05/2016 05:13 PM, Joseph Brenner wrote:

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

\pset pager off

So what does:

env | grep PAGER

show?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Wait, that's not quite right. The user 'postgres' has no PAGER envar,
but user 'doom' has an empty value:

PAGER=

On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner <doomvox@gmail.com> wrote:

So what does:

env | grep PAGER

show?

Nothing. I have no PAGER settting (I don't normally use one).

On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/05/2016 05:13 PM, Joseph Brenner wrote:

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

\pset pager off

So what does:

env | grep PAGER

show?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

And I guess I did that intentionally, my .bashrc has

# I use emacs shells, I got a "pager" already:
export PAGER=''

On Mon, Dec 5, 2016 at 8:52 PM, Joseph Brenner <doomvox@gmail.com> wrote:

Wait, that's not quite right. The user 'postgres' has no PAGER envar,
but user 'doom' has an empty value:

PAGER=

On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner <doomvox@gmail.com> wrote:

So what does:

env | grep PAGER

show?

Nothing. I have no PAGER settting (I don't normally use one).

On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/05/2016 05:13 PM, Joseph Brenner wrote:

I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:

\pset pager off

So what does:

env | grep PAGER

show?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general