
ok, so i have pg-7.0, apache 1.3.12 and php3 installed on a server.

i'm having difficulty coming up with an appropriate security model to cover
off what i want to do:

- queries via localhost (unix domain sockets) should assume that the pg_user
is the same as the unix user running the process.

- queries via tcp sockets should require a valid pg_user and password

the second is easy enough to facilitate.

the first i haven't been able to figure out.

with a pg_hba.conf entry of "local trust", the user can override their identity
and do anything they want.

with a pg_hba.conf entry of "local password" the user is forced to enter their
password every time.  this wouldn't work very well with scripts in crontabs.

am i missing something here?

-- 
[ Jim Mercer                 jim@reptiles.org              +1 416 506-0654 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]

Wed, 26 Apr 2000 13:22:11 -0400
Jim Mercer <jim@reptiles.org>
pgsql/php3/apache authentication
On Wed, 26 Apr 2000, Jim Mercer wrote:

> - queries via localhost (unix domain sockets) should assume that the pg_user
> is the same as the unix user running the process.

There's no way for the server to determine the system user name of the
other end of a domain socket; at least no one has implemented one yet. So
essentially this isn't going to work.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Thu, 27 Apr 2000 10:02:32 +0200 (MET DST)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] pgsql/php3/apache authentication
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> On Wed, 26 Apr 2000, Jim Mercer wrote:
>
> > - queries via localhost (unix domain sockets) should assume that the pg_user
> > is the same as the unix user running the process.
>
> There's no way for the server to determine the system user name of the
> other end of a domain socket; at least no one has implemented one yet. So
> essentially this isn't going to work.

    The  default  of  "local  all  trust"  is something I allways
    considered insecure. At least because the unix domain  socket
    isn't changed to mode 0700 after creation, so that only users
    in the unix dba (or whatever) group are trusted.

    If we add  a  permissions  field  to  the  local  entry,  the
    postmaster can chmod() the socket file after creating it (and
    maybe drain out waiting connections that slipped  in  between
    after  a  second  before  accepting  the first real one). The
    default hba would then read:

        local  all                               trust 0770
        host   all   127.0.0.1  255.255.255.255  ident sameuser

    There's IMHO no reason, why the postmaster shouldn't  try  to
    create  an  inet  socket bound to 127.0.0.1:pgport by default
    too.  And it must not be  considered  an  error  (while  some
    notice would be nice) if the creation of that socket fails.

    Also  we change libpq that if it get's an EPERM at connect(2)
    to the unix domain socket, it  tries  again  via  inet.  Some
    microseconds  overhead  but  transparent  for  non-dba  local
    users.

    Now someone can add users, he really trusts to the dba  group
    in  /etc/group.   Or  he can open the entire DB system to all
    local users by changing the permissions to 0777.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Thu, 27 Apr 2000 11:17:39 +0200 (CEST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] pgsql/php3/apache authentication
Peter Eisentraut writes:
> On Wed, 26 Apr 2000, Jim Mercer wrote:
> 
> > - queries via localhost (unix domain sockets) should assume that the pg_user
> > is the same as the unix user running the process.
> 
> There's no way for the server to determine the system user name of the
> other end of a domain socket; at least no one has implemented one yet. So
> essentially this isn't going to work.

The client can pass an SCM_CREDENTIALS (Linux) or SCM_CREDS (BSDish)
socket control message down the Unix domain socket and the kernel will
fill in the client's credentials (including PID, uid and gid) for the
receiver to read. Some Unices don't support this though. If noone else
implements this, I'll try to find time to do it myself though I've
only touched the server side of pg authentication before and haven't
looked at what exactly the client side sends across already. Without
SCM_CRED[ENTIAL]S, it gets very messy passing reliable (or even
semi-reliable) authentication information. STREAMS has another way to
send/receive credentials but not via the socket API.

--Malcolm

-- 
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services

Thu, 27 Apr 2000 10:51:32 +0100
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Re: [HACKERS] pgsql/php3/apache authentication
On Thu, Apr 27, 2000 at 11:17:39AM +0200, Jan Wieck wrote:
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > On Wed, 26 Apr 2000, Jim Mercer wrote:
> >
> > > - queries via localhost (unix domain sockets) should assume that the pg_user
> > > is the same as the unix user running the process.
> >
> > There's no way for the server to determine the system user name of the
> > other end of a domain socket; at least no one has implemented one yet. So
> > essentially this isn't going to work.

given that, i'm looking at changing things so that i use:

local        all                                           password
host         all         127.0.0.1       255.255.255.255   ident sameuser

this will force all connections through the unix domain socket to need a
password.

it will allow unfettered access if the launching process is owned by
a valid pg_user.

is there a performance penalty associated with forcing the bulk of my
processing through the loopback, as opposed to the unix domain socket?

-- 
[ Jim Mercer                 jim@reptiles.org              +1 416 506-0654 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]

Thu, 27 Apr 2000 14:58:47 -0400
Jim Mercer <jim@reptiles.org>
Re: [HACKERS] pgsql/php3/apache authentication
At 02:58 PM 27-04-2000 -0400, Jim Mercer wrote:
>On Thu, Apr 27, 2000 at 11:17:39AM +0200, Jan Wieck wrote:
>> [Charset iso-8859-1 unsupported, filtering to ASCII...]
>> > On Wed, 26 Apr 2000, Jim Mercer wrote:
>> >
>> > > - queries via localhost (unix domain sockets) should assume that the
pg_user
>> > > is the same as the unix user running the process.
>> >
>> > There's no way for the server to determine the system user name of the
>> > other end of a domain socket; at least no one has implemented one yet. So
>> > essentially this isn't going to work.
>
>given that, i'm looking at changing things so that i use:
>
>local        all                                           password
>host         all         127.0.0.1       255.255.255.255   ident sameuser
>
>this will force all connections through the unix domain socket to need a
>password.
>
>it will allow unfettered access if the launching process is owned by
>a valid pg_user.

I always thought ident services should be grouped with fortune cookie
services and so on :). But, since it's localhost it could work.

>is there a performance penalty associated with forcing the bulk of my
>processing through the loopback, as opposed to the unix domain socket?

I believe there's a bit more latency but it could be about a millisecond or
less.

You could always do some benchmarks. e.g. time 1000 queries which return
lots of data.

Cheerio,

Link.


Fri, 28 Apr 2000 09:12:13 +0800
Lincoln Yeoh <lylyeoh@mecomb.com>
Re: Re: [HACKERS] pgsql/php3/apache authentication
> >given that, i'm looking at changing things so that i use:
> >
> >local        all                                           password
> >host         all         127.0.0.1       255.255.255.255   ident sameuser
> >
> >this will force all connections through the unix domain socket to need a
> >password.
> >
> >it will allow unfettered access if the launching process is owned by
> >a valid pg_user.
>
> I always thought ident services should be grouped with fortune cookie
> services and so on :). But, since it's localhost it could work.

    Never  trust  an  identd running on a system you don't have a
    static ARP entry for -  right?  Still  not  secure  (on  some
    systems  it's  possible  to  fake  the mac address), but good
    enough for most purposes.

> >is there a performance penalty associated with forcing the bulk of my
> >processing through the loopback, as opposed to the unix domain socket?
>
> I believe there's a bit more latency but it could be about a millisecond or
> less.
>
> You could always do some benchmarks. e.g. time 1000 queries which return
> lots of data.

    One of the reasons  for  using  relational  databases  is  to
    reduce   the   amount  of  IO  needed  to  get  a  particular
    information.  So IPC  throughput  shouldn't  be  the  a  real
    problem  -  except  there  is  some major problem with the DB
    layout or the application coding.  In that case I'd suggest

    if it doesn't fit, don't force it - use a bigger hammer!


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Fri, 28 Apr 2000 03:52:37 +0200 (CEST)
wieck@debis.com (Jan Wieck)
Re: Re: [HACKERS] pgsql/php3/apache authentication
On Thu, 27 Apr 2000, Jan Wieck wrote:

>     The  default  of  "local  all  trust"  is something I allways
>     considered insecure.

No kidding.

>     If we add  a  permissions  field  to  the  local  entry,  the
>     postmaster can chmod() the socket file after creating it (and
>     maybe drain out waiting connections that slipped  in  between
>     after  a  second  before  accepting  the first real one). The
>     default hba would then read:
> 
>         local  all                               trust 0770
>         host   all   127.0.0.1  255.255.255.255  ident sameuser

I think I like that idea.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Fri, 28 Apr 2000 10:05:31 +0200 (MET DST)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] pgsql/php3/apache authentication
On Thu, 27 Apr 2000, Malcolm Beattie wrote:

> > There's no way for the server to determine the system user name of the
> > other end of a domain socket; at least no one has implemented one yet. So
> > essentially this isn't going to work.
> 
> The client can pass an SCM_CREDENTIALS (Linux) or SCM_CREDS (BSDish)
> socket control message down the Unix domain socket and the kernel will
> fill in the client's credentials (including PID, uid and gid) for the
> receiver to read. Some Unices don't support this though.

This might be doable but I think I'd like to see exactly how many Unices
support this. I wouldn't be too excited about a solution that only works
on Linux and ???BSD (or any other combination). Is there any way one can
check?


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Fri, 28 Apr 2000 10:09:25 +0200 (MET DST)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] pgsql/php3/apache authentication
Peter Eisentraut writes:
> On Thu, 27 Apr 2000, Malcolm Beattie wrote:
> 
> > > There's no way for the server to determine the system user name of the
> > > other end of a domain socket; at least no one has implemented one yet. So
> > > essentially this isn't going to work.
> > 
> > The client can pass an SCM_CREDENTIALS (Linux) or SCM_CREDS (BSDish)
> > socket control message down the Unix domain socket and the kernel will
> > fill in the client's credentials (including PID, uid and gid) for the
> > receiver to read. Some Unices don't support this though.
> 
> This might be doable but I think I'd like to see exactly how many Unices
> support this. I wouldn't be too excited about a solution that only works
> on Linux and ???BSD (or any other combination). Is there any way one can
> check?

An autoconf test of the various ways would be possible. Since my
previous message, I've found that Linux has another way of getting
peer credentials too. The disadvantage is that it's Linux-only (as
far as I know). The big advantage is that it doesn't need any changes
to the client side at all: the server simply does
    struct ucred peercred;
    int solen = sizeof(peercred);
    getsockopt(port->sock, SOL_SOCKET, SO_PEERCRED, &peercred, &solen);
and you then have peercred.uid (and gid and pid) telling you who bound
the client socket.

I've done a small patch (it only touches backend/libpq/auth.c,
backend/libpq/hba.c and include/libpq/hba.h) against 7.0RC1 (though I
guess it would probably work against pretty much any version). It
only affects the build of postmaster. It lets you use the keyword
"ident" in pg_hba.conf on Unix domain connections as well as the
normal use for just TCP connections (with a usermap, just the same).
For TCP, ident means "ask the peer's ident server for username
information"; for Unix domain the patch makes ident mean "ask the
kernel about the peer's uid information and look username up with
getpwuid". I've tested it here and it seems to work fine: you have
compile postmaster (at least) with -DHAVE_SO_PEERCRED since I didn't
want to get into messing with autoconf at this stage. For example,
    make COPT="-DHAVE_SO_PEERCRED"
works for me. I've made the patch available as
    http://users.ox.ac.uk/~mbeattie/postgresql-peercred.patch
since I'm not subscribed to pgsql-patches. It's Linux-only (until or
unless other O/Ses pick up SO_PEERCRED) so it may well not be
considered portable enough to include in the main distribution
(except as a separate patch maybe?) but some people might like to
apply it for the added security themselves.

--Malcolm

-- 
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services

Wed, 10 May 2000 10:22:30 +0100
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Re: [HACKERS] pgsql/php3/apache authentication
On Wed, May 10, 2000 at 10:22:30AM +0100, Malcolm Beattie wrote:
> 
> I've done a small patch (it only touches backend/libpq/auth.c,
> backend/libpq/hba.c and include/libpq/hba.h) against 7.0RC1 (though I
> guess it would probably work against pretty much any version). It
> works for me. I've made the patch available as
>     http://users.ox.ac.uk/~mbeattie/postgresql-peercred.patch
> since I'm not subscribed to pgsql-patches. It's Linux-only (until or

Take a look at subscribing to pgsql-loophole: That'll let you post to
the pgsql lists without receiving traffic from them directly: most useful
for pgsql-patches.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Wed, 10 May 2000 09:23:30 -0500
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Re: pgsql/php3/apache authentication
On Wed, 10 May 2000, Ross J. Reedstrom wrote:

> On Wed, May 10, 2000 at 10:22:30AM +0100, Malcolm Beattie wrote:
> > 
> > I've done a small patch (it only touches backend/libpq/auth.c,
> > backend/libpq/hba.c and include/libpq/hba.h) against 7.0RC1 (though I
> > guess it would probably work against pretty much any version). It
> > works for me. I've made the patch available as
> >     http://users.ox.ac.uk/~mbeattie/postgresql-peercred.patch
> > since I'm not subscribed to pgsql-patches. It's Linux-only (until or
> 
> Take a look at subscribing to pgsql-loophole: That'll let you post to
> the pgsql lists without receiving traffic from them directly: most useful
> for pgsql-patches.

actually, do a 'subscribe-nomail' to any one of the lists will also give
you that ability ... 

This new majordomo2 has features up the wazoo ...



Wed, 10 May 2000 11:44:34 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: pgsql/php3/apache authentication