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-24T08:42:12Z
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 Tue, Feb 21, 2023 at 11:09 AM Zheng Li <zhengli10@gmail.com> wrote:
>
> On Mon, Feb 20, 2023 at 3:23 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > 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.
>
> OK, I agree. I think the use cases for matching the owner are likely
> more than the other way around. I can make the subscription option
> "match_ddl_owner" on by default in the next version.
>
> > 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.
>
> This is also my understanding.
>
> > > > 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?
>
> Yes, I think DROP and ALTER commands (and other non-CREATE commands)
> can be executed by the subscription owner (superuser).

I think the subscription owner might not be a superuser in the future
as we are discussing on this thread[1].

Regards,

[1] https://www.postgresql.org/message-id/flat/9DFC88D3-1300-4DE8-ACBC-4CEF84399A53%40enterprisedb.com

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