Re: [HACKERS] Deadlock situation using foreign keys (reproduceable)
I wrote this patch for my system, and it works fine. However, it's a really ugly workaround. I can publish the source if anybody is interested. Am Montag, 26. August 2002 06:33 schrieb Thomas O'Dowd: Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't make it into 7.3. I presume there are other folk out there suffering from the same problems that I'm having. What approaches if any have people taken to work around this problem? I read in the list that one user patched his postmaster to explictly ignore the RI FOR UPDATE on tables he knew weren't changing. Can't find the detailed message in the archives right now, but I read it earlier. Any other work-arounds? Tom. On Mon, 2002-08-26 at 12:46, Stephan Szabo wrote: My question is if this is now resolved for 7.3? I'm trying to figure out what I can do with my 7.2.1 problems... Has anyone got a back-ported patch, perhaps against 7.2.2? It's actually not resolved because my attempts at the lower strength lock opened up holes and fixing those opened up new deadlock situations, and I ran out of time in the 7.3 cycle to try to make it work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Database Caching
On Sun, Aug 25, 2002 at 09:35:24PM -0400, J. R. Nield wrote: I'm not sure about query result caching or 'relation caching', since the first would seem to run into problems with concurrent updates, and the second is sort-of what the buffer cache does. Query plan caching sounds like a really good idea though. Neil Conway's PREPARE patch already does this for an individual backend. Do you think it would be hard to make it use shared memory, and check if a query has already been prepared by another backend? Maybe it could use something like a whitespace insensitive checksum for a shared hash key. The original version of query plan cache allows exactly this. But after some discussion the shared memory usage in qcache was remove. I think better and more robus solution is store cached planns in backend memory and allows to run backend as persistent (means not startup/stop for each client connection). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL 7.2.2 and docs
On Mon, 26 Aug 2002, Tatsuo Ishii wrote: Also I notice an announce for release 7.2.3 appeaing on http://www.postgresql.org. You do? Where? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL 7.2.2 and docs
I think he means 7.2.2... Chris On Mon, 26 Aug 2002, Vince Vielhaber wrote: On Mon, 26 Aug 2002, Tatsuo Ishii wrote: Also I notice an announce for release 7.2.3 appeaing on http://www.postgresql.org. You do? Where? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: + if (!superuser() MyBackendId MaxBackends - ReservedBackends) + elog(ERROR, Normal user limit exceeded); This coding is wrong on its face: the slot number you happen to find has no relationship to the number of slots remaining free, except as an existence proof that the number of slots free was 0 before you took one. Yes. I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. Therefore, if MyBackendId is under the lower limit it doesn't matter if it's the only slot free since the 'safety' measure has already been used in restricting access to the last free slots and it just so happens that those sessions are still active. I take Neil's point about the order of the tests. That's my stupidity when rearranging stuff after noticing in tests that the user information wasn't available where I was [also stupidly] expecting it to be first time around. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] @(#)Mordred Labs advisory 0x0006: Two minor DoS conditions in PostgreSQL
Sir Mordred The Traitor [EMAIL PROTECTED] writes:
template1=# select substring('',2,2147483647);
With CVS HEAD (with database encoding = SQL_ASCII and UNICODE), I get:
nconway=# select substring('',2,2147483647);
ERROR: negative substring length not allowed
With REL7_2_STABLE, I get:
template1=# select substring('',2,2147483647);
ERROR: MemoryContextAlloc: invalid request size 2147483651
template1=# select bpchar('x',1);
With both CVS HEAD and REL7_2_STABLE, I get:
template1=# select bpchar('x',1);
zsh: 7312 segmentation fault (core dumped) ./psql template1
(note that it's the client, and not the backend, that crashes)
Although the backend does allocate a couple hundred megs of memory
while processing the query.
Although I haven't looked at the code yet, it's probably worth noting
that the two test cases posted above are not cut-and-dry DoS
opportunities, AFAICT -- however, the code may still be vulnerable.
Cheers,
Neil
--
Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
//@(#) Mordred Labs advisory 0x0007
Release data: 26/08/02
Name: Remote DoS condition in PostgreSQL
Versions affected: all versions
Conditions: entry in a pg_hba.conf file that matches attacker's host.
Risk: average
---[ Description:
Upon connecting to a database, postmaster will fork a new process.
After that, a child process will call a
src/backend/postmaster/postmaster.c:DoBackend() routine,
which after processing a startup packet (see src/include/libpq/pqcomm.h),
will invoke a src/backend/libpq/auth.c:ClientAuthentication() routine to
perform client authentication.
If there is an entry in pg_hba.conf file, that matches an attacker's host,
an attacker could trigger
invocation of src/backend/libpq/auth.c:recv_and_check_password0(), which
fails to detect a DoS condition.
---[ Details:
Consider this snip of code from src/backend/libpq/auth.c:
[snip]
static int recv_and_check_password0(Port *port) {
int32 len;
char *buf;
if (pq_getint(len, 4) == EOF)
return STATUS_EOF;
len -= 4;
buf = palloc(len); /* len is taken from a packet */
[snip]
Note, that the size of palloced memory is taken from the user's input,
which is stupid if you ask me.
--[ How to reproduce:
I dont want to provide any tools to illustrate this vulnerability.
--[ Solution
Disable network access for untrusted users.
This letter has been delivered unencrypted. We'd like to remind you that
the full protection of e-mail correspondence is provided by S-mail
encryption mechanisms if only both, Sender and Recipient use S-mail.
Register at S-mail.com: http://www.s-mail.com/inf/en
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
On 26 Aug 2002 at 14:46, Sir Mordred The Traitor wrote:
[snip]
static int recv_and_check_password0(Port *port) {
int32 len;
char *buf;
if (pq_getint(len, 4) == EOF)
return STATUS_EOF;
len -= 4;
buf = palloc(len); /* len is taken from a packet */
[snip]
So that should read,
buf=palloc((lenLENMAX?SAFELEN:len));
is what you want to say?
sounds good to me.. But if it is taken from the packet, won't that be tripped
to MTA size? Just a naïve question. Never saw much of postgres code myself..
Bye
Shridhar
--
Wilcox's Law: A pat on the back is only a few centimeters from a kick in the
pants.
---(end of broadcast)---
TIP 3: 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] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
Sir Mordred The Traitor [EMAIL PROTECTED] writes: Note, that the size of palloced memory is taken from the user's input, which is stupid if you ask me. Beyond causing an out of memory error during the handshake, I fail to see how there can be any problem. palloc is considerably more robust than malloc. I dont want to provide any tools to illustrate this vulnerability. Perhaps you haven't tried. It may indeed make sense to put a range check here, but I'm getting tired of hearing the words dos attack applied to conditions that cannot be exploited to cause any real problem. All you are accomplishing is to spread FUD among people who aren't sufficiently familiar with the code to evaluate the seriousness of problems... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Release of v7.2.2 (Was: Re: [HACKERS] @(#)Mordred Labs ad...)
Builds and runs fine under HP/Compaq Tru64 aka Digital Unix aka OSF/1 (this is getting difficult...) version 4.0f/g using standard cc: template1=# SELECT version(); version PostgreSQL 7.2.2 on alphaev56-dec-osf4.0g, compiled by cc -std $ gmake check == All 79 tests passed. == Same using GCC: == All 79 tests passed. == -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-22-755750 It is more complicated than you think -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
On Monday 26 August 2002 10:46 am, Sir Mordred The Traitor wrote: Conditions: entry in a pg_hba.conf file that matches attacker's host. Risk: average --[ Solution Disable network access for untrusted users. TCP/IP access must be enabled as well. TCP/IP accessibility is OFF by default. I for one thought that it was normal operating procedure to only allow access to trusted machines; maybe I'm odd in that regard. Hey, if I can connect to postmaster I can DoS it quite easily, but flooding it with connection requests. But, if we can thwart this, all the better. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: 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] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
The point is really simple. Allocate a huge chunk of memory (no sense to cause out of memory error, as palloc will bail is a requested size 1 gb). The postgres will be ready to suck your input, via pg_getbytes(), now in a loop send junk to postgresql. Of course you can fork a number of processes to improve your effect. The issues is that postgres allocate a chunk of memory and reads data, using an user's input, which has not completed authentication. This is badly anyway. Of course i tried, and wrote proggy for that, but i can repeat, i dont want to provide it here. Sir Mordred The Traitor [EMAIL PROTECTED] writes: Note, that the size of palloced memory is taken from the user's input, which is stupid if you ask me. Beyond causing an out of memory error during the handshake, I fail to see how there can be any problem. palloc is considerably more robust than malloc. I dont want to provide any tools to illustrate this vulnerability. Perhaps you haven't tried. It may indeed make sense to put a range check here, but I'm getting tired of hearing the words dos attack applied to conditions that cannot be exploited to cause any real problem. All you are accomplishing is to spread FUD among people who aren't sufficiently familiar with the code to evaluate the seriousness of problems... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) This letter has been delivered unencrypted. We'd like to remind you that the full protection of e-mail correspondence is provided by S-mail encryption mechanisms if only both, Sender and Recipient use S-mail. Register at S-mail.com: http://www.s-mail.com/inf/en ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
-*- Lamar Owen [EMAIL PROTECTED] [ 2002-08-26 15:19 ]: TCP/IP access must be enabled as well. TCP/IP accessibility is OFF by default. I for one thought that it was normal operating procedure to only allow access to trusted machines; maybe I'm odd in that regard. Hey, if I can connect to postmaster I can DoS it quite easily, but flooding it with connection requests. But, if we can thwart this, all the better. Well, ISP's that offer webhosting and database connectivity might also be running a PostgreSQL server that only allows connections from that specific webserver (TCP port 5432 access not blocked as well as an pg_hba.conf entry). Now, if a user with access to the webserver has privileges to open a socket connection, he could exploit this. -- Regards, Tolli [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
so basically if you are an idiot admin, and leave the postgresql box open (explicitly opening stuff), and under certian conditions, you can get DoS'd? hrm, this may not be your biggest problem. maybe if the dba has a clue and only explicitly allows certian ips to even route to the box, and then certian users (1 or 2 or so) that is not available to the public (ie, internet), they would be better off. i would be that with the lazy/ignorant setup of the dba/admin, that a DoS of postgresql is not the biggest problem, sure one of their redhat boxes has gotten rooted already... there is nothing that is more important for security and databases than setting them up correctly, and their place on the network. the database is the crown jewel that should never been seen or touched except for when _absolutely_ needed, and that must be under heavy control. there is a bigger problem here than postgresql, it is the dumbass factor of people that try to run a db, and are vuln to anything... and then complain about it... i find this very annoying. know what you are doing, or stfu is my opinion -chris ps - note this was not directed at any one person, but to the mass of people that never should run a db, and go back to eating paint chips. - disclaimer: i do not speak on behalf of devis (devis.com). i speak on my own behalf. - /rant-mode Lamar Owen writes: On Monday 26 August 2002 10:46 am, Sir Mordred The Traitor wrote: Conditions: entry in a pg_hba.conf file that matches attacker's host. Risk: average --[ Solution Disable network access for untrusted users. TCP/IP access must be enabled as well. TCP/IP accessibility is OFF by default. I for one thought that it was normal operating procedure to only allow access to trusted machines; maybe I'm odd in that regard. Hey, if I can connect to postmaster I can DoS it quite easily, but flooding it with connection requests. But, if we can thwart this, all the better. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: 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 -- Chris Humphries Development InfoStructure 540.366.9809 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Deadlock situation using foreign keys (reproduceable)
On Mon, 26 Aug 2002, Mario Weilguni wrote: Not a lower strength lock, I would, but I'm not so familiar with the postgres internals. I modified ri-triggers.c to exclude certain tables from the locking itself (because I know the tables are not updated). It might help the op of this thread, so it's probably worth posting. :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] btw
By the way, seems like a beer DOSsed me a little:-). I've made i mistake in email subject. Remove-remote This letter has been delivered unencrypted. We'd like to remind you that the full protection of e-mail correspondence is provided by S-mail encryption mechanisms if only both, Sender and Recipient use S-mail. Register at S-mail.com: http://www.s-mail.com/inf/en ---(end of broadcast)--- TIP 3: 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] TODO Done. Superuser backend slot reservations
Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. But that doesn't do the job, does it? My view of the feature is that when there are at least MaxBackends - ReservedBackends slots in use (by either su or non-su connections) then no new non-su jobs should be let in. For example, if the system is full (with a mix of su and non-su jobs) and one non-su job quits, don't we want to hold that slot for a possible su connection? Your approach does have the advantage of being very cheap to test (I think my semantics would require counting the active backends), but I'm not sure that it really does what we want. Tom is right. If the last two slots are held by two long-running super-user backends, and the slots fill, there will be no reserved slots. The trick is that when the maximum number of backends is almost exceeded, only let the supuer-user in. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
[EMAIL PROTECTED] (Bruce Momjian) wrote Sir-* does have a point. A valid host in pg_hba.conf can cause DOS by just connecting over and over, but allocating almost all of the memory on the machine would affect other applications running on the machine, even non-networked applications, as well as PostgreSQL, while a connection DOS effects only PostgreSQL. oh woe is he the man who does not use rlimits, or perhaps, concurrency limits? it seems this is nothing new, all network available services are subject to dos or ddos attacks... and if you dont setup limits on your machine, then other things can be affected... inetd, bind, sendmail, finger, insert favorite network accessible program here, etc... I do agree that pgsql should not just arbitrarily allocate memory like this, as defensive programming, but I cannot agree that this is a bug or problem in pgsql per se. As a side note, if someone wanted to shift the discussion to allowing concurrency limits in pgsql, how would/could this fit into the context of another thread where it was discussed to be able to always allow certain users to login... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] How To Make Things Appear More Dramatic
An alarmist style when posting a serious error is a good idea. Hey guys, I found a possible problem... Does not seem to generate the needed level of excitement. DOS attacks means that business stops. I think that should generate a furrowed brow, to say the least. Obviously people have forgotten past history. The Symbolics guys had _great_ techniques for this that were well documented: It is considered artful to append many messages on a subject, leaving only the most inflammatory lines from each, and reply to all in one swift blow. The choice of lines to support your argument can make or break your case. -- from the Symbolics Guidelines for Sending Mail % State opinions in the syntax of fact: ...as well as the bug in LMFS where you have to expunge directories to get rid of files. -- from the Symbolics Guidelines for Sending Mail % People can be set wondering by loading obscure personal patchable systems, and sending bug reports. Who would not stop and wonder upon seeing Experimental TD80-TAPE 1.17, MegaDeath 2.5...? The same for provocatively-named functions and variables in stack traces. -- from the Symbolics Guidelines for Sending Mail % Know the list of large, chronic problems. If there is any problem with the window system, blame it on the activity system. Any lack of user functionality should be attributed to the lack of a command processor. A suprisingly large number of people will believe that you have thought in depth about the issue to which you are alluding when you do. -- from the Symbolics Guidelines for Sending Mail % Know how to blow any problem up into insolubility. Know how to use the phrase The new ~A system to insult its argument, e.g., I guess this destructuring LET thing is fixed in the new Lisp system, or better yet, PROLOG. -- from the Symbolics Guidelines for Sending Mail % Never hit someone head on, always sideswipe. Never say, Foo's last patch was brain-damaged, but rather, While fixing the miscellaneous bugs in 243.xyz [foo's patch], I found -- from the Symbolics Guidelines for Sending Mail % Idiosyncratic indentations, double-spacing, capitalization, etc., while stamps of individuality, leave one an easy target for parody. -- from the Symbolics Guidelines for Sending Mail % Strong language gets results. The reloader is completely broken in 242 will open a lot more eyes than The reloader doesn't load files with intermixed spaces, asterisks, and 's in their names that are bigger than 64K. You can always say the latter in a later paragraph. -- from the Symbolics Guidelines for Sending Mail % Including a destination in the CC list that will cause the recipients' mailer to blow out is a good way to stifle dissent. -- from the Symbolics Guidelines for Sending Mail % When replying, it is often possible to cleverly edit the original message in such a way as to subtly alter its meaning or tone to your advantage while appearing that you are taking pains to preserve the author's intent. As a bonus, it will seem that your superior intellect is cutting through all the excess verbiage to the very heart of the matter. -- from the Symbolics Guidelines for Sending Mail % Referring to undocumented private communications allows one to claim virtually anything: we discussed this idea in our working group last year, and concluded that it was totally brain-damaged. -- from the Symbolics Guidelines for Sending Mail % Points are awarded for getting the last word in. Drawing the conversation out so long that the original message disappears due to being indented off the right hand edge of the screen is one way to do this. Another is to imply that anyone replying further is a hopeless cretin and is wasting everyone's valuable time. -- from the Symbolics Guidelines for Sending Mail % Keeping a secret Hall Of Flame file of people's mail indiscretions, or copying messages to private mailing lists for subsequent derision, is good fun and also a worthwhile investment in case you need to blackmail the senders later. -- from the Symbolics Guidelines for Sending Mail % Users should cultivate an ability to make the simplest molehill into a mountain by finding controversial interpretations of innocuous sounding statements that the sender never intended or imagined. -- from the Symbolics Guidelines for Sending Mail % Obversely, a lot of verbal mileage can also be gotten by sending out incomprehensible, cryptic, confusing or unintelligible messages, and then iteratively correcting the mistaken interpretations in the replys. -- from the Symbolics Guidelines for Sending Mail % Trivialize a user's bug report by pointing out that it was fixed independently long ago in a system that hasn't been released yet. -- from the Symbolics Guidelines for Sending Mail % Send messages calling for fonts not available to the recipient(s). This can (in the case of Zmail) totally disable the user's machine and mail system
Re: [HACKERS] anonymous composite types - how to pass tupdesc to the function
Joe Conway [EMAIL PROTECTED] writes: I'm trying to come up with the best method to pass the query string columndef, or better yet the tuple description, to the function. Any suggestions on an approach? Can't it get it for itself from the results of the query, ie, look at PQftype() and so on to build a tupledesc? I guess there are some gotchas with inconsistent type OIDs between remote and local databases, but that still seems much less of a risk than manual errors in giving the columnset definition. You could at least check that PQfsize matches the local type's typlen as a way of detecting chance collisions of user-defined type OIDs. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Queries using rules show no rows modified?
Any chance we can resolve this before 7.3? I will add it to the TODO list. --- Jan Wieck wrote: Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Of cource it is nice to have a complete solution immediately but it doesn't seem easy. My patch is only a makeshift solution but fixes the most siginificant case(typical updatable views). I would like to devise a complete solution *before* we consider installing makeshift solutions (which will institutionalize wrong behavior). There seems to be some feeling here that in the presence of rewrites you only want to know that something happened. Are you suggesting that the returned tuple count should be the sum of all counts from insert, update, and delete actions that happened as a result of the query? We could certainly implement that, but it does not seem like a good idea to me. IMHO the answer should only be a number if the rewritten querytree list consists of one query of the same command type. everything else has to lead into unknown. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
Lamar Owen wrote: And dealing with a real name would be nice, IMHO. Otherwise we may end up with 'SMtT' as the nickname -- Hmmm, 'SMitTy' perhaps? :-) Reminds me of 'Uncle George' who did quite a bit for the Alpha port and then disappeared. Funny you mention that. Now knowing someone's name is troubling, but I am not sure why. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: 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] anonymous composite types - how to pass tupdesc to
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: I'm trying to come up with the best method to pass the query string columndef, or better yet the tuple description, to the function. Any suggestions on an approach? Can't it get it for itself from the results of the query, ie, look at PQftype() and so on to build a tupledesc? Hmm. Good point. That certainly works for dblink. I guess most functions with need for anonymous composite types would be able to derive a tupdesc from libpq (dblink), SPI (tablefunc.c:crosstab), function arguments (tablefunc.c:crosstab), or it would be known in advance (guc.c:show_all_settings). Can anyone think of a use case where the *only* source of tuple description would come from the query column def? I guess there are some gotchas with inconsistent type OIDs between remote and local databases, but that still seems much less of a risk than manual errors in giving the columnset definition. You could at least check that PQfsize matches the local type's typlen as a way of detecting chance collisions of user-defined type OIDs. Another good point. Thanks! Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
-Original Message- From: Lamar Owen [mailto:[EMAIL PROTECTED]] Sent: Monday, August 26, 2002 10:50 AM To: Bruce Momjian; Tom Lane Cc: Sir Mordred The Traitor; [EMAIL PROTECTED] Subject: Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL On Monday 26 August 2002 12:59 pm, Bruce Momjian wrote: Tom Lane wrote: It may indeed make sense to put a range check here, but I'm getting tired of hearing the words dos attack applied to conditions that cannot be exploited to cause any real problem. All you are accomplishing is to spread FUD among people who aren't sufficiently familiar with the code to evaluate the seriousness of problems... It isn't fun to have our code nit-picked apart, and Sir-* is over-hyping the vulnerability, but it is a valid concern. The length should probably be clipped to a reasonable length and a comment put in the code describing why. The pseudo-security-alert format used isn't terribly palatable here, IMHO. On BugTraq it might fly -- but not here. An alarmist style when posting a serious error is a good idea. Hey guys, I found a possible problem... Does not seem to generate the needed level of excitement. DOS attacks means that business stops. I think that should generate a furrowed brow, to say the least. A simple 'Hey guys, I found a possible problem when.' without the big-sounding fluff would sit better with me, at least. The substance of the message is perhaps valuable -- but the wrapper distracts from the substance. As long as the needed data is included (here is how to reproduce the problem...) I don't see any problem. And dealing with a real name would be nice, IMHO. Otherwise we may end up with 'SMtT' as the nickname -- Hmmm, 'SMitTy' perhaps? :-) Reminds me of 'Uncle George' who did quite a bit for the Alpha port and then disappeared. If he wants to call himself 'Sir Modred' or 'Donald Duck' or 'Jack the Ripper' or whatever, I don't see how it matters. He is providing a valuable service by location of serious problems. These are the sort of thing that must be addressed. This is the *EXACT* sort of information that is needed to make PostgreSQL become as robust as Oracle, SQL*Server, DB/2, etc. Every free database engine project should be so lucky as to have a 'Sir Modred' IMO-YMMV. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Queries using rules show no rows modified?
Bruce Momjian [EMAIL PROTECTED] writes: Any chance we can resolve this before 7.3? I don't think so; the discussion trailed off without any agreement on what the behavior should be, and so thinking about how to implement it seems premature. At this point I think we have more critical issues to focus on for 7.3 ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] RPMs for release 7.2.2
RPMs for the security bugfix release 7.2.2 are available now for your happy downloading in two forms and versions on ftp.postgresql.org. You may have to wait on mirror propagation, which could take a few hours. For those who want it as close to the previous release as possible, I have uploaded the binaries I built the other day for the 0.1PGDG prerelease -- they are identical in packaging to the last 7.2.1 PGDG RPM's I released. For those who want a more serviceable contrib packaging, I have finally applied Peter Eisentraut's patch for the contrib tree building. Also, due to the difficulty usually found when upgrading postgresql from a previous MAJOR version, this version's server subpackage Conflicts with server subpackages of release 7.1.3 and earlier. This is a prelude to the complete removal of the broken semifunctional upgrade capability -- you have been warned, and you will have to either override the upgrade with rpm --nodeps or rpm -e the previous postgresql-server FIRST, after dumping your data. Download the 7.2.2-1PGDG release for the latter, 7.2.2-0.1PGDG release for the former. Both are available at ftp://ftp.postgresql.org/pub/binary/v7.2.2/RPMS/redhat-7.3 Source RPMS at ftp://ftp.postgresql.org/pub/binary/v7.2.2/RPMS/SRPMS No SPARC binaries for Red Hat 6.2 yet... :-) CHANGELOG: * Mon Aug 26 2002 Lamar Owen [EMAIL PROTECTED] - 7.2.2-1PGDG - Applied PeterE's contrib patch -- contrib is now completely restructured. - server conflicts with less than 7.2, as a warning to the upcoming -- yank of the broken partial upgrade. * Thu Aug 22 2002 Lamar Owen [EMAIL PROTECTED] - 7.2.2 security fix. - deprecate postgresql-dump system. - 7.2.2-0.1PGDG PRERELEASE! -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] contrib/ intarray, ltree, intagg broken(?) by array changes
Joe Conway and I have just committed some changes in the internal representation of Postgres arrays: an element-type-OID field is added to the array header, and alignment calculations are now done the same way as in ordinary tuple storage, instead of taking shortcuts. I believe that these changes need to be reflected into the intarray, ltree, and intagg contrib modules. intarray and ltree both seem to be mapping their own declarations onto arrays using largely-similar code. But while intarray fails its regression test, I find ltree still passes. So I'm confused about what that code is really doing and don't want to touch it. I tried to fix intagg, but since there is no regression test for it I'm unsure whether it's okay. Could you folks take a look at CVS tip and see what changes are needed, if any? In the longer run, it might be possible to improve these routines to be array-type-polymorphic using the new features. But with the 7.3 beta date nearly upon us, I'd counsel first making the existing functionality work again... regards, tom lane ---(end of broadcast)--- TIP 3: 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] Think I see a btree vacuuming bug
Is this fixed, and if not, can I have some TODO text? --- Tom Lane wrote: If a VACUUM running concurrently with someone else's indexscan were to delete the index tuple that the indexscan is currently stopped on, then we'd get a failure when the indexscan resumes and tries to re-find its place. (This is the infamous my bits moved right off the end of the world error condition.) What is supposed to prevent that from happening is that the indexscan retains a buffer pin (but not a read lock) on the index page containing the tuple it's stopped on. VACUUM will not delete any tuple until it can get a super exclusive lock on the page (cf. LockBufferForCleanup), and the pin prevents it from doing so. However: suppose that some other activity causes the index page to be split while the indexscan is stopped, and that the tuple it's stopped on gets relocated into the new righthand page of the pair. Then the indexscan is holding a pin on the wrong page --- not the one its tuple is in. It would then be possible for the VACUUM to arrive at the tuple and delete it before the indexscan is resumed. This is a pretty low-probability scenario, especially given the new index-tuple-killing mechanism (which renders it less likely that an indexscan will stop on a vacuum-able tuple). But it could happen. The only solution I've thought of is to make btbulkdelete acquire super exclusive lock on *every* leaf page of the index as it scans, rather than only locking the pages it actually needs to delete something from. And we'd need to tweak _bt_restscan to chain its pins (pin the next page to the right before releasing pin on the previous page). This would prevent a btbulkdelete scan from overtaking ordinary indexscans, and thereby ensure that it couldn't arrive at the tuple on which an indexscan is stopped, even with splitting. I'm somewhat concerned that the more stringent locking will slow down VACUUM a good deal when there's lots of concurrent activity, but I don't see another answer. Ideas anyone? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Think I see a btree vacuuming bug
Bruce Momjian [EMAIL PROTECTED] writes: Is this fixed, and if not, can I have some TODO text? It's not fixed. I'd like to fix it for 7.3, but I was hoping someone would think of a better way to fix it than I did ... regards, tom lane --- Tom Lane wrote: If a VACUUM running concurrently with someone else's indexscan were to delete the index tuple that the indexscan is currently stopped on, then we'd get a failure when the indexscan resumes and tries to re-find its place. (This is the infamous my bits moved right off the end of the world error condition.) What is supposed to prevent that from happening is that the indexscan retains a buffer pin (but not a read lock) on the index page containing the tuple it's stopped on. VACUUM will not delete any tuple until it can get a super exclusive lock on the page (cf. LockBufferForCleanup), and the pin prevents it from doing so. However: suppose that some other activity causes the index page to be split while the indexscan is stopped, and that the tuple it's stopped on gets relocated into the new righthand page of the pair. Then the indexscan is holding a pin on the wrong page --- not the one its tuple is in. It would then be possible for the VACUUM to arrive at the tuple and delete it before the indexscan is resumed. This is a pretty low-probability scenario, especially given the new index-tuple-killing mechanism (which renders it less likely that an indexscan will stop on a vacuum-able tuple). But it could happen. The only solution I've thought of is to make btbulkdelete acquire super exclusive lock on *every* leaf page of the index as it scans, rather than only locking the pages it actually needs to delete something from. And we'd need to tweak _bt_restscan to chain its pins (pin the next page to the right before releasing pin on the previous page). This would prevent a btbulkdelete scan from overtaking ordinary indexscans, and thereby ensure that it couldn't arrive at the tuple on which an indexscan is stopped, even with splitting. I'm somewhat concerned that the more stringent locking will slow down VACUUM a good deal when there's lots of concurrent activity, but I don't see another answer. Ideas anyone? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Think I see a btree vacuuming bug
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Could we just block splits of pages containing pins? That's not an improvement IMHO. The objection to the fix I suggested is that it makes it harder for VACUUM to make progress in the presence of contention. Replacing that with an approach that blocks foreground processes from making progress is not better. Yes. Considering there are splits going on where backends are losing their pins, it seems you have to either prevent the backends from losing their pins, prevent the splits, or prevent vacuum from removing tuples on split pages that hold pins. Rather than having vacuum pin all the pages, could vacuum block in cases where pins exist in pages that _could_ contain tuples caused by a recent split, meaning there are pins in pre-split locations? If the page splits, how does the index scan find the new page to start again? It moves right until it finds the tuple it was on. That will either be in the pinned page, or some page to its right. Could the index scan be made to handle cases where the index tuple it was stopped on is gone? Don't see how. With no equal keys, you could test each tuple you scan over to see if it's the expected key; but that would slow things down tremendously I fear. In any case it fails completely when there are equal keys, since you could not tell where in a run of equal keys to resume scanning. You really have to find the exact index tuple you stopped on, AFAICS. So it uses the tid to find the old spot. Got it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: Default privileges for new databases (was Re: [HACKERS] Can't import
Have we addressed this? I don't think so. --- Tom Lane wrote: Ron Snyder [EMAIL PROTECTED] writes: May 31 16:11:50 vault pgcvs[2135]: [91] LOG: query: Create Temporary Table pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid); May 31 16:11:50 vault pgcvs[2135]: [93] ERROR: quickview: not authorized to create temp tables My theory is that I'm getting this last message (not authorized to create temp tables) because the permissions have been tightened down. Yeah. Right at the moment, new databases default to only-db-owner-has- any-rights, which means that others cannot create schemas or temp tables in that database (unless they're superusers). I'm of the opinion that this is a bad default, but was waiting to see if anyone complained before starting a discussion about it. Probably we should have temp table creation allowed to all by default. I'm not convinced that that's a good idea for schema-creation privilege though. Related issues: what should initdb set as the permissions for template1? Would it make sense for newly created databases to copy their permission settings from the template database? (Probably not, since the owner is likely to be different.) What about copying those per-database config settings Peter just invented? Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] MemoryContextAlloc: invalid request size 1934906735
I have been getting the subject message ever since upgrading to 7.2.1. I
tried 7.2.2 with the same thing. It seems to be related to my chkpass type
(see contrib) as it only happens on tables with that type. I tried it on a
new database with a very simple table and still see it. After compiling
chkpass.c and running the SQL to create the type create a table with one
field with chkpass type. Add a number of rows, I did 24, then vacuum it.
You get something similar to the above. Sometimes you get Memory exhausted
in AllocSetAlloc(929654141) instead and once in a while there is no error.
Given table x with field c as chkpass run UPDATE x SET c = ':a'; on it.
This never fails. Now try UPDATE x SET c = 'a'; and let chkpass crypt the
value. This usually fails with one of the above messages. The number is
constant until you run the UPDATE again.
Somehow the value of the password string is polluting the size storage. I
know this because every time this happens, the first 2nd, 3rd and 4th bytes
(after adjusting for endianness) of the integer are the 6th, 7th and 8th
characters of the encrypted password.
I have another type which is built like this except that it is an indexable
type and that doesn't seem to have any problem. It is constructed the same
way otherwise. The palloc calls appear to be correct. Can anyone see why
this would suddenly be a problem?
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 3: 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] TODO Done. Superuser backend slot reservations
On Mon, 26 Aug 2002, Bruce Momjian wrote: Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: I was taking the line that the last slots in the array are reserved. Those are not going to be taken by non su connections. But that doesn't do the job, does it? My view of the feature is that when there are at least MaxBackends - ReservedBackends slots in use (by either su or non-su connections) then no new non-su jobs should be let in. For example, if the system is full (with a mix of su and non-su jobs) and one non-su job quits, don't we want to hold that slot for a possible su connection? Your approach does have the advantage of being very cheap to test (I think my semantics would require counting the active backends), but I'm not sure that it really does what we want. Tom is right. If the last two slots are held by two long-running super-user backends, and the slots fill, there will be no reserved slots. The trick is that when the maximum number of backends is almost exceeded, only let the supuer-user in. Okay, it's not how I was thinking as you know but I've got nothing against it other than the backend slot scan time. I don't think that would be a significant drain of cpu time so I'll implement that scheme and resubmit. Got some other stuff to do first so it won't be done immediately but will in the next day or so; in time for beta assuming it doesn't fall foul of any patch review interval required. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Default privileges for new databases (was Re: [HACKERS] Can't import large objects in most recent cvs)
Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Default privileges for new databases (was Re: [HACKERS] Can't import
Sorry, I am confused. Why can we modify temp's permissions on CREATE DATABASE but not public's permissions? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Efficient DELETE Strategies
Added to TODO: * Allow DELETE to handle table aliases for self-joins [delete] --- Manfred Koizar wrote: On Mon, 10 Jun 2002 09:56:27 -0400, Tom Lane [EMAIL PROTECTED] wrote: Does anyone know whether other systems that support the UPDATE extension for multiple tables also support a DELETE extension for multiple tables? If so, what's their syntax? MSSQL seems to guess what the user wants. All the following statements do the same: (0) DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.i=t2.i) (1) DELETE t1 FROM t2 WHERE t1.i=t2.i (2a) DELETE t1 FROM t2, t1 WHERE t1.i=t2.i (2b) DELETE t1 FROM t2 INNER JOIN t1 ON t1.i=t2.i (3a) DELETE t1 FROM t2, t1 a WHERE a.i=t2.i (3b) DELETE t1 FROM t2 INNER JOIN t1 a ON a.i=t2.i (4a) DELETE a FROM t2, t1 a WHERE a.i=t2.i (4b) DELETE a FROM t2 INNER JOIN t1 a ON a.i=t2.i (5) DELETE t1 FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) (6) DELETE a FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i) (0) is standard SQL and should always work. As an extension I'd like (1) or (2), but only one of them and forbid the other one. I'd also forbid (3), don't know what to think of (4), and don't see a reason why we would want (5) or (6). I'd rather have (7) or (8). These don't work: (7) DELETE t1 a FROM t2 WHERE a.i = t2.i Incorrect syntax near 'a'. (8) DELETE FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i = t2.i) Incorrect syntax near 'a'. Self joins: (2as) DELETE t1 FROM t1, t1 b WHERE 2*b.i=t1.i (4as) DELETE a FROM t1 a, t1 b WHERE 2*b.i=a.i (4bs) DELETE a FROM t1 a INNER JOIN t1 b on 2*b.i=a.i These don't work: DELETE t1 FROM t1 b WHERE 2 * b.i = t1.i The column prefix 't1' does not match with a table name or alias name used in the query. DELETE t1 FROM t1 a, t1 b WHERE 2 * b.i = a.i The table 't1' is ambiguous. And as if there aren't enough ways yet, I just discovered that (1) to (6) just as much work with DELETE FROM where I wrote DELETE ... Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ident-des patches
I haven't seen any demand for ident DES so I have not applied this patch. If it becomes a feature request, we can revisit this. Thanks. --- David M. Kaplan wrote: Hi, I added the code to make IDENT authentification work even if the responses are DES encrypted. The changes are contained in the attached tar.gz file. There is a readme included in the tar.gz which explains things. The tar file contains the following files: ident-des.patch src/backend/libpq/ident-des.c src/include/libpq/ident-des.h README.ident-des Thanks, David Kaplan [ application/x-gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Default privileges for new databases (was Re: [HACKERS] Can't
Mostly because a user may explicitly create a database with wanted permissions, only to have this 'special code' remove them. I personally intend to immediately revoke permissions on public in template1, to allow the database owner to grant them as needed. On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote: Sorry, I am confused. Why can we modify temp's permissions on CREATE DATABASE but not public's permissions? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Default privileges for new databases (was Re: [HACKERS] Can't
Oh, so we don't modify public writeability of template1 because the admin may want to disable write in template1 so all future databases will have it disabled. I see. So template1 is writable (yuck) only so databases created from template1 are writeable to world by default. Is that accurate? --- Rod Taylor wrote: Mostly because a user may explicitly create a database with wanted permissions, only to have this 'special code' remove them. I personally intend to immediately revoke permissions on public in template1, to allow the database owner to grant them as needed. On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote: Sorry, I am confused. Why can we modify temp's permissions on CREATE DATABASE but not public's permissions? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: Default privileges for new databases (was Re: [HACKERS] Can't
On Mon, 2002-08-26 at 23:45, Bruce Momjian wrote: Oh, so we don't modify public writeability of template1 because the admin may want to disable write in template1 so all future databases will have it disabled. I see. So template1 is writable (yuck) only so databases created from template1 are writeable to world by default. Is that accurate? I believe thats the crux of the issue -- but those of us who don't want newly created DBs to be world writable have no issues with that :) Could create a template2 as the default 'copy from' template. Make it connectible strictly by superusers. Template1 becomes a holding area for those without a db to connect to and can be locked down. Another is to enable users to connect to the server without requiring a database. This basically removes the secondary requirement of template1 to be the holding area for those otherwise without a home. --- Rod Taylor wrote: Mostly because a user may explicitly create a database with wanted permissions, only to have this 'special code' remove them. I personally intend to immediately revoke permissions on public in template1, to allow the database owner to grant them as needed. On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote: Sorry, I am confused. Why can we modify temp's permissions on CREATE DATABASE but not public's permissions? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Default privileges for new databases (was Re: [HACKERS] Can't
It just bothers me that of all the databases that should be locked down, it should be template1, and it isn't by default. --- Rod Taylor wrote: On Mon, 2002-08-26 at 23:45, Bruce Momjian wrote: Oh, so we don't modify public writeability of template1 because the admin may want to disable write in template1 so all future databases will have it disabled. I see. So template1 is writable (yuck) only so databases created from template1 are writeable to world by default. Is that accurate? I believe thats the crux of the issue -- but those of us who don't want newly created DBs to be world writable have no issues with that :) Could create a template2 as the default 'copy from' template. Make it connectible strictly by superusers. Template1 becomes a holding area for those without a db to connect to and can be locked down. Another is to enable users to connect to the server without requiring a database. This basically removes the secondary requirement of template1 to be the holding area for those otherwise without a home. --- Rod Taylor wrote: Mostly because a user may explicitly create a database with wanted permissions, only to have this 'special code' remove them. I personally intend to immediately revoke permissions on public in template1, to allow the database owner to grant them as needed. On Mon, 2002-08-26 at 22:27, Bruce Momjian wrote: Sorry, I am confused. Why can we modify temp's permissions on CREATE DATABASE but not public's permissions? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Have we addressed this? I don't think so. No, it's not done yet. My inclination is * Template1 has temp table creation and schema creation disabled (disallowed to world) by default. * CREATE DATABASE sets up new databases with temp table creation allowed to world and schema creation allowed to DB owner only (regardless of what the template database had). The owner can adjust this default afterwards if he doesn't like it. It would be nice to lock down the public schema in template1 too, but I see no good way to do that, because CREATE DATABASE can't readily fiddle with protections *inside* the database --- the only games we can play are with the protections stored in the pg_database row itself. So public's permissions are going to be inherited from the template database, and that means template1's public has to be writable. Objections anyone? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Use of LOCAL in SET command
Has this been resolved? --- Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Sorry to nag about this so late, but I fear that the new command SET LOCAL will cause some confusion later on. Okay... SQL uses LOCAL to mean the local node in a distributed system (SET LOCAL TRANSACTION ...) and the current session as opposed to all sessions (local temporary table). The new SET LOCAL command adds the meaning this transaction only. Instead we could simply use SET TRANSACTION, which would be consistent in behaviour with the SET TRANSACTION ISOLATION LEVEL command. Hmm ... this would mean that the implicit parsing of SET TRANSACTION ISOLATION LEVEL would change (instead of SET / TRANSACTION ISOLATION LEVEL you'd now tend to read it as SET TRANSACTION / ISOLATION LEVEL) but I guess that would still not create any parse conflicts. I'm okay with this as long as we can fix psql's command completion stuff to handle it intelligently. I hadn't gotten round to looking at that point yet for the LOCAL case; do you have any thoughts? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: 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: Default privileges for new databases (was Re: [HACKERS] Can't
Bruce Momjian [EMAIL PROTECTED] writes: So template1 is writable (yuck) only so databases created from template1 are writeable to world by default. Is that accurate? Yup. I had a probably-harebrained idea about this: the writeability of public is only a serious issue when it is the default creation-target schema. It's likely that you'd say create table foo without reflecting about the fact that you're connected to template1; much less likely that you'd say create table public.foo. So, what if the default per-database GUC settings for template1 include setting the search_path to empty? That would preclude accidental table creation in template1's public schema. As long as CREATE DATABASE doesn't copy the per-database GUC settings of the template database, copied databases wouldn't be similarly crippled. Now I'm not entirely convinced that CREATE DATABASE shouldn't copy the per-database GUC settings of the template. But at the moment it doesn't, and if we're willing to institutionalize that behavior then it'd provide a way out. Or is that too weird? regards, tom lane ---(end of broadcast)--- TIP 3: 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] Use of LOCAL in SET command
Bruce Momjian [EMAIL PROTECTED] writes: Has this been resolved? I think the resolution was to do nothing. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Default privileges for new databases (was Re: [HACKERS] Can't
I had a good chuckle with this. It is the type of shoot for the moon idea I would have. Maybe I am rubbing off on you. :-) The only problem I see with this solution is it makes admins think their template1 is safe, when it really isn't. That seems more dangerous than leaving it world-writable. I don't think accidental writes into template1 are common enough to add a possible admin confusion factor. What we really need is some mode on template1 that says, I am not world-writable, but the admin hasn't made me world-non-writable, so I will create new databases that are world-writable. Does that make sense? I have an idea. Could we have the template1 per-database GUC settings control the writeability of databases created from template1, sort of a 'creation GUC setting', so we could run it on the new database once it is created? That way, we could make template1 public non-world-writable, and put something in the template1 per-database GUC setting to make databases created from template1 world-writable. If someone removes that GUC setting, the databases get created non-world writable. Oh, there I go again, shooting at the moon. ;-) Another idea. Is there a GUC setting we could put in template1 that would disable writing to public for world and _couldn't_ be revoked by the user, except for super users? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: So template1 is writable (yuck) only so databases created from template1 are writeable to world by default. Is that accurate? Yup. I had a probably-harebrained idea about this: the writeability of public is only a serious issue when it is the default creation-target schema. It's likely that you'd say create table foo without reflecting about the fact that you're connected to template1; much less likely that you'd say create table public.foo. So, what if the default per-database GUC settings for template1 include setting the search_path to empty? That would preclude accidental table creation in template1's public schema. As long as CREATE DATABASE doesn't copy the per-database GUC settings of the template database, copied databases wouldn't be similarly crippled. Now I'm not entirely convinced that CREATE DATABASE shouldn't copy the per-database GUC settings of the template. But at the moment it doesn't, and if we're willing to institutionalize that behavior then it'd provide a way out. Or is that too weird? regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. --- Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Bruce, I have a working patch for this I just need to test it further. It occured to me that there was a bug with the previous implementation in as much as it didn't handle situations where the user dropped the temp table in a transaction block. As such, I have added a flag to the structure marking it dead. This also needs to be undone at ABORT :-). (I knew there was a reason for storing the ON COMMIT flag in the system catalogs). Once I have tested further I will send off. If I come across a problem or cannot get to this by Wednesday (tomorrow) I will send them off to Neil Conway so that they can make it in. (Unfortunately, I am v. busy atm). Gavin On Tue, 27 Aug 2002, Bruce Momjian wrote: Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. --- Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
What about Gavin's CREATE OR REPLACE stuff? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gavin Sherry Sent: Tuesday, 27 August 2002 1:21 PM To: Bruce Momjian Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] [PATCHES] CREATE TEMP TABLE ON COMMIT Bruce, I have a working patch for this I just need to test it further. It occured to me that there was a bug with the previous implementation in as much as it didn't handle situations where the user dropped the temp table in a transaction block. As such, I have added a flag to the structure marking it dead. This also needs to be undone at ABORT :-). (I knew there was a reason for storing the ON COMMIT flag in the system catalogs). Once I have tested further I will send off. If I come across a problem or cannot get to this by Wednesday (tomorrow) I will send them off to Neil Conway so that they can make it in. (Unfortunately, I am v. busy atm). Gavin On Tue, 27 Aug 2002, Bruce Momjian wrote: Gavin, how are you doing with this. As I remember, the only remaining issue was where to store the 'drop on commit' information in the backend. If that is all there is, we can come up with a solution. -- - Gavin Sherry wrote: On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] MemoryContextAlloc: invalid request size 1934906735
D'Arcy J.M. Cain [EMAIL PROTECTED] writes: I have been getting the subject message ever since upgrading to 7.2.1. I tried 7.2.2 with the same thing. It seems to be related to my chkpass type (see contrib) as it only happens on tables with that type. FWIW, I couldn't see any problem in CVS tip. Could you provide an exact sequence-to-reproduce? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
