On Thu, Sep 14, 2017 at 04:08:08PM -0400, Robert Haas wrote: > On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janes <jeff.janes@gmail.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.
Hmmm, I don't think that's really right.
What I'd like instead is for the FDW client to tell the FDW server the session_user/current_user on behalf of which it's acting, and let the FDW server decide how to proceed. This could be done by doing a SET SESSION fdw.client.session_user... and so on.
So then the FDW client would still have to authenticate itself, presumably as a superuser, to the FDW server, and just tell the server "trust me on who I'm representing here"?
We use Kerberos principal names as PG user/role names, _with_ @REALM included, so a user foo@BAR is likely to make sense to the FDW server.
Of course, if you're not using Kerberos then the local and remote user namespaces might be completely distinct, but by letting the FDW server know a) the FDW client's username (via authentication) and b) the true username on the client side (via SET/set_config()), the server might have enough information to decide whether it trusts (a) to impersonate (b) and how to map (b) to a local user.
With Kerberos, shouldn't the ultimate client be able (in theory) to authenticate to the end server through the intermediate server, without giving the intermediate server general impersonation privileges? If that can be done in theory, any idea on how to implement it?
