From owner-freebsd-ports@FreeBSD.ORG Wed Jan 31 06:15:58 2007 Return-Path: X-Original-To: freebsd-ports@freebsd.org Delivered-To: freebsd-ports@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 8BC9816A400 for ; Wed, 31 Jan 2007 06:15:58 +0000 (UTC) (envelope-from mfuhr@fuhr.org) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by mx1.freebsd.org (Postfix) with ESMTP id 2445313C4A3 for ; Wed, 31 Jan 2007 06:15:57 +0000 (UTC) (envelope-from mfuhr@fuhr.org) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l0V6FhiE095877 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 30 Jan 2007 23:15:46 -0700 (MST) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id l0V6FhPQ024693; Tue, 30 Jan 2007 23:15:43 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.8/8.13.8/Submit) id l0V6Fg5B024692; Tue, 30 Jan 2007 23:15:42 -0700 (MST) (envelope-from mfuhr) Date: Tue, 30 Jan 2007 23:15:42 -0700 From: Michael Fuhr To: George Hartzell Message-ID: <20070131061541.GA24386@winnie.fuhr.org> References: <20070130010910.GA90927@winnie.fuhr.org> <17855.37182.608042.111363@satchel.alerce.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <17855.37182.608042.111363@satchel.alerce.com> Cc: Bill Moran , freebsd-ports@freebsd.org Subject: Re: postgresql's 502.pgsql periodic script and passwords X-BeenThere: freebsd-ports@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Porting software to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 31 Jan 2007 06:15:58 -0000 On Tue, Jan 30, 2007 at 10:41:02AM -0800, George Hartzell wrote: > Michael Fuhr writes: > > Another possibility would be to use the "ident" method over a local > > (i.e., Unix-domain) socket. You'd be authenticating via SO_PEERCRED; > > no .pgpass file would be necessary. > > I saw a reference to that via google, and tried it as sketched, but it > didn't fly. It seemed to involve pg_hga.conf, a pg_ident.conf, > and.... > > Can you describe a known-working configuration? Here's an example pg_hba.conf: # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres ident sameuser local all all md5 # IPv4 local connections: host all postgres 0.0.0.0/0 reject host all all 127.0.0.1/32 md5 This configuration allows the OS user postgres to connect as the database user postgres via a Unix socket without having to enter a password (ident sameuser). All other Unix socket connections or TCP connections from localhost (127.0.0.1) must provide a password. Any OS user other than postgres who attempts to connect as the database user postgres over a Unix socket will be rejected. Any attempts to connect as the database user postgres over a TCP connection from anywhere (0.0.0.0/0) will be rejected. The "ident sameuser" method causes the backend to ask the OS what user is at the other end of the Unix socket. If the OS username matches the database username then the connection is authenticated; otherwise it's rejected. You can use pg_ident.conf to define other OS-user-to-database-user mappings. > Would this be somehow more secure or flexible (aka "better") than the > .pgpass solution? Using ident authentication over a Unix socket allows a particular OS user or set of OS users to connect to the database without having to enter a password, while not allowing anybody else to do so. This is useful for unattended processes such as cron jobs. An advantage over using .pgpass is that you don't need to have cleartext passwords lying around where they might be compromised (wrong file permissions, misplaced backup tape, shoulder surfing, etc.). -- Michael Fuhr