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.