1

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.