test=# create schema private;
CREATE SCHEMA
test=# create sequence private.seq;
CREATE SEQUENCE
test=# create function bump() returns bigint language sql security definer as
$$ select nextval('private.seq'); $$;
CREATE FUNCTION
test=# revoke usage on schema private from pfrost;
REVOKE
test=# grant select, update on private.seq to pfrost;
GRANT
test=# set role pfrost;
SET
test=> select bump();
bump
------
1
(1 row)
test=> select nextval('private.seq');
ERROR: permission denied for schema private
test=> select currval('private.seq');
ERROR: permission denied for schema private
test=> select lastval();
lastval
---------
1
(1 row)
Aparrently, lastval remembers the last sequence by OID, and the check
for usage on a schema is made when resolving a name to an OID. Thus, the
schema usage check is never made for lastval.
Firstly there is the problem that this potentially reveals information
that was not visible prior to 8.1. Granted, I don't think this is a
serious security issue for most applications, but it does suprise me.
There is also the larger problem of the implementation of schema usage
checks. More serious functions might be added in the future that suffer
from the same vulnerability. For all I know, there might be some now. I
should think that a much better place for this check would be in the
same place that checks the ACL for the object itself.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
