Thread: missing GRANT on pg_subscription columns

Hi,

I was checking the GRANT on pg_subscription and noticed that the command is not
correct. There is a comment that says "All columns of pg_subscription except
subconninfo are readable". However, there are columns that aren't included: oid
and subsynccommit. It seems an oversight in the commits 6f236e1eb8c and
887227a1cc8.

There are monitoring tools and data collectors that aren't using a
superuser to read catalog information (I usually recommend using pg_monitor).
Hence, you cannot join pg_subscription with relations such as
pg_subscription_rel or pg_stat_subscription because column oid has no
column-level privilege. I'm attaching a patch to fix it (indeed, 2 patches
because of additional columns for v14). We should add instructions in the minor
version release notes too.

This issue was reported by Israel Barth.


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

"Euler Taveira" <euler@eulerto.com> writes:
> I was checking the GRANT on pg_subscription and noticed that the command is not
> correct. There is a comment that says "All columns of pg_subscription except
> subconninfo are readable". However, there are columns that aren't included: oid
> and subsynccommit. It seems an oversight in the commits 6f236e1eb8c and
> 887227a1cc8.

Ugh.

> There are monitoring tools and data collectors that aren't using a
> superuser to read catalog information (I usually recommend using pg_monitor).
> Hence, you cannot join pg_subscription with relations such as
> pg_subscription_rel or pg_stat_subscription because column oid has no
> column-level privilege. I'm attaching a patch to fix it (indeed, 2 patches
> because of additional columns for v14). We should add instructions in the minor
> version release notes too.

I agree with fixing this in HEAD.  But given that this has been wrong
since v10 with zero previous complaints, I doubt that it is worth the
complication of trying to do something about it in the back branches.
Maybe we could just adjust the docs there, instead.

            regards, tom lane

On Thu, Jun 3, 2021 at 10:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Euler Taveira" <euler@eulerto.com> writes:
> > I was checking the GRANT on pg_subscription and noticed that the command is not
> > correct. There is a comment that says "All columns of pg_subscription except
> > subconninfo are readable". However, there are columns that aren't included: oid
> > and subsynccommit. It seems an oversight in the commits 6f236e1eb8c and
> > 887227a1cc8.
>
> Ugh.
>
> > There are monitoring tools and data collectors that aren't using a
> > superuser to read catalog information (I usually recommend using pg_monitor).
> > Hence, you cannot join pg_subscription with relations such as
> > pg_subscription_rel or pg_stat_subscription because column oid has no
> > column-level privilege. I'm attaching a patch to fix it (indeed, 2 patches
> > because of additional columns for v14). We should add instructions in the minor
> > version release notes too.
>
> I agree with fixing this in HEAD.  But given that this has been wrong
> since v10 with zero previous complaints, I doubt that it is worth the
> complication of trying to do something about it in the back branches.
> Maybe we could just adjust the docs there, instead.
>

This sounds reasonable to me. Euler, can you provide the doc updates
for back-branches?

-- 
With Regards,
Amit Kapila.

On Mon, Jun 7, 2021 at 2:38 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Thu, Jun 3, 2021 at 10:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > "Euler Taveira" <euler@eulerto.com> writes:
> > > I was checking the GRANT on pg_subscription and noticed that the command is not
> > > correct. There is a comment that says "All columns of pg_subscription except
> > > subconninfo are readable". However, there are columns that aren't included: oid
> > > and subsynccommit. It seems an oversight in the commits 6f236e1eb8c and
> > > 887227a1cc8.
> >
> > Ugh.
> >
> > > There are monitoring tools and data collectors that aren't using a
> > > superuser to read catalog information (I usually recommend using pg_monitor).
> > > Hence, you cannot join pg_subscription with relations such as
> > > pg_subscription_rel or pg_stat_subscription because column oid has no
> > > column-level privilege. I'm attaching a patch to fix it (indeed, 2 patches
> > > because of additional columns for v14). We should add instructions in the minor
> > > version release notes too.
> >
> > I agree with fixing this in HEAD.  But given that this has been wrong
> > since v10 with zero previous complaints, I doubt that it is worth the
> > complication of trying to do something about it in the back branches.
> > Maybe we could just adjust the docs there, instead.
> >
>
> This sounds reasonable to me. Euler, can you provide the doc updates
> for back-branches?

Attached patch has the documentation changes for the back-branches. As
there is no specific reason for this, I have just mentioned
"Additionally normal users can't access columns oid and
subsynccommit." The same patch applies till V10 branch.

Regards,
Vignesh

On Mon, Jun 28, 2021 at 11:02 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Mon, Jun 7, 2021 at 2:38 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Thu, Jun 3, 2021 at 10:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >
>
> Attached patch has the documentation changes for the back-branches. As
> there is no specific reason for this, I have just mentioned
> "Additionally normal users can't access columns oid and
> subsynccommit." The same patch applies till V10 branch.
>

Thanks for the patch. Tom has already pushed the code as part of
commit 3590680b85, so I am not sure if it is still valuable to fix
docs in back-branches.


-- 
With Regards,
Amit Kapila.