0

I want to revoke execute from app.my_function function, so users with role doctor can't call it, I can't figure out what wrong with my code, I spend the whole day trying to find a solution.

CREATE SCHEMA app;

CREATE ROLE doctor;

GRANT USAGE ON SCHEMA app TO doctor;

CREATE FUNCTION app.my_function() AS $$ ... $$ LANGUAGE SQL;

REVOKE ALL ON FUNCTION app.my_function() FROM doctor;
lawayben
  • 33
  • 1
  • 8
  • In much less then a day you could have gone to the docs, in this case [Privileges](https://www.postgresql.org/docs/current/ddl-priv.html) and find that `GRANT USAGE ON SCHEMA app TO doctor;` does not do what you obviously think it does. It would also be helpful to know what it is you trying to achieve? Add as update to your question. – Adrian Klaver Sep 08 '21 at 20:18
  • I want to revoke execute from app.my_function, so users with role doctor can't call it – lawayben Sep 08 '21 at 20:33
  • You never granted execute permission, so there is no need to revoke it –  Sep 08 '21 at 20:37
  • Read the docs at the link I sent, it explains what the various privileges do on various objects. Short version your `GRANT` does not work on objects in the schema, so there is no privilege to `REVOKE`. You need to spend time at that link as well as [GRANT](https://www.postgresql.org/docs/current/sql-grant.html) and [REVOKE](https://www.postgresql.org/docs/current/sql-revoke.html). Otherwise you will end up hopelessly lost on what you did privilege wise. – Adrian Klaver Sep 08 '21 at 20:42
  • @a_horse_with_no_name but I can call it, even tho I didn't grant any permission, that's what confusing me – lawayben Sep 08 '21 at 20:42
  • If you had read the link you would have seen: `PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. ... EXECUTE privilege for functions and procedures ..."`, where `PUBLIC` is an implicitly granted role. – Adrian Klaver Sep 08 '21 at 20:48
  • @AdrianKlaver I've read this before and I didn't understand it very well, from my understanding, I have to grant EXECUTE privilege to all functions in my custom schema, and then revoke it, I've tried this `ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT EXECUTE ON FUNCTIONS TO doctor;` and it's still not working – lawayben Sep 08 '21 at 21:00
  • @AdrianKlaver I found the solution, you could just tell me that all roles inherit from PUBLIC role, I thought you're talking about PUBLIC schema. – lawayben Sep 08 '21 at 21:28
  • That is wrong. `PUBLIC` is just an additional role that can be granted to an object. If all roles inherited from it, when you revoked it on an object all the other roles would be revoked and that is not the case. You need to go over the links I posted several more times. Failing to understand what is going on is going to get you in a mess. To fully understand the role/privileges system in Postgres is beyond a comment or even an answer, that is the posted links. – Adrian Klaver Sep 08 '21 at 22:20
  • @AdrianKlaver thank you so much for you help, I'll make sure to read the docs in more depth. – lawayben Sep 09 '21 at 10:54

2 Answers2

1

here's the solution of my problem, I didn't know that user defined roles inherit from PUBLIC role which have EXECUTE privilege.

...

REVOKE ALL ON SCHEMA PUBLIC FROM PUBLIC;

ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;

...
lawayben
  • 33
  • 1
  • 8
0

The default privileges for functions (which you can change with ALTER DEFAULT PRIVILEGES) allow EXECUTE to PUBLIC, that is everybody. Revoking the privilege from some other role will have no effect.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263