Re: lastval exposes information that currval does not - Mailing list pgsql-hackers

On Wed, Jul 05, 2006 at 05:57:08PM -0700, Joshua D. Drake wrote:
> 
> > I am well aware of what security definer means. The significant part of
> > this example is that lastval() will allow the caller to see the value of
> > a sequence where currval('seq') will not. This means that things which
> > might have been forbidden in 8.0 are now accessible in 8.1.
> >
> > It also means that revoking usage on a schema is not sufficient to
> > prevent a user from accessing things within that schema, a property that
> > makes me quite uncomfortable.
> 
> Then the public schema must drive you nuts :). If you were to create the 
> function as a non-super user you would probably be good.
> 
> Joshua D. Drake

I use the public schema for public things. You are still missing the
point of the example. I could have written it any number of other ways.
I could have granted update, but not select on the sequence to the
non-superuser.

Not using security definer will not change the fact that although I can
not use currval to get the current value of the sequence, I can get it
with lastval. This behavour is unexpected, not explicitly documented,
not really useful, and new in 8.1. This means it could potentially open
new security holes in existing programs. The suprising and hardly
documented behaviour means that new programs are likely to be
vulnerable.

More importantly, it reveals that the security check for schema usage is
not part of the ACL check for actions like selecting a table. This means
that revoking usage on a schema provides no security guarantee. For
example:

test=# create schema private;
test=# grant usage on schema private to public;
test=# create table private.insecure as select 1;
test=# grant select on private.insecure to public;
test=# set role pfrost;
test=> prepare exploit as select * from private.insecure;
test=> reset role;
test=# revoke usage on schema private from public;
test=# set role pfrost;
test=> select * from private.insecure;
ERROR:  permission denied for schema private
test=> execute exploit;?column? 
----------       1
(1 row)

test=> 

I hope the above example is strong enough to elicit a comment from a
qualified developer. If it is not, consider that stored procedures
contain prepared statements, and many client applications cache prepared
statements as well. Thus, revoking usage on a schema is about as good as
nothing until all sessions have ended. It also means that any function
which operates with OIDs can potentially bypass the schema usage check.