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.
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.