Re: [HACKERS] For review: Server instrumentation patch
It could be argued that there should be provision for a limitation on the locations in which COPY can write (and maybe read) files. If COPY is a security hole then we should close it, not use that as precedent to open another hole. Yeah. It's worth pointing out in this connection that server-side COPY is already pretty well crippled if you are running under SELinux, because the security policy constrains what parts of the filesystem the daemon can reach at all. I don't know a lot about SELinux, but wouldn't this give the exact same level of security for the new admin functions in this case? Nonetheless, the patch makes it vastly easier for an attacker to do bad things, and vastly harder for an admin to try to lock down the database adequately. For instance, the question of .so security can be attacked by not installing any .so's that you don't want used; likewise a contrib file-access module can be left off the system if it's considered a hazard. But if the functionality is part of the core database then it's exceedingly difficult for someone who doesn't want it to get rid of it. (I believe that you'd actually have to recompile the server with the dangerous functions removed; just deleting their pg_proc entries doesn't stop someone from recreating those entries.) Let me suggest another nice way for a superuser to do whatever he wants. How about CREATE UNTRUSTED PROCEDURAL LANGUAGE? If you have say pl/perl or pl/tcl on the system, you just create the untrusted version and away you go - because they use the same .so. This lets you not only modify files on the system, but execute arbitrary code on the system. If you want to protect the system from a hacked superuser, you will have to remove the concept of untrusted procedural languages as well. Oh, and probably LOAD; since the superuser can LOAD any .so on the system IIRC - including stuf that a local user may stuff in /tmp/ or wherever. Saying we don't need to lock this down because there are other possible attacks is about like leaving your front door open because you know that a determined burglar could get in by breaking a window. You may or may not want to install steel bars over the windows, but that's no argument for leaving the door open. I would rather equal it with we don't lock the door, because the door 1m to the left of it is open anyway. You don't need to break anything to get in either way. Instead of trying to pick on one feature, how about trying something constructive instead? Let's say we add a GUC like restrict_superuser, that disables COPY to local files, untrusted procedural languages (both creation and using the ones that already exist), the new access functions, the LOAD command etc. Then the admin can chose what to do about superuser access levels - the requirement may dependon SELinux for example. next mail: Given that COPY to/from a file is already allowed only to superusers, I'm not sure how effective a GUC variable will be in constraining what they do with it. We'd have to at least restrict it to SIGHUP, which'd mean you couldn't change it without the ability to write the config file. You are kidding, right? Your original argument here was that restricted to superuser is not security for the new functions, how can restricted to superusers be security for COPY?! //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] For review: Server instrumentation patch
Magnus Hagander wrote: Instead of trying to pick on one feature, how about trying something constructive instead? Let's say we add a GUC like restrict_superuser, that disables COPY to local files, untrusted procedural languages (both creation and using the ones that already exist), the new access functions, the LOAD command etc. Then the admin can chose what to do about superuser access levels - the requirement may dependon SELinux for example. I could go for this. Creating a setting that disallowed creation/calling of plperlu functions would be fairly trivial. I still think, security considerations aside, that an API for config settings would be a much better piece of design than providing file system access functions. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] regression failure on latest CVS
Sorry to follow up my own post but this is weird. I've tested again and more closely. And intervall check is ok when configured with --enable-debug and fails (with the same error) otherwise. It could be a compiler optimizer bug or the way the code is written. Could someone point me to the source file so that I have a look? BTW this is still on UnixWare 714 Regards, On Fri, 22 Jul 2005 ohp@pyrenet.fr wrote: Date: Fri, 22 Jul 2005 11:28:52 +0200 (MET DST) From: ohp@pyrenet.fr Newsgroups: pgsql.hackers Subject: regressin failure on latest CVS Hi, I tried the latest cvs this morning (07/22 11:00 CET) and interval test fails. Here's the regression.diffs. *** ./expected/interval.out Fri Jul 22 10:32:21 2005 --- ./results/interval.outFri Jul 22 11:07:54 2005 *** *** 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! - ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input --- 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! ! @ 4 years 1 mon 9 days 4 hours 18 mins 23 secs (1 row) -- test long interval input == Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Couple of minor buildfarm issues
Tom Lane wrote: One pretty silly point: I notice that http://www.pgbuildfarm.org/index.html says The build farm software does not currently run on Windows. This is out of date no? Fixed, thanks. One not so trivial question: do we have a policy about system/compiler updates on buildfarm members? Arguably, if I'm running say OS X 10.2 and I update to 10.4, it's no longer the same machine and we should retire the buildfarm ID (the animal) and issue a new one to denote that the results aren't necessarily comparable. (I choose the OS X update as an example with malice aforethought, since in fact our older branches do not work on 10.4.) Ditto for, say, a major gcc update. At the very least it seems we need some longitudinal tracking of OS and compiler and other software versions (eg Python version is relevant to plpython). As best I can see from here, the identification of a buildfarm member's software versions is static, and that isn't very realistic unless we enforce it to be so. These thoughts do not come upon me as a bolt from the blue. ;-) The personality of a machine is defined by the tuple of: OS, OSVersion, Compiler, CompilerVersion, Architecture except that for Linux we say that the OS is the Distribution, not the kernel (or glibc) version, because when we started somebody (I forget who) said that the kernel versionwas probably the least interesting datum. OTOH, in some cases the word distribution has been stretched more than somewhat - Gentoo is really more a build your own distro system than a distribution in itself. Now, we have several problems with the setup. First, the fields to describe all these are text fields both on the registration form and in the database, and there are no edit restrictions or check/FK constraints. So we have something of a mess, which I need to get around to cleaning up. Secondly, as you observe, machines change. I have resisted suggestions to allow arbitrary dynamic updates of personality, precisely so that we can track history in a sensible fashion. My current plan is to provide generational personalities only for cases of OSVersion or CompilerVersion update. Build results will be tied to a particular generation, and this will be reflected on the history page. Any other change would require retirement/reregistration. Right now, we don't have that, and any change in these items should result in member retirement/reregistration. We don't consider configuration settings ( e.g. --enable-integer-datetimes or --with-perl) to be part of the personality, and we don't currently track changes in them, nor in versions of third party libraries we might use ( e.g. openssl or libz). There is a limit to the lengths to which we can reasonably go, and I feel we are probably not too far from the sweet spot. Enforcing these details is probably going to be hard. This is mainly a trust system. On another note, I am close to having provision for the complete log upload you requested - my test box has it working and loading into the db - next I will work on providing web access to it. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] For review: Server instrumentation patch
Magnus Hagander [EMAIL PROTECTED] writes: Yeah. It's worth pointing out in this connection that server-side COPY is already pretty well crippled if you are running under SELinux, because the security policy constrains what parts of the filesystem the daemon can reach at all. I don't know a lot about SELinux, but wouldn't this give the exact same level of security for the new admin functions in this case? It would prevent them from reaching unwanted parts of the filesystem, yes, but that has little to do with the privilege-escalation problem. I see I had better spell out the reasoning here. Assume that a bad guy has gotten hold of your database superuser password and is now trying to parlay that into shell-level access to the underlying system (which is to say, the ability to execute shell commands of his choice; whether he ever acquires a login password is secondary). If you've installed an utrusted PL then the game is over immediately, so let's assume you didn't. One way that the attacker might proceed is to try to make a .so file that he can LOAD into the backend containing the equivalent of a system() function. I believe this is not feasible using COPY in its current form, mainly because you can't write arbitrary binary files with it (no embedded zeroes for instance). With a function to write arbitrary file contents, one very large stumbling block goes away. There's still a problem of getting the file to have the right executable permission bits, but that might be surmounted in various ways, for instance by finding an existing program or .so file that the backend has permission to overwrite. (Somebody argued yesterday that the attacker could always build such a file by executing a shell script, but that misses the point: we are considering how the attacker can get to the point of being able to issue shell commands, not what he can do once he's got that.) So it seems clear to me that adding a file-write function adds a very substantial amount of risk from a privilege-escalation point of view. Yes, it's only one link in a chain, but it's a big link. Let me suggest another nice way for a superuser to do whatever he wants. How about CREATE UNTRUSTED PROCEDURAL LANGUAGE? If you have say pl/perl or pl/tcl on the system, you just create the untrusted version and away you go - because they use the same .so. Yeah, I was thinking earlier about proposing that the trusted and untrusted versions need to be distinct .so's, so that the admin can physically remove the untrusted ones to prevent this scenario. But, again, the existence of security hole A is not justification for introducing security hole B. Instead of trying to pick on one feature, how about trying something constructive instead? That'd be fine with me --- but we have to introduce that *before* we add obvious new security risks, not after. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] regression failure on latest CVS
On Jul 25 2005, ohp@pyrenet.fr wrote: Sorry to follow up my own post but this is weird. I've tested again and more closely. And intervall check is ok when configured with --enable-debug and fails (with the same error) otherwise. It could be a compiler optimizer bug or the way the code is written. Could someone point me to the source file so that I have a look? Look at 'firefly' on the pgbuildfarm, and tell me what I need to change to duplicate your setup. LER BTW this is still on UnixWare 714 Regards, On Fri, 22 Jul 2005 ohp@pyrenet.fr wrote: Date: Fri, 22 Jul 2005 11:28:52 +0200 (MET DST) From: ohp@pyrenet.fr Newsgroups: pgsql.hackers Subject: regressin failure on latest CVS Hi, I tried the latest cvs this morning (07/22 11:00 CET) and interval test fails. Here's the regression.diffs. *** ./expected/interval.outFri Jul 22 10:32:21 2005 --- ./results/interval.out Fri Jul 22 11:07:54 2005 *** *** 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! - ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input --- 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! ! @ 4 years 1 mon 9 days 4 hours 18 mins 23 secs (1 row) -- test long interval input == Regards -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] For review: Server instrumentation patch
snip good explanation. Thanks. Let me suggest another nice way for a superuser to do whatever he wants. How about CREATE UNTRUSTED PROCEDURAL LANGUAGE? If you have say pl/perl or pl/tcl on the system, you just create the untrusted version and away you go - because they use the same .so. Yeah, I was thinking earlier about proposing that the trusted and untrusted versions need to be distinct .so's, so that the admin can physically remove the untrusted ones to prevent this scenario. But, again, the existence of security hole A is not justification for introducing security hole B. Instead of trying to pick on one feature, how about trying something constructive instead? That'd be fine with me --- but we have to introduce that *before* we add obvious new security risks, not after. So what do you think of the proposed GUC? Or what about a parameter to restrict both COPY and the utility functions to certain subdirs only? (BTW, I was under the impression that the admin functions were restricted to the pgdata directory already, but I could be wrong - I don't have the latest version of the patch around) //Magnus ---(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] For review: Server instrumentation patch
Magnus Hagander [EMAIL PROTECTED] writes: That'd be fine with me --- but we have to introduce that *before* we add obvious new security risks, not after. So what do you think of the proposed GUC? Well, it has more or less the same problem as the GUC in the COPY-only-to-given-places proposal, which is that GUCs were never intended to prevent superusers from changing their values. Right now a remote superuser can't change a postmaster-start-time-only GUC, but if we ever introduce a real remote admin facility I'd expect it to support that, so it seems like a GUC intended not to be changeable by superusers would have to be its own special category. I'd be inclined not to expose it as a GUC at all, but make it some other mechanism (maybe a postmaster command-line switch only?) Or what about a parameter to restrict both COPY and the utility functions to certain subdirs only? (BTW, I was under the impression that the admin functions were restricted to the pgdata directory already, but I could be wrong - I don't have the latest version of the patch around) We've gone back and forth on that with respect to the proposed admin functions, and I forget which way the current patch is. But it doesn't do much to stop the privilege escalation risk: if you can write into any of the same directories you can LOAD from, the risk exists. (And detecting whether two paths overlap is very hard in general, considering directory symlinks, AFS mounts, etc, so we probably couldn't hope to forbid LOADing from any writable directory.) regards, tom lane ---(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] For review: Server instrumentation patch
* Tom Lane ([EMAIL PROTECTED]) wrote:
didn't. One way that the attacker might proceed is to try to make a .so
file that he can LOAD into the backend containing the equivalent of a
system() function. I believe this is not feasible using COPY in its
current form, mainly because you can't write arbitrary binary files with
it (no embedded zeroes for instance). With a function to write
Now, I'm not the best hacker in the world, so I didn't actually get this
all the way to working (wish I had more time to play with it but I don't
really), but:
test=# create function unlink (text) RETURNS integer LANGUAGE 'C' AS
'/lib/libc-2.3.2.so', 'unlink';
CREATE FUNCTION
test=# select unlink('/tmp/test');
unlink
-1
I had created /tmp/test, but it appears the 'oldstyle' function calls
pass in the arguments with some garbage on the front (about 4 bytes it
looked like from gdb). Figure out how to skip those 4 bytes per
argument and you hardly need any other .so, you've got libc. I suspect
it can be done. The newstyle API looks like it'd probably make it a bit
more difficult but still, being able to load any function from any .so
you've got access to seems *extremely* powerful to me, just as much as
any untrusted language.
If you want to secure your system against a superuser()-level intrusion
then you need to secure the unix account, or disable creation of
C-language and other untrusted languages (at least).
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] For review: Server instrumentation patch
Andrew Dunstan wrote: It also just strikes me as just the wrong way to go about solving the apparent problem. If we want to make remote configuration or other operations possible, then instead of granting access to server resident files we should invent and implement an API that provides superusers the appropriate operations. For one thing, this would mean that if we ever decided to replace the current flat file system we use with something else we need not break clients that use the API. Just granting file access even if restricted to the data dir strikes me as a kludge. I thought an API for postgresql.conf is what we agreed to, but I don't see it on the TODO list. Is that correct? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] For review: Server instrumentation patch
That'd be fine with me --- but we have to introduce that *before* we add obvious new security risks, not after. So what do you think of the proposed GUC? Well, it has more or less the same problem as the GUC in the COPY-only-to-given-places proposal, which is that GUCs were never intended to prevent superusers from changing their values. Right now a remote superuser can't change a postmaster-start-time-only GUC, but if we ever introduce a real remote admin facility I'd expect it to support that, so it seems like a GUC intended not to be changeable by superusers would have to be its own special category. I'd be inclined not to expose it as a GUC at all, but make it some other mechanism (maybe a postmaster command-line switch only?) If you make it a postmaster-start-only, and it restricts the remote-admin-functionality, then you will not be able to change it remotely. Making it a GUC makes it a whole lot easier to deal with for the admin - especially in cases like win32 when it's not as easy to edit the startup parameters for service based processes. Or what about a parameter to restrict both COPY and the utility functions to certain subdirs only? (BTW, I was under the impression that the admin functions were restricted to the pgdata directory already, but I could be wrong - I don't have the latest version of the patch around) We've gone back and forth on that with respect to the proposed admin functions, and I forget which way the current patch is. But it doesn't do much to stop the privilege escalation risk: if you can write into any of the same directories you can LOAD from, the risk exists. (And detecting whether two paths overlap is very hard in general, considering directory symlinks, AFS mounts, etc, so we probably couldn't hope to forbid LOADing from any writable directory.) Right. That's gotta be at least as bad as dealing with URLs, and we've seen how many bugs there have been in those in pretty much all webservers... //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] For review: Server instrumentation patch
Andrew Dunstan [EMAIL PROTECTED] writes: I still think, security considerations aside, that an API for config settings would be a much better piece of design than providing file system access functions. I agree with that. Given what we currently have, though, remote config and remote log examination do require filesystem access. But IMHO there's no very good reason why admin actions requiring filesystem access shouldn't be programmed in an untrusted PL, rather than through separate file-access functions. Andreas argued that he didn't want to make pgAdmin functionality dependent on the availability of an untrusted PL, but I think that argument is bogus. If the admin doesn't want to install an untrusted PL for pgAdmin to use, why in the world would he be happy with equivalent functionality being installed in such a way that he can't get rid of it? regards, tom lane ---(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] For review: Server instrumentation patch
I still think, security considerations aside, that an API for config settings would be a much better piece of design than providing file system access functions. I agree with that. For the record, me too. But I don't see that happening for 8.1, considering the feature freeze and timescale... Given what we currently have, though, remote config and remote log examination do require filesystem access. But IMHO there's no very good reason why admin actions requiring filesystem access shouldn't be programmed in an untrusted PL, rather than through separate file-access functions. Andreas argued that he didn't want to make pgAdmin functionality dependent on the availability of an untrusted PL, but I think that argument is bogus. If the admin doesn't want to install an untrusted PL for pgAdmin to use, why in the world would he be happy with equivalent functionality being installed in such a way that he can't get rid of it? Not trying to speak for Andreas here, I see the problem as an added dependency *outside* postgresql. If he were to use pl/perl, he couldn o longer admin a postgresql server without perl on it (and perl installed as a shared lib). Same for python and tcl - which I beleive rounds up all the PLs. Plus the admin will have to have included it in ./configure and run createlang with it. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] For review: Server instrumentation patch
Stephen Frost [EMAIL PROTECTED] writes: If you want to secure your system against a superuser()-level intrusion then you need to secure the unix account, or disable creation of C-language and other untrusted languages (at least). Very likely --- which is why Magnus' idea of an explicit switch to prevent superuser filesystem access seems attractive to me. It'd have to turn off LOAD and creation of new C functions as well as COPY and the other stuff we discussed. However, once again, the availability of security hole A does not justify creating security hole B. For example, even with creation of new C functions disabled, a superuser attacker might be able to use a file-write function to overwrite an existing .so and thereby subvert an existing C-function definition to do something bad. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regression failure on stats test
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: FYI, I am seeing the same stats regression failures in CVS, even after the recent commits to improve sleep(). As near as I can tell, this is fixed by pgstat.c rev 1.101. I'm not clear why --- the patch certainly zeroes some table fields that were going uninitialized before, but I do not have a clear picture of how that resulted in the observed symptoms. But I haven't seen the failure happen during continuous regression testing today, whereas I was able to make it happen several times before I applied that patch. Has anyone else seen this failure since updating to latest pgstat.c? My regression tests are fine now. Sorry for the incorrect failure report. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] For review: Server instrumentation patch
Bruce Momjian pgman@candle.pha.pa.us writes: Andrew Dunstan wrote: It also just strikes me as just the wrong way to go about solving the apparent problem. I thought an API for postgresql.conf is what we agreed to, but I don't see it on the TODO list. Is that correct? Like you, I seem to recall some prior discussion along this line, but I think it didn't get solid enough to produce a TODO item. Right now the best we could do is * Better support for remote database administration, eg, APIs to change configuration and restart the postmaster which is mighty vague. 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] regression failure on stats test
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: FYI, I am seeing the same stats regression failures in CVS, even after the recent commits to improve sleep(). Has anyone else seen this failure since updating to latest pgstat.c? My regression tests are fine now. Sorry for the incorrect failure report. It was perfectly correct at the time ... and I'm still not clear on how the uninitialized table entries would result in this symptom, let alone why we'd see identical behaviors on several different platforms. No time to look into it closer though ... got a big stack of patches to get reviewed ... regards, tom lane ---(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] For review: Server instrumentation patch
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Andrew Dunstan wrote: It also just strikes me as just the wrong way to go about solving the apparent problem. I thought an API for postgresql.conf is what we agreed to, but I don't see it on the TODO list. Is that correct? Like you, I seem to recall some prior discussion along this line, but I think it didn't get solid enough to produce a TODO item. Right now the best we could do is * Better support for remote database administration, eg, APIs to change configuration and restart the postmaster which is mighty vague. Added to TODO: o Allow postgresql.conf file values to be changed via an SQL API o Allow the server to be stopped/restarted via an SQL API -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated
[ getting back to this thread... ] Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: I think a better answer is to have a rolinherit flag in pg_authid, which people can set off for spec compatibility or on for backwards compatibility to the GROUP feature. In either setting, the permissions given to a particular authid are clear from pg_authid and don't vary depending on magic SET variables. This is nonstandard and not done in practice. Authorization changes being allowed by 'SET ROLE' is what the spec calls for. Not supporting that ability would be unfortunate and it seems there'd be no point to having 'SET ROLE' at all. I think maybe you misunderstood what I was suggesting. The function of the flag as I imagine it is: * rolinherit = false: role does not automatically have the privileges of roles it is a member of. It must do SET ROLE to gain the privileges of a role it is a member of. (This emulates the spec behavior for users.) * rolinherit = true: role has the privileges of all roles it is a member of, without needing to do SET ROLE. (This handles the spec behavior for roles, and is also needed for users when backwards compatibility with our old behavior for groups is wanted, and also provides an approximate equivalent to Oracle's SET ROLE ALL.) If users have rolinherit = false and roles have rolinherit = true, everything behaves per spec, except that I don't want to support the aspect of the spec that says you can SET ROLE at the outer level and still have the privileges of the SESSION_USER. I think SET ROLE should effectively drop the SESSION_USER's privileges (except that subsequent SET ROLE commands will be checked against the SESSION_USER's role memberships, not the current effective role). If both users and roles have rolinherit = true, we have a good emulation of the old group-based behavior. For backwards compatibility we probably have to have CREATE USER defaulting to rolinherit = true. Is it sufficient to say if you want the spec-compatible behavior you always have to say CREATE USER ... NOINHERIT? Since the spec doesn't actually define a CREATE USER command, this is not a spec violation in a technical sense. But people who are migrating towards using SET ROLE might wish it defaulted to NOINHERIT. We could (either now or in a future release) add a GUC variable to control the default, I suppose. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated
* Tom Lane ([EMAIL PROTECTED]) wrote: [ getting back to this thread... ] Happy to, was getting worried you'd forgotten or ignored it. ;) * rolinherit = false: role does not automatically have the privileges of roles it is a member of. It must do SET ROLE to gain the privileges of a role it is a member of. (This emulates the spec behavior for users.) * rolinherit = true: role has the privileges of all roles it is a member of, without needing to do SET ROLE. (This handles the spec behavior for roles, and is also needed for users when backwards compatibility with our old behavior for groups is wanted, and also provides an approximate equivalent to Oracle's SET ROLE ALL.) If users have rolinherit = false and roles have rolinherit = true, everything behaves per spec, except that I don't want to support the aspect of the spec that says you can SET ROLE at the outer level and still have the privileges of the SESSION_USER. I think SET ROLE should effectively drop the SESSION_USER's privileges (except that subsequent SET ROLE commands will be checked against the SESSION_USER's role memberships, not the current effective role). I don't particularly like deviating from the spec in this regard (since I don't think it'd be all that hard to implement what the spec calls for), but it doesn't bother me that much. If both users and roles have rolinherit = true, we have a good emulation of the old group-based behavior. For backwards compatibility we probably have to have CREATE USER defaulting to rolinherit = true. While I agree that this is what Oracle's SET ROLE ALL does initially, it's possible for a user to 'SET ROLE a' and drop the permissions given by the other roles in which the user is in. Will that still be possible with your proposed solution, or will doing 'SET ROLE a' have no effect when 'rolinherit = true'? That's really my main concern. For my systems I expect to want to do 'rolinherit = true' generally but I really don't like the idea that 'SET ROLE a' has no effect then. Thinking about it a bit more I suppose I could live with it since it's per-role and I tend to set up unprivileged accounts, which is where I'd really be more concerned about 'SET ROLE a' working. We should probably issue a warning or something if my hypothosis on 'SET ROLE' above is correct in the 'rolinherit = true' case so that people don't get the wrong idea that they've dropped privileges in cases when they actually havn't. Is it sufficient to say if you want the spec-compatible behavior you always have to say CREATE USER ... NOINHERIT? Since the spec doesn't actually define a CREATE USER command, this is not a spec violation in a technical sense. But people who are migrating towards using SET ROLE might wish it defaulted to NOINHERIT. We could (either now or in a future release) add a GUC variable to control the default, I suppose. Being able to control the default would be nice but I don't believe it would be a requirement. I would actually like to have a variable to control if SESSION_USER privileges are kept across a SET ROLE or not, though primairly to conform to the spec than expectation that I'd personally use it much. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: everything behaves per spec, except that I don't want to support the aspect of the spec that says you can SET ROLE at the outer level and still have the privileges of the SESSION_USER. I think SET ROLE should effectively drop the SESSION_USER's privileges (except that subsequent SET ROLE commands will be checked against the SESSION_USER's role memberships, not the current effective role). I don't particularly like deviating from the spec in this regard (since I don't think it'd be all that hard to implement what the spec calls for), but it doesn't bother me that much. The problem I have with the spec's way is that it creates a disconnect between the privilege environment seen at the outer level and the environment seen within SECURITY DEFINER functions --- unless you want to allow SET ROLE to have the union behavior within SECURITY DEFINER functions too, which I don't want to support (and it's not legal per spec anyway to do SET ROLE inside a function). While I agree that this is what Oracle's SET ROLE ALL does initially, it's possible for a user to 'SET ROLE a' and drop the permissions given by the other roles in which the user is in. Will that still be possible with your proposed solution, or will doing 'SET ROLE a' have no effect when 'rolinherit = true'? That's really my main concern. According to my proposal SET ROLE x would drop the user's privileges and thus be a privilege restriction operation, never a privilege addition operation, if the user has rolinherit = true. If we don't say that SET ROLE drops the session user's privileges then indeed SET ROLE would be a no-op when the session user has rolinherit = true... 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] [PATCHES] Roles - SET ROLE Updated
* Tom Lane ([EMAIL PROTECTED]) wrote: The problem I have with the spec's way is that it creates a disconnect between the privilege environment seen at the outer level and the environment seen within SECURITY DEFINER functions --- unless you want to allow SET ROLE to have the union behavior within SECURITY DEFINER functions too, which I don't want to support (and it's not legal per spec anyway to do SET ROLE inside a function). Essentially the union behavior is what the spec seems to say- except that only one or the other is valid inside a SECURITY DEFINER, as I understand it. So, you make everything do the union, but when you go into a SECURITY DEFINER function you set the one-not-set to NULL and handle that correctly in the union. I'm not advocating allowing SET ROLE inside a function, no. Again, this is more about the spec than an actual use-case that I have for it, so we can ignore it until someone with a more concrete problem with it comes along. While I agree that this is what Oracle's SET ROLE ALL does initially, it's possible for a user to 'SET ROLE a' and drop the permissions given by the other roles in which the user is in. Will that still be possible with your proposed solution, or will doing 'SET ROLE a' have no effect when 'rolinherit = true'? That's really my main concern. According to my proposal SET ROLE x would drop the user's privileges and thus be a privilege restriction operation, never a privilege addition operation, if the user has rolinherit = true. If we don't say that SET ROLE drops the session user's privileges then indeed SET ROLE would be a no-op when the session user has rolinherit = true... Right, I would expect it to drop privileges when rolinherit = true. The second issue is one reason I don't particularly care for locking it into the catalog- it means we're building the system in such a way as to be unable to support what Oracle (at least) does today. If we end up needing to support it later, or wanting to, perhaps because the spec follows Oracle's lead and adds SET ROLE ALL, then we've got alot that would need to be changed because things have become dependent on the catalog directly. Otherwise, I think your proposal is fine. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] For review: Server instrumentation patch
Andrew Dunstan wrote: It could be argued that there should be provision for a limitation on the locations in which COPY can write (and maybe read) files. Please note that the genfile functions are already restricted. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: The problem I have with the spec's way is that it creates a disconnect between the privilege environment seen at the outer level and the environment seen within SECURITY DEFINER functions Essentially the union behavior is what the spec seems to say- except that only one or the other is valid inside a SECURITY DEFINER, as I understand it. So, you make everything do the union, but when you go into a SECURITY DEFINER function you set the one-not-set to NULL and handle that correctly in the union. My understanding of things is that per spec, a SECURITY DEFINER function can be owned by either a user or a role, and so within the function either CURRENT_USER or CURRENT_ROLE would return the owner and the other would return NULL. Emulating this would require a hard distinction between users and roles that is simply not there in our implementation, which is why I think they should both return the owner. Right, I would expect it to drop privileges when rolinherit = true. The second issue is one reason I don't particularly care for locking it into the catalog- it means we're building the system in such a way as to be unable to support what Oracle (at least) does today. If we end up needing to support it later, or wanting to, perhaps because the spec follows Oracle's lead and adds SET ROLE ALL, then we've got alot that would need to be changed because things have become dependent on the catalog directly. To some extent SET ROLE ALL can be emulated by ALTER USER ... INHERIT. I'm of two minds about whether an unprivileged user should be allowed to adjust his own rolinherit flag --- in most cases it seems pretty harmless (and Oracle evidently thinks it is) --- but one could imagine that the roles have been set up on the assumption that you can't get more than one role's privileges at a time. INHERIT (or SET ROLE ALL) would break that assumption, and perhaps allow people to do unwanted stuff. 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] regression failure on latest CVS
Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure This is the problem I have. Regards On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 09:00:41 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Sorry to follow up my own post but this is weird. I've tested again and more closely. And intervall check is ok when configured with --enable-debug and fails (with the same error) otherwise. It could be a compiler optimizer bug or the way the code is written. Could someone point me to the source file so that I have a look? Look at 'firefly' on the pgbuildfarm, and tell me what I need to change to duplicate your setup. LER BTW this is still on UnixWare 714 Regards, On Fri, 22 Jul 2005 ohp@pyrenet.fr wrote: Date: Fri, 22 Jul 2005 11:28:52 +0200 (MET DST) From: ohp@pyrenet.fr Newsgroups: pgsql.hackers Subject: regressin failure on latest CVS Hi, I tried the latest cvs this morning (07/22 11:00 CET) and interval test fails. Here's the regression.diffs. *** ./expected/interval.out Fri Jul 22 10:32:21 2005 --- ./results/interval.outFri Jul 22 11:07:54 2005 *** *** 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! - ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input --- 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! ! @ 4 years 1 mon 9 days 4 hours 18 mins 23 secs (1 row) -- test long interval input == Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] For review: Server instrumentation patch
Andreas Pflug wrote: Andrew Dunstan wrote: It could be argued that there should be provision for a limitation on the locations in which COPY can write (and maybe read) files. Please note that the genfile functions are already restricted. Yes, that's what I thought. The argument is about how safe that is, especially if you can't turn it off, isn't it? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] regression failure on latest CVS
On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure Do we have a clue as to which .c module the compiler/optimizer is (possibly) screwing up? I have connections in SCO's compiler group (They'll want a small test case :( ) LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Roles - SET ROLE Updated
* Tom Lane ([EMAIL PROTECTED]) wrote: My understanding of things is that per spec, a SECURITY DEFINER function can be owned by either a user or a role, and so within the function either CURRENT_USER or CURRENT_ROLE would return the owner and the other would return NULL. Emulating this would require a hard distinction between users and roles that is simply not there in our implementation, which is why I think they should both return the owner. I would have been more inclined to just pick one and always set it and leave the other always null. For that, CURRENT_USER would be more backwards-compatible, but for our implementation I'd tend to think CURRENT_ROLE is more appropriate. That'd follow the spec closer and would be closer to what functions written to the spec would expect. I don't use SECURITY DEFINER functions much though so perhaps others have a stronger opinion. I've been a bit suprised at the lack of commentary from other people, perhaps they're just waiting to destroy whatever we come up with once it's actually been implemented. :) To some extent SET ROLE ALL can be emulated by ALTER USER ... INHERIT. Yeah, but that affects all sessions too, not just a single one, which makes it quite a different thing. I'm of two minds about whether an unprivileged user should be allowed to adjust his own rolinherit flag --- in most cases it seems pretty harmless (and Oracle evidently thinks it is) --- but one could imagine that the roles have been set up on the assumption that you can't get more than one role's privileges at a time. INHERIT (or SET ROLE ALL) would break that assumption, and perhaps allow people to do unwanted stuff. This is actually what I was thinking about when I was saying at some point prior in this thread that we should have an option to indicate if SET ROLE ALL is allowed or not. I don't think that users should be allowed to adjust their own rolinherit flag. I think the default should probably be 'true', even for users, but if an admin sets it to false then I think that should be enforced and users shouldn't be allowed to change it. I suspect it's possible to disable 'SET ROLE ALL' in Oracle, and to turn off having it done upon connection. I'd be somewhat suprised if it wasn't possible but I havn't really investigated it either way. I don't know if Oracle has a way to let you do it per-user/per-role though. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Fri, 22 Jul 2005 19:11:36 -0400 Tom Lane [EMAIL PROTECTED] wrote: BTW, I'd like to look at 302906, but its [Details] link is broken. Ugh, I tried digging onto the internal systems and it looks like they were destroyed (or not saved) somehow. It'll have to be rerun. Sorry... Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] regression failure on latest CVS
On Jul 25 2005, ohp@pyrenet.fr wrote: On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure Do we have a clue as to which .c module the compiler/optimizer is (possibly) screwing up? According to Bruce, it's in timestamp.c Did you get the same problem? Haven't tried (I can't get to my box from here, easily), but I did give a heads up to my contacts at SCO. Will try tonight. LER I have connections in SCO's compiler group (They'll want a small test case :( ) LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(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] regression failure on latest CVS
On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure Do we have a clue as to which .c module the compiler/optimizer is (possibly) screwing up? According to Bruce, it's in timestamp.c Did you get the same problem? I have connections in SCO's compiler group (They'll want a small test case :( ) LER -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(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] For review: Server instrumentation patch
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 25 July 2005 15:18 To: Magnus Hagander Cc: Andrew Dunstan; Andreas Pflug; Bruce Momjian; Dave Page; PostgreSQL-development Subject: Re: [HACKERS] For review: Server instrumentation patch Or what about a parameter to restrict both COPY and the utility functions to certain subdirs only? (BTW, I was under the impression that the admin functions were restricted to the pgdata directory already, but I could be wrong - I don't have the latest version of the patch around) It does. Prior to feature freeze, that was the *only* concern raised with the patch, despite significant discssion. We've gone back and forth on that with respect to the proposed admin functions, and I forget which way the current patch is. But it doesn't do much to stop the privilege escalation risk: if you can write into any of the same directories you can LOAD from, the risk exists. (And detecting whether two paths overlap is very hard in general, considering directory symlinks, AFS mounts, etc, so we probably couldn't hope to forbid LOADing from any writable directory.) I'm not going to repeat all the other arguments here because they've been put forward perfectly well by others, but I feel I must point out my dismay at what seems like a complete disregard for non-core applications that has been expressed to me off-list by a number of people. This patch has been discussed on and off since before 8.0 was released, and some time prior to feature freeze I took over the task of trying to get it accepted from Andreas so he could continue with other work. The patch was discussed in great depth again (prior to feature freeze) and none of these concerns were raised. Had they been, we might have worked to find an alternative solution to the problem to allow PostgreSQL to boast simple features offered by every other modern DBMS I've used. Instead, because we are so far past feature freeze, there is no chance that an altenative solution will be accepted. The common belief in the messages I've received seems to be that users that prefer to use a GUI are simply not welcome. True or not (and I do hope that is not the case), it's a great shame that it seems that PostgreSQL will remain configurable only from the command line - as one well respected member of the community wrote to me: I hope no other open source DBMS guys are following this thead. They must be ROFL seeing how Core is trying to prevent remote administrability. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] ORDER BY field not in return list
Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More buildfarm stuff
On Sun, Jul 24, 2005 at 06:01:46PM -0600, Michael Fuhr wrote: On Sun, Jul 24, 2005 at 06:40:35PM -0400, Tom Lane wrote: I think someone mentioned this already, but it'd be a good idea to compare the python situation to plperl. On my Linux box, libperl.so shows several references to pthread_xxx symbols ... not the same ones libpython.so depends on, but pthread symbols none the less. I'd kind of expect them both to fail if the problem is that the stock libc doesn't include pthreads. I have a FreeBSD 4.11-STABLE box with Perl 5.8.7 built from the ports collection, and nm libperl.so shows no pthread functions. The port's Makefile has a WITH_THREADS option that I don't think is enabled by default. The Python port's Makefile, however, has a WITHOUT_THREADS option, so I think it *does* build a threaded Python by default. FWIW, AFAICT I did build the port with default options. Though, nm shows no symbols for my libpython(s)... [EMAIL PROTECTED]:38]~:47nm `locate libpython|grep .so` /usr/local/lib/compat/pkg/libpython2.3.so.1: /usr/libexec/elf/nm: /usr/local/lib/compat/pkg/libpython2.3.so.1: no symbols /usr/local/lib/libpython2.4.so: /usr/libexec/elf/nm: /usr/local/lib/libpython2.4.so: no symbols /usr/local/lib/libpython2.4.so.1: /usr/libexec/elf/nm: /usr/local/lib/libpython2.4.so.1: no symbols /usr/local/lib/python2.4/config/libpython2.4.so: /usr/libexec/elf/nm: /usr/local/lib/python2.4/config/libpython2.4.so: no symbols [EMAIL PROTECTED]:39]~:48 But I do have a /usr/local/lib/python2.4/config/libpython2.4.a that has a bunch of symbols, though I'm not sure if that means anything or what I'm looking for in the nm output. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] More buildfarm stuff
Jim C. Nasby wrote: On Sun, Jul 24, 2005 at 07:38:46PM -0400, Larry Rosenman wrote: Andrew Dunstan wrote: Larry, please try building and testing (especially PL installcheck) on that box using as close as possible to the same config setup as octopus: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=octopusdt=2005-07-2 4%2008 05:01 thanks andrew It appears that the box is down at the moment. As soon as I can get someone back in Dallas (I'm in Charlotte) To get it back up I will. One question: was the python install on octopus from ports? Yes: [EMAIL PROTECTED]:33]~:39pkg_info|grep -i python charm-1.6.0 A menu-driven python-based livejournal client py24-psyco-1.4 Python Specializing Compiler py24-qt-3.14.1 Python bindings for the Qt toolkit py24-sip-4.2.1 Python to C and C++ bindings generator python-2.4.1_1 An interpreted object-oriented programming language [EMAIL PROTECTED]:34]~:40 I can give you an account if it would help. Feel free to drop my an email if you have any questions or if I can help in some way. Can you try rebuilding python and it's dependencies WITHOUT_THREADS? I think that would get us where we need? My box is being recalcitrint(sp?). -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regression failure on latest CVS
Would it be useful to hackers if build animals periodically ran builds with those options removed? On Mon, Jul 25, 2005 at 07:19:05PM +0200, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure This is the problem I have. Regards On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 09:00:41 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Sorry to follow up my own post but this is weird. I've tested again and more closely. And intervall check is ok when configured with --enable-debug and fails (with the same error) otherwise. It could be a compiler optimizer bug or the way the code is written. Could someone point me to the source file so that I have a look? Look at 'firefly' on the pgbuildfarm, and tell me what I need to change to duplicate your setup. LER BTW this is still on UnixWare 714 Regards, On Fri, 22 Jul 2005 ohp@pyrenet.fr wrote: Date: Fri, 22 Jul 2005 11:28:52 +0200 (MET DST) From: ohp@pyrenet.fr Newsgroups: pgsql.hackers Subject: regressin failure on latest CVS Hi, I tried the latest cvs this morning (07/22 11:00 CET) and interval test fails. Here's the regression.diffs. *** ./expected/interval.out Fri Jul 22 10:32:21 2005 --- ./results/interval.out Fri Jul 22 11:07:54 2005 *** *** 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! - ! @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs (1 row) -- test long interval input --- 217,224 -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; avg ! ! @ 4 years 1 mon 9 days 4 hours 18 mins 23 secs (1 row) -- test long interval input == Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Couple of minor buildfarm issues
On Mon, Jul 25, 2005 at 08:49:45AM -0400, Andrew Dunstan wrote: We don't consider configuration settings ( e.g. --enable-integer-datetimes or --with-perl) to be part of the personality, and we don't currently track changes in them, nor in versions of third party libraries we might use ( e.g. openssl or libz). There is a limit to the lengths to which we can reasonably go, and I feel we are probably not too far from the sweet spot. Well, the config options are always sent back in status reports... maybe if there was just a summary page that listed what those options were on a per-report basis; or even maybe diffing between reports to show changes. Something else that I think would be good to send back with each status report is version info for everything relevant. gcc is obvious, I think the uname stuff reported covers all those bases. I think some linux distros have a file in /etc that specifies what distro it is, so including that might be good. Finally, it would be good to include version info for any external dependancies, especially since this could change depending on options specified to configure. I suspect that doing that will involve a change to configure, or maybe adding something to a makefile that just produces a sumary. Or perhaps the info is available in config.log. In any case, having a summary of config options and relevant version info should make it pretty easy to spot changes. Also, if the info is machine-readable it would be easy to do a summary report of what different options have how much coverage, etc. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] More buildfarm stuff
On Sun, Jul 24, 2005 at 07:38:46PM -0400, Larry Rosenman wrote: Andrew Dunstan wrote: Larry, please try building and testing (especially PL installcheck) on that box using as close as possible to the same config setup as octopus: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=octopusdt=2005-07-24%2008 :05:01 thanks andrew It appears that the box is down at the moment. As soon as I can get someone back in Dallas (I'm in Charlotte) To get it back up I will. One question: was the python install on octopus from ports? Yes: [EMAIL PROTECTED]:33]~:39pkg_info|grep -i python charm-1.6.0 A menu-driven python-based livejournal client py24-psyco-1.4 Python Specializing Compiler py24-qt-3.14.1 Python bindings for the Qt toolkit py24-sip-4.2.1 Python to C and C++ bindings generator python-2.4.1_1 An interpreted object-oriented programming language [EMAIL PROTECTED]:34]~:40 I can give you an account if it would help. Feel free to drop my an email if you have any questions or if I can help in some way. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ORDER BY field not in return list
On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote: On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: On Mon, 2005-07-25 at 18:11 -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Seems like it should work. Is it not returning in fullname order in your tests? Full name isn't a field in the results, so how would it be ORDERing based on it? fullname is a field in the table being joined in order to restrict the results to just those with cs.detail = 'test' ... but company itself doesn't have a field fullname ... I'm still not seeing the problem. cs.fullname is in the product of the join, and you can order the result thereby, and not return the column. That's what I was missing ... the product of the join ... I was seeing the end result as being all that an ORDER BY had to work with, vs the JOIN of all the tables ... ---(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] ORDER BY field not in return list
On Mon, 2005-07-25 at 19:08 -0300, Marc G. Fournier wrote: On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: On Mon, 2005-07-25 at 18:11 -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Seems like it should work. Is it not returning in fullname order in your tests? Full name isn't a field in the results, so how would it be ORDERing based on it? fullname is a field in the table being joined in order to restrict the results to just those with cs.detail = 'test' ... but company itself doesn't have a field fullname ... I'm still not seeing the problem. cs.fullname is in the product of the join, and you can order the result thereby, and not return the column. -jwb ---(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] regression failure on latest CVS
ohp@pyrenet.fr wrote: On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. I'm forcing a run with --enable-cassert only, to see what happens (per request from SCO). I've saved off the first failure to send to SCO. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ORDER BY field not in return list
On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... Why would it have no effect? If there is a one to many mapping between fullname and id, and if the rows in c with the same fullname have different rows in c.*, then it does matter. For the casual observer, only seeing the output of the table, they would see a consistent reporting order, but would be unable to derive how the table rows were sorted. :-) mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ORDER BY field not in return list
Am Montag, den 25.07.2005, 18:11 -0300 schrieb Marc G. Fournier: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... It just does the sorting as you requested. Check the order of the resulting c.id. See: experiment=# SELECT * FROM A; a_id | a_value --+- 1 | abc 2 | bcd 3 | def (3 rows) experiment=# SELECT * FROM B; b_id | b_value --+- 1 | xyz 2 | ijk 3 | abc (3 rows) experiment=# SELECT a.* FROM a,b WHERE a.a_id=b.b_id ORDER BY b_value; a_id | a_value --+- 3 | def 2 | bcd 1 | abc (3 rows) So no reason for warnings :-) ---(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] Couple of minor buildfarm issues
Jim C. Nasby wrote: On Mon, Jul 25, 2005 at 08:49:45AM -0400, Andrew Dunstan wrote: We don't consider configuration settings ( e.g. --enable-integer-datetimes or --with-perl) to be part of the personality, and we don't currently track changes in them, nor in versions of third party libraries we might use ( e.g. openssl or libz). There is a limit to the lengths to which we can reasonably go, and I feel we are probably not too far from the sweet spot. Well, the config options are always sent back in status reports... maybe if there was just a summary page that listed what those options were on a per-report basis; or even maybe diffing between reports to show changes. It's listed at the top of every log page. I am not sure where we should put it on other pages - the dashboard page is pretty full now - adding 2 or 3 lines per machine to reflect the config options doesn't sound like a good idea. At one stage I thought of stealing some vertical space for 8 or 10 columns of 10 pixels or so to show the state of the most importand build flag. I still might do that, if I can standardise the OS and Compiler info so that they get shorter (e.g. is just knowing that we have gcc n.m.o enough, or do we need the longer info produced by gcc -v? I'm inclined to reduce it to n.m.o.) Something else that I think would be good to send back with each status report is version info for everything relevant. gcc is obvious, I think the uname stuff reported covers all those bases. I think some linux distros have a file in /etc that specifies what distro it is, so including that might be good. Finally, it would be good to include version info for any external dependancies, especially since this could change depending on options specified to configure. I suspect that doing that will involve a change to configure, or maybe adding something to a makefile that just produces a sumary. Or perhaps the info is available in config.log. In any case, having a summary of config options and relevant version info should make it pretty easy to spot changes. Also, if the info is machine-readable it would be easy to do a summary report of what different options have how much coverage, etc. I have just about finished work on uploading complete logs, and config.log will contain version info on a lot of 3rd party stuff. For a sample, see the stage logs listed at http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=orioledt=2005-07-25%2017:39:02 I do have one plea, which is that people with ideas review the requested features tracker on pgfoundry. I keep this up fairly well, even though some of the items are moderately old. See http://pgfoundry.org/tracker/?atid=241group_id=140func=browse Many of the ideas that have been discussed are already on the list. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Patch to fix plpython on OS X
On Sun, Jul 24, 2005 at 10:55:08AM -0400, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: A problem with this patch is that it assumes a version of Python based on the OS, which might clean up the current buildfarm but that isn't really correct. Is there a better way to handle this? Yes --- just let pg_regress deal with it as if it were a locale problem. I've committed it that way. regards, tom lane FYI, cuckoo went green with this build: http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=cuckoodt=2005-07-25%2008:05:02 -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ORDER BY field not in return list
On Mon, 25 Jul 2005, Jeffrey W. Baker wrote: On Mon, 2005-07-25 at 18:11 -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Seems like it should work. Is it not returning in fullname order in your tests? Full name isn't a field in the results, so how would it be ORDERing based on it? fullname is a field in the table being joined in order to restrict the results to just those with cs.detail = 'test' ... but company itself doesn't have a field fullname ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ORDER BY field not in return list
On Mon, 25 Jul 2005, Jim C. Nasby wrote: On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... Uh, I'd hope it had an effect. Note that RDBMSes have been moving towards allowing fields in ORDER BY that aren't in the SELECT list, though in the past it was common that anything in ORDER BY had to also be in SELECT. 'k, in the test case I've been working with, the query always returns 1 row, so my test case wouldn't have shown a difference ... but, if it does have an affect, how? The ORDER BY is on the final result set, and if there is no cs.fullname in that result, what exactly is it ordering? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ORDER BY field not in return list
On Mon, Jul 25, 2005 at 07:06:46PM -0300, Marc G. Fournier wrote: On Mon, 25 Jul 2005, Jim C. Nasby wrote: On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... Uh, I'd hope it had an effect. Note that RDBMSes have been moving towards allowing fields in ORDER BY that aren't in the SELECT list, though in the past it was common that anything in ORDER BY had to also be in SELECT. 'k, in the test case I've been working with, the query always returns 1 row, so my test case wouldn't have shown a difference ... but, if it does have an affect, how? The ORDER BY is on the final result set, and if there is no cs.fullname in that result, what exactly is it ordering? decibel=# select usename, usesysid from pg_user; usename | usesysid --+-- postgres |1 decibel | 100 (2 rows) decibel=# select usesysid from pg_user order by usename; usesysid -- 100 1 (2 rows) decibel=# explain analyze select usesysid from pg_user order by usename; QUERY PLAN -- Sort (cost=1.02..1.02 rows=1 width=68) (actual time=0.182..0.183 rows=2 loops=1) Sort Key: pg_shadow.usename - Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=68) (actual time=0.060..0.077 rows=2 loops=1) Total runtime: 0.518 ms (4 rows) decibel=# -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More buildfarm stuff
On Mon, Jul 25, 2005 at 04:03:58PM -0600, Michael Fuhr wrote: On Mon, Jul 25, 2005 at 04:40:19PM -0500, Jim C. Nasby wrote: FWIW, AFAICT I did build the port with default options. Though, nm shows no symbols for my libpython(s)... [EMAIL PROTECTED]:38]~:47nm `locate libpython|grep .so` /usr/local/lib/compat/pkg/libpython2.3.so.1: /usr/libexec/elf/nm: /usr/local/lib/compat/pkg/libpython2.3.so.1: no symbols Try nm -D (or --dynamic) or objdump -T (or --dynamic-syms) when looking at a shared object. Here's what I get: % nm -D /usr/local/lib/libpython2.4.so.1 | grep pthread U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self % nm -D /usr/local/lib/perl5/5.8.7/mach/CORE/libperl.so | grep pthread [no output] [EMAIL PROTECTED]:06]~:36nm -D `locate libpython|grep .so`|egrep ':|pthread' /usr/local/lib/compat/pkg/libpython2.3.so.1: U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self U pthread_sigmask /usr/local/lib/libpython2.4.so: U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self /usr/local/lib/libpython2.4.so.1: U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self /usr/local/lib/python2.4/config/libpython2.4.so: U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self [EMAIL PROTECTED]:06]~:37 I'm currently re-building python without threading. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] DROP GROUP leaves permissions a mess ...
Using an 8.0.3 database, if I do:
CREATE GROUP testgrp WITH USER pgsql;
GRANT ALL ON timezone TO GROUP testgrp;
DROP GROUP testgrp;
The table permissions still contain the reference to the 'group':
public | timezone| table| {pgsql=arwdRxt/pgsql,group
100=arwdRxt/pgsql}
And you can't REVOKE those permissions afterwards:
ams=# REVOKE ALL ON timezone FROM GROUP testgrp;
ERROR: group testgrp does not exist
ams=# REVOKE ALL ON timezone FROM GROUP 100;
ERROR: group 100 does not exist
Should there not be an ERROR returned when you try and drop a user/group
that has permissions on a table in the database, to prevent this?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] More buildfarm stuff
On Mon, Jul 25, 2005 at 05:54:45PM -0400, Larry Rosenman wrote: Jim C. Nasby wrote: On Sun, Jul 24, 2005 at 07:38:46PM -0400, Larry Rosenman wrote: Andrew Dunstan wrote: Larry, please try building and testing (especially PL installcheck) on that box using as close as possible to the same config setup as octopus: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=octopusdt=2005-07-2 4%2008 05:01 thanks andrew It appears that the box is down at the moment. As soon as I can get someone back in Dallas (I'm in Charlotte) To get it back up I will. One question: was the python install on octopus from ports? Yes: [EMAIL PROTECTED]:33]~:39pkg_info|grep -i python charm-1.6.0 A menu-driven python-based livejournal client py24-psyco-1.4 Python Specializing Compiler py24-qt-3.14.1 Python bindings for the Qt toolkit py24-sip-4.2.1 Python to C and C++ bindings generator python-2.4.1_1 An interpreted object-oriented programming language [EMAIL PROTECTED]:34]~:40 I can give you an account if it would help. Feel free to drop my an email if you have any questions or if I can help in some way. Can you try rebuilding python and it's dependencies WITHOUT_THREADS? I think that would get us where we need? My box is being recalcitrint(sp?). [EMAIL PROTECTED]:01]~:35dict recalcitrint No definitions found for recalcitrint, perhaps you mean: gcide: Recalcitrant wn: recalcitrant moby-thes: recalcitrant [EMAIL PROTECTED]:01]~:36 :P Sure, I'll try rebuilding. I'm honestly not sure if allowing threads was doing me any good at all. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] More buildfarm stuff
On Mon, Jul 25, 2005 at 04:40:19PM -0500, Jim C. Nasby wrote: FWIW, AFAICT I did build the port with default options. Though, nm shows no symbols for my libpython(s)... [EMAIL PROTECTED]:38]~:47nm `locate libpython|grep .so` /usr/local/lib/compat/pkg/libpython2.3.so.1: /usr/libexec/elf/nm: /usr/local/lib/compat/pkg/libpython2.3.so.1: no symbols Try nm -D (or --dynamic) or objdump -T (or --dynamic-syms) when looking at a shared object. Here's what I get: % nm -D /usr/local/lib/libpython2.4.so.1 | grep pthread U pthread_attr_destroy U pthread_attr_init U pthread_attr_setstacksize U pthread_create U pthread_detach U pthread_self % nm -D /usr/local/lib/perl5/5.8.7/mach/CORE/libperl.so | grep pthread [no output] -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ORDER BY field not in return list
On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... Uh, I'd hope it had an effect. Note that RDBMSes have been moving towards allowing fields in ORDER BY that aren't in the SELECT list, though in the past it was common that anything in ORDER BY had to also be in SELECT. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ORDER BY field not in return list
On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote: Just curious as to whether or not a warning or something should be issued in a case like: SELECT c.* FROM company c, company_summary cs WHERE c.id = cs.id AND cs.detail = 'test' ORDER BY cs.fullname; Unless I'm missing something, the ORDER BY clause has no effect, but an EXPLAIN shows it does take extra time, obviously ... Uh, I'd hope it had an effect. Note that RDBMSes have been moving towards allowing fields in ORDER BY that aren't in the SELECT list, though in the past it was common that anything in ORDER BY had to also be in SELECT. Prior to SQL:1999, the spec required that any column referenced in an ORDER BY clause must also be referenced in the SELECT. SQL:1999 (feature E1210-02) relaxed this to allow columns to be specified in the ORDER BY clause but not in the SELECT. -- Matt Emmerton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DROP GROUP leaves permissions a mess ...
On Mon, Jul 25, 2005 at 07:02:37PM -0300, Marc G. Fournier wrote: Should there not be an ERROR returned when you try and drop a user/group that has permissions on a table in the database, to prevent this? Yes, this is a long-standing known bug and has been fixed in 8.1. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) La Primavera ha venido. Nadie sabe como ha sido (A. Machado) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP GROUP leaves permissions a mess ...
* Marc G. Fournier ([EMAIL PROTECTED]) wrote: Should there not be an ERROR returned when you try and drop a user/group that has permissions on a table in the database, to prevent this? That's exactly what the shared-dependency patch that was recently applied to CVS HEAD is supposed to deal with, I believe... Stephen signature.asc Description: Digital signature
Re: [HACKERS] regression failure on latest CVS
Andrew Dunstan wrote: Larry Rosenman wrote: ohp@pyrenet.fr wrote: On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. I'm forcing a run with --enable-cassert only, to see what happens (per request from SCO). I've saved off the first failure to send to SCO. I assume that in the SCO compiler turning debugging on turns optimisation off? If so, that would at least make some kind of sense (i.e. this would a case of bad optimisation). cheers andrew Yes, -g disables -O. And REL8_0_STABLE passes without --enable-cassert and --enable-debug set. So, off the stuff goes to my contacts @SCO. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] regression failure on latest CVS
Larry Rosenman wrote: ohp@pyrenet.fr wrote: On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. I'm forcing a run with --enable-cassert only, to see what happens (per request from SCO). I've saved off the first failure to send to SCO. I assume that in the SCO compiler turning debugging on turns optimisation off? If so, that would at least make some kind of sense (i.e. this would a case of bad optimisation). cheers andrew ---(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] regression failure on latest CVS
Larry Rosenman wrote: ohp@pyrenet.fr wrote: On Mon, 25 Jul 2005, Larry Rosenman wrote: Date: 25 Jul 2005 12:47:01 -0500 From: Larry Rosenman ler@lerctr.org To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] regression failure on latest CVS On Jul 25 2005, ohp@pyrenet.fr wrote: Hi Larry, I'm quitge sure you'll see a problem if you remove --enable-debug --enable-cassert from your ./configure For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. I'm forcing a run with --enable-cassert only, to see what happens (per request from SCO). I've saved off the first failure to send to SCO. Just --enable-cassert fails as well. SCO also asked if 8.0-STABLE also has issues. Running That test now. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Couple of minor buildfarm issues
On Mon, Jul 25, 2005 at 07:06:33PM -0400, Andrew Dunstan wrote: Well, the config options are always sent back in status reports... maybe if there was just a summary page that listed what those options were on a per-report basis; or even maybe diffing between reports to show changes. It's listed at the top of every log page. I am not sure where we should put it on other pages - the dashboard page is pretty full now - adding 2 or 3 lines per machine to reflect the config options doesn't sound like a good idea. At one stage I thought of stealing some vertical space for 8 or 10 columns of 10 pixels or so to show the state of the most importand build flag. I still might do that, if I can standardise the OS and Compiler info so that they get shorter (e.g. is just knowing that we have gcc n.m.o enough, or do we need the longer info produced by gcc -v? I'm inclined to reduce it to n.m.o.) Oh, I wasn't suggesting on the main status page, just in some way that it's easy to see a) coverage amongst different machines (so this would show the latest info for each machine) and b) what changes have been made on a specific machine in the past. I think those two would cover most cases. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(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] regression failure on latest CVS
Larry Rosenman ler@lerctr.org writes: For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. FWIW, I just checked that CVS tip works OK for me without these options, with either integer or float timestamps. I don't see any new warnings, either. It could well be that the recent changes have introduced some portability problem in the interval code, but someone's going to have to actually dig for it :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Rollback issue with SET ROLE
The new SET ROLE code has a bit of a problem with rollback of SET operations. For example, regression=# create user foo; CREATE ROLE regression=# create role bar; CREATE ROLE regression=# set role bar; SET regression= show role; role -- bar (1 row) regression= begin; BEGIN regression= set session authorization foo; SET regression= show role; role -- none (1 row) regression= rollback; ROLLBACK regression=# show role; role -- none (1 row) Ideally the ROLLBACK should have restored the ROLE setting that obtained prior to BEGIN. The reason it doesn't is that the ROLLBACK effectively does a SET SESSION AUTHORIZATION prior-auth-value, and that naturally clears the ROLE setting. I've been chewing on this problem for a couple hours and have come to the conclusion that it's not going to be possible to fix it without some changes to the GUC infrastructure --- there just isn't support for tracking changes to related but separate GUC variables. Even with a fix for that, there are some related nasty cases. Consider BEGIN; SET LOCAL SESSION AUTHORIZATION foo; SET ROLE bar; COMMIT; The SET ROLE, being nonlocal, should persist past the COMMIT. But the right to do SET ROLE bar would have been checked against foo's role memberships. If the outer-level session user doesn't have membership in foo, this leaves us in an illegal situation. A possible plan B is to forbid doing either SET SESSION AUTHORIZATION or SET ROLE inside a transaction block, so that none of these cases arise. This restriction is sanctioned by the SQL spec. However, we've historically allowed SET SESSION AUTHORIZATION inside a transaction block, and in fact there's a SET LOCAL SESSION AUTHORIZATION in the regression tests. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] regression failure on latest CVS
Tom Lane wrote: Larry Rosenman ler@lerctr.org writes: For those following along at home: Removing --enable-cassert and --enable-debug from the options causes Firefly to fail. FWIW, I just checked that CVS tip works OK for me without these options, with either integer or float timestamps. I don't see any new warnings, either. It could well be that the recent changes have introduced some portability problem in the interval code, but someone's going to have to actually dig for it :-( regards, tom lane Thanks, Tom. I've reported my findings to the compiler Guys at SCO. We'll see what they say tomorrow. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 US ---(end of broadcast)--- TIP 6: explain analyze is your friend
