On Tue, Feb 21, 2023 at 11:09 AM Zheng Li <zhengl...@gmail.com> wrote: > > On Mon, Feb 20, 2023 at 3:23 AM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > > > On Fri, Feb 17, 2023 at 1:13 PM Zheng Li <zhengl...@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