Thread: Grant / Revoke on Views and prepared statements

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

On Tue, 2012-10-23 at 17:56 +0300, Alex Cornford wrote:
> 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.

> 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