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.klaver@aklaver.com