2

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.

Michal Špondr
  • 1,337
  • 2
  • 21
  • 44

2 Answers2

4

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;
Michal Špondr
  • 1,337
  • 2
  • 21
  • 44
0

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

Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32
  • Huh, my mistake, I am using viewer everywhere. Fixed. – Michal Špondr Jun 01 '17 at 12:30
  • 1
    I mean that owner of this function is either `viewer` role (or whatever else you want to revoke execute from) or owner is granted to `viewer` role. In this case changing permissions for this role will have no effect, since owner can change his own objects and so postgres grants permissions by default. – Łukasz Kamiński Jun 01 '17 at 12:33
  • All tables and functions were created by _postgres_ user. GRANTs and REVOKEs are also done by _postgres_ user. Now I am connected as _viewer_ user and I am still able to execute these functions. Maybe I just don't follow what you mean. – Michal Špondr Jun 01 '17 at 12:39
  • Does this SQL return any rows? If yes, what roleid are there? `select roleid::regrole, member::regrole, grantor::regrole from pg_auth_members where member = 'viewer'::regrole::oid` – Łukasz Kamiński Jun 01 '17 at 12:45
  • I think I get your idea. I removed grants from public and added them to specific user and now it works. – Michal Špondr Jun 01 '17 at 12:57
  • Oh, yeah, public as well. Can you post your solution as accepted answer so that those looking for unanswered questions won't see this one? – Łukasz Kamiński Jun 01 '17 at 13:19