PostgreSQL 9.5: Hide password from dblink connection

Question

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

I am using the dblink for this.

Procedure:

Step 1: Created extension.

CREATE EXTENSION dblink;

Step 2: Making dblink connection string.

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

Step 3:

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

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

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.

You may want to have a look to FDW (postgresql.org/docs/current/static/postgres-fdw.html) instead of dblink. In the former you only have to specify the password once, which a trusted user can do from a secure terminal. Then you can allow users from a given role to use it. — Fabian Pijcke
– Fabian Pijcke, Commented Jan 5, 2017 at 13:34
The .pgpass file must be created on the server where Postgres is running and must be in the $HOME directory of the operating system user under which Postgres is started (typically postgres on Linux). — user330315
– user330315, Commented Jan 5, 2017 at 13:55

alvaro nortes · Accepted Answer · 2020-03-09 14:52:42Z

Install dblink extension:

CREATE EXTENSION dblink;

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');

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
        );

More info:

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

Collectives™ on Stack Overflow

PostgreSQL 9.5: Hide password from dblink connection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related