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

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.

--
Euler Taveira
EDB https://www.enterprisedb.com/