Re: Permissions

"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