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] [GENERAL] UUID's as primary keys
On Sat, Jul 08, 2006 at 05:54:26PM -0400, Jim Nasby wrote: +1. If there's enough user demand we can look at adding the type to core (I don't see any real advantage to contrib over pgFoundry for this). I'm not sure if it makes sense to add a generic 16 byte RAW to core, either. I'd *much* rather see effort expended on a generic RAW type which had it's size defined as part of the type and didn't use varlena. You could place a nice wrapper around type generators, which would let you say: DECLARE TYPE RAW(16); After which point you could use that type in function declarations and such. It would create an OID for that type would could be used as normal. I think that trying to get the backend to pay more attention to typmods is not going to be successful. Simply because functions and operators have an affect on the typmod and once you start relying on typmods to decode a tuple, you've got a real problem. As an example, what do you get when you concatenate two CHAR(4)'s? Do you get another CHAR(4) or is it a CHAR(8)? How does the backend know? You'd have to accompany each function with another function just to tell you how the typmods would be related. The only way out I can think of is that RAW(n) is merely a sort of template and RAW(x) cannot be used in a place where RAW(y) is expected (if xy). Hence it makes sense to have a template that people can instantiate instances of and let the rest of the system treat them as new types, unrelated to anything else. 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
[HACKERS] getting type name
Hi, How do i get a char * type from a type Oid. i.e. getStringTypeFromOid(INT4OID) will return int4. 10x. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(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] getting type name
On Sun, Jul 09, 2006 at 09:03:21PM +0300, Tzahi Fadida wrote: How do i get a char * type from a type Oid. i.e. getStringTypeFromOid(INT4OID) will return int4. Server-side or client-side? In the backend I think you could use format_type_be() or format_type_with_typemod(), both declared in utils/builtins.h and defined in src/backend/utils/adt/format_type.c. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] getting type name
On Sunday 09 July 2006 21:49, Michael Fuhr wrote: On Sun, Jul 09, 2006 at 09:03:21PM +0300, Tzahi Fadida wrote: How do i get a char * type from a type Oid. i.e. getStringTypeFromOid(INT4OID) will return int4. Server-side or client-side? In the backend I think you could use Backend. format_type_be() or format_type_with_typemod(), both declared in utils/builtins.h and defined in src/backend/utils/adt/format_type.c. They return format_type_be(INT4OID) = integer or format_type_be(FLOAT8OID) = double precision I need to use this in a query with the :: cast operator. There is a function SPI_gettype but it works on relations. I wish to work directly with the oid types without opening a relation. 10x. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] getting type name
On Sun, Jul 09, 2006 at 10:08:42PM +0300, Tzahi Fadida wrote: They return format_type_be(INT4OID) = integer or format_type_be(FLOAT8OID) = double precision I need to use this in a query with the :: cast operator. The problem being? test=# select '1'::integer, '4.5'::double precision; int4 | float8 --+ 1 |4.5 (1 row) 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] getting type name
On Monday 10 July 2006 00:29, Martijn van Oosterhout wrote: On Sun, Jul 09, 2006 at 10:08:42PM +0300, Tzahi Fadida wrote: They return format_type_be(INT4OID) = integer or format_type_be(FLOAT8OID) = double precision I need to use this in a query with the :: cast operator. The problem being? Just if it is a one-to-one conversion, otherwise the same type equality functions i use would potentially not work properly. test=# select '1'::integer, '4.5'::double precision; int4 | float8 --+ 1 |4.5 (1 row) Have a nice day, -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(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] Statement Queuing
A while ago in connection with the 8.2 planning [1] there was some discussion of resource management and statement queuing [2]. I am currently looking at implementing a resource management/queuing module for Greenplum - initially targeting Bizgres, but I'm thinking it could be beneficial for non-Bizgres (i.e Postgresql) users too. There has been some discussion on the Bizgres list already [3] together with some WIP code [4] and a review [5]. the code is a little rough - representing my getting to grips with the standard lock mechanism in order to produce enough of a prototype to study the issues. In that light I would very much appreciate comments concerning the design itself and also feedback for the questions posted in the review [4] - either here, the Bizgres-general list or both. Here is a lightning overview of this whole resource queuing/scheduling business to hopefully put it in context (very short version of [3]): For certain workloads (particularly DSS and reporting), the usual controls (max_connections or a connection pool, work_mem etc) are not really enough to stop the situation where several simultaneously executing expensive queries temporarily cripple a system. This is particularly the case where user specified queries are permitted. What is needed is some way to throttle or queue these queries in some finer manner - such as (simple case) restricting the number of simultaneously executing queries, or restricting the total cost of all simultaneously executing queries (others are obviously possible, these are just the simplest). To make this work a new object - a resource queue is proposed, which holds limits and current counters for resources, plus a new sort of lock, something like a standard one, but instead of deterministic conflict rules based on lockmethod, a check on the counter/total for the relevant resource is performed instead. best wishes Mark [1] http://archives.postgresql.org/pgsql-hackers/2006-03/msg01122.php [2] http://archives.postgresql.org/pgsql-hackers/2006-03/msg00821.php [3] http://pgfoundry.org/pipermail/bizgres-general/2006-June/000492.html [4] http://homepages.paradise.net.nz/markir/download/bizgres/bizgres-resschedular-06-29.patch [5] http://lists.pgfoundry.org/pipermail/bizgres-general/2006-July/000521.html ---(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
[HACKERS] pgsql-patches considered harmful
Pursuant to a conversation this evening I would like to a suggestion: BIRT pgsql-patches should be abolished in favour of something else that accomplishes the bandwidth-reduction aspect without the downsides. My complaint is that -patches serves to a) siphon off some of the most technical discussion from -hackers to somewhere where fewer hackers read regularly leaving a lower signal-to-noise ratio on -hackers. b) partition the discussions in strange ways making it harder to carry on coherent threads or check past discussions for conclusions. c) encourages patches to sit in queues until a committer can review it rather than have non-committers eyeballing it or even applying it locally and using it before it's ready to be committed to HEAD. The only defence I've heard for the existence of -patches is that it avoids large attachments filling people's inboxes. To that end I would suggest replacing it with a script on the mail server to strip out attachments and replace them with a link to some place where they can be downloaded. This could conceivably evolve into some sort of simple patch queue system where committers could view a list of patches and mark them when they get rejected or committed. I'm not suggesting anything like a bug tracking system, just a simple page should suffice. I fear by sending this I may have just volunteered to execute it. But if it's the case that people support my suggestion I would be happy to do so. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] cursors, current_user, and SECURITY DEFINER
While replying to the information_schema for all users thread in pgsql-sql I noticed that a cursor returned from a SECURITY DEFINER function evalutes current_user as the user who executes FETCH, not as the user who defined the function that opened the cursor. Here are the question and my response, which contains an example: http://archives.postgresql.org/pgsql-sql/2006-07/msg00137.php http://archives.postgresql.org/pgsql-sql/2006-07/msg00140.php I can understand that evaluating current_user at FETCH time makes sense from an execution standpoint, but what user should it evaluate to? In one sense current_user is the user who executed FETCH, but since the cursor was opened with the function definer's privileges, one might argue that the cursor's current_user ought to be the function definer. Is the current behavior intentional? If so, what's the rationale? If not, are there good reasons for doing it one way or the other? I haven't considered the implications thoroughly enough to have a position either way. -- Michael Fuhr ---(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] row() is [not] null infelicities
The SQL spec has some detailed discussion of some strange null behaviours. Specifically row(1,null) is null is false but row(1,null) is not null is *also* supposed to be false. Postgres currently gets this wrong. is [not] null is apparently supposed to mean all the fields are (not) null. So in the following the first query is correct but the second is incorrect: pgbench=# select row(1::integer, null::integer) is null; ?column? -- f (1 row) pgbench=# select row(1::integer, null::integer) is not null; ?column? -- t (1 row) -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] row() is [not] null infelicities
Greg Stark [EMAIL PROTECTED] writes: The SQL spec has some detailed discussion of some strange null behaviours. Sorry, forgot the reference. This is section 8.7 null predicate of the SQL/Foundation. Pages 397-398 in this draft. -- greg ---(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] pgsql-patches considered harmful
On Sunday 09 July 2006 20:00, Greg Stark wrote: Pursuant to a conversation this evening I would like to a suggestion: BIRT pgsql-patches should be abolished in favour of something else that accomplishes the bandwidth-reduction aspect without the downsides. Alternatively, people could just use patches for patch submission and keep all discussion on hackers. Joshua D. Drake -- === 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql-patches considered harmful
On Mon, 9 Jul 2006, Greg Stark wrote: Pursuant to a conversation this evening I would like to a suggestion: BIRT pgsql-patches should be abolished in favour of something else that accomplishes the bandwidth-reduction aspect without the downsides. My complaint is that -patches serves to a) siphon off some of the most technical discussion from -hackers to somewhere where fewer hackers read regularly leaving a lower signal-to-noise ratio on -hackers. b) partition the discussions in strange ways making it harder to carry on coherent threads or check past discussions for conclusions. c) encourages patches to sit in queues until a committer can review it rather than have non-committers eyeballing it or even applying it locally and using it before it's ready to be committed to HEAD. The only defence I've heard for the existence of -patches is that it avoids large attachments filling people's inboxes. To that end I would suggest replacing it with a script on the mail server to strip out attachments and replace them with a link to some place where they can be downloaded. This could conceivably evolve into some sort of simple patch queue system where committers could view a list of patches and mark them when they get rejected or committed. I'm not suggesting anything like a bug tracking system, just a simple page should suffice. I fear by sending this I may have just volunteered to execute it. But if it's the case that people support my suggestion I would be happy to do so. I, for one, would be interested in something like that ... somehow, this 'stripping' would have to be done within Majordomo2 itself, or ... Leave pgsql-patches@ as an alias that is the stripper, with the end result forwarded over to the pgsql-hackers@ list? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] row() is [not] null infelicities
Greg Stark [EMAIL PROTECTED] writes: The SQL spec has some detailed discussion of some strange null behaviours. BTW, Teodor Sigaev pointed out today that we are also doing array comparisons (array_eq, array_cmp) wrong. In the recent extension to make arrays support NULL entries, I had made these functions treat NULL as greater than all non-nulls, per btree sort order. But this seems wrong and also counter to spec: if an array comparison finds a NULL before determining its result, it should return NULL, same as a row comparison would do. The problem with this is that it breaks btree indexing of array columns (... and I think btree indexing of rowtypes has a problem too ...). btree wants to have a well-defined ordering of any two non-null values. Ideas? A nearby issue is that the spec seems to want IS [NOT] DISTINCT FROM to drill down into array and row values, ie, comparing arrays with these functions needs to consider null entries as comparable instead of forcing a null result. AFAICS this will require special-casing array and row types in IS [NOT] DISTINCT FROM ... anyone see a better way? regards, tom lane ---(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] pgsql-patches considered harmful
Joshua D. Drake [EMAIL PROTECTED] writes: On Sunday 09 July 2006 20:00, Greg Stark wrote: BIRT pgsql-patches should be abolished in favour of something else that accomplishes the bandwidth-reduction aspect without the downsides. Alternatively, people could just use patches for patch submission and keep all discussion on hackers. If this is chosen as the preferred path, we could get the list bot to add Reply-To: pghackers in pgsql-patches postings to help push discussions there. I'd vote for doing the same in pgsql-committers, which also gets its share of non-null discussion content. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
