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]
[HACKERS] @(#)Mordred Labs advisory 0x0006: Two minor DoS conditions in PostgreSQL
"..if someone has direct SQL access to your database, they can
already do more damage than what this vulnerability addresses. Specifically
DROP TABLE is available to users with direct SQL command line access..."
That's true of course, but i really dont want to do any damage, i might
even don't want
to get noticed...
".. the cracker must be able to execute arbitrary SQL commands against the
database,
and by that stage of the game, a DoS
attack is already trivial (e.g. disable GEQO and execute a 15 table join
query)..."
Hmm, sounds complex, why bother?. Thanks for the advice anyway :-)
//@(#) Mordred Labs advisory 0x0006
Release data: 26/08/02
Name: Two dumb DoS conditions in PostgreSQL
Versions affected: all versions
Risk: very low
---[ Description:
1) Upon invoking a substring(text, integer, integer) function, a
src/backend/utils/adt/varlena.c:text_substr()
function will gets called, which fails to detect a simple dos condition,
triggered by a very high third argument.
Multibyte support must be enabled for this attack to work.
The vulnerable encodings are: SQL_ASCII, LATIN1.
With others, you just will get SIGSEGV (not checked).
2) Upon invoking a bpchar(char, integer) function, a
src/backend/utils/adt/varchar.c:bpchar() function
will gets called, which suffers from a dos condition.
--[ How to reproduce:
template1=# select substring('',2,2147483647);
template1=# select bpchar('x',1);
--[ Solution
No one is available.
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] PostgreSQL 7.2.2: Security Release
On Mon, 26 Aug 2002, Christopher Kings-Lynne wrote: > *sigh* Someone's marked postgres 7.2.1 as forbidden in FreeBSD ports: > > FORBIDDEN= "buffer overruns acknowledged by authors--see > http://www3.us.postgresql.org/news.html>" > > Somewhat of an overreaction...I'm hassling the maintainer at the moment... already fixed ... > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On Behalf Of Marc G. Fournier > > Sent: Monday, 26 August 2002 10:17 AM > > To: Bruce Momjian > > Cc: Gavin Sherry; Neil Conway; PostgreSQL Hackers > > Subject: Re: [HACKERS] [GENERAL] PostgreSQL 7.2.2: Security Release > > > > > > On Sun, 25 Aug 2002, Bruce Momjian wrote: > > > > > > > > OK, I understand your point. What do we need to do now that the > > > announcement has already been made? > > > > I'm still slightly confused here ... from what Neil/Gavin have stated so > > far, all it sounds like is that if I pass a wrong date/time string, it > > will crash the backend ... or is this what I'm missing? > > > > > > > > > > > > -- > > - > > > > > > Gavin Sherry wrote: > > > > On Sat, 24 Aug 2002, Bruce Momjian wrote: > > > > > > > > > > > > > > The issue is data-provoked crashes vs. query-invoked > > crashes. Marc's > > > > > point, and I think it was clear enough, is that you can't > > just poke at > > > > > the TCP port and hope to do anything bad, which was the > > thrust of the > > > > > argument, I think. > > > > > > > > Bruce, > > > > > > > > I am convinced that someone with enough time on their hands > > and some code > > > > pointed to by Florian Weimer could exploit the datetime > > overrun issue by > > > > crafting a datetime string in such a way as to overrun the buffer and > > > > smash the stack. > > > > > > > > In applications which pass date/time data directly to the > > database without > > > > any validation (is this datetime string greater than 52 bytes? does it > > > > look like a date/time string?) then a malicious user without direct > > > > database access could crash the database by taking advantage > > of the short > > > > comings in Postgres and the application. > > > > > > > > As such, I would recommend all people who offer direct access to the > > > > database and/or have applications which user date/time data > > > > types/functionality to upgrade to 7.2.2. > > > > > > > > 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 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO Done. Superuser backend slot reservations
"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. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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((len>LENMAX?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] Deadlock situation using foreign keys (reproduceable)
On Mon, 26 Aug 2002, Mario Weilguni wrote: > 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. Which, the for update one or a lower strength lock? In either case, certainly. ---(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
>Hey, if I can connect to postmaster I can DoS it quite easily, but flooding it >with connection requests. Hm, that's true of course, but now i will do this with a couple of connections. Lets say, bot on a owned machine, connects to a database, send a crafted packet, postgresql will allocate a huge amount of memory, and will be happy to read anything it recvs from my bot. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Deadlock situation using foreign keys (reproduceable)
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). -Ursprüngliche Nachricht- Von: Stephan Szabo [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 26. August 2002 17:29 An: Mario Weilguni Cc: [EMAIL PROTECTED] Betreff: Re: [HACKERS] Deadlock situation using foreign keys (reproduceable) On Mon, 26 Aug 2002, Mario Weilguni wrote: > 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. Which, the for update one or a lower strength lock? In either case, certainly. ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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. - 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] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
-*- Sir Mordred The Traitor <[EMAIL PROTECTED]> [ 2002-08-26 15:32 ]: > >Hey, if I can connect to postmaster I can DoS it quite easily, but > flooding it > >with connection requests. > > Hm, that's true of course, but now i will do this with a couple of > connections. > Lets say, bot on a owned machine, connects to a database, > send a crafted packet, > postgresql will allocate a huge amount of memory, and will be > happy to read anything it recvs from my bot. Speaking of which. If I understand correctly, a new backend is forked and the connection dispatched to that specific backend, once access has been granted (with means of user/pass authentication, ident or whatever). Is there any check for connection to the postmaster that have not been dispatched to a new backend after X bytes (or seconds?), to free resources (would that make any sense? :) And another (perhaps silly) thought: Currently, if the authentication process is exploited, it would kill the postmaster, resulting in a total crash of the whole database system. Would it be beneficial to split the connection handling/authorization process to a seperate process, and if that process dies, the postmaster would simply start a new one, there for not affecting any other backends that are running (for authorized users) ? Or am I way of track? :) -- Regards, Tolli [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
Tom Lane wrote: > 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... 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. 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. There are a whole host of vulnerability scenarios that we have not explored. In fact, with 7.2.2, we got into the data-based crash vs. query-based crash issue that we have not discussed before in detail. With this report, we have a non-authorized user causing possible memory exhaustion for all applications on the server. This is a greater vulnerability than the random query argument because in such cases the user is authorized to issue queries. So, Sir-* and others, please understand that databases have their own vulnerability envelope that is much more complex than a standard network application like ftp. There is almost an entirely new vocabulary for describing such vulnerabilities which we have been crafting as we discuss it. This vocabulary includes: connection-enabled hosts vs. any host authenticated vs. non-authenticated users users with data insertion access users with query access I think the last group can not be protected from malevolent queries, but the former can be tightened, and Sir-* is working on that. Sir-*, when describing vulnerabilities, especially in public forums, please try to use this language so people are not overly agitated by your warnings. -- 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]
[HACKERS] anonymous composite types - how to pass tupdesc to the function
I'm looking at modifications to dblink to allow it to make use of table functions. One item which is very much needed is the ability to define dblink as returning setof record, so that it can return a tuple as described at runtime in the query string. 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? 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
[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, , 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
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
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. 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. 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. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
On Monday 26 August 2002 02:23 pm, Dann Corbit wrote: > 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. The historical style on this list has avoided histrionics -- although I have myself been guilty of the hyperbole problem. Making a big stink in no wise guarantees it being heard, and may very well cause some to bristle, as Tom has done. It just doesn't fit the style of this list, that's all. > As long as the needed data is included (here is how to reproduce the > problem...) I don't see any problem. When you have to read and process nearly 1,000 e-mails a day (as I have had to do, although my average is a mere 400 or so per day), the subject line and the first screenful of the message will be looked at, and no more. The substance needs to be early in the message, and the subject needs to be short and descriptive. These are just simply traditions, protocols, and ettiquette for Internet mailing lists, as well as other fora such as Usenet. If someone wants me to pay attention to a message, the subject needs to be on the point, and the point needs to be early in the message. Otherwise I may simply be so rushed when it arrives in my mailboxen (more than one, as I have autorouting mail filters in place) that it gets ignored. I know I am not alone in processing mail this way. > > And dealing with a real name would be nice, IMHO. Otherwise > > we may end up > 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. I'm sorry, but I have more respect for someone who isn't afraid to use their real name. I've been on both sides of that fence. Even in the security business, where people routinely use pseudonyms, I personally prefer to know their real name. If I _know_ Aleph One is Elias Levy, then that's easy enough. If the information is easily available, then that's enough. So, it makes a difference to me, like it, lump it, or think it's insane. And, yes, I agree he IS providing a valuable service -- with that I have no complaints. But there is a distinct civility and culture to this list, and I'd like to see it stay that way. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] 2nd cut at SSL documentation
With Bear disappearing, I am now unsure of our current SSL code. He submitted a bunch of patches that seemed to improve our SSL capabilities, but I am unsure if it is done, and exactly how it was improved. Attached is the documentation Bear supplied. I have removed the interfaces/ssl directory because I couldn't figure out what to do with it, but this documentation does help. The files in interfaces/ssl where: client.conf mkcert.sh*pgkeygen.sh root.conf server.conf Would someone who understands SSL coding please comment? --- Bear Giles wrote: > A second cut at SSL documentation > > > > SSL Support in PostgreSQL > = > > Who needs it? > = > > The sites that require SSL fall into one (or more) of several broad > categories. > > *) They have insecure networks. > >Examples of insecure networks are anyone in a "corporate hotel," >any network with 802.11b wireless access points (WAP) (in 2002, >this protocol has many well-known security weaknesses and even >'gold' connections can be broken within 8 hours), or anyone >accessing their database over the internet. > >These sites need a Virtual Private Network (VPN), and either >SSH tunnels or direct SSL connections can be used. > > *) They are storing extremely sensitive information. > >An example of extremely sensitive information is logs from >network intrusion detection systems. This information *must* >be fully encrypted between front- and back-end since an attacker >is presumably sniffing all traffic within the VPN, and if they >learn that you know what they are doing they may attempt to >cover their tracks with a quick 'rm -rf /' and 'dropdb' > >In the extreme case, the contents of the database itself may >be encrypted with either the crypt package (which provides >symmetrical encryption of the records) or the PKIX package >(which provides public-key encryption of the records). > > *) They are storing information which is considered confidential >by custom, law or regulation. > >This includes all records held by your doctor, lawyer, accountant, >etc. In these cases, the motivation for using encryption is not >a conscious evaulation of risk, but the fear of liability for >'failure to perform due diligence' if encryption is available but >unused and an attacker gains unauthorized access to the harm of >others. > > *) They have 'road warriors.' > >This includes all sites where people need to have direct access >to the database (not through a proxy such as a secure web page) >from changing remote addresses. Client certificates provide a >clean way to grant this access without opening up the database >to the world. > > Who does not need it? > - > > It's at least as important to know who does not need SSL as it > is to know who does. Sites that do not need SSL fall into several > broad categories. > > *) Access is limited to the Unix socket. > > *) Access is limited to a physically secure network. > >"Physically secure" networks are common in the clusters and >colocation sites - all database traffic is restricted to dedicated >NIC cards and hubs, and all servers and cabling are maintained in >locked cabinets. > > > Using SSH/OpenSSH as a Virtual Private Network (VPN) > > > SSH and OpenSSH can be used to construct a Virtual Private Network > (VPN) to provide confidentiality of PostgreSQL communications. > These tunnels are widely available and fairly well understood, but > do not provide any application-level authentication information. > > To set up a SSH/OpenSSH tunnel, a shell account for each > user should be set up on the database server. It is acceptable > for the shell program to be bogus (e.g., /bin/false), if the > tunnel is set up in to avoid launching a remote shell. > > On each client system the $HOME/.ssh/config file should contain > an additional line similiar to > > LocalForward psql.example.com:5432 > > (replacing psql.example.com with the name of your database server). > By putting this line in the configuration file, instead of specifying > it on the command line, the tunnel will be created whenever a > connection is made to the remote system. > > The psql(1) client (or any client) should be wrapped with a script > that establishes an SSH tunnel when the program is launched: > > #!/bin/sh > HOST=psql.example.com > IDENTITY=$HOME/.ssh/identity.psql > /usr/bin/ssh -1 -i $IDENTITY -n $HOST 'sleep 60' & \ > /usr/bin/psql -h $HOST -p $1 > > Alternately, the system could run a daemon that establishes and maintains > the tunnel. This is preferrable when multiple users need to establish > similar tunnels to the same remote site. > > Unfortunately, th
Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: Remove DoS in PostgreSQL
> -Original Message- > From: Lamar Owen [mailto:[EMAIL PROTECTED]] > Sent: Monday, August 26, 2002 11:41 AM > To: Dann Corbit; Bruce Momjian; Tom Lane > Cc: Sir Mordred The Traitor; [EMAIL PROTECTED] > Subject: Re: [HACKERS] @(#)Mordred Labs advisory 0x0007: > Remove DoS in PostgreSQL [snip] > And, yes, I agree he IS providing a valuable service -- with > that I have no > complaints. But there is a distinct civility and culture to > this list, and > I'd like to see it stay that way. I want to make it clear that I think the best way to report a problem is with formal, rigorous, complete structure. That structure should be known to the receiving body. If there is a procedure or standard form for producing the needed information, it is better to follow the standard procedure. On the other hand (and the point I had hoped to make) some people like to take lined paper and write on it sideways [Bradbury's "Fahrenheight 451" quote]. Instead of complaining that they are writing sideways on the page, extract the information of value. If the can be convinced to "stop writing in a perpendicular manner" so that the majority will be more comfortable with it, so much the better. But standardized or not, it can still contain information of great value. ---(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
Lamar Owen wrote: > And, yes, I agree he IS providing a valuable service -- with that I have no > complaints. But there is a distinct civility and culture to this list, and > I'd like to see it stay that way. Well, when someone is a "Sir", we do give them a little more latitude. (Oh, hold one, that isn't his real name. I see now.) ;-) -- 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
On Monday 26 August 2002 02:51 pm, Dann Corbit wrote: > I want to make it clear that I think the best way to report a problem is > with formal, rigorous, complete structure. That structure should be > known to the receiving body. If there is a procedure or standard form > for producing the needed information, it is better to follow the > standard procedure. Ok, let's do that then. SOP is to use the bug reporting form contained on the website, which posts a bug report to [EMAIL PROTECTED], which is the canonical bugreport list. That is if we want to get that formal. > But standardized or not, > it can still contain information of great value. Yes, it can. I can use steganography to issue an RPM release announcement, too. But will it be effective? :-) If it isn't read, it won't be acted upon. His announcements have been a difficult read, that's all. The substance is OK; the presentation is lacking, IMHO. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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: > 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 Certainly anything that makes this error message less likely is bad. Perhaps we need to hook up a random() call to output the error message to balance this. :-) > 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. OK, let me see if I can summarize: Index scan stops on dead index tuple, holds pin Someone inserts/updates and the page splits Vacuum comes along and removes the index tuple And the problem is that after the split, the index scan doesn't hold a pin on the newly split page. This seems more like a problem that the index scan pin doesn't prevent it from losing the pin after a split. Could we just block splits of pages containing pins? If the page splits, how does the index scan find the new page to start again? Could the index scan be made to handle cases where the index tuple it was stopped on is gone? -- 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] Think I see a btree vacuuming bug
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. > 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. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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| 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])
Re: [HACKERS] Think I see a btree vacuuming bug
> > 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. Won't it still point to the same heap page and slot ? That additional info should be sufficient to find the exact index tuple. And it usually won't be that far away, no ? Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
