Select works only when connected from login postgres

From: Joseph Brenner <doomvox(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Select works only when connected from login postgres
Date: 2016-12-03 20:08:30
Message-ID: CAFfgvXWLOE2novHzYjmQK8-J6TmHz42G8f3X0SORM44+stUGmw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-12-03 20:13:18 Re: Select works only when connected from login postgres
Previous Message Kiriakos Georgiou 2016-12-03 18:08:23 Re: Avoid sorting when doing an array_agg

Browse pgsql-hackers by date

  From Date Subject
Next Message Adrian Klaver 2016-12-03 20:13:18 Re: Select works only when connected from login postgres
Previous Message Tobias Bussmann 2016-12-03 17:42:21 Re: Parallel execution and prepared statements