Postgresql – Permission denied on function after schema copy

permissionspostgresql-9.6schema-copy

I had one schema 'public' and simple function in the schema named 'isvalidoption'.

What was done:

  • backup schema using pg_dump;
  • rename schema to 'development';
  • create new schema 'public';
  • restore it from backup using pg_restore.

The problem:

  • when I select from any table omitting schema prefix like 'SELECT *
    FROM mytable;' it's OK
  • when I run function with schema prefix like 'SELECT public.isvalidoption();' or 'SELECT development.isvalidoption()' it's OK;
  • when I run function omitting schema prefix like 'SELECT isvalidoption();' I get ERROR: permission denied for function isvalidoption

User has USAGE on both schemas and EXECUTE on function in both schemas also he is owner of the function in both schemas.

PostgreSQL version is 9.6.1.

'SELECT current_user;' shows correct user.

'SHOW search_path;' shows '"$user", public'.

Any ideas?

Best Answer

After hours of reading of documentation I found that PosgreSQL first searches pg_catalog schema even before all schemas listed in search_path.

So checked pg_catalog catalog and I don't know why but my function was defined there and user postgres was the owner of the function.

I just deleted my function from pg_catalog and it's OK and works perfect as expected.

I still don't know how the function got there in pg_catalog.

Related Question