[pgpool-hackers: 301] Re: postgres_fdw set schema to pg_catalog

[Tatsuo Ishii]

> Recently a user wants to use postgres_fdw which pgpool as a SERVER. I
> don't know why but postgres_fdw set the schema search path to
> pg_catalog. Pgpool wants to execute has_pgpool_regclass() against
> PostgreSQL with the schema search path specified by postgres_fdw, and
> the query fails because pgpool_regclass() is registered in public
> schema. I'm not sure if we want to fix this or how to fix it:
> 
> 1) If the use case (postgres_fdw accesses pgpool) is extremely rare,
>    it's not worth the trouble. Also there's a workaround (register
>    pgpool_regclass() to pg_catalog).
> 
> 2) It's worth to fix. Because users may want to disable the schema
>    pgpool_regclass() lives.
> 
> 3) If #1 or #2 is true, how to fix it.
> 
>    a) register pgpool_regclass in pg_catalog.
> 
>    b) modify the query used in has_pgpool_regclass(). Below is the
>       proposed change. This does not help the case #1 or #2, but
>       properly tells that pgpool_regclass() is not available, rather
>       than throwing an error.
> 
> 	  SELECT count(*) from (SELECT has_function_privilege('%s',
> 	  'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT 1
> 	  FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace AS n WHERE
> 	  p.proname = 'pgpool_regclass' AND n.nspname = 'public' AND
> 	  p.pronamespace = n.oid)) AS s;

I went into #3-a.

Because #3-b is not perfect because in the future user may want to
register pgpool_regclass in other than public schema.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp