Thread: dblink: give search_path

Hi all

I try to execute a function not in the Schema I connect to with
dblink. Is there way to tell dblink to set search_path in a specific
way? I have not found a solution in the documentation. I tried with
the set search_path definition in the function declarations to no avail.

Function Schema: logger
Database: act
User: act
User Default Schema: act

Kind regards

Thiemo

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Zitat von Rene Romero Benavides <rene.romero.b@gmail.com>:

> What about setting the search path at the user level?
> ALTER ROLE act SET search_path = act,logger;
>
> Best.

Thanks for the inspiration. Maybe it is best to create a dedicated
user for logging anyway...

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

On 04/10/2018 11:44 PM, Thiemo Kellner wrote:
> Hi all
> 
> I try to execute a function not in the Schema I connect to with dblink. 
> Is there way to tell dblink to set search_path in a specific way? I have 
> not found a solution in the documentation. I tried with the set 
> search_path definition in the function declarations to no avail.

https://www.postgresql.org/docs/10/static/contrib-dblink-connect.html

"
Notes

If untrusted users have access to a database that has not adopted a 
secure schema usage pattern, begin each session by removing 
publicly-writable schemas from search_path. One could, for example, add 
options=-csearch_path= to connstr. This consideration is not specific to 
dblink; it applies to every interface for executing arbitrary SQL commands.
"

or schema qualify the function:

select logger.some_func();

> 
> Function Schema: logger
> Database: act
> User: act
> User Default Schema: act
> 
> Kind regards
> 
> Thiemo
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 04/10/2018 11:44 PM, Thiemo Kellner wrote:
> Hi all
> 
> I try to execute a function not in the Schema I connect to with dblink. 
> Is there way to tell dblink to set search_path in a specific way? I have 
> not found a solution in the documentation. I tried with the set 
> search_path definition in the function declarations to no avail.

In addition to my previous suggestions:

test=# SELECT public.dblink_connect('dbname=production ');
  dblink_connect
----------------
  OK

test=# select * from  public.dblink('show search_path') as 
t1(search_path text);
  search_path
-------------
  main

test=# select public.dblink_exec('set search_path=main,utility');
  dblink_exec
-------------
  SET
(1 row)

test=# select * from  public.dblink('show search_path') as 
t1(search_path text);
   search_path
---------------
  main, utility

> 
> Function Schema: logger
> Database: act
> User: act
> User Default Schema: act
> 
> Kind regards
> 
> Thiemo
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Zitat von Adrian Klaver <adrian.klaver@aklaver.com>:

> In addition to my previous suggestions:
>
> test=# SELECT public.dblink_connect('dbname=production ');
>  dblink_connect
> ----------------
>  OK
>
> test=# select * from  public.dblink('show search_path') as
> t1(search_path text);
>  search_path
> -------------
>  main
>
> test=# select public.dblink_exec('set search_path=main,utility');
>  dblink_exec
> -------------
>  SET
> (1 row)
>
> test=# select * from  public.dblink('show search_path') as
> t1(search_path text);
>   search_path
> ---------------
>  main, utility
>

Hi Adrian. Thanks for pointing this out. I wonder why I did not think
of it myself. However, I moved to a dedicated logging user such
avoiding this Problem. I also seems a clean solution that way.

--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.