Postgresql dblink ERROR: function does not exist

Question

I wanted to call a function from another database using dblink

Created a connection

select dblink_connect('b_dblink','hostaddr=someHost dbname=paydb user=user password=pass');

After that I try to call a function located in another database

select * 
from dblink('b_dblink', 'select * from report_prov()') 
as t(id bigint, created_dt timestamp without time zone, amount numeric, status_id character varying, service_id integer, agent_id bigint, external_id character varying);

Then I get an ERROR

ERROR:  function report_prov() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  Error occurred on dblink connection named "b_dblink": could not execute query.
SQL state: 42883

The function itself (select * from report_prov();), works well for me. But if I try to call it via dblink, I get an error

There is no reason for this not to work. You are probably connecting to the wrong database or using the wrong schema or something like that. — jjanes
– jjanes, Commented Feb 5, 2022 at 16:28

Saitejareddy · Accepted Answer · 2022-02-05 11:23:28Z

After executing this:

select dblink_connect('b_dblink','hostaddr=someHost dbname=paydb user=user password=pass');

You should also create wrapper as mentioned below:

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect 
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c       
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

reference for the above code is here

And as you trying to access the function from the other db, you need to give this function permission to the user as show below. assuming user name as my_user.

GRANT USAGE ON SCHEMA my_schema TO my_user;

The above command allows the execution of functions but not the access to tables. So, if my_user executes the function, it still produces an access error, if the function accesses tables etc. in my_schema. To avoid this, you may define your function as security definer:

ALTER FUNCTION my_schema.my_function(...) SECURITY DEFINER SET search_path = public;

Also an other way to give execute permission to specific users::

REVOKE ALL ON FUNCTION my_schema.my_function(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION my_schema.my_function(...) TO my_user;

Collectives™ on Stack Overflow

Postgresql dblink ERROR: function does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related