Re: Select works only when connected from login postgres

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

Did you receive the previous suggestions?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com