Hi
We have not discussed much about the ownership of replicated objects.
Currently, replicated
objects belong to the subscription owner. However, it makes sense to
allow replicated
objects to keep the same owner from the publisher for certain use
cases otherwise users
may need to run lots of ALTER TABLE/OBJ OWNER TO manually. This issue
has been raised in [1] and [2].
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.
This patch needs to be applied
on top of the v69 DDL replication patch set.
Specifically, the changes include:
1. Change event trigger functions to collect the current role in
CollectedCommand.
2. Change Deparser function deparse_utility_command to encode the
owner role in the top-level
json element such as {myowner:role_name, fmt:..., identity:...} of the
deparsed jsonb output.
Also change the function deparse_ddl_json_to_string to retrieve the
myowner element from
the jsonb string.
3. Introduce a new subscription option match_ddl_owner: when turned
on, the apply worker
will apply DDL messages in the role retrieved from the "myowner" field
of the deparsed
jsonb string. The default value of match_ddl_owner is off.
Here is an example,
publisher:
CREATE PUBLICATION mypub
FOR ALL TABLES with (ddl = 'all');
CREATE ROLE user1;
subscriber:
CREATE SUBSCRIPTION mysub
CONNECTION 'dbname=source_db host=localhost user=master port=5433'
PUBLICATION mypub with (match_ddl_owner=true);
CREATE ROLE user1;
publisher:
CREATE TABLE t1 (a int, b varchar);
GRANT ALL ON schema public TO user1;
SET SESSION AUTHORIZATION user1;
CREATE TABLE t2 (a int, b varchar);
subscriber:
\d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------------------
public | t1 | table | master
public | t2 | table | user1
Your feedback is appreciated,
Zane
[1] https://www.postgresql.org/message-id/CAGfChW4vxVCgWs2%3Db%2BSDag0j3G-3Aqw5XvKnHVAReB-iysyj%2Bg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAAD30UKX%3DPbojrjU0webYy7Y9mz1HmDTM3dx_JJXpc%2BdXW-EQQ%40mail.gmail.com