Authenticating user `postgres'

Arcady Genkin <a.genkin@utoronto.ca> writes:

The problem I'm having is that I want local UNIX user `postgres', who
is the database superuser, to be able to connect to any database
without a password.

You can't make postgres' life any easier than you make it for anyone
else coming from the same machine, because the authentication mechanism
is chosen on the basis of the client machine and (possibly) the target
database --- not on the basis of who the user is. Offhand I'd think it
foolish to make it easier to get into the superuser account than regular
accounts anyway.

One possibility is to run an IDENT daemon and allow ident-authenticated
connections from 127.0.0.1. Then "pg_dumpall -h localhost" would work
without a password. Trusting other people's IDENT daemons is widely
considered a bad idea, but I see no reason not to trust your own.

regards, tom lane

Actually, you can, but I find it dubious from a security standpoint. You can
set the environment variables PGUSER and PGPASSWORD in your shell startup
script for the postgres user.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

Alvaro Herrera <alvherre@atentus.com> writes:

The problem I'm having is that I want local UNIX user `postgres', who
is the database superuser, to be able to connect to any database
without a password. I want, for example, to be able to run a cronjob
like "pg_dumpall >backup", but I can't do that with my current setup.

You have at least two possible ways to solve this:
1. use the PGPASSWORD environment variable to hold the postgres
password.

But what if user 'postgres' has different passwords in different
databases? Or is it only template[12] that matter for pg_dumpall?

2. use a "ident" entry in pg_hba.conf to authorize only localhost.

But I only want UNIX user *postgres* to be able to connect without
password.

I remember there being a "match" auth-type...

It isn't in the docs...
http://www.postgresql.org/idocs/index.php?client-authentication.html

Thank you for your reply,
--
Arcady Genkin
i=1; while 1, hilb(i); i=i+1; end

Tom Lane <tgl@sss.pgh.pa.us> writes:

Offhand I'd think it foolish to make it easier to get into the
superuser account than regular accounts anyway.

Not so much if the database only listens on unix domain socket, which
has tight permissions, and a UNIX user has to identify himself with a
valid password anyways.

One possibility is to run an IDENT daemon and allow ident-authenticated
connections from 127.0.0.1. Then "pg_dumpall -h localhost" would work
without a password. Trusting other people's IDENT daemons is widely
considered a bad idea, but I see no reason not to trust your own.

I don't want to enable TCP connections at all.

Besides, I want other local clients to use passwords.

Thanks for your reply,
--
Arcady Genkin
i=1; while 1, hilb(i); i=i+1; end

Arcady Genkin <a.genkin@utoronto.ca> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Offhand I'd think it foolish to make it easier to get into the
superuser account than regular accounts anyway.

Not so much if the database only listens on unix domain socket, which
has tight permissions, and a UNIX user has to identify himself with a
valid password anyways.

So? If you can trust local connections from the user who is superuser
to be correctly authenticated, then you can also trust local connections
from the users who are non-superusers. I really completely fail to see
the point of requiring a password to connect to non-critical accounts
while having no password (*LESS* security) for the critical superuser
account.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Arcady Genkin <a.genkin@utoronto.ca> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Offhand I'd think it foolish to make it easier to get into the
superuser account than regular accounts anyway.

Not so much if the database only listens on unix domain socket, which
has tight permissions, and a UNIX user has to identify himself with a
valid password anyways.

So? If you can trust local connections from the user who is superuser
to be correctly authenticated, then you can also trust local connections
from the users who are non-superusers. I really completely fail to see
the point of requiring a password to connect to non-critical accounts
while having no password (*LESS* security) for the critical superuser
account.

Suppose that one of the non-superusers accounts is user `apache'.
There is a higher chance that this user account is compromised, than
the `postgres' account. I can see your point, though.
--
Arcady Genkin