How to Revoke Execute Privileges on Functions in PostgreSQL by Default

Question

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.

@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
– Jeff G, Commented Oct 27, 2016 at 23:01
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
– Nick, Commented Oct 27, 2016 at 23:13
@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
– Jeff G, Commented Oct 27, 2016 at 23:17
Strange... Seems to work without the IN SCHEMA public, though I have no idea what the difference is... — Nick Barnes
– Nick Barnes, Commented Oct 28, 2016 at 5:34

Jeff G · Accepted Answer · 2016-10-28 13:47:17Z

7

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;

edited Oct 28, 2016 at 13:47

Jeff G

4,7354 gold badges46 silver badges85 bronze badges

answered Oct 28, 2016 at 11:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fred Munck · Accepted Answer · 2022-06-27 20:23:02Z

0

I can use "FOR ROLE"?

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

answered Jun 27, 2022 at 20:23

Fred Munck

1

Collectives™ on Stack Overflow

How to Revoke Execute Privileges on Functions in PostgreSQL by Default

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related