Allow BDR to apply the changes as owner of the table or owner of DSN specified

[BRupireddy2]

Describe the problem

All the workers in BDR (supervisor, per-db workers and apply workers) will run as superuser – evident from passing invalid user name NULL and user oid invalid to BackgroundWorkerInitializeConnection() BackgroundWorkerInitializeConnectionByOid() and the code https://github.com/postgres/postgres/blob/master/src/backend/utils/init/postinit.c#L895 in InitPostgres. Checkout this fact being called out in https://www.postgresql.org/docs/current/bgworker.html (look for BackgroundWorkerInitializeConnection or BackgroundWorkerInitializeConnectionByOid).

FWIW, the table sync and apply workers in upstream logical replication will run as subscription owner - https://github.com/postgres/postgres/blame/a44d96add2eb377ab70055a54b713c5c78380383/src/include/replication/worker_internal.h#L48 and https://github.com/postgres/postgres/blob/a44d96add2eb377ab70055a54b713c5c78380383/src/backend/replication/logical/worker.c#L4460.

And, a feature went into PG16 postgres/postgres@4826759. The feature enables applying the changes as the table owner not as the subscription owner - https://www.postgresql.org/message-id/CA%2BTgmoaSCkg9ww9oppPqqs%2B9RVqCexYCE6Aq%3DUsYPfnOoDeFkw%40mail.gmail.com.

What are you trying to solve?

I think we need to do something similar for BDR too. First, allow BDR to run as the user specified in DSN, and then run as the table owner. Perhaps, an option to bdr_create_group or bdr_join_group can help here.

[BRupireddy2]

Looks like role is changed for apply (and per-db?) worker at some point of time internally by BDR - see code around set_config_option/SetConfigOption("role", perpetrator...). Study if that's in all cases or not.