On Tue, Jun 7, 2022, at 12:03 AM, Laurenz Albe wrote:
On Sat, 2022-06-04 at 21:18 +0000, Phil Florent wrote:
> 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.