Thread: pgsql/php3/apache authentication
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. ]
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
[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) #
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
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. ]
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.
> >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) #
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
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
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