CODEWITHSUNDEEP
postgresqlsql-grant
Michal Špondr
Michal Špondr

Reputation: 1535

Revoke execute privileges on insertion functions in PostgreSQL

I have database with role viewer:

CREATE ROLE viewer WITH NOSUPERUSER NOCREATEDB NOCREATEROLE;

and also database schema called i (as for interface). There are insert functions in schema i, i.e.:

SELECT * FROM i.insert_machine(1,2,3);

inserts new row in table data.machine. There are some non-insertion functions, too, e.g. i.error_table(integer) which should be allowed to execute by user viewer. Grant options are:

GRANT USAGE ON SCHEMA i TO viewer;
GRANT SELECT ON ALL TABLES IN SCHEMA i TO viewer;

I want to forbid user viewer to call these insert_* methods. How should I do it? If I try both:

REVOKE ALL ON FUNCTION i.insert_machine(int, int, int) FROM viewer;
REVOKE EXECUTE ON FUNCTION i.insert_machine(int, int, int) FROM viewer;

I am still able to run this method and get result.

Upvotes: 2

Views: 2349

Answers (2)

Michal Špondr
Michal Špondr

Reputation: 1535

I think I've find it out. I have to revoke execute rights from public and opt-in only specific roles:

REVOKE EXECUTE ON FUNCTION i.insert_machine(integer, integer, integer) FROM public;
GRANT EXECUTE ON FUNCTION i.insert_machine(integer, integer, integer) TO writer;

Upvotes: 4

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Change owner to different role. It must be eventviewer at the moment.

Upvotes: 0

Related Questions