Grant / Revoke on Views and prepared statements

[Alex Cornford]

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

[Jeff Davis]

Post by Alex Cornford
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 ?

Access control works on views, functions, schemas, etc.; but does not
distinguish prepared vs. ad-hoc statements. So you can't force users to
use prepared statements.

You can get a similar effect by putting the queries into functions and
revoking all privileges from the user except to call those functions.
That should help avoid problems with SQL injection. Also, if you write
the functions in PL/pgSQL, they will be prepared automatically.

Post by Alex Cornford
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.

That works for me. The REVOKE command above has a typo; it should be
"FROM" not "TO".

Here's what I did:

(as superuser)

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

(reconnect as role1)

=> select * from test_table;
ERROR: permission denied for relation test_table
=> select * from test_view;
t1 | t2
----+----
(0 rows)

Regards,
Jeff Davis