Thread: Permissions
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
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.
"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
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
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
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