Re: No select privileges when not connecting from login postgres

On 11/30/2016 08:48 PM, Joseph Brenner wrote:
> I'm trying to get a new build of 9.6.1 working on a machine
> running Debian stable (jessie) and I'm seeing some odd
> behavior where things work correctly if I run psql when
> logged in as postgres, but if I run it as user 'doom' (my
> usual login), I don't seem to have any select privileges.
> Even this fails silenlty:
>
> 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
> (1 row)
>
> 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).
>
> Looking at how the program files are installed, 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
>
> So I added doom to the staff group and reloaded pg, but that
> didn't help either. The files in the data tree are all
> owned by postgres, but I don't think that's unusual:
>
> drwx------ 1 postgres postgres 42 Nov 26 16:14 base
>
> I'm running out of ideas for things to check. Any suggestions?

When you are logged in as OS user 'doom', the case that is not working,
what does:

psql -V

show?

Is the psql connection the exact same as the one you show for the
postgres OS user case?

>
>

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