Обсуждение: Error from the foreign RDBMS on a foreign table I have no privilege on
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... Hi, I noticed a behaviour I didn'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=*>... github.com |
Best regards,
Phil
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
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 permissionson 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.
Re: Error from the foreign RDBMS on a foreign table I have no privilege on
От
Kyotaro Horiguchi
Дата:
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
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
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
Re: Error from the foreign RDBMS on a foreign table I have no privilege on
От
Kyotaro Horiguchi
Дата:
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
Re: Error from the foreign RDBMS on a foreign table I have no privilege on
От
Kyotaro Horiguchi
Дата:
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
Re: Error from the foreign RDBMS on a foreign table I have no privilege on
От
Kyotaro Horiguchi
Дата:
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
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
Re: Error from the foreign RDBMS on a foreign table I have no privilege on
От
Kyotaro Horiguchi
Дата:
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
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
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
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
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
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
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
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
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
> > 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
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