How to fix Permission denied for function in PostgreSQL

First, identify which function is causing the error and inspect its current privileges.

-- As a superuser or function owner, check the function ACL
SELECT proname, proacl
FROM pg_proc
WHERE proname = 'your_function_name';

The proacl column shows the access control list. If it's NULL, PUBLIC has default EXECUTE permission. If it's set, it shows explicitly granted/revoked privileges.

The most direct fix is to grant EXECUTE permission to the user or role that needs to call the function.

-- Grant to a specific user
GRANT EXECUTE ON FUNCTION function_name(argument_types) TO username;

-- Grant to a role
GRANT EXECUTE ON FUNCTION function_name(argument_types) TO role_name;

-- Grant to all users (public)
GRANT EXECUTE ON FUNCTION function_name(argument_types) TO PUBLIC;

Make sure to include the function's argument types. For example: GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO monitoring_user;

Users need USAGE privilege on the schema containing the function to execute it. Grant schema access if the user lacks it.

-- Grant schema usage
GRANT USAGE ON SCHEMA schema_name TO username;

-- Grant on public schema
GRANT USAGE ON SCHEMA public TO username;

Without USAGE on the schema, users cannot access any objects in it, including functions.

If a user needs to execute multiple functions in a schema, grant permission on all of them at once.

-- Grant execute on all current functions in a schema
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO username;

-- Set default privileges for future functions
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name
GRANT EXECUTE ON FUNCTIONS TO username;

This is common for monitoring, backup, or automation accounts that interact with multiple functions.

Switch to the user account and attempt to execute the function to confirm the permission has been granted.

-- Connect as the user
SET ROLE username;

-- Try executing the function
SELECT * FROM function_name();

-- Reset to original role if in the same session
RESET ROLE;

If the function executes successfully, the permission grant worked. If it still fails, check that all steps above were completed.

On managed services like Azure PostgreSQL Flexible Server or AWS RDS, certain system functions may have restrictions that GRANT cannot override.

-- Try granting, but be aware some functions may still fail on managed databases
GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO monitoring_role;

If the grant succeeds but the function still fails, contact your database provider. Some extensions like pg_cron or timescaledb may require extension recreation or provider-side fixes. For pg_cron issues on Azure, try dropping and recreating the extension.