Feature Recommendations for Logical Subscriptions - Mailing list pgsql-hackers
Business Scenario:
The BI department requires real-time data from the operational database. In our current approach (on platform 14), we create a separate database within our department's real-time backup instance, set up a logical replication account, replicate required tables to this isolated database via logical replication, and then create a dedicated account with column-level permissions on specific tables for the BI department.
Recommendations:
1、Column Filtering Functionality: During implementation, some tables may contain sensitive data or long fields (e.g., text columns), while other fields in these tables still need to be replicated to another database or instance. Manually specifying individual columns becomes cumbersome, especially for tables with many fields, and complicates future field additions. We recommend adding a column filtering feature to logical replication to streamline this process.
2、Logical Replication Account Permissions:
Logical replication permissions should be decoupled from general database access permissions.
Proposed workflow:
Create a dedicated account with logical replication privileges only.
Create a logical replication slot and grant this account access only to the authorized replication slot.
This account would have no direct access to the database itself but could subscribe to and consume data from the permitted replication slot.
This approach allows securely providing the account to the BI department. They can subscribe to the replication slot and perform downstream processing independently, without risking exposure of unauthorized data.
pgsql-hackers by date: