Re: Error from the foreign RDBMS on a foreign table I have no privilege on - Mailing list pgsql-hackers

From Etsuro Fujita
Subject Re: Error from the foreign RDBMS on a foreign table I have no privilege on
Date
Msg-id CAPmGK15P6vFivC9uNHtnt0imTZqbkD4UGButbV=y4QL8BVtwgw@mail.gmail.com
Whole thread Raw
In response to Re: Error from the foreign RDBMS on a foreign table I have no privilege on  (Kyotaro Horiguchi <horikyota.ntt@gmail.com>)
List pgsql-hackers
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.

Best regards,
Etsuro Fujita



pgsql-hackers by date:

Previous
From: Bharath Rupireddy
Date:
Subject: Re: Multi-Master Logical Replication
Next
From: Robert Haas
Date:
Subject: Re: [PATCH] Expose port->authn_id to extensions and triggers