Thread: Permissions

Permissions

From
Heath Johns
Date:
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.

I imagine this is a common scenario.  Any thoughts?


Thanks,

Heath Johns

Re: Permissions

From
"Brett W. McCoy"
Date:
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.
>
> I imagine this is a common scenario.  Any thoughts?

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

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Carmel, New York, has an ordinance forbidding men to wear coats and
trousers that don't match.


Re: Permissions

From
Tom Lane
Date:
"Brett W. McCoy" <bmccoy@chapelperilous.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

Re: Permissions

From
Heath Johns
Date:
First off, thank you for your reply.  I have an additional problem
however.  I have many stunnels (ssh like tcp tunnels) coming into this
box that communicate with pgsql, all of which I need to password
authenticate. They obviously only work on tcp sockets.  If I were to
ident them, it would surely come back with the owner of the stunnel.
Also, these tunnels map an external port to localhost, so I cannot do
host based authentication.

I would be more than happy if the situation were reversed, with unix
users not requiring a password, and all tcp connections being password
authenticated (which would seem to me the more logical arangement).
Unfortunately, there appears to be no way to do this.

I realise that this situation is rather unique, so I'm not expecting the
developers to have thought of and included a standard way of addressing
it.  So I suppose the question is whether there are any hacks out there
that would do the trick...


Heath



On Tue, Feb 27, 2001 at 01:50:23PM -0500, Tom Lane wrote:
> "Brett W. McCoy" <bmccoy@chapelperilous.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

Re: Permissions

From
Tom Lane
Date:
Heath Johns <public@elesi.org> writes:
> First off, thank you for your reply.  I have an additional problem
> however.  I have many stunnels (ssh like tcp tunnels) coming into this
> box that communicate with pgsql, all of which I need to password
> authenticate. They obviously only work on tcp sockets.  If I were to
> ident them, it would surely come back with the owner of the stunnel.
> Also, these tunnels map an external port to localhost, so I cannot do
> host based authentication.

Okay, that is a little bit messy.

> I would be more than happy if the situation were reversed, with unix
> users not requiring a password, and all tcp connections being password
> authenticated (which would seem to me the more logical arangement).
> Unfortunately, there appears to be no way to do this.

Um, the users coming in through stunnels must reach the postmaster via
TCP, no?  Or are they first logging into local shells and then running
psql locally?  If they do use TCP connections then it seems like

    local    all trust
    host    all 127.0.0.1 255.255.255.255 password

will do what you just said.  The local users just have to be careful
not to set PGHOST ...

            regards, tom lane

Re: Permissions

From
Heath Johns
Date:
Thanks again for responding. :)

The solution that you posted would appear to be what I am looking for.
However IIRC, I think I tried that.  I logged into the unix account
'heath' and I did a psql -u and typed in 'postgres' at the user prompt.
It believed me and let right on in.  I kept on looking because I didn't
like the idea of anyone who had a unix account, regardless how trivial,
having what amounted to root access to the rmdbs.

Under the circumstances, I believe that would be the best option though.

Your help is much appreciated...


Heath Johns



On Tue, Feb 27, 2001 at 06:08:04PM -0500, Tom Lane wrote:
> Heath Johns <public@elesi.org> writes:
> > First off, thank you for your reply.  I have an additional problem
> > however.  I have many stunnels (ssh like tcp tunnels) coming into this
> > box that communicate with pgsql, all of which I need to password
> > authenticate. They obviously only work on tcp sockets.  If I were to
> > ident them, it would surely come back with the owner of the stunnel.
> > Also, these tunnels map an external port to localhost, so I cannot do
> > host based authentication.
>
> Okay, that is a little bit messy.
>
> > I would be more than happy if the situation were reversed, with unix
> > users not requiring a password, and all tcp connections being password
> > authenticated (which would seem to me the more logical arangement).
> > Unfortunately, there appears to be no way to do this.
>
> Um, the users coming in through stunnels must reach the postmaster via
> TCP, no?  Or are they first logging into local shells and then running
> psql locally?  If they do use TCP connections then it seems like
>
>     local    all trust
>     host    all 127.0.0.1 255.255.255.255 password
>
> will do what you just said.  The local users just have to be careful
> not to set PGHOST ...
>
>             regards, tom lane