Re: lastval exposes information that currval does not

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.

On Jul 5, 2006, at 14:51, Phil Frost wrote:

> test=# create function bump() returns bigint language sql security
> definer as $$ select nextval('private.seq'); $$;

SECURITY DEFINER means that the function runs with the permissions of
the role used to create the function (ran the CREATE FUNCTION
command). Due to your # prompt, I'm guessing that you were a
superuser when you ran this command. Thus, bump() will be run with
the superuser's permissions.

The superuser most definitely has permissions to access private.seq.

This has nothing to do with schema security or lastval() versus
currval().

Check out the CREATE FUNCTION documentation:

http://www.postgresql.org/docs/8.1/interactive/sql-
createfunction.html

- Chris

On Wed, Jul 05, 2006 at 08:06:12PM -0400, Chris Campbell wrote:
> On Jul 5, 2006, at 14:51, Phil Frost wrote:
>
> >test=# create function bump() returns bigint language sql security
> >definer as $$ select nextval('private.seq'); $$;
>
> SECURITY DEFINER means that the function runs with the permissions of
> the role used to create the function (ran the CREATE FUNCTION
> command). Due to your # prompt, I'm guessing that you were a
> superuser when you ran this command. Thus, bump() will be run with
> the superuser's permissions.
>
> The superuser most definitely has permissions to access private.seq.
>
> This has nothing to do with schema security or lastval() versus
> currval().
>
> Check out the CREATE FUNCTION documentation:
>
> http://www.postgresql.org/docs/8.1/interactive/sql-
> createfunction.html

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.

> 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

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

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.

On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
> 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.

I'm pretty sure that's by design, especially given this tidbit of the
docs:

"Essentially this allows the grantee to "look up" objects within the
schema."

Though perhaps the intention is to change this once we have a means
to invalidate plans.

The docs probably should elaborate that once something's been looked
up you no longer need permissions on the schema it resides in.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:
> On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
> >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.
>
> The docs probably should elaborate that once something's been looked
> up you no longer need permissions on the schema it resides in.

I'm not sure this is really unexpected behaviour. On UNIX it is clearly
defined that file permissions are checked only on open. Once you've
opened it, changing permissions on the file won't affect you. If
someone passes you a read/write descriptor to a file, you can
read/write it even if you didn't have permissions to open the
file/socket/whatever yourself.

I'm not sure it makes sense to be able to revoke someone's permissions
on an object they've already accessed. From a transactional point of
view, the revoke should at the very least not affect transactions
started prior to the revokation. Some things are shared across an
entire session, and the rule extends to them. Is this a bug? Maybe, but
it is debatable.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Sun, Jul 09, 2006 at 02:32:24PM +0200, Martijn van Oosterhout wrote:
> On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:
> > On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
> > >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.
> >
> > The docs probably should elaborate that once something's been looked
> > up you no longer need permissions on the schema it resides in.
>
> I'm not sure this is really unexpected behaviour. On UNIX it is clearly
> defined that file permissions are checked only on open. Once you've
> opened it, changing permissions on the file won't affect you. If
> someone passes you a read/write descriptor to a file, you can
> read/write it even if you didn't have permissions to open the
> file/socket/whatever yourself.
>
> I'm not sure it makes sense to be able to revoke someone's permissions
> on an object they've already accessed. From a transactional point of
> view, the revoke should at the very least not affect transactions
> started prior to the revokation. Some things are shared across an
> entire session, and the rule extends to them. Is this a bug? Maybe, but
> it is debatable.

On UNIX it is also clearly defined that if one does not have execute
permissions on a directory, one can not open files within it by *any*
*means*. There are no procedures that bypass this by taking an inode
number directly.

It is generally understood in the UNIX commuinity that adding a function
in a new version that grants capabilities that were previously
unavailable is an obvious security bug.

If it doesn't make sense to be able to revoke permissions on objects
already accessed, why is this the behaviour of everything except the
schema usage check? Does your definition of "already accessed" include
"accessed in a 'security definer' procedure intended to prevent the
caller from accessing an object directly"?

Given that there are already several ways to bypass the check for usage
on a schema, and the developers seem to not be bothered at all by adding
more, of what security use is the schema usage privilege?

Is drawing a weak analogy to another system with a significantly
different security model a good way to validate security for PostgreSQL?

Is it a good idea to have a privilege with surprising semantics?

I'm sorry to keep arguing about this issue, but I am quite disturbed
with the lack of concern over security in the developer commuinity.
Perhaps the mindset here is that the SQL server will always be behind a
firewall and accessed through a web application. I'm here to say this is
not the case. Firewalls are comprimised, and not all applications are
web applications. I'd really not like to have to write a middleware
server just because the security in PostgreSQL is insufficient.

At a minimum, I'd like to see the documentation updated to document the
weakness of the usage privilege, and how to prevent these exploits. I'll
write the patch if there is agreement. Ideally, I'd like to see the
usage privilege changed to something more consistent and useful.

On Sun, Jul 09, 2006 at 11:24:38AM -0400, Phil Frost wrote:
> On UNIX it is also clearly defined that if one does not have execute
> permissions on a directory, one can not open files within it by *any*
> *means*. There are no procedures that bypass this by taking an inode
> number directly.

Well, not entirely true. If a file exists in multiple directories, you
can open it as long as any of the directories are currently accessable
to you (which is not the same as being accessable if you logged in
again).

However, the issue has been confused here by two completely different
examples. In one case you prepare a statement and then execute it later
which succeeds even though if you reprepared the statement it would
fail. This is no different from the UNIX case where having an open file
survives removing of permissions and even deletion.

> It is generally understood in the UNIX commuinity that adding a function
> in a new version that grants capabilities that were previously
> unavailable is an obvious security bug.

In this case you're referring to the lastval() issue. That case is
debatable I guess... You're suggesting it return a permission error
instead.

It's a little odd, though it think it's defensible position though. IMO
you should simply drop the lastval() function altogether, since I don't
think it's really that useful in exchange for the problems it creates.

> If it doesn't make sense to be able to revoke permissions on objects
> already accessed, why is this the behaviour of everything except the
> schema usage check? Does your definition of "already accessed" include
> "accessed in a 'security definer' procedure intended to prevent the
> caller from accessing an object directly"?

Well, that's a good question. At a guess it's because the
select/update/delete permissions are a property of the table, whereas
the schema is not. The table is a member of the schema. All that
suggests is that you should be revoking the permissions on the table
itself, rather than on the schema.

In the same vein, when reloading the pg_hba.conf, the database doesn't
immediatly disconnect all users who would be disallowed by the new
rules.

> Given that there are already several ways to bypass the check for usage
> on a schema, and the developers seem to not be bothered at all by adding
> more, of what security use is the schema usage privilege?

Several other ways? If there were a case where a user who has never had
access to a schema could access something in it, that would be an
issue. But arguing about when a revoke should take effect is a
completely different issue.

IME the developers are extremely interested in security issues.

> At a minimum, I'd like to see the documentation updated to document the
> weakness of the usage privilege, and how to prevent these exploits. I'll
> write the patch if there is agreement. Ideally, I'd like to see the
> usage privilege changed to something more consistent and useful.

I think it might be helpful for the documentation to state that USAGE
controls whether people can lookup objects within a schema and that
removing USAGE doesn't block access to the objects themselves, only
that they may not be referred to by name. To do that you need to revoke
permissions on the objects themselves.

I'm not a core developer though, so my opinions aren't really that
relevent. Do other database systems work the way you expect?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Docs updated:

<para>
For schemas, allows the grantee to find objects contained in the
specified schema (assuming that the objects' own privilege requirements
are also met).
</para>

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Sun, Jul 09, 2006 at 11:24:38AM -0400, Phil Frost wrote:
> > On UNIX it is also clearly defined that if one does not have execute
> > permissions on a directory, one can not open files within it by *any*
> > *means*. There are no procedures that bypass this by taking an inode
> > number directly.
>
> Well, not entirely true. If a file exists in multiple directories, you
> can open it as long as any of the directories are currently accessable
> to you (which is not the same as being accessable if you logged in
> again).
>
> However, the issue has been confused here by two completely different
> examples. In one case you prepare a statement and then execute it later
> which succeeds even though if you reprepared the statement it would
> fail. This is no different from the UNIX case where having an open file
> survives removing of permissions and even deletion.
>
> > It is generally understood in the UNIX commuinity that adding a function
> > in a new version that grants capabilities that were previously
> > unavailable is an obvious security bug.
>
> In this case you're referring to the lastval() issue. That case is
> debatable I guess... You're suggesting it return a permission error
> instead.
>
> It's a little odd, though it think it's defensible position though. IMO
> you should simply drop the lastval() function altogether, since I don't
> think it's really that useful in exchange for the problems it creates.
>
> > If it doesn't make sense to be able to revoke permissions on objects
> > already accessed, why is this the behaviour of everything except the
> > schema usage check? Does your definition of "already accessed" include
> > "accessed in a 'security definer' procedure intended to prevent the
> > caller from accessing an object directly"?
>
> Well, that's a good question. At a guess it's because the
> select/update/delete permissions are a property of the table, whereas
> the schema is not. The table is a member of the schema. All that
> suggests is that you should be revoking the permissions on the table
> itself, rather than on the schema.
>
> In the same vein, when reloading the pg_hba.conf, the database doesn't
> immediatly disconnect all users who would be disallowed by the new
> rules.
>
> > Given that there are already several ways to bypass the check for usage
> > on a schema, and the developers seem to not be bothered at all by adding
> > more, of what security use is the schema usage privilege?
>
> Several other ways? If there were a case where a user who has never had
> access to a schema could access something in it, that would be an
> issue. But arguing about when a revoke should take effect is a
> completely different issue.
>
> IME the developers are extremely interested in security issues.
>
> > At a minimum, I'd like to see the documentation updated to document the
> > weakness of the usage privilege, and how to prevent these exploits. I'll
> > write the patch if there is agreement. Ideally, I'd like to see the
> > usage privilege changed to something more consistent and useful.
>
> I think it might be helpful for the documentation to state that USAGE
> controls whether people can lookup objects within a schema and that
> removing USAGE doesn't block access to the objects themselves, only
> that they may not be referred to by name. To do that you need to revoke
> permissions on the objects themselves.
>
> I'm not a core developer though, so my opinions aren't really that
> relevent. Do other database systems work the way you expect?
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
-- End of PGP section, PGP failed!

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, Jul 10, 2006 at 12:49:54PM -0400, Bruce Momjian wrote:
>
> Docs updated:
>
> <para>
> For schemas, allows the grantee to find objects contained in the
> specified schema (assuming that the objects' own privilege requirements
> are also met).
> </para>

I think that misses the point. One can easily find objects in a schema
without usage by examining the system catalogs. The point is that there
are ways to access objects without going through the schema usage check,
and also that the check is made only once at the time a name is resolved
to an oid, which may then be cached in a prepared statement, stored
procedure, lastval, or the like. I would suggest something more like
this:

For schemas, allows the grantee to reference objects within the
specified schema by name. Note that any method of accessing an
object that does not involve naming will not check for this
privilege. For example, any function taking an OID parameter or
lastval(). Also, the check for this privilege will be made only once
when a query is planned, so stored plans such as from prepared
statements or stored procedures will not make the check again when
subsequently executed.

In applications where security is very important, it may be wise to
assure that no users have undesired privileges on objects within a
schema, and not to rely solely on the schema usage privilege.

On Mon, Jul 10, 2006 at 01:42:27PM -0400, Phil Frost wrote:
> I think that misses the point. One can easily find objects in a schema
> without usage by examining the system catalogs. The point is that there
> are ways to access objects without going through the schema usage check,
> and also that the check is made only once at the time a name is resolved
> to an oid, which may then be cached in a prepared statement, stored
> procedure, lastval, or the like. I would suggest something more like
> this:

Can you SELECT/UPDATE/DELETE from a table knowing only its oid? I'd
like to see that trick. lastval() is an odd case, given the user
doesn't actually supply the oid.

> In applications where security is very important, it may be wise to
> assure that no users have undesired privileges on objects within a
> schema, and not to rely solely on the schema usage privilege.

Indeed, never give priveledges unless you're sure you want people to
have them.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Mon, Jul 10, 2006 at 08:24:08PM +0200, Martijn van Oosterhout wrote:
> On Mon, Jul 10, 2006 at 01:42:27PM -0400, Phil Frost wrote:
> > I think that misses the point. One can easily find objects in a schema
> > without usage by examining the system catalogs. The point is that there
> > are ways to access objects without going through the schema usage check,
> > and also that the check is made only once at the time a name is resolved
> > to an oid, which may then be cached in a prepared statement, stored
> > procedure, lastval, or the like. I would suggest something more like
> > this:
>
> Can you SELECT/UPDATE/DELETE from a table knowing only its oid? I'd
> like to see that trick. lastval() is an odd case, given the user
> doesn't actually supply the oid.

I haven't found a way to do this yet, but I wouldn't be suprised if
there is a clever way, especially considering C extensions that might
come from contrib or other sources. It seems like there is a good deal
of potential for non-malicious developers to open unknowingly serious
security holes. I think lastval is a great example of this potential;
fortunately sequence values are rarely compromising. Imagine the
consequences of a function which returns the last inserted row in a
similar manner.

> > In applications where security is very important, it may be wise to
> > assure that no users have undesired privileges on objects within a
> > schema, and not to rely solely on the schema usage privilege.
>
> Indeed, never give priveledges unless you're sure you want people to
> have them.

The way I ran into this problem is moving a table that was previously in
a public schema into a private schema. Since not having usage on the
schema is enough to prevent access most of the time, the change passed
testing. Later I noticed the odd grant on some objects in the private
schema, and poking around I found ways to access them dispite not having
usage on the schema.

The "check just once at plan time, and only when referencing an object
by name" semantics of the schema usage check which differ from the
"check always" semantics of all the other ACL checks I found to be
suprising. Since these fine details are not obvious, expected, or
documented, there seems to be a good deal of potential for
administrative error. Hopefully at least the docs will be updated so
people can at least be aware of the issue.

* Phil Frost (indigo(at)bitglue(dot)com) wrote:
> I haven't found a way to do this yet, but I wouldn't be suprised if
> there is a clever way, especially considering C extensions that might
> come from contrib or other sources. It seems like there is a good deal
> of potential for non-malicious developers to open unknowingly serious
> security holes. I think lastval is a great example of this potential;
> fortunately sequence values are rarely compromising. Imagine the
> consequences of a function which returns the last inserted row in a
> similar manner.

Yes, you can compromise the security of the system by loading C modules.
That's not going to change. If you find examples of such compromises in
core, or in contrib, please bring them to our attention. As for from
other sources, well, you'd have to bring it up with that source..

Thanks,

Stephen

On 7/9/2006 8:32 AM, Martijn van Oosterhout wrote:

> On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:
>> On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
>> >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.
>>
>> The docs probably should elaborate that once something's been looked
>> up you no longer need permissions on the schema it resides in.
>
> I'm not sure this is really unexpected behaviour. On UNIX it is clearly
> defined that file permissions are checked only on open. Once you've
> opened it, changing permissions on the file won't affect you. If
> someone passes you a read/write descriptor to a file, you can
> read/write it even if you didn't have permissions to open the
> file/socket/whatever yourself.

This isn't the case and I do agree with Phil on this. The fact that
another "security definer" function did access an object during the
session should not give the user the ability to access it in the manner
shown in his example. lastval() without arguments should not remember
the sequence by its oid only, but also remember the sequences schema and
to a proper ACL check on that as well.

Just think of it if SELECT without a FROM clause would automatically
assume the same rangetable as the last SELECT in the session. If that
were the case, would you guy's defend the position that "SELECT *" then
should spit out the full content of the last table accessed by the
security definer function just called, even if the user doesn't have
schema permission? I doubt!

Jan

>
> I'm not sure it makes sense to be able to revoke someone's permissions
> on an object they've already accessed. From a transactional point of
> view, the revoke should at the very least not affect transactions
> started prior to the revokation. Some things are shared across an
> entire session, and the rule extends to them. Is this a bug? Maybe, but
> it is debatable.
>
> Have a nice day,

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Updated text:

For schemas, allows access to objects contained in the specified
schema (assuming that the objects' own privilege requirements are
also met). Essentially this allows the grantee to <quote>look up</>
objects within the schema. Without this permission, it is still
possible to see the object names by querying the system tables, but
they cannot be accessed via SQL.

---------------------------------------------------------------------------

Jan Wieck wrote:
> On 7/9/2006 8:32 AM, Martijn van Oosterhout wrote:
>
> > On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:
> >> On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
> >> >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.
> >>
> >> The docs probably should elaborate that once something's been looked
> >> up you no longer need permissions on the schema it resides in.
> >
> > I'm not sure this is really unexpected behaviour. On UNIX it is clearly
> > defined that file permissions are checked only on open. Once you've
> > opened it, changing permissions on the file won't affect you. If
> > someone passes you a read/write descriptor to a file, you can
> > read/write it even if you didn't have permissions to open the
> > file/socket/whatever yourself.
>
> This isn't the case and I do agree with Phil on this. The fact that
> another "security definer" function did access an object during the
> session should not give the user the ability to access it in the manner
> shown in his example. lastval() without arguments should not remember
> the sequence by its oid only, but also remember the sequences schema and
> to a proper ACL check on that as well.
>
> Just think of it if SELECT without a FROM clause would automatically
> assume the same rangetable as the last SELECT in the session. If that
> were the case, would you guy's defend the position that "SELECT *" then
> should spit out the full content of the last table accessed by the
> security definer function just called, even if the user doesn't have
> schema permission? I doubt!
>
>
> Jan
>
> >
> > I'm not sure it makes sense to be able to revoke someone's permissions
> > on an object they've already accessed. From a transactional point of
> > view, the revoke should at the very least not affect transactions
> > started prior to the revokation. Some things are shared across an
> > entire session, and the rule extends to them. Is this a bug? Maybe, but
> > it is debatable.
> >
> > Have a nice day,
>
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck(at)Yahoo(dot)com #
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
>
> Updated text:
>
> For schemas, allows access to objects contained in the specified
> schema (assuming that the objects' own privilege requirements are
> also met). Essentially this allows the grantee to <quote>look up</>
> objects within the schema. Without this permission, it is still
> possible to see the object names by querying the system tables, but
> they cannot be accessed via SQL.

No, this still misses the point entirely. See all my examples in this
thread for ways I have accessed objects without usage to their schema
with SQL.

Phil Frost wrote:
> On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
> >
> > Updated text:
> >
> > For schemas, allows access to objects contained in the specified
> > schema (assuming that the objects' own privilege requirements are
> > also met). Essentially this allows the grantee to <quote>look up</>
> > objects within the schema. Without this permission, it is still
> > possible to see the object names by querying the system tables, but
> > they cannot be accessed via SQL.
>
> No, this still misses the point entirely. See all my examples in this
> thread for ways I have accessed objects without usage to their schema
> with SQL.

OK, well we are not putting a huge paragraph in there. Please suggest
updated text.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote:
> Phil Frost wrote:
> > On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
> > >
> > > Updated text:
> > >
> > > For schemas, allows access to objects contained in the specified
> > > schema (assuming that the objects' own privilege requirements are
> > > also met). Essentially this allows the grantee to <quote>look up</>
> > > objects within the schema. Without this permission, it is still
> > > possible to see the object names by querying the system tables, but
> > > they cannot be accessed via SQL.
> >
> > No, this still misses the point entirely. See all my examples in this
> > thread for ways I have accessed objects without usage to their schema
> > with SQL.
>
> OK, well we are not putting a huge paragraph in there. Please suggest
> updated text.

Well, if you won't explain the whole situation, nor change it, then all
you can really say is it doesn't really work always. How about this:

For schemas, allows access to objects contained in the specified
schema. Note that the converse is not true in many cases: revoking
usage on a schema is not sufficient to prevent access in all cases.
There is precedent for new ways to bypass this check being added in
future releases. It would be unwise to give this privilege much
security value.

Phil Frost wrote:
> On Wed, Jul 12, 2006 at 06:09:31PM -0400, Bruce Momjian wrote:
> > Phil Frost wrote:
> > > On Wed, Jul 12, 2006 at 11:37:37AM -0400, Bruce Momjian wrote:
> > > >
> > > > Updated text:
> > > >
> > > > For schemas, allows access to objects contained in the specified
> > > > schema (assuming that the objects' own privilege requirements are
> > > > also met). Essentially this allows the grantee to <quote>look up</>
> > > > objects within the schema. Without this permission, it is still
> > > > possible to see the object names by querying the system tables, but
> > > > they cannot be accessed via SQL.
> > >
> > > No, this still misses the point entirely. See all my examples in this
> > > thread for ways I have accessed objects without usage to their schema
> > > with SQL.
> >
> > OK, well we are not putting a huge paragraph in there. Please suggest
> > updated text.
>
> Well, if you won't explain the whole situation, nor change it, then all
> you can really say is it doesn't really work always. How about this:
>
> For schemas, allows access to objects contained in the specified
> schema. Note that the converse is not true in many cases: revoking
> usage on a schema is not sufficient to prevent access in all cases.
> There is precedent for new ways to bypass this check being added in
> future releases. It would be unwise to give this privilege much
> security value.

Updated text:

For schemas, allows access to objects contained in the specified
schema (assuming that the objects' own privilege requirements are
also met). Essentially this allows the grantee to <quote>look up</>
objects within the schema. Without this permission, it is still
possible to see the object names, e.g. by querying the system tables,
so this is not a completely secure way to prevent object access.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Jul 19, 2006 at 02:42:49PM -0400, Bruce Momjian wrote:
> Updated text:
>
> For schemas, allows access to objects contained in the specified
> schema (assuming that the objects' own privilege requirements are
> also met). Essentially this allows the grantee to <quote>look up</>
> objects within the schema. Without this permission, it is still
> possible to see the object names, e.g. by querying the system tables,
> so this is not a completely secure way to prevent object access.

I think you are not understanding the nature of the problem I have
described. It is just not the names that can be accessed in the absence
of usage on a schema, it is *the content of the relations*. It is
obvious to anyone who has ever looked in pg_* that metadata is not
hidden by any amount of permission twiddling with grant and revoke. This
isn't great from a security standpoint, but at least it's documented and
expected, so one can design around it.

However, the usage on schema privilege has undocumented, unexpected
behavior. One would think from the documentation and from
experimentation that one can not exercise any privileges on an object
(excepting what can be done through the system catalogs) without having
usage on the schema that contains it. However, this is not always the
case!

If you look at my previous posts, I have repeatedly demonstrated ways to
access objects (not just the names or metadata, but the _full_
_contents_) contained in a schema to which one does not have the 'usage'
privilege. The developers must consider this a "feature", because none
have acknowledged it as a security bug. This being the case, it is
important that people be advised that the schema usage privilege does
not always control access to contained objects, and that the ways which
it can be bypassed are perhaps not numerous, but definitely subtle, and
thus likely to escape security audits and later be discovered by an
attacker. It should be known that the PostgreSQL developers have
recently added a function lastval() which newly exposes such a way to
bypass the check, and that this has not been officially acknowledged as
a security flaw.

OK, text again updated:

For schemas, allows access to objects contained in the specified
schema (assuming that the objects' own privilege requirements are
also met). Essentially this allows the grantee to <quote>look up</>
objects within the schema. Without this permission, it is still
possible to see the object names, e.g. by querying the system tables.
Also, after revoking this permission, existing backends might have
statements that have previously performed this lookup, so this is not
a completely secure way to prevent object access.

---------------------------------------------------------------------------

Phil Frost wrote:
> On Wed, Jul 19, 2006 at 02:42:49PM -0400, Bruce Momjian wrote:
> > Updated text:
> >
> > For schemas, allows access to objects contained in the specified
> > schema (assuming that the objects' own privilege requirements are
> > also met). Essentially this allows the grantee to <quote>look up</>
> > objects within the schema. Without this permission, it is still
> > possible to see the object names, e.g. by querying the system tables,
> > so this is not a completely secure way to prevent object access.
>
> I think you are not understanding the nature of the problem I have
> described. It is just not the names that can be accessed in the absence
> of usage on a schema, it is *the content of the relations*. It is
> obvious to anyone who has ever looked in pg_* that metadata is not
> hidden by any amount of permission twiddling with grant and revoke. This
> isn't great from a security standpoint, but at least it's documented and
> expected, so one can design around it.
>
> However, the usage on schema privilege has undocumented, unexpected
> behavior. One would think from the documentation and from
> experimentation that one can not exercise any privileges on an object
> (excepting what can be done through the system catalogs) without having
> usage on the schema that contains it. However, this is not always the
> case!
>
> If you look at my previous posts, I have repeatedly demonstrated ways to
> access objects (not just the names or metadata, but the _full_
> _contents_) contained in a schema to which one does not have the 'usage'
> privilege. The developers must consider this a "feature", because none
> have acknowledged it as a security bug. This being the case, it is
> important that people be advised that the schema usage privilege does
> not always control access to contained objects, and that the ways which
> it can be bypassed are perhaps not numerous, but definitely subtle, and
> thus likely to escape security audits and later be discovered by an
> attacker. It should be known that the PostgreSQL developers have
> recently added a function lastval() which newly exposes such a way to
> bypass the check, and that this has not been officially acknowledged as
> a security flaw.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

All right, I give up. I guess no one seems to want to admit this is a
bad security policy, or accurately document it. Does that make it an
easter egg?

On Thu, Jul 20, 2006 at 01:59:43PM -0400, Bruce Momjian wrote:
>
> OK, text again updated:
>
> For schemas, allows access to objects contained in the specified
> schema (assuming that the objects' own privilege requirements are
> also met). Essentially this allows the grantee to <quote>look up</>
> objects within the schema. Without this permission, it is still
> possible to see the object names, e.g. by querying the system tables.
> Also, after revoking this permission, existing backends might have
> statements that have previously performed this lookup, so this is not
> a completely secure way to prevent object access.

Phil Frost wrote:
> All right, I give up. I guess no one seems to want to admit this is a
> bad security policy, or accurately document it. Does that make it an
> easter egg?

I'm sure some people agree that there is a problem. It would help,
however, if you were not talking about two different things at once.
And it would help if you actually proposed a change that would improve
matters.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Thu, Jul 27, 2006 at 06:36:30PM +0200, Peter Eisentraut wrote:
> I'm sure some people agree that there is a problem. It would help,
> however, if you were not talking about two different things at once.
> And it would help if you actually proposed a change that would improve
> matters.

What two things are there? I think I've already proposed a solution: do
the schema usage check at the same time as all the other permission
checks. Alternately, document the behavior accurately. I've proposed
text here too, but the revisions keep missing the point. What needs
clarification?

Phil Frost wrote:
> What two things are there?

Well, the subject says "lastval exposes information that currval does
not" while you are talking about schema permissions. I still don't
know what you're really after. One of your posts stated that you have
repeatedly demonstrated ways to overcome some restriction, but there is
actually only one post that contains some SQL that seems to deal with
schemas.

> I think I've already proposed a solution:
> do the schema usage check at the same time as all the other
> permission checks.

This is an oversimplification, because the permission checks are all
done at different times.

> Alternately, document the behavior accurately.
> I've proposed text here too, but the revisions keep missing the
> point. What needs clarification?

I have interpreted your proposed revision as cynical, not as a serious
proposal.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Phil Frost wrote:
> On Thu, Jul 27, 2006 at 06:36:30PM +0200, Peter Eisentraut wrote:
> > I'm sure some people agree that there is a problem. It would help,
> > however, if you were not talking about two different things at once.
> > And it would help if you actually proposed a change that would improve
> > matters.
>
> What two things are there? I think I've already proposed a solution: do
> the schema usage check at the same time as all the other permission
> checks. Alternately, document the behavior accurately. I've proposed
> text here too, but the revisions keep missing the point. What needs
> clarification?

What we should really do is have lastval() fail if the user does not
have appropiate permissions on the schema. Having it not fail is a bug,
and documenting a bug turns it not into a feature, but into a "gotcha".

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> What we should really do is have lastval() fail if the user does not
> have appropiate permissions on the schema. Having it not fail is a bug,
> and documenting a bug turns it not into a feature, but into a "gotcha".

I'm unconvinced that it's either a bug or a gotcha. lastval doesn't
tell you which sequence it's giving you a value from, so I don't really
see the reasoning for claiming that there's a security hole. Also,
*at the time you did the nextval* you did have permissions. Does anyone
really think that a bad guy can't just remember the value he got?
lastval is merely a convenience.

regards, tom lane

Tom Lane wrote:

>Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>
>
>>What we should really do is have lastval() fail if the user does not
>>have appropiate permissions on the schema. Having it not fail is a bug,
>>and documenting a bug turns it not into a feature, but into a "gotcha".
>>
>>
>
>I'm unconvinced that it's either a bug or a gotcha. lastval doesn't
>tell you which sequence it's giving you a value from, so I don't really
>see the reasoning for claiming that there's a security hole. Also,
>*at the time you did the nextval* you did have permissions. Does anyone
>really think that a bad guy can't just remember the value he got?
>lastval is merely a convenience.
>
>
>
>

Is that true even if it was called by a security definer function?

I too don't think that the security danger of knowing the value of a
(possibly unknown) sequence is very high, but that's another argument.

cheers

andrew

On Thu, Jul 27, 2006 at 04:40:45PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > What we should really do is have lastval() fail if the user does not
> > have appropiate permissions on the schema. Having it not fail is a bug,
> > and documenting a bug turns it not into a feature, but into a "gotcha".
>
> I'm unconvinced that it's either a bug or a gotcha. lastval doesn't
> tell you which sequence it's giving you a value from, so I don't really
> see the reasoning for claiming that there's a security hole. Also,
> *at the time you did the nextval* you did have permissions. Does anyone
> really think that a bad guy can't just remember the value he got?
> lastval is merely a convenience.

Not true. The nextval can be done as a different user (think security
definer functions), and then some other user can get the value with
lastval.

Why does that lastval doesn't tell you what sequence it's going to use
negate the security holeness of the issue?

If lastval is merely a convenience, then why does it also do more than
currval? Yeah, it's convenient. But it exposes more information.

If it's not a bug or a gotcha, then it should be at least one of
expected and documented. I would hardly say the short paragraph in the
documentation describes the (weird, i think) semantics of the schema
usage privilege. I would not expect to do anything to objects in a
schema with no usage privilege by any method, since this the observed
behavour by normal methods.

And, what of the precarious of the schema usage check in general? What
if someone forgets about it and writes a function that compromises
something more serious -- say the last inserted row? Wouldn't it be not
so great if your application's security depends on a user not being able
to access some information, but an attacker notices it can be accessed
in some obscure way before you do? I can definately understand if there
are performance reasons, or even just a lack of urgency, but it should
at least be fully documented in an obvious place in the manual.

On Thu, Jul 27, 2006 at 05:01:37PM -0400, Andrew Dunstan wrote:
> Tom Lane wrote:
>
> >Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >>What we should really do is have lastval() fail if the user does not
> >>have appropiate permissions on the schema. Having it not fail is a bug,
> >>and documenting a bug turns it not into a feature, but into a "gotcha".
> >
> >I'm unconvinced that it's either a bug or a gotcha. lastval doesn't
> >tell you which sequence it's giving you a value from, so I don't really
> >see the reasoning for claiming that there's a security hole. Also,
> >*at the time you did the nextval* you did have permissions. Does anyone
> >really think that a bad guy can't just remember the value he got?
> >lastval is merely a convenience.
>
> Is that true even if it was called by a security definer function?
>
> I too don't think that the security danger of knowing the value of a
> (possibly unknown) sequence is very high, but that's another argument.
>
> cheers
>
> andrew

Granted, I can't think of too many ways one could store sensitive
information in a sequence. I think it's more important to consider what
it implies about the system behind the issue. When I revoke some
privilege, I expect it to be enforced regardless of the method by which
one attempts to exercise that privilege.

Being able to bypass the schema usage check by using an OID rather than
a name would be one hell of a security flaw were it not that there are
relatively few ways to access information by an OID exposed. However,
there may be obscure ways to access tables or other more "serious"
information that no one has noticed yet. The fact that this behaviour
isn't exactly obvious leads me to believe developers of the server or
server extensions are likely to unknowingly expose more ways to do this.

* Phil Frost (indigo(at)bitglue(dot)com) wrote:
> Granted, I can't think of too many ways one could store sensitive
> information in a sequence. I think it's more important to consider what
> it implies about the system behind the issue. When I revoke some
> privilege, I expect it to be enforced regardless of the method by which
> one attempts to exercise that privilege.

Attempting to stretch the lastval issue into some systemic problem with
the security system without backing it up with anything substantial at
all really isn't going to help your case in the least. If you're aware
of actual issues then bring them up. If you've got a better approach to
security in Postgres then discuss it (*concretely*, like with code or
catalog changes) and implement it.

> Being able to bypass the schema usage check by using an OID rather than
> a name would be one hell of a security flaw were it not that there are
> relatively few ways to access information by an OID exposed. However,

Got any others beyond 'lastval'? Is 'lastval' even doing what you're
claiming (looking at the actual catalog on disk by using the OID)? My
recollection was that it was actually just storing the value in a bit of
backend-local memory, but I havn't gone and looked at the code yet. Have
you looked at the code behind 'lastval'?

> there may be obscure ways to access tables or other more "serious"
> information that no one has noticed yet. The fact that this behaviour
> isn't exactly obvious leads me to believe developers of the server or
> server extensions are likely to unknowingly expose more ways to do this.

Again, stretching a relatively minor point about lastval to some kind of
systemic problem, with the servers or the developers, isn't going to get
anyone anywhere.

Thanks,

Stephen

On Thu, Jul 27, 2006 at 09:37:22PM -0400, Stephen Frost wrote:
> Got any others beyond 'lastval'? Is 'lastval' even doing what you're
> claiming (looking at the actual catalog on disk by using the OID)? My
> recollection was that it was actually just storing the value in a bit of
> backend-local memory, but I havn't gone and looked at the code yet. Have
> you looked at the code behind 'lastval'?

Well, you got me curious and so I looked at the code in question. The
code does have a check, but it just checks if the user has access to
the sequence. If the user doesn't have SELECT or USAGE on the sequence
in question, lastval() will indeed fail with an error.

> Again, stretching a relatively minor point about lastval to some kind of
> systemic problem, with the servers or the developers, isn't going to get
> anyone anywhere.

Not the least of which is that arguments involving "people can install
C code into the backend and break security" are truisms: installed C
code can do *anything* which is why only superusers can install such
functions...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Fri, Jul 28, 2006 at 09:54:38PM +0200, Martijn van Oosterhout wrote:
> Not the least of which is that arguments involving "people can install
> C code into the backend and break security" are truisms: installed C
> code can do *anything* which is why only superusers can install such
> functions...

My argument was not that installing C code can break things. My argument
was that authors of C code are likely to forget about this "feature" and
unknowingly open new security holes. Obviously no one can force C
extension author to not do stupid or horrible things, but we can at
least help him not unknowingly do horrible things.

Again, fix is really simple. Document the issue, making it damn clear in
the docs that the schema usage check means *nothing* when accessing an
object by OID, and advising users that the ways to access things by OID
are obscure but present and changing, so relying on the schema usage
privilege is not a good idea. I'm not asking for a 2000 line patch here.
A simple documentation change will do -- one that doesn't try to skirt
around the issue like a dirty little secret.

Phil Frost wrote:

> Again, fix is really simple. Document the issue, making it damn clear in
> the docs that the schema usage check means *nothing* when accessing an
> object by OID, and advising users that the ways to access things by OID
> are obscure but present and changing, so relying on the schema usage
> privilege is not a good idea. I'm not asking for a 2000 line patch here.
> A simple documentation change will do -- one that doesn't try to skirt
> around the issue like a dirty little secret.

Such as?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Jul 28, 2006 at 04:42:11PM -0400, Phil Frost wrote:
> Again, fix is really simple. Document the issue, making it damn clear in
> the docs that the schema usage check means *nothing* when accessing an
> object by OID, and advising users that the ways to access things by OID
> are obscure but present and changing, so relying on the schema usage
> privilege is not a good idea. I'm not asking for a 2000 line patch here.
> A simple documentation change will do -- one that doesn't try to skirt
> around the issue like a dirty little secret.

Well, I suppose you could add something like the following:

If you use use low-level functions like relation_open/index_open/etc no
permission checks are done at all. No schema check, nothing. There is
also no check for possible deadlock issues, no check whether you got a
strong enough lock for the operation you are trying to do. Caveat
emptor. If you want to be sure you are not bypassing security checks,
use the SPI interface.

Would that help? You're talking about the schema check as if it's a
special case, but when people use low-level functions they have to
check *everything* themselves.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.