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