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

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, euler(at)eulerto(dot)com
Cc: philflorent(at)hotmail(dot)com, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Error from the foreign RDBMS on a foreign table I have no privilege on
Date: 2022-06-08 02:38:02
Message-ID: 3840766983cb835fa4efc0d5163681a01b2b3ca0.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, 2022-06-08 at 11:12 +0900, Kyotaro Horiguchi wrote:
> At Tue, 07 Jun 2022 11:24:55 -0300, "Euler Taveira" <euler(at)eulerto(dot)com> wrote in
> > On Tue, Jun 7, 2022, at 12:03 AM, Laurenz Albe wrote:
> > > On Sat, 2022-06-04 at 21:18 +0000, Phil Florent wrote:
> > > > I opened an issue with an attached code on oracle_fdw git page : https://github.com/laurenz/oracle_fdw/issues/534 
> > > > Basically I expected to obtain a "no privilege" error from PostgreSQL when I have no read privilege
> > > > on the postgres foreign table but I obtained an Oracle error instead.
> > > > Laurenz investigated and closed the issue but he suggested perhaps I should post that on
> > > > the hackers list since it also occurs with postgres-fdw on some occasion(I have investigated some more,
> > > > and postgres_fdw does the same thing when you turn onuse_remote_estimate.). Hence I do...
> > >
> > > To add more detais: permissions are checked at query execution time, but if "use_remote_estimate"
> > > is used, the planner already accesses the remote table, even if the user has no permissions
> > > on the foreign table.
> > >
> > > I feel that that is no bug, but I'd be curious to know if others disagree.
> > You should expect an error (like in the example) -- probably not at that point.
> > It is behaving accordingly. However, that error is exposing an implementation
> > detail (FDW has to access the remote table at that phase). I don't think that
> > changing the current design (permission check after planning) for FDWs to
> > provide a good UX is worth it. IMO it is up to the FDW author to hide such
> > cases if it doesn't cost much to do it.
>
> It is few lines of code.
>
> > i = -1;
> > while ((i = bms_next_member(rel->relids, i)) >= 0)
> > {
> > RangeTblEntry *rte = root->simple_rte_array[i];
> > aclcheck_error(ACLCHECK_NO_PRIV,
> >    get_relkind_objtype(rte->relkind),
> >    get_rel_name(rte->relid));
> > }
>
> It can be done in GetForeignRelSize callback by individual FDW, but it
> also can be done in set_foreign_size() in core.

If anything, it should be done in the FDW, because it is only necessary if the
FDW calls the remote site during planning.

The question is: is this a bug in postgres_fdw that should be fixed?

Yours,
Laurenz Albe

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-06-08 02:57:37 Re: How about a psql backslash command to show GUCs?
Previous Message Kyotaro Horiguchi 2022-06-08 02:12:10 Re: Error from the foreign RDBMS on a foreign table I have no privilege on