4

I know that in Oracle it's possible to create stored dblink and after that use it in query. For example:

Script for creation dblink:

CREATE PUBLIC DATABASE LINK my_link CONNECT TO my_schema IDENTIFIED BY shema_password USING 'remote';

And after that we can use it in our queries:

SELECT * FROM some_table@my_link;

I didn't find same solution for Postgres. I undestood that we can create named dblink connection:

For this we must use dblink_connect with name param. But created named dblink will destroy after session close.

Or we can create dblink connection for every queries:

SELECT *
FROM dblink('host= port= dbname= user= password=',
            'select table_schema, table_name from information_schema.tables where table_schema = ''data''') AS t1 (table_schema TEXT, table_name TEXT);

Is it possible create stored dblink in Postgres and use it in different queries? Or I should create some function that return dblink connection params which encapsulate them?

I try use foreign table and do next steps:

Create postgres_fdw extension:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Create Server:

CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '-', port '-', dbname '-');

And create mapping for user 'sys' where set remote user and password:

CREATE USER MAPPING FOR sys SERVER light_house OPTIONS ( USER 'remote_user', PASSWORD 'remove_password');

GRANT USAGE ON FOREIGN SERVER my_server TO sys;

Create foreign table in schema:

CREATE FOREIGN TABLE system.my_local_table (
  colums ..
) SERVER my_server OPTIONS (schema_name 'remote_user', table_name 'remote_table'
);

GRANT SELECT ON TABLE system.home_measurement TO argus_sys;

after that I catch next exception:

[2F003] ERROR: password is required 
Description: Non-superuser cannot connect if the server does not request a password. 
Help: Target server's authentication method must be changed.
HAYMbl4
  • 1,450
  • 2
  • 15
  • 29
  • "*Non-superuser cannot connect if the server does not request a password*" - you honestly run a production server that does not require a password to connect? –  Jan 23 '17 at 17:15
  • @a_horse_with_no_name sorry, but I don't undestand. I declare passord to remote user in user mapping (I updated question and added scripts) and I thing that it's enough or I wrong? – HAYMbl4 Jan 24 '17 at 06:31
  • The error message seems to indicate that your server does not _require_ a password. –  Jan 24 '17 at 06:48

1 Answers1

2

You should use a foreign table.

To get rid of the error message, change the pg_hba.conf file on the remote database server to use md5 authentication (don't forget to reload with pg_ctl reload).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I try create foreign table but I can't use it with non superuser. I updated my question, please look it. – HAYMbl4 Jan 23 '17 at 16:50
  • When you say: "foreign server" you mean "remote db instance", right? There I must add configuration for my "local db instance" for example: `host remote_db_name remote_user_name local_ip md5` – HAYMbl4 Jan 24 '17 at 10:51
  • Right, that's what I mean. – Laurenz Albe Jan 24 '17 at 13:20