Is there a possibility to filter queries in PostgreSQL?

Question

I want to limit access to username+password table to one query pattern like:

SELECT count(id) AS auth_result
  FROM user
 WHERE username = '%s' 
   AND password = SHA1('%s')

(this query doesn't pretend to be working from the point of injection vulnerability, just an example)

Is that possible? or am I missing some different approach?

Answer 1

I don't know whether what you plan is good or not but it should be possible with rewrite rules. Never played with it though.

Answer 2

You can revoke all access to user table from all users except the owner, and create a view with like this:

create view auth_view as
select id, username, sha1(password) as sha1pass
  from user;

Then your query will look like:

select count(id)
  from auth_view
 where username = '%s'
   and sha1pass = sha1('%s')

Other possibility might be using PostgreSQL's rule system to rewrite or avoid certain king of queries on user table. But I am not sure what you are trying to do here.

Answer 3

I would suggest you write a function, and make it SECURITY DEFINER. See for example http://www.hagander.net/talks/hidden%20gems%20of%20postgresql.pdf, pages 28-36.