0

I am using dblink with Postgres 12 to pull data from a remote database and inject it into a local environment. I am using dblink, bc it allows me to use a where clause to filter the data however I need to filter it. I am not wanting to check in my creds and I am also wanting to be able to remotely access different db's. I cannot find a way to do this with dblink. Here is the command that I am running:

insert into company(company_id, name, matching_regex)
    (select company.*
     from dblink(
             'dbname=test host=test.amazonaws.com port=5432 user=test password=password',
             'select company_id, name, matching_regex from company'
         ) AS company(company_id bigint, name varchar(100), matching_regex varchar(200)));

I have tried using .pgpass (a file with the creds embedded), but dblink is not able to read it for some reason. Can someone give an example of how to inject the creds, or another way to accomplish what I am trying to do?

jhamm
  • 24,124
  • 39
  • 105
  • 179
  • Postgres version? Define what '.pgpass, but haven't been able to make it work' means? Add this information as update to your question. Why not use [postgres-fdw](https://www.postgresql.org/docs/current/postgres-fdw.html)? – Adrian Klaver Jun 08 '21 at 17:23
  • Does it help you ? https://stackoverflow.com/questions/41486198/postgresql-9-5-hide-password-from-dblink-connection – Luc M Jun 08 '21 at 17:49
  • What about a foreign table using the extension postgres_fdw? Then you can store the credentials in the user mapping. – Frank Heikens Jun 08 '21 at 18:03

0 Answers0