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

[Joseph Brenner]

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


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

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

[Joseph Brenner]

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 <doom...@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 <t...@sss.pgh.pa.us> wrote:
>> Joseph Brenner <doom...@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


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

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

[Joseph Brenner]

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 <t...@sss.pgh.pa.us> wrote:
> Joseph Brenner <doom...@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


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

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

[Joseph Brenner]

> 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 <t...@sss.pgh.pa.us> wrote:
> Joseph Brenner <doom...@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


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

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

[Joseph Brenner]

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 <doom...@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.johns...@gmail.com> wrote:
>> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doom...@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;'
>> 
>>
>> 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.
>>


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

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

[Joseph Brenner]

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.johns...@gmail.com> wrote:
> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doom...@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;'
> 
>
> 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.
>


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

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

[Joseph Brenner]

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 <doom...@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 <doom...@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.kla...@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.kla...@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

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

[Joseph Brenner]

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 <doom...@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.kla...@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.kla...@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

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

[Joseph Brenner]

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

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

[Joseph Brenner]

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 <t...@sss.pgh.pa.us> wrote:
> Joseph Brenner <doom...@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, ju

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

[Joseph Brenner]

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

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

[Joseph Brenner]

> 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.kla...@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/1S  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 (0x7fff033e2000)
>> libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x7f2c34e8f000)
>> libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
>> (0x7f2c34c45000)
>> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f2c34944000)
>> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00

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

[Joseph Brenner]

>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.johns...@gmail.com> wrote:
> On Saturday, December 3, 2016, Joseph Brenner <doom...@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

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

[Joseph Brenner]

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/1S  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 (0x7fff033e2000)
libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x7f2c34e8f000)
libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
(0x7f2c34c45000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f2c34944000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f2c34599000)
libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
(0x7f2c34338000)
libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
(0x7f2c33f3c000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x7f2c33d1f000)
libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x7f2c33af5000)
/lib64/ld-linux-x86-64.so.2 (0x7f2c350bc000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f2c338f1000)

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 (0x7fe9db2ea000)

ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x7fa7337ec000)

On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Joseph Brenner <doom...@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-defau

Re: [GENERAL] No select privileges when not connecting from login postgres

[Joseph Brenner]

> Save yourself some trouble and use the PGDG repos...

Yes, I was just trying one of those a little while ago.  It exhibits
the exact same behavior as my build from scratch.

> In your psql commands you do not show connecting to port 5433

That's correct: my reading of the installation instructions is that
I'm not supposed to have to do that: if you feed an alternate port
number to configure, it's supposed to change the default for both
server and client.

There was at least one experiment though where I started both server
and client with the usual port, 5432-- my feeling is that can't be the
problem, but I'll probably look into some more (Tom Lane seems to feel
there might be an issue there).



On Sat, Dec 3, 2016 at 4:46 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 12/03/2016 02:55 PM, Joseph Brenner wrote:
>>
>> The version in the Debian stable repos right now is 9.4, and I saw an
>> issue with it I wanted to check against the latest version, so I did a
>> build of it from a tarball.
>
>
> Save yourself some trouble and use the PGDG repos:
>
> https://www.postgresql.org/download/linux/debian/
>
> PostgreSQL Apt Repository
>
>>
>> (Admittedly, there's no particular reason I need to be running stable,
>> and I was just wondering the other day why I wasn't using testing or
>> unstable).
>>
>> The build steps I used are relatively conventional (following the
>> directions in the INSTALL file): this is how I ended up with an
>> installation in /usr/local.
>>
>> The only thing unusual about the steps that I followed was I built
>> with port 5433 (rather than 5432) as the default, to make it easier to
>> run it in parallel with the system version, and I'm trying to use the
>> "with-perl" build option to link to a fresh build of perl:
>
>
> In your psql commands you do not show connecting to port 5433
>
>
>>
>> sudo su
>> export LD_LIBRARY_PATH='/home/doom/End/Sys/Perl5/perl-5.24.0'
>> ldconfig
>> ./configure --with-pgport=5433 --with-perl --with-openssl
>>
>> make
>> make install
>> adduser postgres
>> mkdir /usr/local/pgsql/data
>> chown postgres /usr/local/pgsql/data
>> mkdir /var/lib/postgresql-9.6.1/
>> chown postgres /var/lib/postgresql-9.6.1/
>> su - postgres
>> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
>>
>> /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >
>> /var/lib/postgresql-9.6.1/logfile 2>&1 &
>>
>>
>> On Sat, Dec 3, 2016 at 7:23 AM, rob stone <floripa...@gmail.com> wrote:
>>>
>>>
>>> On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote:
>>>>
>>>> I'm trying to get a new build of 9.6.1 working on a machine
>>>> running Debian stable (jessie) and I'm seeing some odd
>>>> behavior where things work correctly if I run psql when
>>>> logged in as postgres, but if I run it as user 'doom' (my
>>>> usual login), I don't seem to have any select privileges.
>>>> Even this fails silenlty:
>>>>
>>>>   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
>>>>(1 row)
>>>>
>>>>
>>>
>>> I run Debian testing (stretch/sid).
>>>
>>> For years now, the Postgres binaries are stored in:-
>>>
>>> /usr/lib/postgresql//bin
>>>
>>> and are root:root owned.
>>>
>>> E.g.:-
>>>
>>> /usr/lib/postgresql/9.6/bin/psql appears as:-
>>>
>>> -rwxr-xr-x 1 root root  580864 Nov  2 21:04 psql
>>>
>>> So, it is executable by anybody, which is not an issue with me.
>>>
>>> The reason behind this path convention is to enable you to run
>>> different major versions of Postgres on the same computer.
>>>
>>> I don't know how you installed Postgres and all I can suggest for
>>> starters is to physically remove it and re-install from the Debian
>>> packages.
>>>
>>> HTH,
>>> Rob
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@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

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

[Joseph Brenner]

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.kla...@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  DATABASEUSERADDRESS METHOD
>> local   all all trust
>> hostall all 127.0.0.1/32trust
>>
>> 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 postgres42 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.kla...@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

Re: [GENERAL] No select privileges when not connecting from login postgres

[Joseph Brenner]

> Any unusual errors in the logs?  Or maybe a "\o /somefile" in your
~doom/.psqlrc?

No, nothing much in the logs after "autovacuum launcher started", and
I don't have a .psqlrc file.

On Sat, Dec 3, 2016 at 6:56 AM, Julien Rouhaud
<julien.rouh...@dalibo.com> wrote:
> On Wed, Nov 30, 2016 at 08:48:41PM -0800, Joseph Brenner wrote:
>> I'm trying to get a new build of 9.6.1 working on a machine
>> running Debian stable (jessie) and I'm seeing some odd
>> behavior where things work correctly if I run psql when
>> logged in as postgres, but if I run it as user 'doom' (my
>> usual login), I don't seem to have any select privileges.
>> Even this fails silenlty:
>>
>>   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
>>(1 row)
>> [...]
>> I'm running out of ideas for things to check.  Any suggestions?
>>
>
> Any unusual errors in the logs?  Or maybe a "\o /somefile" in your
> ~doom/.psqlrc?
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org


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

Re: [GENERAL] No select privileges when not connecting from login postgres

[Joseph Brenner]

The version in the Debian stable repos right now is 9.4, and I saw an
issue with it I wanted to check against the latest version, so I did a
build of it from a tarball.

(Admittedly, there's no particular reason I need to be running stable,
and I was just wondering the other day why I wasn't using testing or
unstable).

The build steps I used are relatively conventional (following the
directions in the INSTALL file): this is how I ended up with an
installation in /usr/local.

The only thing unusual about the steps that I followed was I built
with port 5433 (rather than 5432) as the default, to make it easier to
run it in parallel with the system version, and I'm trying to use the
"with-perl" build option to link to a fresh build of perl:

sudo su
export LD_LIBRARY_PATH='/home/doom/End/Sys/Perl5/perl-5.24.0'
ldconfig
./configure --with-pgport=5433 --with-perl --with-openssl

make
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
mkdir /var/lib/postgresql-9.6.1/
chown postgres /var/lib/postgresql-9.6.1/
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >
/var/lib/postgresql-9.6.1/logfile 2>&1 &


On Sat, Dec 3, 2016 at 7:23 AM, rob stone <floripa...@gmail.com> wrote:
>
> On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote:
>> I'm trying to get a new build of 9.6.1 working on a machine
>> running Debian stable (jessie) and I'm seeing some odd
>> behavior where things work correctly if I run psql when
>> logged in as postgres, but if I run it as user 'doom' (my
>> usual login), I don't seem to have any select privileges.
>> Even this fails silenlty:
>>
>>   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
>>(1 row)
>>
>>
>
> I run Debian testing (stretch/sid).
>
> For years now, the Postgres binaries are stored in:-
>
> /usr/lib/postgresql//bin
>
> and are root:root owned.
>
> E.g.:-
>
> /usr/lib/postgresql/9.6/bin/psql appears as:-
>
> -rwxr-xr-x 1 root root  580864 Nov  2 21:04 psql
>
> So, it is executable by anybody, which is not an issue with me.
>
> The reason behind this path convention is to enable you to run
> different major versions of Postgres on the same computer.
>
> I don't know how you installed Postgres and all I can suggest for
> starters is to physically remove it and re-install from the Debian
> packages.
>
> HTH,
> Rob


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

Re: [GENERAL] No select privileges when not connecting from login postgres

[Joseph Brenner]

Logged in as "doom", -V shows version 9.6.1 as I'd expect:

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

To *literally* answer your question though, I have a different version
installed in my $PATH:

psql -V
psql (PostgreSQL) 9.4.9

But yes, I've been invoking psql the same way as login 'doom' or login
'postgres':

/usr/local/pgsql/bin/psql --dbname=doom --username=doom



On Sat, Dec 3, 2016 at 7:10 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 11/30/2016 08:48 PM, Joseph Brenner wrote:
>>
>> I'm trying to get a new build of 9.6.1 working on a machine
>> running Debian stable (jessie) and I'm seeing some odd
>> behavior where things work correctly if I run psql when
>> logged in as postgres, but if I run it as user 'doom' (my
>> usual login), I don't seem to have any select privileges.
>> Even this fails silenlty:
>>
>>   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
>>(1 row)
>>
>> 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).
>>
>> Looking at how the program files are installed, 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
>>
>> So I added doom to the staff group and reloaded pg, but that
>> didn't help either.  The files in the data tree are all
>> owned by postgres, but I don't think that's unusual:
>>
>>   drwx-- 1 postgres postgres42 Nov 26 16:14 base
>>
>> I'm running out of ideas for things to check.  Any suggestions?
>
>
> When you are logged in as OS user 'doom', the case that is not working, what
> does:
>
> psql -V
>
> show?
>
> Is the psql connection the exact same as the one you show for the postgres
> OS user case?
>
>
>
>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@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

[GENERAL] Select works only when connected from login postgres

[Joseph Brenner]

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  DATABASEUSERADDRESS METHOD
local   all all trust
hostall all 127.0.0.1/32trust

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


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

[GENERAL] No select privileges when not connecting from login postgres

[Joseph Brenner]

I'm trying to get a new build of 9.6.1 working on a machine
running Debian stable (jessie) and I'm seeing some odd
behavior where things work correctly if I run psql when
logged in as postgres, but if I run it as user 'doom' (my
usual login), I don't seem to have any select privileges.
Even this fails silenlty:

  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
   (1 row)

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

Looking at how the program files are installed, 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

So I added doom to the staff group and reloaded pg, but that
didn't help either.  The files in the data tree are all
owned by postgres, but I don't think that's unusual:

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

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


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

Re: [GENERAL] How to do a CREATE DATABASE and then connect to it?

[Joseph Brenner]

Terry Lee Tucker [EMAIL PROTECTED] wrote:

 Joseph Brenner [EMAIL PROTECTED] wrote:

   Joseph Brenner wrote:
 
After you do a CREATE DATABASE, how do you programatically
connect to what you just created?


  It's not a terribly major point, I'm just wondering if it's true that
  there's no postgres SQL analog of the psql \c command.
 
  For example, this certainly works in perl:
 
 use DBI;
 
 my $dbh_1 = DBI-connect(dbi:Pg:dbname=template1, $owner, 
  $db_password, 
   { RaiseError = 1, AutoCommit = 1 }); 
 $dbh_1-do(CREATE DATABASE new_test_db);
 
 $dbh_1-disconnect();
 
  my $dbh_2 = DBI-connect(dbi:Pg:dbname=new_test_db, $owner, 
  $db_password, 
  { RaiseError = 1, AutoCommit =  1 }); 
 
  $dbh_2-do(CREATE TABLE whocares (meaningless INTEGER, blather  
  TEXT)); 
 
  But the need for those two DBI-connects seems inelegant to me.


 I don't see the point. There are two connects either way.
 psql:
 Connection 1: psql template1
 Connection 2: \c new_test_db
 
 Perl:
 Connection 1:  my $dbh_1 = DBI-connect(dbi:Pg:dbname=template1, blah, blah
 Connection 2:  DBI-connect(dbi:Pg:dbname=new_test_db, $owner, blah, blah
 
 The only difference, at least from my point of view, is the method used and 
 the extra disconnect(). But even so, the extra disconnect() is not really 
 necessary.

I think there are two different connects we're talking about here, 
one is the connection to the postgresql, the other is the connection 
to the database (i.e. the dbname, which probably should've been 
called the catalog). 

My guess (and it's only a guess) is that connecting to the postmaster 
is relatively expensive, and that a (hypothetical) CONNECT dbname 
command would be much faster.  

This isn't a big point with me (at least at the moment) because while I
may be pragmatically creating/using/dropping databases soon, I'm not
likely to be doing this in production code.

I was just coming back to postgres after doing other things for awhile,
and the absence of something like a CONNECT dbname in it's SQL 
struck me as a little odd. 


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] How to do a CREATE DATABASE and then connect to it?

[Joseph Brenner]

After you do a CREATE DATABASE, how do you programatically 
connect to what you just created?  

In the psql monitor, you'd use the \c command. 

If the DATABASE already exists when you connect to postgresql, 
you use the name when you connect (e.g. dbname=...). 

I'm getting the impression I need to do this in multiple steps, 
which is workable, but seems a little silly.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Querying for strings that match after prefix

[Joseph Brenner]

badlydrawnbhoy [EMAIL PROTECTED] wrote:

 I hope this is the right forum for this, but please correct me if
 somewhere else is more appropriate.
 
 I need to locate all the entries in a table that match , but only after
 a number of characters have been ignored. I have a table of email
 addresses, and someone else has erroneously entered some addresses
 prefixed with 'mailto:', which I'd like to ignore.
 
 An example would be: [EMAIL PROTECTED] should match
 mailto:[EMAIL PROTECTED]
 
 I've tried the following
 
 select address
 from people
 where address = (select replace(address, 'mailto:', '') from people);
 
 which gives me the error
 
 ERROR:  more than one row returned by a subquery used as an expression



There's no need to use a sub-select for this, this should do the job:

  SELECT REPLACE(address, 'mailto:', '') FROM people;


You also have some options for fuzzy matching in the WHERE clause, e.g.  

  SELECT address FROM people WHERE address LIKE '[EMAIL PROTECTED]' 

Will find all email addresses like [EMAIL PROTECTED], whether or not there's a
'mailto:' prefix.  (% matches any character string). 


This will find all the records with the erroneous mailto:; prefix:

  SELECT address FROM people WHERE address LIKE 'mailto:%' 


---(end of broadcast)---
TIP 6: explain analyze is your friend