Search Postgresql Archives

Select works only when connected from login postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux