Date: Tue, 30 Jan 2007 23:15:42 -0700 From: Michael Fuhr <mike@fuhr.org> To: George Hartzell <hartzell@alerce.com> Cc: Bill Moran <wmoran@collaborativefusion.com>, freebsd-ports@freebsd.org Subject: Re: postgresql's 502.pgsql periodic script and passwords Message-ID: <20070131061541.GA24386@winnie.fuhr.org> In-Reply-To: <17855.37182.608042.111363@satchel.alerce.com> References: <20070130010910.GA90927@winnie.fuhr.org> <17855.37182.608042.111363@satchel.alerce.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20070131061541.GA24386>