How to Revoke Execute Privileges on Functions in PostgreSQL by Default

1
votes

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.

1
postgresqlpostgresql-9.5
What role(s) does public currently belong to? - Nick
@Nicarus I'm fairly new to PostgreSQL roles, and therefore don't understand your question. I have tried the same command, but replaced the final public identifier with a specific user's name. That also has no effect on the output of \ddp. - Jeff G
Run this: SELECT * FROM pg_roles WHERE rolename = 'public'; You should be able to see the privs. and roles for that role. An example would be the role is superuser and therefore you altering the privs would not have an impact. - Nick
@Nicarus That select statement (I had to change rolename to rolname) returned 0 rows. It is my understanding that 'public' is a built-in keyword meaning all users. Is that not correct? - Jeff G
Strange... Seems to work without the IN SCHEMA public, though I have no idea what the difference is... - Nick Barnes

1 Answers

6
votes

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;