Re: [HACKERS] lastval exposes information that currval does not
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@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
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@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
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? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] lastval exposes information that currval does not
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
On Thu, Jul 27, 2006 at 04:40:45PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] lastval exposes information that currval does not
On Thu, Jul 27, 2006 at 05:01:37PM -0400, Andrew Dunstan wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
* Phil Frost ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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 quotelook 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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 quotelook 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] lastval exposes information that currval does not
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 quotelook 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
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@svana.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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
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@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
* Phil Frost ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] lastval exposes information that currval does not
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] 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.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
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.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
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/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
