Horton

Thread

Commits

  1. Handle empty or all-blank PAGER setting more sanely in psql.

  1. Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-03T20:08:30Z

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

  2. Re: Select works only when connected from login postgres

    Adrian Klaver <adrian.klaver@aklaver.com> — 2016-12-03T20:13:18Z

    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

  3. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-03T23:04:55Z

    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

  4. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-04T00:51:35Z

    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

  5. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-04T03:38:11Z

    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

  6. Re: Select works only when connected from login postgres

    David G. Johnston <david.g.johnston@gmail.com> — 2016-12-04T03:48:13Z

    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,

  7. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-04T05:01:11Z

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

  8. Re: Select works only when connected from login postgres

    Adrian Klaver <adrian.klaver@aklaver.com> — 2016-12-04T05:11:03Z

    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

  9. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-04T05:38:25Z

    > 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

  10. Re: Select works only when connected from login postgres

    Adrian Klaver <adrian.klaver@aklaver.com> — 2016-12-04T15:06:47Z

    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

  11. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-04T17:14:58Z

    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

  12. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-04T17:31:40Z

    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

  13. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-04T17:51:49Z

    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

  14. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T01:13:07Z

    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

  15. Re: Select works only when connected from login postgres

    David G. Johnston <david.g.johnston@gmail.com> — 2016-12-06T01:20:39Z

    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.

  16. Re: Select works only when connected from login postgres

    Adrian Klaver <adrian.klaver@aklaver.com> — 2016-12-06T01:33:01Z

    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

  17. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-06T01:37:06Z

    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

  18. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T04:50:42Z

    > 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

  19. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T04:52:37Z

    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

  20. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T04:53:41Z

    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

  21. Re: Select works only when connected from login postgres

    David G. Johnston <david.g.johnston@gmail.com> — 2016-12-06T05:03:42Z

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

> And I guess I did that intentionally, my .bashrc has
>
>   # I use emacs shells, I got a "pager" already:
>   export PAGER=''
>
>
​PAGER= psql --pset=pager=always -c 'select 1;'​
<nothing on the screen>

Remove PAGER= and I'm good.

I guess that psql could be a bit more helpful by reporting something to
stderr if the value of PAGER is not an executable (platform dependent...)

I find it a bit odd that all of your queries were using the pager...did I
miss where you reported that setting?

David J.

  22. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T05:28:11Z

    Well yeah, trying to run a PAGER that's not there might throw an error.
Or you know, nothing in PAGER might imply "pager off".

> I find it a bit odd that all of your queries were using the pager...did I miss where you reported that setting?

I didn't report it because I wasn't looking in that direction.   A
PAGER set to blank for login 'doom' and no PAGER setting for login
'postgres' explains much of what I was seeing, I think: selects run as
 'doom' tended to be blank (unless I had a pset no pager somewhere),
selects run as 'postgres' always worked.






On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doomvox@gmail.com> wrote:
>>
>> And I guess I did that intentionally, my .bashrc has
>>
>>   # I use emacs shells, I got a "pager" already:
>>   export PAGER=''
>>
>
> PAGER= psql --pset=pager=always -c 'select 1;'
> <nothing on the screen>
>
> Remove PAGER= and I'm good.
>
> I guess that psql could be a bit more helpful by reporting something to
> stderr if the value of PAGER is not an executable (platform dependent...)
>
> I find it a bit odd that all of your queries were using the pager...did I
> miss where you reported that setting?
>
> David J.
>

  23. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T16:33:01Z

    Looking back on the order of events, I think it went like this:

Back in around May, I was annoyed at pager behavior and wanted to get
rid of them-- I tried a blank PAGER setting in my .bashrc (and forgot
about it).

I also noticed the psql option "\pset pager off".  For not particular
reason, it didn't occur to me to just put that in a .psqlrc file, and
instead I had it in a command-line alias:

  alias psql="psql --pset='pager=off'"

Then more recently I started experimenting with a new build of 9.6.1:

The behavior at that stage looked like a difference between 9.4 (run
with a bare "psql") and 9.6 (run using a path to the new build).

Then later, as I was experimenting with multiple postgres
installations, I  got more careful about using paths to make sure I
knew which one I was running.  That cut the alias out of the picture,
and the .bashrc setting took over:

The behavior at that stage looked like a difference between user login
'doom' and 'postgres'.

I kept chasing after obscure file permissions settings or postgres
internal permissions (maybe there was something about the new row
level security features?), and so on.  The folks here kept assuming it
had to be some kind of version skew cross-talk between the different
installations.

Something I'd thought of vaguely (and wish I'd tried) was just
creating a new user and trying to reproduce the behavior in a clean
account set-up (there are always options to suppress rc files for
testing, but suppressing .bashrc isn't that simple).

The next thing on the list for me was to remove everything except the
new 9.6.1 pgdb binary install: by itself that wouldn't have gotten any
closer to isolating the problem, but it would've eliminated some
distractions.

Oddly enough, if I'd been reading up-to-date pg docs, I might not have
tried the .psqlrc setup that pointed the finger at the pager
situation: that was just something I was doing on the side, and I
didn't think it'd have any effect on a --command invocation, so I
didn't worry about it.

So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
that's possible.  Falling back to "pager off" would make sense to me.











On Mon, Dec 5, 2016 at 9:28 PM, Joseph Brenner <doomvox@gmail.com> wrote:
> Well yeah, trying to run a PAGER that's not there might throw an error.
> Or you know, nothing in PAGER might imply "pager off".
>
>> I find it a bit odd that all of your queries were using the pager...did I miss where you reported that setting?
>
> I didn't report it because I wasn't looking in that direction.   A
> PAGER set to blank for login 'doom' and no PAGER setting for login
> 'postgres' explains much of what I was seeing, I think: selects run as
>  'doom' tended to be blank (unless I had a pset no pager somewhere),
> selects run as 'postgres' always worked.
>
>
>
>
>
>
> On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doomvox@gmail.com> wrote:
>>>
>>> And I guess I did that intentionally, my .bashrc has
>>>
>>>   # I use emacs shells, I got a "pager" already:
>>>   export PAGER=''
>>>
>>
>> PAGER= psql --pset=pager=always -c 'select 1;'
>> <nothing on the screen>
>>
>> Remove PAGER= and I'm good.
>>
>> I guess that psql could be a bit more helpful by reporting something to
>> stderr if the value of PAGER is not an executable (platform dependent...)
>>
>> I find it a bit odd that all of your queries were using the pager...did I
>> miss where you reported that setting?
>>
>> David J.
>>

  24. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-06T17:51:36Z

    Joseph Brenner <doomvox@gmail.com> writes:
> Looking back on the order of events, I think it went like this:
> [ careful postmortem ]

Thanks for following up!

> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
> that's possible.  Falling back to "pager off" would make sense to me.

Agreed.  One thing that would be very simple is to treat an empty PAGER
value the same as "unset".  Detecting whether a nonempty value is behaving
sanely seems a great deal harder; depending on what pager you're using
and how you stop it, nonzero exit codes from the called process might
be normal.  I think it might be practical to issue a warning if we get
an exit code of 126 or 127, though.  We have a comment in archive-command
invocation:

     * Per the Single Unix Spec, shells report exit status > 128 when a called
     * command died on a signal.  Also, 126 and 127 are used to report
     * problems such as an unfindable command; treat those as fatal errors
     * too.

The relevant text in POSIX is

	If a command is not found, the exit status shall be 127. If the
	command name is found, but it is not an executable utility, the
	exit status shall be 126. Applications that invoke utilities
	without using the shell should use these exit status values to
	report similar errors.

I don't believe we want to complain about exit on a signal, because
SIGTERM or SIGINT is a typical exit in some pager setups.  But these
two codes strongly suggest something broken about your PAGER value.

So I propose
(1) ignore PAGER if it's an empty string
(2) if pclose returns exit code 126 or 127, report that the PAGER
command didn't work.  I'm not sure how complex that is, because IIRC
the pclose is at some remove from the popen call, but if it's not
unreasonably hairy we should do it.

			regards, tom lane

  25. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T19:04:21Z

    > Agreed.  One thing that would be very simple is to treat an empty PAGER
> value the same as "unset".

Sounds excellent.

> Detecting whether a nonempty value is behaving
> sanely seems a great deal harder ...

I was thinking a check for existence and executability, but I guess
that's covered already...  if you use a random string as PAGER you get
a sh error:

  export PAGER="nadatech"
  /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
--username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
'hello' AS world;"

  sh: 1: nadatech: not found

So the empty PAGER value case is the only one that doesn't seem
covered already.  (I'm talented about finding these things...)






On Tue, Dec 6, 2016 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joseph Brenner <doomvox@gmail.com> writes:
>> Looking back on the order of events, I think it went like this:
>> [ careful postmortem ]
>
> Thanks for following up!
>
>> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
>> that's possible.  Falling back to "pager off" would make sense to me.
>
> Agreed.  One thing that would be very simple is to treat an empty PAGER
> value the same as "unset".  Detecting whether a nonempty value is behaving
> sanely seems a great deal harder; depending on what pager you're using
> and how you stop it, nonzero exit codes from the called process might
> be normal.  I think it might be practical to issue a warning if we get
> an exit code of 126 or 127, though.  We have a comment in archive-command
> invocation:
>
>      * Per the Single Unix Spec, shells report exit status > 128 when a called
>      * command died on a signal.  Also, 126 and 127 are used to report
>      * problems such as an unfindable command; treat those as fatal errors
>      * too.
>
> The relevant text in POSIX is
>
>         If a command is not found, the exit status shall be 127. If the
>         command name is found, but it is not an executable utility, the
>         exit status shall be 126. Applications that invoke utilities
>         without using the shell should use these exit status values to
>         report similar errors.
>
> I don't believe we want to complain about exit on a signal, because
> SIGTERM or SIGINT is a typical exit in some pager setups.  But these
> two codes strongly suggest something broken about your PAGER value.
>
> So I propose
> (1) ignore PAGER if it's an empty string
> (2) if pclose returns exit code 126 or 127, report that the PAGER
> command didn't work.  I'm not sure how complex that is, because IIRC
> the pclose is at some remove from the popen call, but if it's not
> unreasonably hairy we should do it.
>
>                         regards, tom lane

  26. Re: Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-06T20:13:36Z

    Joseph Brenner <doomvox@gmail.com> writes:
>> Agreed.  One thing that would be very simple is to treat an empty PAGER
>> value the same as "unset".

> Sounds excellent.

Actually, after thinking about it a bit longer, should PAGER-set-but-
empty be treated as equivalent to "pager off", rather than as a request
to use the default pager?  I could see arguments either way for that.

>> Detecting whether a nonempty value is behaving
>> sanely seems a great deal harder ...

> I was thinking a check for existence and executability, but I guess
> that's covered already...  if you use a random string as PAGER you get
> a sh error:

>   export PAGER="nadatech"
>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
> 'hello' AS world;"

>   sh: 1: nadatech: not found

Hm, so you do; so my thought that this needs explicit code on our end
seems wrong.  [ experiments... ]  It seems like the specific case of
PAGER being empty or all-white-space causes the shell to think that
it's executing an empty line and just do nothing (in particular, not
print any error).  pclose then returns EPIPE, at least on my platform,
which we could report but it doesn't seem like a very useful report.
Any other case seems to provoke a shell complaint that's probably
sufficient for diagnosis.

So what I'm thinking now is that if PAGER is empty or all white space
then we should not try to use it as a shell command; we can either
treat the case as "pager off" or as "use default pager".  Everything
else we can leave to the invoked shell to complain about.

Comments?

			regards, tom lane

  27. Re: Select works only when connected from login postgres

    David G. Johnston <david.g.johnston@gmail.com> — 2016-12-06T20:27:13Z

    On Tue, Dec 6, 2016 at 1:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> So what I'm thinking now is that if PAGER is empty or all white space
> then we should not try to use it as a shell command; we can either
> treat the case as "pager off" or as "use default pager".  Everything
> else we can leave to the invoked shell to complain about.
>

​My pick would be to leave \pset pager to control the on/off nature of the
pager and treat both an unset and an empty string PAGER identically and
solely for the purpose of choosing which pager to use should one be
required.

David J.
​

  28. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T21:53:08Z

    Well, my take would be that if you've taken the trouble to set an
empty string as the PAGER that means something, and it probably means
you don't want any pager to be used.

But then, I would say that.


On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joseph Brenner <doomvox@gmail.com> writes:
>>> Agreed.  One thing that would be very simple is to treat an empty PAGER
>>> value the same as "unset".
>
>> Sounds excellent.
>
> Actually, after thinking about it a bit longer, should PAGER-set-but-
> empty be treated as equivalent to "pager off", rather than as a request
> to use the default pager?  I could see arguments either way for that.
>
>>> Detecting whether a nonempty value is behaving
>>> sanely seems a great deal harder ...
>
>> I was thinking a check for existence and executability, but I guess
>> that's covered already...  if you use a random string as PAGER you get
>> a sh error:
>
>>   export PAGER="nadatech"
>>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
>> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
>> 'hello' AS world;"
>
>>   sh: 1: nadatech: not found
>
> Hm, so you do; so my thought that this needs explicit code on our end
> seems wrong.  [ experiments... ]  It seems like the specific case of
> PAGER being empty or all-white-space causes the shell to think that
> it's executing an empty line and just do nothing (in particular, not
> print any error).  pclose then returns EPIPE, at least on my platform,
> which we could report but it doesn't seem like a very useful report.
> Any other case seems to provoke a shell complaint that's probably
> sufficient for diagnosis.
>
> So what I'm thinking now is that if PAGER is empty or all white space
> then we should not try to use it as a shell command; we can either
> treat the case as "pager off" or as "use default pager".  Everything
> else we can leave to the invoked shell to complain about.
>
> Comments?
>
>                         regards, tom lane

  29. Re: Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-06T21:54:39Z

    But on the other hand, if you've got a blank PAGER envar and a "\pset
pager something", the pset should win (just as it does now).


On Tue, Dec 6, 2016 at 1:53 PM, Joseph Brenner <doomvox@gmail.com> wrote:
> Well, my take would be that if you've taken the trouble to set an
> empty string as the PAGER that means something, and it probably means
> you don't want any pager to be used.
>
> But then, I would say that.
>
>
> On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Joseph Brenner <doomvox@gmail.com> writes:
>>>> Agreed.  One thing that would be very simple is to treat an empty PAGER
>>>> value the same as "unset".
>>
>>> Sounds excellent.
>>
>> Actually, after thinking about it a bit longer, should PAGER-set-but-
>> empty be treated as equivalent to "pager off", rather than as a request
>> to use the default pager?  I could see arguments either way for that.
>>
>>>> Detecting whether a nonempty value is behaving
>>>> sanely seems a great deal harder ...
>>
>>> I was thinking a check for existence and executability, but I guess
>>> that's covered already...  if you use a random string as PAGER you get
>>> a sh error:
>>
>>>   export PAGER="nadatech"
>>>   /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
>>> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
>>> 'hello' AS world;"
>>
>>>   sh: 1: nadatech: not found
>>
>> Hm, so you do; so my thought that this needs explicit code on our end
>> seems wrong.  [ experiments... ]  It seems like the specific case of
>> PAGER being empty or all-white-space causes the shell to think that
>> it's executing an empty line and just do nothing (in particular, not
>> print any error).  pclose then returns EPIPE, at least on my platform,
>> which we could report but it doesn't seem like a very useful report.
>> Any other case seems to provoke a shell complaint that's probably
>> sufficient for diagnosis.
>>
>> So what I'm thinking now is that if PAGER is empty or all white space
>> then we should not try to use it as a shell command; we can either
>> treat the case as "pager off" or as "use default pager".  Everything
>> else we can leave to the invoked shell to complain about.
>>
>> Comments?
>>
>>                         regards, tom lane

  30. Re: [GENERAL] Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-07T02:07:09Z

    Joseph Brenner <doomvox@gmail.com> writes:
> Well, my take would be that if you've taken the trouble to set an
> empty string as the PAGER that means something, and it probably means
> you don't want any pager to be used.

Yeah, on reflection that argument seems pretty persuasive.  So I propose
the attached patch.

BTW, I realized while testing this that there's still one gap in our
understanding of what went wrong for you: cases like "SELECT 'hello'"
should not have tried to use the pager, because that would've produced
less than a screenful of data.  But that's irrelevant here, because it
can easily be shown that psql doesn't behave nicely if PAGER is set to
empty when it does try to use the pager.

			regards, tom lane

  31. Re: [HACKERS] Select works only when connected from login postgres

    Daniel Verite <daniel@manitou-mail.org> — 2016-12-07T10:31:55Z

    	Tom Lane wrote:

> BTW, I realized while testing this that there's still one gap in our
> understanding of what went wrong for you: cases like "SELECT 'hello'"
> should not have tried to use the pager, because that would've produced
> less than a screenful of data

At some point emacs was mentioned as the terminal:

>> And I guess I did that intentionally, my .bashrc has
>>
>>   # I use emacs shells, I got a "pager" already:
>>   export PAGER=''

The M-x shell mode of emacs has a so-called "dumb" terminal
emulation (that's the value of $TERM) where the notion of a "page"
doesn't quite apply.

For instance, when using emacs 24.3 with my default pager on an
Ubuntu desktop, this is what I get:

test=> select 1;
WARNING: terminal is not fully functional
-  (press RETURN)
 ?column? 
----------
	1
(1 row)

I suspect that psql is unable to determine the screen size
of the "dumb" terminal, and that it's the fault of the terminal
rather than psql.
The warning is displayed by "less" AFAICS.

There are other psql features like tab-completion that don't work
in this mode because emacs interpret keystrokes first for
itself, in effect mixing emacs functionalities with these of the
application run in the terminal. It's awesome sometimes
and irritating at other times depending on what you expect :)

OTOH it has also a M-x term command/mode that provides a
more sophisticated screen emulation into which paging seems
to work exactly like in a normal terminal and the emacs key bindings
are turned off. 


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

  32. Re: [GENERAL] Select works only when connected from login postgres

    Joseph Brenner <doomvox@gmail.com> — 2016-12-07T18:58:56Z

    Yes, I have a tendency to use emacs sub-shells (and occasionally M-x
sql-postgres)--

I thought I'd reproduced the behavior in an xterm, but I was just
trying again and I don't see it.  It does seem that the dumbness of my
dumb terminal is a factor.

If I understand the way this works, it could be an even more baffling
behavior if I were using an xterm: with a blank PAGER your output
would disappear only if the select exceeded a certain number of
lines...


On Wed, Dec 7, 2016 at 2:31 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
>         Tom Lane wrote:
>
>> BTW, I realized while testing this that there's still one gap in our
>> understanding of what went wrong for you: cases like "SELECT 'hello'"
>> should not have tried to use the pager, because that would've produced
>> less than a screenful of data
>
> At some point emacs was mentioned as the terminal:
>
>>> And I guess I did that intentionally, my .bashrc has
>>>
>>>   # I use emacs shells, I got a "pager" already:
>>>   export PAGER=''
>
> The M-x shell mode of emacs has a so-called "dumb" terminal
> emulation (that's the value of $TERM) where the notion of a "page"
> doesn't quite apply.
>
> For instance, when using emacs 24.3 with my default pager on an
> Ubuntu desktop, this is what I get:
>
> test=> select 1;
> WARNING: terminal is not fully functional
> -  (press RETURN)
>  ?column?
> ----------
>         1
> (1 row)
>
> I suspect that psql is unable to determine the screen size
> of the "dumb" terminal, and that it's the fault of the terminal
> rather than psql.
> The warning is displayed by "less" AFAICS.
>
> There are other psql features like tab-completion that don't work
> in this mode because emacs interpret keystrokes first for
> itself, in effect mixing emacs functionalities with these of the
> application run in the terminal. It's awesome sometimes
> and irritating at other times depending on what you expect :)
>
> OTOH it has also a M-x term command/mode that provides a
> more sophisticated screen emulation into which paging seems
> to work exactly like in a normal terminal and the emacs key bindings
> are turned off.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite

  33. Re: [HACKERS] Select works only when connected from login postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2016-12-07T20:04:58Z

    Joseph Brenner <doomvox@gmail.com> writes:
> I thought I'd reproduced the behavior in an xterm, but I was just
> trying again and I don't see it.  It does seem that the dumbness of my
> dumb terminal is a factor.

Evidently.

> If I understand the way this works, it could be an even more baffling
> behavior if I were using an xterm: with a blank PAGER your output
> would disappear only if the select exceeded a certain number of
> lines...

Yeah, that was exactly the behavior I was seeing before fixing it
(the fix is pushed btw).

			regards, tom lane