Re: Support logical replication of DDLs - Mailing list pgsql-hackers

On Mon, Mar 27, 2023 at 12:07 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Mar 27, 2023 at 2:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
>
> > I suggest taking a couple of steps back from the minutiae of the
> > patch, and spending some hard effort thinking about how the thing
> > would be controlled in a useful fashion (that is, a real design for
> > the filtering that was mentioned at the very outset), and about the
> > security issues, and about how we could get to a committable patch.
> >
>
> Agreed. I'll try to summarize the discussion we have till now on this
> and share my thoughts on the same in a separate email.
>

The idea to control what could be replicated is to introduce a new
publication option 'ddl' along with current options 'publish' and
'publish_via_partition_root'. The values of this new option could be
'table', 'function', 'all', etc. Here 'all' enables the replication of
all supported DDL commands. Example usage for this would be:
Example:
Create a new publication with all ddl replication enabled:
  CREATE PUBLICATION pub1 FOR ALL TABLES with (ddl = 'all');

Enable table ddl replication for an existing Publication:
  ALTER PUBLICATION pub2 SET (ddl = 'table');

This is what seems to have been discussed but I think we can even
extend it to support based on operations/commands, say one would like
to publish only 'create' and 'drop' of tables. Then we can extend the
existing publish option to have values like 'create', 'alter', and
'drop'.

Another thing we are considering related to this is at what level
these additional options should be specified. We have three variants
FOR TABLE, FOR ALL TABLES, and FOR TABLES IN SCHEMA that enables
replication. Now, for the sake of simplicity, this new option is
discussed to be provided only with FOR ALL TABLES variant but I think
we can provide it with other variants with some additional
restrictions like with FOR TABLE, we can only specify 'alter' and
'drop' for publish option. Now, though possible, it brings additional
complexity to support it with variants other than FOR ALL TABLES
because then we need to ensure additional filtering and possible
modification of the content we have to send to downstream. So, we can
even decide to first support it only FOR ALL TABLES variant.

The other point to consider for publish option 'ddl = table' is
whether we need to allow replicating dependent objects like say some
user-defined type is used in the table. I guess the difficulty here
would be to identify which dependents we want to allow.

I think in the first version we should allow to replicate only some of
the objects instead of everything. For example, can we consider only
allowing tables and indexes in the first version? Then extend it in a
phased manner?

AFAICR, we have discussed two things related to security. (a)
ownership of objects created via DDL replication. We have discussed
providing an option at subscription level to allow objects to have the
same ownership (as it has on the publisher) after apply to the
subscriber. If that option is not enabled the objects will be owned by
the subscription owner. (b) Allow use of functions replicated to be
used even if they don't use schema qualify objects. Currently, we
override the search_path in apply worker to an empty string to ensure
that apply worker doesn't execute arbitrary expressions as it works
with the privileges of the subscription owner which would be a
superuser. We have discussed providing a search_path as an option at
the subscription level or a GUC to allow apply workers to use a
specified search_path.

Do you have anything else in mind?

--
With Regards,
Amit Kapila.