Re: postgres_fdw super user checks

On Thu, Sep 14, 2017 at 1:08 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > I think that foreign tables ought to behave as views do, where they run
> as
> > the owner rather than the invoker. No one has talked me out of it, but
> no
> > one has supported me on it either. But I think it is too late to change
> > that now.
>
> That's an interesting point. I think that you can imagine use cases
> for either method. Obviously, if what you want to do is drill a hole
> through the Internet to another server and then expose it to some of
> your fellow users, having the FDW run with the owner's permissions
> (and credentials) is exactly right. But there's another use case too,
> which is where you have something that looks like a multi-user
> sharding cluster. You want each person's own credentials to carry
> over to everything they do remotely.
>

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.

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.

>
> I feel like the USER MAPPING stuff is a pretty clunky and annoying way
> of trying to make this work, no matter which of those use cases you
> happen to have. But I'm not exactly sure what would be better,
> either, and like you say, it's a bit late to be breaking compatibility
> at this point.
>

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)? From what I
could tell, it requires USER MAPPING to exist but doesn't give any details,
and doesn't say there can't be something else one could optionally use
instead.

Cheers,

Jeff