Re: Permissions - Mailing list pgsql-novice

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