Re: postgres_fdw super user checks

On Wed, Oct 4, 2017 at 6:13 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> OK. And if you want the first one, you can wrap it in a view currently, but
> if it were changed I don't know what you would do if you want the 2nd one
> (other than having every user create their own set of foreign tables). So I
> guess the current situation is more flexible.

So where does that leave this patch? I don't think it makes this
patch a bad idea, although I kind of lean towads the view that the
patch should just be checking superuser_arg(), not superuser() ||
superuser_arg().

> It does seem like it would then be a good idea to have a user mapping option
> of "pass_the_hash" which would look up md5 hash from the pg_authid (if the
> local username is the same as the remote user name) and use that to connect
> to the foreign server, as an alternative option to recording the password in
> plain text in the mapping itself. But that would require some changes to
> libpq, not just postgres_fdw.
>
> And that wouldn't work for SCRAM. I guess that SCRAM does have some feature
> to allow this kind of delegation, but I don't know enough about it to know
> how hard it would be to implement in postgres_fdw or how useful it would be
> to have.

We really need some kind of method for delegating authentication. I
don't know how it should work.

Generally, password authentication is a silly choice for automated
logins because then you've got to store the password someplace from
which it can be digitally stolen; stealing a password from someone's
brain is a different kind of problem. It's not even a good method for
this situation, yet it's the only one we allow. I think that bites,
but I don't really know what to do about it.

> Yeah, I have not been finding it enjoyable. How much flexibility does the
> SQL/MED spec even give us (I don't have access to the spec)?

Sorry, I don't know.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company