From owner-freebsd-database Tue Sep 28 16:19:27 1999 Delivered-To: freebsd-database@freebsd.org Received: from fledge.watson.org (fledge.watson.org [204.156.12.50]) by hub.freebsd.org (Postfix) with ESMTP id 5D34214FA5 for ; Tue, 28 Sep 1999 16:19:24 -0700 (PDT) (envelope-from robert@cyrus.watson.org) Received: from fledge.watson.org (fledge.pr.watson.org [192.0.2.3]) by fledge.watson.org (8.9.3/8.9.3) with SMTP id TAA09575 for ; Tue, 28 Sep 1999 19:19:23 -0400 (EDT) (envelope-from robert@cyrus.watson.org) Date: Tue, 28 Sep 1999 19:19:23 -0400 (EDT) From: Robert Watson X-Sender: robert@fledge.watson.org Reply-To: Robert Watson To: database@freebsd.org Subject: Postgres -- ancillary data to authenticate? Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: owner-freebsd-database@FreeBSD.ORG Precedence: bulk I have a postgresql database set up on a server, and was upset when I discovered that psql -u allows authentication to the database as any other user without a password, as the default configuration is to trust all local connections. I was wondering if anyone knew of patches (or better yet, it being supported built-in) to use the sendmsg ancilary data to pass uids/gids and authentication the UNIX domain socket, or a setuid/gid/etc binary of psql that is trusuted to gather the info, etc. Similarly, whether anyone knew about support for PAM, BSD-style. My feeling is there should be a big warning label somewhere obvious saying "BY DEFAULT ALL USERS ON THE DATABASE SERVER HAVE FULL ACCESS TO ALL DATABASES" :-). Any suggestions? Robert N M Watson robert@fledge.watson.org http://www.watson.org/~robert/ PGP key fingerprint: AF B5 5F FF A6 4A 79 37 ED 5F 55 E9 58 04 6A B1 TIS Labs at Network Associates, Safeport Network Services To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-database" in the body of the message From owner-freebsd-database Wed Sep 29 0:46:23 1999 Delivered-To: freebsd-database@freebsd.org Received: from smtp04.wxs.nl (smtp04.wxs.nl [195.121.6.59]) by hub.freebsd.org (Postfix) with ESMTP id 44F3B14D1A for ; Wed, 29 Sep 1999 00:45:51 -0700 (PDT) (envelope-from asmodai@wxs.nl) Received: from daemon.ninth-circle.org ([195.121.197.159]) by smtp04.wxs.nl (Netscape Messaging Server 3.61) with ESMTP id AAB5F1D; Wed, 29 Sep 1999 09:45:49 +0200 Received: (from asmodai@localhost) by daemon.ninth-circle.org (8.9.3/8.9.3) id JAA38954; Wed, 29 Sep 1999 09:46:30 +0200 (CEST) (envelope-from asmodai) Date: Wed, 29 Sep 1999 09:46:30 +0200 From: Jeroen Ruigrok/Asmodai To: Robert Watson Cc: database@freebsd.org Subject: Re: Postgres -- ancillary data to authenticate? Message-ID: <19990929094630.E38679@daemon.ninth-circle.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0pre3i In-Reply-To: Organisation: Ninth-Circle Enterprises Sender: owner-freebsd-database@FreeBSD.ORG Precedence: bulk On [19990929 03:17], Robert Watson (robert@cyrus.watson.org) wrote: > >I have a postgresql database set up on a server, and was upset when I >discovered that psql -u allows authentication to the database as any other >user without a password, as the default configuration is to trust all >local connections. I was wondering if anyone knew of patches (or better >yet, it being supported built-in) to use the sendmsg ancilary data to pass >uids/gids and authentication the UNIX domain socket, or a setuid/gid/etc >binary of psql that is trusuted to gather the info, etc. Similarly, >whether anyone knew about support for PAM, BSD-style. Ehm, you missed the obvious: /usr/local/pgsql/lib/pg_hba.conf.sample which you need to copy to: /usr/local/pgsql/lib/pg_hba.conf and which controls access. >My feeling is there should be a big warning label somewhere obvious saying >"BY DEFAULT ALL USERS ON THE DATABASE SERVER HAVE FULL ACCESS TO ALL >DATABASES" :-). > > Robert N M Watson *grin* yeah, RTFM Robert ;) But seriously, this was all discussed in the manuals for installation IIRC. And there's always the SQL GRANT command plus database access restriction. There are options. You just missed a lot of ways to do them. HTH a bit, -- Jeroen Ruigrok van der Werven/Asmodai asmodai(at)wxs.nl The BSD Programmer's Documentation Project Network/Security Specialist BSD: Technical excellence at its best Millions for defence but not one cent for tribute. To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-database" in the body of the message From owner-freebsd-database Wed Sep 29 5:35:37 1999 Delivered-To: freebsd-database@freebsd.org Received: from fledge.watson.org (fledge.watson.org [204.156.12.50]) by hub.freebsd.org (Postfix) with ESMTP id F36651507D for ; Wed, 29 Sep 1999 05:35:32 -0700 (PDT) (envelope-from robert@cyrus.watson.org) Received: from fledge.watson.org (robert@fledge.pr.watson.org [192.0.2.3]) by fledge.watson.org (8.9.3/8.9.3) with SMTP id IAA14276; Wed, 29 Sep 1999 08:35:25 -0400 (EDT) (envelope-from robert@cyrus.watson.org) Date: Wed, 29 Sep 1999 08:35:25 -0400 (EDT) From: Robert Watson X-Sender: robert@fledge.watson.org Reply-To: Robert Watson To: Jeroen Ruigrok/Asmodai Cc: database@freebsd.org Subject: Re: Postgres -- ancillary data to authenticate? In-Reply-To: <19990929094630.E38679@daemon.ninth-circle.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: owner-freebsd-database@FreeBSD.ORG Precedence: bulk On Wed, 29 Sep 1999, Jeroen Ruigrok/Asmodai wrote: > On [19990929 03:17], Robert Watson (robert@cyrus.watson.org) wrote: > > > >I have a postgresql database set up on a server, and was upset when I > >discovered that psql -u allows authentication to the database as any other > >user without a password, as the default configuration is to trust all > >local connections. I was wondering if anyone knew of patches (or better > >yet, it being supported built-in) to use the sendmsg ancilary data to pass > >uids/gids and authentication the UNIX domain socket, or a setuid/gid/etc > >binary of psql that is trusuted to gather the info, etc. Similarly, > >whether anyone knew about support for PAM, BSD-style. > > Ehm, you missed the obvious: > > /usr/local/pgsql/lib/pg_hba.conf.sample > > which you need to copy to: > > /usr/local/pgsql/lib/pg_hba.conf > > and which controls access. Did that. > >My feeling is there should be a big warning label somewhere obvious saying > >"BY DEFAULT ALL USERS ON THE DATABASE SERVER HAVE FULL ACCESS TO ALL > >DATABASES" :-). > > > > Robert N M Watson > > *grin* > > yeah, RTFM Robert ;) > > But seriously, this was all discussed in the manuals for installation > IIRC. > > And there's always the SQL GRANT command plus database access > restriction. There are options. You just missed a lot of ways to do > them. > > HTH a bit, Well, the problem is that the default behavior for local, even once it's copied in, is # trust: No authentication is done. Trust that the user has the # authority to use whatever username he specifies. Before # PostgreSQL version 6, all authentication was done this way. And while you can map UNIX identities to database identities, there is no way to cause PostgreSQL to make use of UNIX authentication (PAM or otherwise) to authenticate the user. You can seperately maintain password files, but that is not what I want to do. Similarly, for IP, you can cause it to use ident, but that is a) untrustworthy except on localhost, and b) doesn't work with UNIX domain sockets. The closest configuration I have found for local access is to use identd (undesirable) and set an environmental variable for all users, PGHOST, to localhost to make it use IP instead of a UNIX domain socket. This way, a new user password is not required, which is what I had hoped to avoid. This is not ideal because it involves overriding configurations in haphazard ways via environmental variables. What I was hoping for was a way to authenticate UNIX domain sockets via the existing well-documented ancillary data, where the kernel provides the information to the postmaster over the domain socket, and/or support for handing off authentication to UNIX-style mechanisms, such as using getpwnam/crypt or getpwname/PAM. And as I mentioned, I was concerned that the default policy was "door-open" not "door-closed". On further inspection it is in the INSTALL document, but interestingly, the port does not copy that out of the build directories into /usr/local/share/doc/pgsql, or anywhere into /usr/local/pgsql that I can find. My feeling is that this counts as "not clearly documented", and that the default configuration should be "password" or "refused" in an installed pg_hba.conf, not default to trusted and the file not installed. And that since the port does not present the instructions to move the pg_hba.conf file, it should modify the post-install-notes to specifically mention this, or copy in the INSTALL file and point the user at it to manually complete the install. I was, from the start, well aware to the SQL grant/revoke/ACLs, but not that they were not enforced by default, as no authentication was performed in the default configuration. :-) Since the code doesn't seem to exist for UNIX domain authentication, I'll take a look at writing that tonight--adding a sendmsg/recvmsg over the socket shouldn't be too difficult, from the looks of the auth code. I don't have much experience with adding PAM support to programs, but that might be nice to add also--dealing with policy over when a database identity is mapped to a UNIX identity is not something I know enough about yet--by default createuser asks if it should map the database identity to the equivilent UNIX uid--I assume therefore that the database is using numeric uid's internally, and this mapping just reflects selecting the same uid for the same username? The sameuser flag in pg_hba.conf seems to be the policy mechanism to reflect that for ident... Robert N M Watson robert@fledge.watson.org http://www.watson.org/~robert/ PGP key fingerprint: AF B5 5F FF A6 4A 79 37 ED 5F 55 E9 58 04 6A B1 TIS Labs at Network Associates, Safeport Network Services To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-database" in the body of the message