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

At Tue, 07 Jun 2022 11:24:55 -0300, "Euler Taveira" <euler@eulerto.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.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center