postgres user without changing every tables owner
309 views
Skip to first unread message
רום כהן
Apr 18, 2024, 8:27:51 AM4/18/24
to debezium
Hello,
We are new to debezium on postgres and we have a couple of questions regarding permissions for the debezium user on postgres.
We will run the debezium connector on different instances of postgres. The debezium user isn't the owner of the tables, we saw in the docs that we can change the ownership to be a group of both users (debezium user and owner):
"To add tables to a publication, the user must be an owner of the table. But because the source table already exists, you need a mechanism to share ownership with the original owner. To enable shared ownership, you create a PostgreSQL replication group, and then add the existing table owner and the replication user to the group."
We wanted to make sure that there is no other way beside give the debezium user owner permissions because we might not get permissions from the DBA's. We thought about creating a publication with the user of the owner, add the tables that we want and then give owner permissions to the debezium user but we got an error:
when configuring publication.autocreate.mode: filtered and table.include.list: yyyyy.zzzzz
"unable to update filtered publication xxxx for yyyyy.zzzzz
caused by: ERROR: must be owner of table zzzzz"
Thank you for any assistance, we would really like to avoid changing all the owners of every table in every single instance
We are new to debezium on postgres and we have a couple of questions regarding permissions for the debezium user on postgres.
We will run the debezium connector on different instances of postgres. The debezium user isn't the owner of the tables, we saw in the docs that we can change the ownership to be a group of both users (debezium user and owner):
"To add tables to a publication, the user must be an owner of the table. But because the source table already exists, you need a mechanism to share ownership with the original owner. To enable shared ownership, you create a PostgreSQL replication group, and then add the existing table owner and the replication user to the group."
We wanted to make sure that there is no other way beside give the debezium user owner permissions because we might not get permissions from the DBA's. We thought about creating a publication with the user of the owner, add the tables that we want and then give owner permissions to the debezium user but we got an error:
when configuring publication.autocreate.mode: filtered and table.include.list: yyyyy.zzzzz
"unable to update filtered publication xxxx for yyyyy.zzzzz
caused by: ERROR: must be owner of table zzzzz"
Thank you for any assistance, we would really like to avoid changing all the owners of every table in every single instance
Chris Cranford
Apr 18, 2024, 2:21:36 PM4/18/24
to debe...@googlegroups.com
Hi -
This comes down to these two questions:
1. Do you want PostgreSQL to filter events via the publication or rely on Debezium's filter?
2. Do you want Debezium to manage the tables associated with the publication or are you okay with doing this manually?
You can use a super-user account that does have permissions to create the publication using "ALL_TABLES" or to "ALTER" the publication separately from the connector. The connector does not need to be the one that creates the publication nor does it need to be responsible for applying the publication filters; this can all be done manually. This allows you to have the restricted access that your DBA team may require while still being able to use Debezium.
Setting "publication.autocreate.mode=disabled" how you would manage the publication manually. You simply need to make sure that the publication is created with the desired name, and that you set the "publication.name" on the connector so that the connector uses the correct, existing publication.
Thanks,
Chris
This comes down to these two questions:
1. Do you want PostgreSQL to filter events via the publication or rely on Debezium's filter?
2. Do you want Debezium to manage the tables associated with the publication or are you okay with doing this manually?
You can use a super-user account that does have permissions to create the publication using "ALL_TABLES" or to "ALTER" the publication separately from the connector. The connector does not need to be the one that creates the publication nor does it need to be responsible for applying the publication filters; this can all be done manually. This allows you to have the restricted access that your DBA team may require while still being able to use Debezium.
Setting "publication.autocreate.mode=disabled" how you would manage the publication manually. You simply need to make sure that the publication is created with the desired name, and that you set the "publication.name" on the connector so that the connector uses the correct, existing publication.
Thanks,
Chris
--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9473383b-6cb4-44b3-9e4f-20ca627cfb7dn%40googlegroups.com.
רום כהן
Apr 21, 2024, 2:14:40 AM4/21/24
to debezium
Hi Chris
thank u for your help
thank u for your help
ב-יום חמישי, 18 באפריל 2024 בשעה 21:21:36 UTC+3, Chris Cranford כתב/ה:
Reply all
Reply to author
Forward
0 new messages