Skip site navigation (1) Skip section navigation (2)

Re: Permissions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Brett W(dot) McCoy" <bmccoy(at)chapelperilous(dot)net>
Cc: Heath Johns <public(at)elesi(dot)org>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Permissions
Date: 2001-02-27 18:50:23
Message-ID: 4928.983299823@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Brett W. McCoy" <bmccoy(at)chapelperilous(dot)net> writes:
> On Tue, 27 Feb 2001, Heath Johns wrote:
>> I need every postgres account to be authenticated by password.  However
>> I would also like to have the local unix user 'postgres' to be able to
>> bypass that password authentication.  The reason is that I have a cron
>> job that calls pg_dump under that account and I would rather not have to
>> put the master password for my rdbms in a script.

> You need something like this in your $PGDATA/pg_hba.conf file:

> local        postgres                                      trust
> local        template1                                     trust
> host         all         127.0.0.1     255.255.255.255     password

"local trust" means that *anyone* on the local system can get in,
simply by pretending to be postgres:

	export PGUSER=postgres
	psql ...

That probably wasn't what Heath had in mind.  In any case, the above
does not allow access by *user* postgres to any database, but rather
access to *database* postgres by any user.  Again, not what was asked
for.

Assuming Heath's system is running identd (or that he can install it),
a better answer is

local	all	password
host	all  127.0.0.1     255.255.255.255 ident
... plus appropriate entries for remote access, if wanted ...

This requires a password for Unix-socket connections, but will let
people in on local TCP connections ("-h localhost") with no password,
so long as their PG username matches what ident reports.  This is as
secure as your user login procedures allow, unless someone manages to
compromise your identd daemon (but if they have root, you're screwed
anyway...).  It's also more convenient than requiring passwords.

Unfortunately ident only works with TCP connections, so you can't
use it for the "local" case too :-(.  If everyone is willing to do
"export PGHOST=localhost" then you could just forget about password
management entirely:

local	all	reject
host	all  127.0.0.1     255.255.255.255 ident


			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Larry HolishDate: 2001-02-27 19:13:08
Subject: populating a field automatically
Previous:From: Brett W. McCoyDate: 2001-02-27 18:13:51
Subject: Re: Permissions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group