Re: Select works only when connected from login postgres

Yes, and sorry about the re-post.  I thought my original message was
hung-up in moderation, so I was doing an unsub/resub fandango to get
email addresses to match.


On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> 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