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