Re: Support logical replication of DDLs

On Fri, Feb 17, 2023 at 1:13 PM Zheng Li <zhengli10(at)gmail(dot)com> wrote:
>
> > > I've implemented a prototype to allow replicated objects to have the
> > > same owner from the publisher in
> > > v69-0008-Allow-replicated-objects-to-have-the-same-owner-from.patch.
> > >
> >
> > I also think it would be a helpful addition for users.A few points
> Thanks for supporting this addition.
>
> > that come to my mind are: (a) Shouldn't the role have the same
> > privileges (for ex. rolbypassrls or rolsuper) on both sides before we
> > allow this? (b) Isn't it better to first have a replication of roles?
>
> > I think if we have (b) then it would be probably a bit easier because
> > if the subscription has allowed replicating roles and we can confirm
> > that the role is replicated then we don't need to worry about the
> > differences.
>
> Yes, having role replication will help further reduce the manual
> effort. But even if we don't end up doing role replication soon, I
> think we can still provide this subscription option (match_ddl_owner,
> off by default) and document that the same roles need to be on both
> sides for it to work.

From the user perspective, I expect that the replicated objects are
created on the subscriber by the same owner as the publisher, by
default. I think that the same name users must exist on both sides (by
role replication or manually if not supported yet) but the privileges
of the role doesn't necessarily need to match. IOW, it's sufficient
that the role on the subscriber has enough privileges to create the
object.

>
> > Now, coming to implementation, won't it be better if we avoid sending
> > the owner to the subscriber unless it is changed for the replicated
> > command? Consider the current case of tables where we send schema only
> > if it is changed. This is not a direct mapping but it would be better
> > to avoid sending additional information and then process it on the
> > subscriber for each command.
>
> Right, we can do some optimization here: only send the owner for
> commands that create objects (CREATE TABLE/FUNCTION/INDEX etc.) Note
> that ALTER TABLE/OBJECT OWNER TO is replicated so we don't need to
> worry about owner change.

What role will be used for executing ALTER and DROP commands on the
subscriber? the subscription owner?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com