Re: Error from the foreign RDBMS on a foreign table I have no privilege on

On Thu, 2022-06-09 at 21:55 +0900, Etsuro Fujita wrote:
> On Thu, Jun 9, 2022 at 9:49 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Wed, 2022-06-08 at 19:06 +0900, Etsuro Fujita wrote:
> > > On Wed, Jun 8, 2022 at 2:51 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
> > > > At Wed, 08 Jun 2022 07:05:09 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in
> > > > > diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
> > > > > index b43d0aecba..b4b7e36d28 100644
> > > > > --- a/doc/src/sgml/postgres-fdw.sgml
> > > > > +++ b/doc/src/sgml/postgres-fdw.sgml
> > > > > @@ -274,6 +274,14 @@ OPTIONS (ADD password_required 'false');
> > > > >         but only for that table.
> > > > >         The default is <literal>false</literal>.
> > > > >        </para>
> > > > > +
> > > > > +      <para>
> > > > > +       Note that <command>EXPLAIN</command> will be run on the remote server
> > > > > +       at query planning time, <emphasis>before</emphasis> permissions on the
> > > > > +       foreign table are checked.  This is not a security problem, since the
> > > > > +       subsequent error from the permission check will prevent the user from
> > > > > +       seeing any of the resulting data.
> > > > > +      </para>
> > > > >       </listitem>
> > > > >      </varlistentry>
> > > > 
> > > > Looks fine.  I'd like to add something like "If needed, depriving
> > > > unprivileged users of relevant user mappings will prevent such remote
> > > > executions that happen at planning-time."
> > > 
> > > I agree on that point; if the EXPLAIN done on the remote side is
> > > really a problem, I think the user should revoke privileges from the
> > > remote user specified in the user mapping, to prevent it.  I’d rather
> > > recommend granting to the remote user privileges consistent with those
> > > granted to the local user.
> > 
> > I don't think that is better.  Even if the local and remote privileges are
> > consistent, you will get an error from the *remote* table access when trying
> > to use a foreign table on which you don't have permissions.
> > The above paragraph describes why.
> > Note that the original complaint against oracle_fdw that led to this thread
> > was just such a case.
> 
> I thought you were worried about security, so I thought that that
> would be a good practice becasue that would reduce such risks, but I
> got the point.  However, I'm not 100% sure we really need to document
> something about this, because 1) this doesn't cause any actual
> problems, as you described, and 2) this is a pretty-exceptional case
> IMO.

I am not sure if it worth adding to the documentation.  I would never have thought
of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
to learn that permissions aren't checked until the executor kicks in.
It makes sense, but some documentation might help others in that situation.

I'll gladly leave the decision to your judgement as a committer.

Yours,
Laurenz Albe