Select works only when connected from login postgres - Mailing list pgsql-general

From Joseph Brenner
Subject Select works only when connected from login postgres
Date
Msg-id CAFfgvXWLOE2novHzYjmQK8-J6TmHz42G8f3X0SORM44+stUGmw@mail.gmail.com
Whole thread Raw
Responses Re: Select works only when connected from login postgres
Re: Select works only when connected from login postgres
List pgsql-general
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 |
   ...


pgsql-general by date:

Previous
From: Kiriakos Georgiou
Date:
Subject: Re: Avoid sorting when doing an array_agg
Next
From: Adrian Klaver
Date:
Subject: Re: Select works only when connected from login postgres