I think we pretty much have conceptual agreement on the shape of the
solution to this problem: when a view is created with CREATE SECURITY
VIEW, restrict functions and operators that might disclose tuple data
from being pushed down into view (unless, perhaps, the user invoking
the view has sufficient privileges to execute the underlying query
anyhow, e.g. superuser or view owner). What we have not resolved is
exactly how we're going to decide which functions and operators might
do such a dastardly thing. I think the viable options are as follows:
1. Adopt Heikki's proposal of treating indexable operators as non-leaky.
http://archives.postgresql.org/pgsql-hackers/2010-06/msg00291.php
Or, perhaps, and even more restrictively, treat only
hashable/mergeable operators as non-leaky.
2. Add an explicit flag to pg_proc, which can only be set by
superusers (and which is cleared if a non-superuser modifies it in any
way), allowing a function to be tagged as non-leaky. I believe that
it would be reasonable to set this flag for all of our built-in
indexable operators (though I'd read the code before doing it), but it
would remove the need for the assumption that third-party operators
are equally sane.
CREATE OR REPLACE FUNCTION doesnt_leak() RETURNS integer AS $$SELECT
42$$ IMMUTABLE SEAWORTHY; -- doesn't leak
This problem is not going away, so we should try to decide on something.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company