CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Jeff G
Jeff G

Reputation: 4677

How to Revoke Execute Privileges on Functions in PostgreSQL by Default

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.

Upvotes: 2

Views: 6352

Answers (2)

Fred Munck
Fred Munck

Reputation: 1

I can use "FOR ROLE"?

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

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246383

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;

Upvotes: 7

Related Questions