Re: Too easy to log in as the "postgres" user?

I've noticed that if I just log in to my server, I don't su to root,
or become the postgres user, I can get straight into the database as
the postgres user merely with "psql -U postgres -h localhost".  My
user account isn't a member of the postgres group.

It appears I've not applied my security settings correctly.  What can
I do to prevent access this way?  I'd still want to be able to su to
the postgres user and log in that way, but not with the -U parameter
allowing access.

The pg_hba.conf is probably relevant here, so this is the setup:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

Thanks

Thom Brown
Crawley, UK

In response to Thom Brown :
> I've noticed that if I just log in to my server, I don't su to root,
> or become the postgres user, I can get straight into the database as
> the postgres user merely with "psql -U postgres -h localhost".  My
> user account isn't a member of the postgres group.
>
> It appears I've not applied my security settings correctly.  What can
> I do to prevent access this way?  I'd still want to be able to su to
> the postgres user and log in that way, but not with the -U parameter
> allowing access.
>
> The pg_hba.conf is probably relevant here, so this is the setup:
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> local all all trust
> # IPv4 local connections:
> host all all 127.0.0.1/32 trust
> # IPv6 local connections:
> host all all ::1/128 trust

Try to change trust to sameuser.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)

> From: Thom Brown <thombrown(at)gmail(dot)com>
> Subject: [GENERAL] Too easy to log in as the "postgres" user?
> To: "PGSQL Mailing List" <pgsql-general(at)postgresql(dot)org>
> Date: Thursday, 15 October, 2009, 11:38 AM
> I've noticed that if I just log in to
> my server, I don't su to root,
> or become the postgres user, I can get straight into the
> database as
> the postgres user merely with "psql -U postgres -h
> localhost".  My
> user account isn't a member of the postgres group.
>
> It appears I've not applied my security settings
> correctly.  What can
> I do to prevent access this way?  I'd still want to be
> able to su to
> the postgres user and log in that way, but not with the -U
> parameter
> allowing access.

You just need to change the local connections to any authentication method other than trust.

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

Glyn

2009/10/15 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>:
>>
>> The pg_hba.conf is probably relevant here, so this is the setup:
>>
>> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>>
>> # "local" is for Unix domain socket connections only
>> local   all         all                               trust
>> # IPv4 local connections:
>> host    all         all         127.0.0.1/32          trust
>> # IPv6 local connections:
>> host    all         all         ::1/128               trust
>
> Try to change trust to sameuser.
>

I've made that change, but now PostgreSQL won't start, and outputs the
following error in the log:

2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT: line 74 of
configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
2009-10-15 11:52:41 BST [18720]: [3-1] LOG: invalid authentication
method "sameuser

* Thom Brown (thombrown(at)gmail(dot)com) wrote:
> 2009/10/15 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>:
> >>
> >> The pg_hba.conf is probably relevant here, so this is the setup:
> >>
> >> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
> >>
> >> # "local" is for Unix domain socket connections only
> >> local   all         all                               trust
> >> # IPv4 local connections:
> >> host    all         all         127.0.0.1/32          trust
> >> # IPv6 local connections:
> >> host    all         all         ::1/128               trust
> >
> > Try to change trust to sameuser.
> >
>
> I've made that change, but now PostgreSQL won't start, and outputs the
> following error in the log:
>
> 2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT: line 74 of
> configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
> 2009-10-15 11:52:41 BST [18720]: [3-1] LOG: invalid authentication
> method "sameuser

You need it to be 'ident sameuser', not just 'sameuser'. Also, only do
that for the 'local' line. Comment out the host lines if you don't need
them. If you do need them, change them to something else (eg: md5 if
you want password-based, gssapi if you have a Kerberos or MS/Active
Directory infrastructure, ldap is also an option, etc...).

'local' is used when connecting over a unix socket, eg: psql -d blah
'host' is used when connecting over a network: psql -d blah -h myhost

Stephen

Stephen

2009/10/15 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Thom Brown (thombrown(at)gmail(dot)com) wrote:
>>
>> I've made that change, but now PostgreSQL won't start, and outputs the
>> following error in the log:
>>
>> 2009-10-15 11:52:41 BST [18720]: [2-1] CONTEXT:  line 74 of
>> configuration file "/var/lib/postgresql/8.4/data/pg_hba.conf"
>> 2009-10-15 11:52:41 BST [18720]: [3-1] LOG:  invalid authentication
>> method "sameuser
>
> You need it to be 'ident sameuser', not just 'sameuser'.  Also, only do
> that for the 'local' line.  Comment out the host lines if you don't need
> them.  If you do need them, change them to something else (eg: md5 if
> you want password-based, gssapi if you have a Kerberos or MS/Active
> Directory infrastructure, ldap is also an option, etc...).
>
> 'local' is used when connecting over a unix socket, eg: psql -d blah
> 'host' is used when connecting over a network: psql -d blah -h myhost
>
>        Stephen
>

Okay, I've just ended up commenting out the host lines and it's
effective enough as far as logging in is concerned. However, the
websites which use the database are no longer able to connect. I
should point out that they are connecting to pgbouncer through a
specific port number. I haven't been successful in getting the sites
to connect through a unix socket.

Thom

* Thom Brown (thombrown(at)gmail(dot)com) wrote:
> Okay, I've just ended up commenting out the host lines and it's
> effective enough as far as logging in is concerned. However, the
> websites which use the database are no longer able to connect. I
> should point out that they are connecting to pgbouncer through a
> specific port number. I haven't been successful in getting the sites
> to connect through a unix socket.

If you want access controls on network-based connections, you'll have to
switch to using one of the other auth methods I mentioned. Probably the
simplest is to use 'md5' and then set passwords for the users who log
into the database.

Unix sockets are only possible if the application (pgbouncer, in your
case) and the database are on the same system. If this is the case, you
may need to set the unix_socket parameter in pgbouncer, and make sure
that you do not have 'host' set in pgbouncer.

Thanks,

Stephen

Thom,

You appear to be trusting all connections what I think you want is the
following:

local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 ident sameuser
# IPv6 local connections:
host all all ::1/128 ident sameuser

Remember that you need to get postgres to reread the file after
changing it by using pg_ctl reload or kill -HUP {pid}

Andy Bailey

On Thu, Oct 15, 2009 at 5:38 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> I've noticed that if I just log in to my server, I don't su to root,
> or become the postgres user, I can get straight into the database as
> the postgres user merely with "psql -U postgres -h localhost".  My
> user account isn't a member of the postgres group.
>
> It appears I've not applied my security settings correctly.  What can
> I do to prevent access this way?  I'd still want to be able to su to
> the postgres user and log in that way, but not with the -U parameter
> allowing access.
>
> The pg_hba.conf is probably relevant here, so this is the setup:
>
> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>
> # "local" is for Unix domain socket connections only
> local   all         all                               trust
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          trust
> # IPv6 local connections:
> host    all         all         ::1/128               trust
>
> Thanks
>
> Thom Brown
> Crawley, UK
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Andrew Bailey

(312) 866 9556

NOTA DE CONFIDENCIALIDAD Y DE NO DIVULGACIÓN:
La información contenida en este E-mail y sus archivos adjuntos es
confidencial y sólo puede ser utilizada por el individuo
o la empresa a la cual está dirigido. Si no es el receptor autorizado,
cualquier retención, difusión,
distribución o copia de este mensaje queda prohibida y sancionada por
la ley. Si por error recibe este
mensaje, favor devolverlo y borrarlo inmediatamente.

* Andrew Bailey (hazlorealidad(at)gmail(dot)com) wrote:
> You appear to be trusting all connections what I think you want is the
> following:
>
> local all all ident sameuser
> # IPv4 local connections:
> host all all 127.0.0.1/32 ident sameuser
> # IPv6 local connections:
> host all all ::1/128 ident sameuser
>
> Remember that you need to get postgres to reread the file after
> changing it by using pg_ctl reload or kill -HUP {pid}

ident sameuser for host connections really isn't recommend nor is
terribly secure, in general. Over localhost is better, but using local
is infinitely better, imo.

Stephen