How to grant only functions while revoking tables?

Posted on

Question :

I am a newbie to PostgreSQL, and configuring a new database.

I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table.

GRANT CONNECT ON DATABASE db1 TO role1;
GRANT USAGE ON SCHEMA schema1 TO role1;

REVOKE ALL ON table1 FROM role1;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema1 TO role1;

If I don’t REVOKE table privilege, direct table access will be allowed. If I REVOKE table privilege, and GRANT functions, Users access the functions but see table access restriction error.

psql:./test1.psql:6: ERROR:  permission denied for relation table1

How can I make user role1 can access data using only functions while disallowing all direct access to table? I think I missed something, but I can’t figure it out.

P.S. I am using Postgres 9.2.x.

Answer :

You want to make your functions SECURITY DEFINER and have them owned by a user that does have the requisite rights.

Be very careful when coding SECURITY DEFINER functions. Don’t make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER functions. Where appropriate create multiple roles for different access levels.

See CREATE FUNCTION.

Leave a Reply

Your email address will not be published. Required fields are marked *