Re: GRANT USAGE on FOREIGN SERVER exposes passwords

On 2/5/15 10:13 AM, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> All that having been said, it wouldn't be crazy to try to invent a
>> system to lock this down, but it *would* be complicated. An
>> individual FDW can call its authentication-related options anything it
>> likes; they do not need to be called 'password'. So we'd need a way
>> to identify which options should be hidden from untrusted users, and
>> then a bunch of mechanism to do that.
>
> It's also debatable whether this wouldn't be a violation of the SQL
> standard. I see nothing in the SQL-MED spec authorizing filtering
> of the information_schema.user_mapping_options view.
>
> We actually are doing some filtering of values in user_mapping_options,
> but it's all-or-nothing so far as the options for any one mapping go.
> That's still not exactly supportable per spec but it's probably less of a
> violation than option-by-option filtering would be.
>
> It also looks like that filtering differs in corner cases from what the
> regular pg_user_mappings view does, which is kinda silly. In particular
> I think we should try to get rid of the explicit provision for superuser
> access.
>
> I was hoping Peter would weigh in on what his design considerations
> were for these views ...

I recall that we had extensive discussions about this back in the day.
The SQL standard doesn't provide for any filtering of options, and the
current behavior is just a bare minimum to not appear completely stupid.

Since we don't know which options are security-sensitive, the only
choices are hide everything or hide nothing. Note that if you opt for
hide everything, you will also need to hide everything from servers and
wrappers, since they could also contains passwords in their options.

We could ask the FDW which options are security sensitive, but that
seems quite complicated to implement. We could require the user to
specify which option values they want hidden (SENSITIVE OPTION '...' or
whatever).

I would welcome improvements in this area. I'm not worried about SQL
standard requirements here.