Thread

Authenticating user `postgres'

Arcady Genkin <a.genkin@utoronto.ca> — 2001-09-26T17:35:48Z

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

Re: Authenticating user `postgres'

Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-26T19:57:35Z

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

Re: Authenticating user `postgres'

Keary Suska <hierophant@pcisys.net> — 2001-09-26T22:20:30Z

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
>

Re: Authenticating user `postgres'

Arcady Genkin <a.genkin@utoronto.ca> — 2001-09-27T22:40:46Z

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

Re: Authenticating user `postgres'

Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-28T13:21:47Z

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

Re: Authenticating user `postgres'

Arcady Genkin <a.genkin@utoronto.ca> — 2001-09-28T20:06:57Z

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