Question on any plans to use the Create Server/Create blink_ Mapping to provide Logical Replication Subscriptions the user/password in an encrypted manner
Hello All
Postgresql dblinks and dblink_fdw allow for the use of Server and user mapping to be able to store the user/password of a connection and save it in an encrypted manner.
Logical replication subscription syntax regarding connection info allows for the user/password to be supplied within the subscription ddl.
And the Subscription connection info is visible via the pg_subscription.subconninfo column, which can contain plain-text passwords, is intentionally restricted. Only the pg_read_all_settings role, superusers, and the owner of the subscription can SELECT from this column.
In a dblink the connection info can be provided via the same connection parameters as allowed by the logical subscription syntax, however it is allowed to use a Created ServerName with a user mapping in the connections.
I am not familiar on what it would take to allow logical subscriptions to use User Server/Mapping logic as the dblink extension allows but if it where possible then this would assure that only the role creating the User server/mapping can set the connection user/password and then it can be totally hidden from prying eyes.
I was wondering if this is within any plan to implement in future releases.
Thanks
Anthony Vitale
On Tue, Jul 15, 2025 at 7:28 PM Vitale, Anthony, Sony Music
<anthony.vitale@sonymusic.com> wrote:
Hello All
Postgresql dblinks and dblink_fdw allow for the use of Server and user mapping to be able to store the user/password of a connection and save it in an encrypted manner.
Logical replication subscription syntax regarding connection info allows for the user/password to be supplied within the subscription ddl.
And the Subscription connection info is visible via the pg_subscription.subconninfo column, which can contain plain-text passwords, is intentionally restricted. Only the pg_read_all_settings role, superusers, and the owner of the subscription can SELECT from this column.
In a dblink the connection info can be provided via the same connection parameters as allowed by the logical subscription syntax, however it is allowed to use a Created ServerName with a user mapping in the connections.
I am not familiar on what it would take to allow logical subscriptions to use User Server/Mapping logic as the dblink extension allows but if it where possible then this would assure that only the role creating the User server/mapping can set the connection user/password and then it can be totally hidden from prying eyes.
I was wondering if this is within any plan to implement in future releases.
Please check if the proposal at [1]/messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com suits your needs.
[1]: /messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com
--
Best Wishes,
Ashutosh Bapat
Hello All
I am not sure if I am posting this to the correct PG list, please let me know if there are other lists better suited to answer this question.
Postgresql dblinks and dblink_fdw allow for the use of Server and user mapping to be able to store the user/password of a connection and save it in an encrypted manner.
Logical replication subscription syntax regarding connection info allows for the user/password to be supplied within the subscription ddl.
And the Subscription connection info is visible via the pg_subscription.subconninfo column, which can contain plain-text passwords, is intentionally restricted. Only the pg_read_all_settings role, superusers, and the owner of the subscription can SELECT from this column.
In a dblink the connection info can be provided via the same connection parameters as allowed by the logical subscription syntax, however it is allowed to use a Created ServerName with a user mapping in the connections.
I am not familiar on what it would take to allow logical subscriptions to use User Server/Mapping logic as the dblink extension allows but if it where possible then this would assure that only the role creating the User server/mapping can set the connection user/password and then it can be totally hidden from prying eyes.
I was wondering if this is within any plan to implement in future releases.
Thanks
Anthony Vitale
On Mon, Jul 21, 2025 at 11:43 PM Vitale, Anthony, Sony Music
<anthony.vitale@sonymusic.com> wrote:
I am not sure if I am posting this to the correct PG list, please let me know if there are other lists better suited to answer this question.
Postgresql dblinks and dblink_fdw allow for the use of Server and user mapping to be able to store the user/password of a connection and save it in an encrypted manner.
Logical replication subscription syntax regarding connection info allows for the user/password to be supplied within the subscription ddl.
And the Subscription connection info is visible via the pg_subscription.subconninfo column, which can contain plain-text passwords, is intentionally restricted. Only the pg_read_all_settings role, superusers, and the owner of the subscription can SELECT from this column.
In a dblink the connection info can be provided via the same connection parameters as allowed by the logical subscription syntax, however it is allowed to use a Created ServerName with a user mapping in the connections.
I am not familiar on what it would take to allow logical subscriptions to use User Server/Mapping logic as the dblink extension allows but if it where possible then this would assure that only the role creating the User server/mapping can set the connection user/password and then it can be totally hidden from prying eyes.
Can you check the work being discussed in thread [1]/messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com and see if that
addresses your requirement?
[1]: /messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com
--
With Regards,
Amit Kapila.
Hi
Yes, it is exactly what I am looking for
However, it appears that this patch (The Ability to create server with FOR CONNECTION ONLY) was never implemented OR at least I can't find it.
Do you know if there is any way to find out what ever happened to this ?
Thanks
Can you check the work being discussed in thread [1]/messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com and see if that addresses your requirement?
[1]: /messages/by-id/149ff9264db27cdf724b65709fbbaee4bf316835.camel@j-davis.com
--
With Regards,
Amit Kapila.
On Tue, Jul 22, 2025 at 7:33 PM Vitale, Anthony, Sony Music
<anthony.vitale@sonymusic.com> wrote:
Yes, it is exactly what I am looking for
However, it appears that this patch (The Ability to create server with FOR CONNECTION ONLY) was never implemented OR at least I can't find it.
Do you know if there is any way to find out what ever happened to this ?
I see that the last version of the patch is posted in email [1]/messages/by-id/e0c6fd0fa6036df36779c8bd8ae763c6f4064135.camel@j-davis.com. Then
there were some comments which Jeff told he will address for next
release, see [2]/messages/by-id/606b345bcbcb229515ab155e37e13de9333f2c09.camel@j-davis.com. You can help by reviewing/testing the patch or if
the author doesn't have time, you can ask in that thread whether you
can continue working on the patch by addressing open comments.
[1]: /messages/by-id/e0c6fd0fa6036df36779c8bd8ae763c6f4064135.camel@j-davis.com
[2]: /messages/by-id/606b345bcbcb229515ab155e37e13de9333f2c09.camel@j-davis.com
--
With Regards,
Amit Kapila.