Horton

Thread

  1. Error from the foreign RDBMS on a foreign table I have no privilege on

    Phil Florent <philflorent@hotmail.com> — 2022-06-04T21:18:02Z

    Hi,
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 on use_remote_estimate.). Hence I do...
[https://opengraph.githubassets.com/e4d1de8890f6f00ee432d365f033677636df1c545e9d4c10ad623c5de5e7553e/laurenz/oracle_fdw/issues/534]<https://github.com/laurenz/oracle_fdw/issues/534>
Oracle error on a foreign table I have no privilege on · Issue #534 · laurenz/oracle_fdw<https://github.com/laurenz/oracle_fdw/issues/534>
Hi, I noticed a behaviour I didn&#39;t expect. Not really a bug but I obtained an Oracle error instead of a PostgreSQL error with a foreign table I had no privilege on. -- superuser prodige31=*&gt;...
github.com

Best regards,
Phil

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-07T03:03:00Z

    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.

Yours,
Laurenz Albe

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

    Euler Taveira <euler@eulerto.com> — 2022-06-07T14:24:55Z

    
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/

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2022-06-08T02:12:10Z

    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

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-08T02:38:02Z

    On Wed, 2022-06-08 at 11:12 +0900, Kyotaro Horiguchi wrote:
> 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.

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-06-08T03:04:52Z

    Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Wed, 2022-06-08 at 11:12 +0900, Kyotaro Horiguchi wrote:
> RangeTblEntry *rte = root->simple_rte_array[i];
> aclcheck_error(ACLCHECK_NO_PRIV,
>    get_relkind_objtype(rte->relkind),
>    get_rel_name(rte->relid));

I think it's completely inappropriate for FDWs to be taking it on
themselves to inject privilege checks.  The system design is that
that is checked at executor start; not before, not after.

			regards, tom lane

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2022-06-08T03:09:27Z

    At Wed, 08 Jun 2022 04:38:02 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in 
> 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?

It's depends on what we think about allowing remote access trials
through unprivileged foreign table in any style.  It won't be a
problem if the system is configured appropriately but too-frequent
estimate accesses via unprivileged foreign tables might be regarded as
an attack attempt.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2022-06-08T04:06:25Z

    At Wed, 08 Jun 2022 12:09:27 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> At Wed, 08 Jun 2022 04:38:02 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in 
> > 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?
> 
> It's depends on what we think about allowing remote access trials
> through unprivileged foreign table in any style.  It won't be a
> problem if the system is configured appropriately but too-frequent
> estimate accesses via unprivileged foreign tables might be regarded as
> an attack attempt.

In other words, I don't think it's not a bug and no need to fix.  If
one want to prevent such estimate accesses via unprivileged foreign
tables, it is enough to prevent non-privileged users from having a
user mapping.  This might be worth documenting?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2022-06-08T04:08:16Z

    At Tue, 07 Jun 2022 23:04:52 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in 
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Wed, 2022-06-08 at 11:12 +0900, Kyotaro Horiguchi wrote:
> > RangeTblEntry *rte = root->simple_rte_array[i];
> > aclcheck_error(ACLCHECK_NO_PRIV,
> >    get_relkind_objtype(rte->relkind),
> >    get_rel_name(rte->relid));
> 
> I think it's completely inappropriate for FDWs to be taking it on
> themselves to inject privilege checks.  The system design is that
> that is checked at executor start; not before, not after.

Ah, yes.  It's not good that checking it at multiple stages, and the
only one place should be executor start.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-08T05:05:09Z

    On Wed, 2022-06-08 at 13:06 +0900, Kyotaro Horiguchi wrote:
> At Wed, 08 Jun 2022 12:09:27 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> > At Wed, 08 Jun 2022 04:38:02 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in 
> > > 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?
> > 
> > It's depends on what we think about allowing remote access trials
> > through unprivileged foreign table in any style.  It won't be a
> > problem if the system is configured appropriately but too-frequent
> > estimate accesses via unprivileged foreign tables might be regarded as
> > an attack attempt.
> 
> In other words, I don't think it's not a bug and no need to fix.  If
> one want to prevent such estimate accesses via unprivileged foreign
> tables, it is enough to prevent non-privileged users from having a
> user mapping.  This might be worth documenting?

I take Tom's comment above as saying that the current behavior is fine.
So yes, perhaps some documentation would be in order:

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>

Yours,
Laurenz Albe

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

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2022-06-08T05:51:39Z

    At Wed, 08 Jun 2022 07:05:09 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in 
> I take Tom's comment above as saying that the current behavior is fine.
> So yes, perhaps some documentation would be in order:
> 
> 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."

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-06-08T10:06:11Z

    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
> > I take Tom's comment above as saying that the current behavior is fine.
> > So yes, perhaps some documentation would be in order:
> >
> > 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.

Best regards,
Etsuro Fujita

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-09T00:49:02Z

    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.

Yours,
Laurenz Albe

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-06-09T12:55:46Z

    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

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-09T16:26:29Z

    On Thu, 2022-06-09 at 21:55 +0900, Etsuro Fujita wrote:
> 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.

I am not sure if it worth adding to the documentation.  I would never have thought
of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
to learn that permissions aren't checked until the executor kicks in.
It makes sense, but some documentation might help others in that situation.

I'll gladly leave the decision to your judgement as a committer.

Yours,
Laurenz Albe

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-06-10T08:17:22Z

    On Fri, Jun 10, 2022 at 1:26 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Thu, 2022-06-09 at 21:55 +0900, Etsuro Fujita wrote:
> > 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.
>
> I am not sure if it worth adding to the documentation.  I would never have thought
> of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
> to learn that permissions aren't checked until the executor kicks in.
> It makes sense, but some documentation might help others in that situation.

+1 for adding such a document.

> I'll gladly leave the decision to your judgement as a committer.

IIRC, there are no reports about this from the postgres_fdw users, so
my inclination would be to leave the documentation alone, for now.

Best regards,
Etsuro Fujita

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2022-06-10T09:17:07Z

    On Fri, 2022-06-10 at 17:17 +0900, Etsuro Fujita wrote:
> > I am not sure if it worth adding to the documentation.  I would never have thought
> > of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
> > to learn that permissions aren't checked until the executor kicks in.
> > It makes sense, but some documentation might help others in that situation.
> 
> +1 for adding such a document.
> 
> > I'll gladly leave the decision to your judgement as a committer.
> 
> IIRC, there are no reports about this from the postgres_fdw users, so
> my inclination would be to leave the documentation alone, for now.

I understand that you are for documenting the timing of permission checks,
but not in the postgres_fdw documentation.  However, this is the only occasion
where the user might notice unexpected behavior on account of the timing of
permission checks.  Other than that, I consider this below the threshold for
user-facing documentation.

I'm ok with just doing nothing here, I just wanted it discussed in public.

Yours,
Laurenz Albe

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

    Phil Florent <philflorent@hotmail.com> — 2022-06-10T16:20:09Z

    Hi,
Thanks for your explanations.
Test case had no real-world logic anyway. It was just an oversight in a one-time use legacy migration script.
Regards,
Phil
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Friday, June 10, 2022 11:17:07 AM
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; euler@eulerto.com <euler@eulerto.com>; philflorent@hotmail.com <philflorent@hotmail.com>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Error from the foreign RDBMS on a foreign table I have no privilege on

On Fri, 2022-06-10 at 17:17 +0900, Etsuro Fujita wrote:
> > I am not sure if it worth adding to the documentation.  I would never have thought
> > of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
> > to learn that permissions aren't checked until the executor kicks in.
> > It makes sense, but some documentation might help others in that situation.
>
> +1 for adding such a document.
>
> > I'll gladly leave the decision to your judgement as a committer.
>
> IIRC, there are no reports about this from the postgres_fdw users, so
> my inclination would be to leave the documentation alone, for now.

I understand that you are for documenting the timing of permission checks,
but not in the postgres_fdw documentation.  However, this is the only occasion
where the user might notice unexpected behavior on account of the timing of
permission checks.  Other than that, I consider this below the threshold for
user-facing documentation.

I'm ok with just doing nothing here, I just wanted it discussed in public.

Yours,
Laurenz Albe

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

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2022-07-04T10:59:45Z

    On Fri, Jun 10, 2022 at 6:17 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Fri, 2022-06-10 at 17:17 +0900, Etsuro Fujita wrote:
> > > I am not sure if it worth adding to the documentation.  I would never have thought
> > > of the problem if Phil hadn't brought it up.  On the other hand, I was surprised
> > > to learn that permissions aren't checked until the executor kicks in.
> > > It makes sense, but some documentation might help others in that situation.
> >
> > +1 for adding such a document.
> >
> > > I'll gladly leave the decision to your judgement as a committer.
> >
> > IIRC, there are no reports about this from the postgres_fdw users, so
> > my inclination would be to leave the documentation alone, for now.
>
> I understand that you are for documenting the timing of permission checks,
> but not in the postgres_fdw documentation.

Yes, I think so.

> However, this is the only occasion
> where the user might notice unexpected behavior on account of the timing of
> permission checks.  Other than that, I consider this below the threshold for
> user-facing documentation.

I think PREPARE/EXECUTE have a similar issue:

postgres=# create table t1 (a int, b int);
CREATE TABLE
postgres=# create user foouser;
CREATE ROLE
postgres=# set role foouser;
SET
postgres=> prepare fooplan (int, int) as insert into t1 values ($1, $2);
PREPARE
postgres=> execute fooplan (9999, 9999);
ERROR:  permission denied for table t1

The user foouser is allowed to PREPARE the insert statement, without
the insert privilege on the table t1, as the permission check is
delayed until EXECUTE.

So I thought it would be good to add a note about the timing to the
documentation about the Postgres core, such as arch-dev.sgml (the
"Overview of PostgreSQL Internals" chapter).  But as far as I know,
there aren’t any reports on the PREPARE/EXECUTE behavior, either, so
there might be less need to do so, I think.

Thanks for the discussion!

Sorry for the delay.

Best regards,
Etsuro Fujita