Thread: Authenticating user `postgres'
Hello: I have a pg_hba.conf question. Currently the authentication is setup as thus: local db1 password passwd.db1 local db2 password passwd.db2 local all password passwd.su So, I store passwords for users in separate files for each database. The third line is for the internal postgres templates. 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. Any ideas? Many thanks, -- Arcady Genkin i=1; while 1, hilb(i); i=i+1; end
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" > From: Tom Lane <tgl@sss.pgh.pa.us> > Date: Wed, 26 Sep 2001 15:57:35 -0400 > To: Arcady Genkin <a.genkin@utoronto.ca> > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
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