Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
It's always best to mention your PostgreSQL versions in questions, btw. –  Craig Ringer Apr 6 '13 at 17:53
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.