How to Revoke Execute Privileges on Functions in PostgreSQL by Default

postgresql, postgresql-9.5

Solution

If you specify `IN SCHEMA` with `ALTER DEFAULT PRIVILEGES`, you can only grant permissions, but not revoke them.

The documentation says:

Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type.

Therefore, you must revoke from the global default privileges by changing your command to:

ALTER DEFAULT PRIVILEGES FOR USER myAdmin
    REVOKE EXECUTE ON FUNCTIONS FROM public;

Problem

I am trying to set up default privileges in PostgreSQL 9.5.4 using the command `ALTER DEFAULT PRIVILEGES...`. This works when trying to grant permissions, but I can't figure out how to revoke execute permissions from functions by default. I have tried: ``` ALTER DEFAULT PRIVILEGES FOR USER myAdmin IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM public; ``` This appears to have no effect on the output of `\ddp`. Is there a way to prevent functions from being executable by users other than the owner, unless otherwise granted? Thanks.

Original source