Re: postgres_fdw super user checks

On 4 October 2017 at 18:13, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> 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.

Sounds like it would be a useful option on a Foreign Server to allow
it to run queries as either the invoker or the owner. We have that
choice for functions, so we already have the concept and syntax
available. We could have another default at FDW level that specifies
what the default is for that type of FDW, and if that is not
specified, we keep it like it currently is.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services