RE: why can't a table be part of the same publication as its schema - Mailing list pgsql-hackers

On Wednesday, September 21, 2022 4:06 AM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> > On Sep 20, 2022, at 12:36 PM, Jonathan S. Katz <jkatz@postgresql.org>
> wrote:
> >
> > This behavior exists "FOR ALL TABLES" without the "IN SCHEMA" qualifier.
> This was discussed multiple times on the original thread[1].
> 
> Yes, nobody is debating that as far as I can see.  And I do take your point that
> this stuff was discussed in other threads quite a while back.
> 
> > I tried to diligently read the sections where we talk about granting +
> privileges[2][3] to see what it says about "ALL * IN SCHEMA". Unless I missed it,
> and I read through it twice, it does not explicitly state whether or not "GRANT"
> applies to all objects at only that given moment, or to future objects of that
> type which are created in that schema. Maybe the behavior is implied or is part
> of the standard, but it's not currently documented.
> 
> Interesting.  Thanks for that bit of research.
> 
> > We do link to "ALTER DEFAULT PRIVILEGES" at the bottom of the GRANT[2]
> docs, but we don't give any indication as to why.
> >
> > (This is also to say we should document in GRANT that ALL * IN SCHEMA does
> not apply to future objects;
> 
> Yes, I agree this should be documented.
> 
> > if you need that behavior use ALTER DEFAULT PRIVILEGES. Separate thread :)
> >
> > I understand there is a risk of confusion of the similar grammar across
> commands, but the current command in logical replication has this is building
> on the existing behavior.
> 
> I don't complain that it is buidling on the existing behavior.  I'm *only*
> concerned about the keywords we're using for this.  Consider the following:
> 
>    -- AS ADMIN
>    CREATE USER bob NOSUPERUSER;
>    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO bob;
>    SET ROLE bob;
>    CREATE PUBLICATION bobs_pub FOR ALL TABLES IN SCHEMA foo;
> 
> We're going to have that fail in pg15 because the FOR ALL TABLES IN SCHEMA
> option is reserved to superusers.  But we agreed that was a stop-gap solution
> that we'd potentially loosen in the future.  Certainly we'll need wiggle room in
> the syntax to perform that loosening:
> 
>    --- Must be superuser for this in pg15, and in subsequent releases.
>    CREATE PUBLICATION bobs_pub FOR ALL FUTURE TABLES IN SCHEMA foo;
> 
>    --- Not supported in pg15, but reserved for some future pg versions to
> allow
>    --- non-superusers to create publications on tables currently in schema foo,
>    --- assuming they have sufficient privileges on those tables
>    CREATE PUBLICATION bobs_pub FOR ALL TABLES IN SCHEMA foo;
> 
> Doing it this way makes the syntax consistent between the GRANT...TO bob and
> the CREATE PUBLICATION bobs_pub.  Surely this makes more sense?

Thanks for the suggestion.

My concern is that I am not sure do we really want to add a feature that only
publish all the current tables(not future tables).

I think, if possible, it would be better to find an approach that can release the
superuser restriction for both FOR ALL TABLES and FOR ALL TABLES IN SCHEMA in
the future release. I think another solution might be introduce a new
publication option (like: include_future).

When user execute:
CREATE PUBLICATION ... FOR ALL TABLES IN SCHEMA ... WITH (include_future)

it means we publish all current and future tables and require superuser. We can
set the default value of this option to 'true' and user can set it to false if
they only want to publish the current tables and don't want to use superuser.
And in this approach, we don't need to change the syntax.

Best regards,
Hou zj