Re: Permissions

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(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