Postgresql 9.1 - access tables through functions

Question

I have 3 roles: superuser, poweruser and user. I have a "data" table and functions data_select and data_insert.

Now I would like to determine that only the superuser can access the tabular "data". Poweruser and the user cannot directly access tabular "data", but only through functions.

The user can only run the data_select function, poweruser can run both data_select and data_insert.

So, I can create alice, bob, ... users and inherit their user privileges or poweuser.

Is this really achievable? I fight this on the second day and will not go anywhere.

Thank you for your time.

Answer 1

Yes, it is doable.

"superuser" may be the actual superuser , postgres by default. I renamed the role for ordinary users to usr because user is a reserved word - do not use it as an identifier.

 CREATE ROLE usr; CREATE ROLE poweruser; GRANT usr TO poweruser; -- poweruser can do everything usr can. CREATE ROLE bob PASSWORD <password>; GRANT poweruser TO bob; CREATE ROLE alice PASSWORD <password>; GRANT usr TO alice; REVOKE ALL ON SCHEMA x FROM public; GRANT USAGE ON SCHEMA x TO usr; REVOKE ALL ON TABLE x FROM public; REVOKE ALL ON TABLE y FROM public; CREATE FUNCTION ... SECURITY DEFINER; REVOKE ALL ON FUNCTION ... FROM public; GRANT EXECUTE ON FUNCTION a TO usr; GRANT EXECUTE ON FUNCTION b TO poweruser; 

Or you can create daemon roles without logging in to own functions and hold the appropriate rights in the table. It will be even safer.

If you go this route, you will like ALTER DEFAULT PRIVILEGES (introduced with PostgreSQL 9.0). More on this answer .

Read the chapter Writing SECURITY DEFINER Functions SECURITY DEFINER in the Guide.