Re: dblink: give search_path

[Adrian Klaver]

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.kla...@aklaver.com

Re: dblink: give search_path

[Adrian Klaver]

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.kla...@aklaver.com

Re: dblink: give search_path

[Thiemo Kellner, NHC Barhufpflege]

Zitat von Rene Romero Benavides :


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=0x8F70EFD2D972CBEF



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

Re: dblink: give search_path

[Rene Romero Benavides]

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

Best.

2018-04-11 1:44 GMT-05:00 Thiemo Kellner :

> 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=0x8F70EFD2D972CBEF
>
> 
> This message was sent using IMP, the Internet Messaging Program.
>
>


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

dblink: give search_path

[Thiemo Kellner]

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=0x8F70EFD2D972CBEF



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