postgresql's 502.pgsql periodic script and passwords
Michael Fuhr
mike at fuhr.org
Wed Jan 31 06:15:58 UTC 2007
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
More information about the freebsd-ports
mailing list