CODEWITHSUNDEEP
postgresqldatabase-replicationpostgresql-15
morpen
morpen

Reputation: 11

Securing Postgres' Logical Replication Column Lists

I'm looking into publishing a replica database for consumption by other users in my org. This database contains a lot of sensitive data, so a solution I was considering was using Postgres 15's Logical Replication Column Lists, which allow you to only publish specific columns to your subscribers. However, these come with a caveat:

The choice of columns can be based on behavioral or performance reasons. However, do not rely on this feature for security: a malicious subscriber is able to obtain data from columns that are not specifically published. If security is a consideration, protections can be applied at the publisher side.

So my question is twofold: how can a malicious subscriber obtain data from unpublished columns, and what are the protections that can be applied at the publisher side?

I tried searching around the documentation and SO, but couldn't find any details on the topic. Thanks!

Upvotes: 1

Views: 61

Answers (1)

Stavros Koureas
Stavros Koureas

Reputation: 1482

Based on this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=923def9a533a7d986acfb524139d8b9e5466d0a5

The description maintion:

Columns not included in this list are not sent to the subscriber, allowing the schema on the subscriber to be a subset of the publisher schema.

So practically if the data of the columns are not sent this should be safe right? Reading the code a bit, I do not think that data are sent and then later are discarded on subscriber, I also do not see any change on subscription.

In addition in this article, they describe the choosing the columns means lower network traffic, so this indicating that data are not sent at all. Finally, they also describe it as a kind of data protection.

https://www.postgresql.fastware.com/blog/column-lists-in-logical-replication-publications

Although it is strange to see this announcement that cannot be rely on this feature for security. I think that is meant that if there is a malicious subscriber to be malicious it is connected to your source database, so it holds your connection string and handshakes etc, so maybe it can access also your source directly...

Upvotes: 0

Related Questions