Re: Added schema level support for publication.

On Mon, Aug 16, 2021 at 7:01 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Peter Smith <smithpb2250(at)gmail(dot)com> writes:
> > Then the question from Peter E. [2] "Why can't I have a publication
> > that publishes tables t1, t2, t3, *and* schemas s1, s2, s3." would
> > have an intuitive solution like:
>
> > CREATE PUBLICATION pub1
> > FOR TABLE t1,t2,t3 AND
> > FOR ALL TABLES IN SCHEMA s1,s2,s3;
>
> That seems a bit awkward, since the existing precedent is
> to use commas.
>

AFAICS, the closest to this proposal we have is Grant/Revoke syntax
where we can give privilege on individual objects and all objects in
the schema. Is that you are referring to existing precedent or
something else?

> We shouldn't need more than one FOR noise-word,
> either. So I was imagining syntax more like, say,
>
> CREATE PUBLICATION pub1 FOR
> TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2,
> SEQUENCE seq1,seq2, ALL SEQUENCES IN SCHEMA s3,s4;
>
> Abstractly it'd be
>
> createpub := CREATE PUBLICATION pubname FOR cpitem [, ... ] [ WITH ... ]
>
> cpitem := ALL TABLES |
> TABLE name |
> ALL TABLES IN SCHEMA name |
> ALL SEQUENCES |
> SEQUENCE name |
> ALL SEQUENCES IN SCHEMA name |
> name
>
> The grammar output would need some post-analysis to attribute the
> right type to bare "name" items, but that doesn't seem difficult.
>

The current patch (v26-0002-Added-schema-level-support-for-publication
at [1]) implements this syntax in roughly the way you have proposed
here. But, one thing I find a bit awkward is how it needs to keep a
separate flag to distinguish between names of different objects for
the post-analysis phase. The reason is that in CREATE PUBLICATION
syntax [2] one could supply additional decorators like *, ONLY with
table name but the same shouldn't be allowed be with schema name or
other object names. Is that okay or do you have any better ideas about
the same?

OTOH, if we implement something like Grant/Revoke where we can give
privilege on individual objects and all objects in the schema but not
in the same statement then such special flags won't be required to
distinguish different object names and we can build something on the
lines of current "privilege_target:" in gram.y.

[1] - https://www.postgresql.org/message-id/CALDaNm3EwAVma8n4YpV1%2BQWiccuVPxpqNfbbrUU3s3XTHcTXew%40mail.gmail.com
[2] - https://www.postgresql.org/docs/devel/sql-createpublication.html

--
With Regards,
Amit Kapila.