Horton

Re: Support logical replication of DDLs

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Zheng Li <zhengli10@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Smith <smithpb2250@gmail.com>, Ajin Cherian <itsajin@gmail.com>, vignesh C <vignesh21@gmail.com>, li jie <ggysxcq@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Japin Li <japinli@hotmail.com>, rajesh singarapu <rajesh.rs0541@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-02-20T08:22:50Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add a run_as_owner option to subscriptions.

  2. Refactor pgoutput_change().

  3. Print the correct aliases for DML target tables in ruleutils.

  4. Fix object identity string for transforms

  5. Add grantable MAINTAIN privilege and pg_maintain role.

  6. Get rid of recursion-marker values in enum AlterTableType

  7. Release cache tuple when no longer needed

  8. Empty search_path in logical replication apply worker and walsender.

  9. Refactor format_type APIs to be more modular

  10. Use wrappers of PG_DETOAST_DATUM_PACKED() more.

On Fri, Feb 17, 2023 at 1:13 PM Zheng Li <zhengli10@gmail.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