Re: Select works only when connected from login postgres - Mailing list pgsql-general
From | Joseph Brenner |
---|---|
Subject | Re: Select works only when connected from login postgres |
Date | |
Msg-id | CAFfgvXXwiPd0vicMAo5xeUUNJeAyPQo0=mUasL3QpqUMWx2jYQ@mail.gmail.com Whole thread Raw |
In response to | Re: Select works only when connected from login postgres (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Select works only when connected from login postgres
Re: Select works only when connected from login postgres Re: Select works only when connected from login postgres |
List | 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
pgsql-general by date: