Thread: postgres_fdw, remote triggers and schemas

Hi,

I've observed an issue whereby a parent table with a trigger that
redirects inserts to a child table fails to run the trigger
successfully if written to using a foreign table:

Example:

Database 1:

CREATE TABLE parent (id int, content text);

CREATE TABLE child () INHERITS (parent);

CREATE OR REPLACE FUNCTION redirect_func () RETURNS trigger AS $$
BEGIN INSERT INTO child VALUES (NEW.*); RETURN NULL;
END; $$ language plpgsql;

CREATE TRIGGER parent_trig BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE redirect_func();


Database 2:

CREATE FOREIGN TABLE foreign_parent (id int, content text) SERVER local_pg_db OPTIONS (table_name 'parent');


Then...

postgres=# INSERT INTO foreign_parent VALUES (2, 'test2');
ERROR:  relation "child" does not exist
CONTEXT:  Remote SQL command: INSERT INTO public.parent(id, content)
VALUES ($1, $2)
PL/pgSQL function public.redirect_func() line 3 at SQL statement

I've run that remote SQL command in isolation on database 1 and it
completes successfully.

It appears that this is caused by the relation reference in the
trigger function not being explicit about the schema, as if I remove
"public" from the search_path, I can generate this issue on database 1
with the same statement.  The search_path only contains 'pg_catalog'
on the foreign table connection.

Is this unintended, or is it something users should fix themselves by
being explicit about relation schemas in trigger functions?  Should
the schema search path instead pick up whatever the default would be
for the user being used for the connection?

Thom

Thom Brown <thom@linux.com> writes:
> I've observed an issue whereby a parent table with a trigger that
> redirects inserts to a child table fails to run the trigger
> successfully if written to using a foreign table:

That trigger is making unsafe assumptions about what search_path
it's run under.  If you don't want to schema-qualify the reference
to "child", consider attaching a "SET search_path" clause to the
trigger function definition.

> Is this unintended, or is it something users should fix themselves by
> being explicit about relation schemas in trigger functions?  Should
> the schema search path instead pick up whatever the default would be
> for the user being used for the connection?

postgres_fdw intentionally runs the remote session with a very minimal
search_path (I think just pg_catalog, in fact).  I would argue that
any trigger that breaks because of that is broken anyway, since it
would fail --- possibly with security implications --- if some ordinary
user modified the search path.
        regards, tom lane

On 15 November 2013 21:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> Is this unintended, or is it something users should fix themselves by
>> being explicit about relation schemas in trigger functions?  Should
>> the schema search path instead pick up whatever the default would be
>> for the user being used for the connection?
>
> postgres_fdw intentionally runs the remote session with a very minimal
> search_path (I think just pg_catalog, in fact).  I would argue that
> any trigger that breaks because of that is broken anyway, since it
> would fail --- possibly with security implications --- if some ordinary
> user modified the search path.

That makes sense.  Would it be worth putting a note in the
documentation about the behaviour of the search path on the
postgres_fdw page?

-- 
Thom