Re: Select works only when connected from login postgres

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