PostgreSQL 9.5:隐藏 dblink 连接的密码 [英] PostgreSQL 9.5: Hide password from dblink connection

问题描述

我想要你位于另一个数据库中的表.

I want to you table which is located in the other database.

我为此使用了 dblink.

程序:

第 1 步:创建扩展.

Step 1: Created extension.

CREATE EXTENSION dblink;

第 2 步:制作 dblink 连接字符串.

Step 2: Making dblink connection string.

select dblink_connect('con','host=127.0.0.1 dbname=makdb user=postgres password=postgres');

第 3 步:

select * from dblink('con','select cola,colb from tbl_test') as tbl(cola int,colb varchar(10));

我的问题:如何在第 2 步中隐藏密码?

My Question: How do i hide password in step 2?

通过搜索我知道我需要创建 .pgpass 文件.但是在如何创建以及在哪个步骤中我需要使用该文件名时陷入了困境.

By searching i came to know that i need to create .pgpass file. But got stuck in how to create and in which step i need to use that file name.

推荐答案

安装 dblink 扩展:

Install dblink extension:

CREATE EXTENSION dblink;

安装 postgres_fdw 扩展(可用于访问存储在外部 PostgreSQL 服务器中的数据):

Install postgres_fdw extension (which can be used to access data stored in external PostgreSQL servers):

CREATE EXTENSION postgres_fdw;

创建一个新的外部服务器连接:

Create a new foreign server connection:

CREATE server myserver foreign data wrapper postgres_fdw
OPTIONS (dbname 'foreign_dbname', host 'foreign_host');

为您最近创建的外部服务器连接和您的数据库创建一个用户映射.

Create a user mapping for the foreign server connection that you recently created and your database.

CREATE USER MAPPING FOR "user_in_current_database"
SERVER myserver OPTIONS (user 'foreign_user', password 'foreign_password');

在创建了 conexion 的远程数据库中选择一些字段.请注意，您不再需要使用用户名和密码.

Select some fields in a remote db with the conexion created. Notice that you does not need use the user and password anyrmore.

SELECT tmp_table.*
FROM dblink(
             'myserver',
             '
             SELECT field1,
                 field2
             FROM table
             '
         )
         AS tmp_table(
                      field1 TEXT,
                      field2 BIGINT
        );

更多信息:

https://www.postgresql.org/docs/9.5/postgres-fdw.html

https://www.postgresql.org/docs/current/sql-createserver.html

https://www.postgresql.org/docs/current/sql-createusermapping.html

这篇关于PostgreSQL 9.5:隐藏 dblink 连接的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！