Grant / Revoke on Views and prepared statements - Mailing list pgsql-novice

I am using Postgres 9.2.1 on Centos 6.3 (64 bit), accessed by pgadmin.

I am designing a web software system. I want to secure my database by giving minimum privileges to task defined roles. I want to block user level access to the tables. I am trying to only allow access via views and prepared statement for user roles. Is it possible to grant / revoke on a prepared statement ?

Trying view permissions

CREATE TABLE test_table (t1 int, t2 varchar, confidential_data varchar, ...);
CREATE VIEW test_view AS SELECT t1, t2 FROM test_table;
REVOKE SELECT ON test_table TO role1;
GRANT SELECT ON test_view TO role1;

SELECT * FROM test_view;   -> I get a "permission denied for relation test_table"  I hoped the grant on the view would allow select on the view while preventing select direct on the table, but the grant on the view has not overridden the revoke on the table.

Am I trying something fundamentally unworkable ?
_________________________________________________

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

I prefer to block SQL injection at db level (OWASP defense strategy 2) -  does anyone have a howto or a guide to  postgresql database security using something similar to stored_procedures ?



Thanks
Alex