function not found in trigger function postgres_fdw

Question

I have created two postgres databases. Version 9.5.4. The first database called persist. I am working with some example code from postgrest for authentication. The code works great, here is my persist database definition:

create schema localschema;

grant all on schema localschema to postgres;

create table  localschema.users (
  email    text primary key check ( email ~* '^.+@.+\..+$' ),
  pass     text not null check (length(pass) < 512),
  role     name not null check (length(role) < 512),
  verified boolean not null default false
  -- If you like add more columns, or a json column
);
create or replace function
localschema.encrypt_pass() returns trigger
  language plpgsql
  as $$
declare
  mvar text;
begin
  if tg_op = 'INSERT' or new.pass <> old.pass then
    new.pass = crypt(new.pass, gen_salt('bf'::text)); 
  end if;
  return new;
end
$$;
drop trigger if exists encrypt_pass on localschema.users;
create trigger encrypt_pass
  before insert or update on localschema.users
  for each row
  execute procedure localschema.encrypt_pass();

Fairly simple, here I call an insert to the table followed by a select, you can see the pass use crypted via the trigger/function encrypt_pass:

persist=# insert into localschema.users values('[email protected]','123123','postgres');
persist=# select * from localschema.users;
  email  |                             pass                             |   role   | verified 
---------+--------------------------------------------------------------+----------+----------
 [email protected] | $2a$06$5p5eM6sJAfxZ4qSv0Jgx..GlflYkNeE7aY.D4kR9K0glRZv2wU7ue | postgres | f
(1 row)

Works great. Next step is postgres_fdw, I import the foreign schema to the remote database to my other database:

CREATE EXTENSION IF NOT EXISTS "postgres_fdw";

create server self foreign data wrapper postgres_fdw options(host '127.0.0.1', dbname 'persist', port '5432');
  create user mapping for postgres server self options (user 'postgres');
create  schema "localschema";
  grant all on schema localschema to postgres;
import foreign schema localschema from server self into "localschema" options ( import_default 'true');

Then a select to prove it is working:

cache=# select * from localschema.users;
  email  |                             pass                             |   role   | verified 
---------+--------------------------------------------------------------+----------+----------
 [email protected] | $2a$06$5p5eM6sJAfxZ4qSv0Jgx..GlflYkNeE7aY.D4kR9K0glRZv2wU7ue | postgres | f
(1 row)

The problem is that I can't insert into this table. Here is the error in action:

cache=# insert into localschema.users values ('[email protected]','234234','postgres');
ERROR:  function gen_salt(text) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  Remote SQL command: INSERT INTO localschema.users(email, pass, role, verified) VALUES ($1, $2, $3, $4)
PL/pgSQL function localschema.encrypt_pass() line 6 at assignment

It is weird, any function can't be found, it isn't just gen_salt. I have put some raise exception statements on the persist side to verify that my current_user is indeed postgres. So, why would all of my functions disappear when I execute via postgres_fdw?

What am I missing? I am pretty sure I have had this working, but in all my setup/tear down I somehow lost the proper incantation.

Answer 1

This answer was originally posted inside the question itself by the OP:

---

I finally figured out the issue right after I posted this. Dang. Right from the postgres_fdw manual postgres-fdw:

In the remote sessions opened by postgres_fdw, the search_path parameter is set to just pg_catalog, so that only built-in objects are visible without schema qualification. This is not an issue for queries generated by postgres_fdw itself, because it always supplies such qualification. However, this can pose a hazard for functions that are executed on the remote server via triggers or rules on remote tables. For example, if a remote table is actually a view, any functions used in that view will be executed with the restricted search path. It is recommended to schema-qualify all names in such functions, or else attach SET search_path options (see CREATE FUNCTION) to such functions to establish their expected search path environment.

I changed the function so that it uses the full path to what it calls:

create or replace function
localschema.encrypt_pass() returns trigger
  language plpgsql
  as $$
declare
  mvar text;
begin
  if tg_op = 'INSERT' or new.pass <> old.pass then
    new.pass = public.crypt(new.pass, public.gen_salt('bf'::text));
  end if;
  return new;
end
$$;