Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
On Mon, 2002-08-12 at 11:38, Mario Weilguni wrote:
> Am Montag, 12. August 2002 08:02 schrieb Don Baccus:
> > Curt Sampson wrote:
> > > On Sun, 11 Aug 2002, Don Baccus wrote:
> > >>I've been wanting to point out that SQL views are really, when
> > >>scrutinized, "just syntactic sugar" ...
> > >
> > > Oh? Ok, please translate the following into equivalant SQL that
> > > does not use a view:
> > >
> > > CREATE TABLE t1 (key serial, value1 text, value2 text);
> > > CREATE VIEW v1 AS SELECT key, value1 FROM t1;
> > > GRANT SELECT ON v1 TO sorin;
> >
> > Granulize GRANT to the table column level. Then GRANT "SELECT" perms
> > for the user on every column from the two tables that happen to be
> > included in the view.
> >
> > Yes, it's awkward. So are the VIEW-based replacements for PG's type
> > extensibility features.
>
> But this is not a replacement for a view, isn't it? With a view I can do this:
> create view v1 as select name, salary from workers where type <> 'MANAGEMENT';
>
> with column permissions I must give access to all workers salary including the
>management, but not with a view.
I guess that bare-bones replacement of CREATE VIEW with CREATE TABLE and
CREATE RULE ... ON SELECT DO INSTEAD ... would have exaclty the same
semantics as CREATE VIEW, including the ability to GRANT .
so the no-view-syntactic-sugar equivalent would be
CREATE TABLE v1 AS SELECT * FROM t1 WHERE false;
CREATE RULE v1ins AS
ON SELECT TO tv1
DO INSTEAD
SELECT t1."key",
t1.value2
FROM t1
WHERE (t1."type" <> 'MANAGEMENT'::text);
GRANT SELECT ON v1 TO sorin;
Actually it seems that GRANT is also syntactic sugar for rules and the
above could be replaced with
CREATE RULE v1ins AS
ON SELECT TO tv1
DO INSTEAD
SELECT t1."key",
t1.value2
FROM t1
WHERE (t1."type" <> 'MANAGEMENT'::text)
AND CURRENT_USER IN ( SELECT username
FROM grantees
WHERE tablename = 'v1'
AND command = 'select' )
INSERT INTO GRANTEES(tablename,command,username)
VALUES('v1','select','sorin');
Hannu
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
On Sun, 11 Aug 2002, Don Baccus wrote: > Obviously it would require extending SQL, but since you in part argue > that SQL sucks in regard to the relational model this shouldn't matter, > right? Well, if we're going to go so far as to get rid of SQL, we can go all the way with the D&D thing, and VIEWs will no longer be syntatic sugar because views and tables will be the same thing. (I'll leave you how specify physical storage as an exercise for the reader. :-)) But anyway, I have no particularly huge objection to syntatic sugar alone. I do have objections to it when it's not saving much typing. (It is in this case, but that could be fixed with better automatic support of view updates.) But my real objection is when it makes things more confusing, rather than less, which I think is definitely happening here. I've never seen a rigourous explanation of our model of table inheritance, nor any model that was more obviously correct than another. And the parallel drawn with inheritance in OO languages is a false parallel that adds to the confusion. (For example, the distinction between types and instances of types is critical in OO theory. What are the TI equivalants of this?) All this is borne out by the regular questions one sees about inheritance in the mailing lists. I'll admit a good part of it is due to the broken implementation of inheritance, but all of the problems I've ever seen are easily solved with very simple relational solutions. Maybe the inheritance thing is causing people to turn off the relational parts of their brain or something. > I give up. Your right hand waves your dick more frequently and with > much more vigor than mine. First you ask for more meaningful discussion. Then you make comments like this. Hello? If you really don't intend to stop completely with the insulting comments, let me know and I can killfile you and we'll be done with this. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Am Montag, 12. August 2002 08:02 schrieb Don Baccus: > Curt Sampson wrote: > > On Sun, 11 Aug 2002, Don Baccus wrote: > >>I've been wanting to point out that SQL views are really, when > >>scrutinized, "just syntactic sugar" ... > > > > Oh? Ok, please translate the following into equivalant SQL that > > does not use a view: > > > > CREATE TABLE t1 (key serial, value1 text, value2 text); > > CREATE VIEW v1 AS SELECT key, value1 FROM t1; > > GRANT SELECT ON v1 TO sorin; > > Granulize GRANT to the table column level. Then GRANT "SELECT" perms > for the user on every column from the two tables that happen to be > included in the view. > > Yes, it's awkward. So are the VIEW-based replacements for PG's type > extensibility features. But this is not a replacement for a view, isn't it? With a view I can do this: create view v1 as select name, salary from workers where type <> 'MANAGEMENT'; with column permissions I must give access to all workers salary including the management, but not with a view. best regards, mario weilguni ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Interesting message about printf()'s in PostgreSQL
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: >> src/backend/port/dynloader/freebsd.c > This one is perhaps dodgy. You ahve this: > static char error_message[BUFSIZ]; > Then you have this: > sprintf(error_message, "dlopen (%s) not supported", file); > Where file isn't restricted in length I think... Yeah. In practice I'm not sure there's a problem --- the callers may all limit the filename string to MAXPGPATH, which is well below BUFSIZ. But changing the sprintf to snprintf is a cheap, localized way to be sure. 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] Interesting message about printf()'s in PostgreSQL
> I've just finished a quick grep through the backend sources for > "sprintf", and identified the following files as containing possible > problems: > src/backend/port/dynloader/freebsd.c This one is perhaps dodgy. You ahve this: static char error_message[BUFSIZ]; Then you have this: sprintf(error_message, "dlopen (%s) not supported", file); Where file isn't restricted in length I think... So does that mean if you go: CREATE FUNCTION blah AS '/home/chriskl/[9 characters here].so' LANGUAGE 'C'; Sort of thing you could crash it? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Tom Lane wrote: > Curt Sampson <[EMAIL PROTECTED]> writes: > >>On Sun, 11 Aug 2002, Don Baccus wrote: >> >>>Granulize GRANT to the table column level. >> > >>Can you please show me the code for that? > > > It's required by the SQL spec. PG hasn't got it, but the spec is > perfectly clear about how it should be done. > > I think this is really a bit irrelevant to the thread topic, though. As far as the last goes, not really. Curtis argues from false premises, and this is one. If it were the only false premise he argues from, sure, I'd agree it's irrelevant but sadly Curtis argues from false premises by default. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: > On Sun, 11 Aug 2002, Don Baccus wrote: > > >>>Oh? Ok, please translate the following into equivalant SQL that >>>does not use a view: >>>... >> >>Granulize GRANT to the table column level. > > > Can you please show me the code for that? After all, I showed you > all of my code when doing equivalants. Obviously it would require extending SQL, but since you in part argue that SQL sucks in regard to the relational model this shouldn't matter, right? You're arguing the superiority of the relational model as described by D&D over other models, non-relational SQL (which all agree has weaknesses) and most likely God. So don't flip-flop between the "oh, SQL sucks think about the relational model" and "SQL doesn't support that". Pick one or the other. Argue SQL or D&D/relational model. It's not hard to propose *extensions* to SQL that would allow granting of perms on a column rather than table level. > Or are you saying that it's syntactic sugar only in some imaginary > version of postgres that does not exist? Sort of like the idealized relational model that isn't implemented by SQL nor PG, but yet you reference again and again when it suits you to ignore the shortcomings of SQL92? Sure. Sorry, for a moment I thought you were interested in a meaningful discussion rather than a dick-waving contest but I was wrong. I give up. Your right hand waves your dick more frequently and with much more vigor than mine. This has nothing to do with with anything I care about, though. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
> > > Oh? Ok, please translate the following into equivalant SQL that > > > does not use a view: > > > ... > > Granulize GRANT to the table column level. > > Can you please show me the code for that? After all, I showed you > all of my code when doing equivalants. > > Or are you saying that it's syntactic sugar only in some imaginary > version of postgres that does not exist? MySQL has column permissions and I _think_ the sql standard has them as well. Chris ---(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] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson <[EMAIL PROTECTED]> writes: > On Sun, 11 Aug 2002, Don Baccus wrote: >> Granulize GRANT to the table column level. > Can you please show me the code for that? It's required by the SQL spec. PG hasn't got it, but the spec is perfectly clear about how it should be done. I think this is really a bit irrelevant to the thread topic, though. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
On Sun, 11 Aug 2002, Don Baccus wrote: > > Oh? Ok, please translate the following into equivalant SQL that > > does not use a view: > > ... > Granulize GRANT to the table column level. Can you please show me the code for that? After all, I showed you all of my code when doing equivalants. Or are you saying that it's syntactic sugar only in some imaginary version of postgres that does not exist? cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: > On Sun, 11 Aug 2002, Don Baccus wrote: > > >>I've been wanting to point out that SQL views are really, when >>scrutinized, "just syntactic sugar" ... > > > Oh? Ok, please translate the following into equivalant SQL that > does not use a view: > > CREATE TABLE t1 (key serial, value1 text, value2 text); > CREATE VIEW v1 AS SELECT key, value1 FROM t1; > GRANT SELECT ON v1 TO sorin; Granulize GRANT to the table column level. Then GRANT "SELECT" perms for the user on every column from the two tables that happen to be included in the view. Yes, it's awkward. So are the VIEW-based replacements for PG's type extensibility features. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] cash_out bug
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Is it worth starting a thread about it at this stage? It > is a pretty serious problem. I agree, but given the lack of movement over the last couple years, I'm not expecting a solution to suddenly emerge for 7.3 ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
On Sun, 11 Aug 2002, Don Baccus wrote: > I've been wanting to point out that SQL views are really, when > scrutinized, "just syntactic sugar" ... Oh? Ok, please translate the following into equivalant SQL that does not use a view: CREATE TABLE t1 (key serial, value1 text, value2 text); CREATE VIEW v1 AS SELECT key, value1 FROM t1; GRANT SELECT ON v1 TO sorin; cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cash_out bug
> The issue here is (once again) that we're overloading type oid 0
> ("opaque") to mean too many different, incompatible things. I've
> ranted about this before and will not repeat my previous remarks.
> The bottom line is that we need to eliminate "opaque" in favor of
> a set of pseudo-datatypes with different, crisply-defined semantics.
> We've had some discussions about it but no complete proposal has been
> made. Since eliminating "opaque" is going to break just about every
> extant user-defined datatype, I'm not in a hurry to do it until we
> can get it right the first time...
I guess if anyone were to make a complete proposal, it would have to be you
then methinks... Is it worth starting a thread about it at this stage? It
is a pretty serious problem.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: > The problem is, table inheritance is just syntatic sugar for creating > separate tables, and a view that does a UNION SELECT on them all > together, projecting only the common columns. I've been wanting to point out that SQL views are really, when scrutinized, "just syntactic sugar" ... -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(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] cash_out bug
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes:
> Is this a problem in that the functions are definined to return opaque (eg.
> PG_RETURN_VOID) but are then still usable in SELECT statements?
The issue here is (once again) that we're overloading type oid 0
("opaque") to mean too many different, incompatible things. I've
ranted about this before and will not repeat my previous remarks.
The bottom line is that we need to eliminate "opaque" in favor of
a set of pseudo-datatypes with different, crisply-defined semantics.
We've had some discussions about it but no complete proposal has been
made. Since eliminating "opaque" is going to break just about every
extant user-defined datatype, I'm not in a hurry to do it until we
can get it right the first time...
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] [SECURITY] DoS attack on backend possible (was: Re:
Yea, I added that TODO entry, and I am embarrased that a single cash_out call could crash the backend. I thought about not making this public knowledge, but making it public hasn't marshalled any forces to fix it so maybe I was wrong to put it on TODO. --- Gavin Sherry wrote: > On Mon, 12 Aug 2002, Justin Clift wrote: > > > Hi Chris, > > > > Christopher Kings-Lynne wrote: > > > > > > > > Still, I believe this should require a 7.2.2 release. Imagine a university > > > database server for a course for example - the students would just crash it > > > all the time. > > > > Hey yep, good point. > > > > Is this the only way that we know of non postgresql-superusers to be > > able to take out the server other than by extremely non-optimal, > > resource wasting queries? > > > > Check the TODO: > > You are now connected as new user s. > template1=> select cash_out(2); > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !> \q > [swm@laptop a]$ bin/psql template1 > psql: could not connect to server: Connection refused > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.3987"? > [swm@laptop a]$ > > --- > > Gavin > > > ---(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] Interesting message about printf()'s in PostgreSQL
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > ... Anyway, who cares about printfs > when stuff like select cash_out(2) is documented? Well, they're two different issues. The cash_out problem is intrinsically difficult to fix, and *will* break user-defined datatypes when we fix it --- so I'm not eager to rush in a half-baked fix. OTOH, sprintf overruns are usually localized fixes, and there's no excuse for letting one go once we've identified it. I've just finished a quick grep through the backend sources for "sprintf", and identified the following files as containing possible problems: src/backend/port/dynloader/freebsd.c src/backend/port/dynloader/netbsd.c src/backend/port/dynloader/nextstep.c src/backend/port/dynloader/openbsd.c src/include/libpq/pqcomm.h src/pl/plpgsql/src/pl_comp.c Will work on these. There are a lot of sprintf's in contrib/ as well; anyone care to eyeball those? Anyone want to look for other trouble spots? BTW, one should distinguish "an already-authorized user is able to force a database restart" from more dire conditions such as "any random cracker can get root on your box". I'm getting fairly tired of chicken-little warnings from people who should know better. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cash_out bug
> It turns out to be a far more serious bug than that, and is not limited > to cash_out. All these functions have the same problem: > > select proname from pg_proc where proargtypes=(select proargtypes from > pg_proc where proname='cash_in') and pronargs=1 and proisstrict='t' > order by proname; Is this a problem in that the functions are definined to return opaque (eg. PG_RETURN_VOID) but are then still usable in SELECT statements? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cash_out bug
I said: > It turns out to be a far more serious bug than that, and is not limited > to cash_out. All these functions have the same problem: > > With a few exceptions (the test(*) is long, as it requires one to wait for > the database to start after testing each function, but there are 16 out > of 107 functions(**) that cause backend crash). This should read "16 out of 107 that do _not_ cause backend crash". -- Alvaro Herrera () "Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cash_out bug
Hello: I was investigating the bug about "select cash_out(2)" crashing the backend. I thought fixing was a simple matter of checking whether some argument to the function was NULL or not. I added a NULL checking, but it obviously is not triggered, because the data received is not NULL, but a non-pointer integer that cannot be dereferenced (doing so cause a segmentation fault). It turns out to be a far more serious bug than that, and is not limited to cash_out. All these functions have the same problem: select proname from pg_proc where proargtypes=(select proargtypes from pg_proc where proname='cash_in') and pronargs=1 and proisstrict='t' order by proname; With a few exceptions (the test(*) is long, as it requires one to wait for the database to start after testing each function, but there are 16 out of 107 functions(**) that cause backend crash). The problem is related to PG_GETARG_POINTER in some way or another; the dereferencing of a non-pointer integer makes the backend crash. I, however, do not know how to fix this. I'm just pointing out this problem so someone more knowledgeable than me with this pointer mess can figure a way out. (*) The test is SELECT="select proname from pg_proc where proargtypes='' and pronargs=1 and proisstrict='t' order by proname;" for i in `psql regression -c "$SELECT"`; do echo $i >> func_test psql regression -c "select $i(1);" >> func_test 2>&1 sleep 9 done (**) Those are: boolout charout cidout date_out int2out int4out nabstimeout oidout regclassout regoperatorout regoperout regprocedureout regprocout regtypeout reltimeout smgrout xidout -- Alvaro Herrera () "The Gord often wonders why people threaten never to come back after they've been told never to return" (www.actsofgord.com) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Interesting message about printf()'s in PostgreSQL
> I see one unsubstantiated allegation about PG intermixed with a ton > of content-free navel-gazing. Don't waste my time. For instance, when I submitted patches for fulltextindex 7.2 it freely used unchecked sprintf's everywhere. Even now I'm not sure what'll happen if a malicious user really tried to crash it. Anyway, who cares about printfs when stuff like select cash_out(2) is documented? > I have no doubt that some problems remain (cf recent agonizing over > whether there is a buffer overrun problem in the date parser) ... > but unspecific rumors don't help anyone. As always, the best form of > criticism is a diff -c patch. Maybe we could form a bunch of people on this list interested in checking for security issues and fixing them. I'd be in, time be willing... Chris ---(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] Interesting message about printf()'s in PostgreSQL
On Mon, 12 Aug 2002, Justin Clift wrote: > Hi everyone, > > Whilst looking around for some more PostgreSQL related stuff, this > message turned up: > > http://mail.wirex.com/pipermail/sardonix/2002-February/51.html > > The interesting bit is in an email messages included about halfway > down. It speaks of Bad Things in the PostgreSQL source code and of > PostgreSQL needing an audit. Christoper's point about University access to postgres and possible security/DoS problems is a good one. A thorough security audit of PostgreSQL would be a very good idea. Naturally, the biggest problems are that it is very time consuming to do an audit, just about all parts of the code need to be reviewed and it yields few exciting results. Perhaps Red Hat or another commercial entity would be interested in helping out given that the commercial space is beginning to be dominated by security rhetoric? Gavin ---(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] Interesting message about printf()'s in PostgreSQL
Justin Clift <[EMAIL PROTECTED]> writes: > Whilst looking around for some more PostgreSQL related stuff, this > message turned up: > http://mail.wirex.com/pipermail/sardonix/2002-February/51.html I see one unsubstantiated allegation about PG intermixed with a ton of content-free navel-gazing. Don't waste my time. There was some considerable effort awhile back towards eliminating unsafe printfs in favor of snprintfs and similar constructs; I doubt that the comments in that message postdate that effort. I have no doubt that some problems remain (cf recent agonizing over whether there is a buffer overrun problem in the date parser) ... but unspecific rumors don't help anyone. As always, the best form of criticism is a diff -c patch. 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
[HACKERS] Interesting message about printf()'s in PostgreSQL
Hi everyone, Whilst looking around for some more PostgreSQL related stuff, this message turned up: http://mail.wirex.com/pipermail/sardonix/2002-February/51.html The interesting bit is in an email messages included about halfway down. It speaks of Bad Things in the PostgreSQL source code and of PostgreSQL needing an audit. Any idea if the things mentioned in this are true? :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
> So my initial thinking is that this is a profound problem. But after a little > more thought, I can make the question_id field of the question table be a > SERIAL type and the primary key. That way, when I insert rows into either > the position question or the binary question table, it will be picking the > values out of the same sequence. I won't have actual primary key integrity > checking, but I'm fairly safe in assuming that it won't be a problem. > > Then my second thought was, perhaps I could write some sort of CHECK procedure > which would verify integrity by hand between the two tables. Or perhaps I > could manually state that the primary key was the question_id field when > creating both the child tables. I'm really not sure if any of these > approaches will work, or which one is best to do. > > So now that I hear there is a way to get from an object-relational solution to > a solution using views, I'd like to know how to do it in general or perhaps > with my particular problem. The problem is, table inheritance is just syntatic sugar for creating separate tables, and a view that does a UNION SELECT on them all together, projecting only the common columns. You want to go the other way around, with a setup like this. table question contains: question id - a unique identifier for each question question_type - binary or position common attributes of binary and position questions table binary_question_data contains: question id - references question table attributes belonging only to binary questions table position_question_data contains: question id - references question table attributes belonging only to position questions If you need frequently to select just binary or just position questions, you can create a pair of views to deal with them, along the lines of CREATE VIEW binary_question AS SELECT question.question_id, ... FROM question, binary_question_data WHERE question.question_id = binary_question.question_id AND question.question_type = 'B' Now you have two data integrity guarantees that you didn't have with table inheritance: two different questions cannot have the same question_id, and a question can never be both a position question and a binary question. > I'm a big fan of OOP, as are the other people working with me on this > project, As am I. That's why I use, for example, Java and Ruby rather than C and perl. > so I would (personally) rather work around the existing inheritance > mechanism Well, an inheritance mechanism alone does not OO make. Please don't think that table inheritance is OO; it's not. > than implement a solution I probably won't understand later > using views, though I'd like to know it also... what is your advice? The implementation with views is standard, very basic relational stuff. Primary keys, foreign keys, and joins. If you do not understand it, I would strongly encouarge you to study it until you do, because you are going to be using this stuff all the time if you use databases. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] [SECURITY] DoS attack on backend possible (was: Re:
On Mon, 12 Aug 2002, Justin Clift wrote: > Hi Chris, > > Christopher Kings-Lynne wrote: > > > > > Still, I believe this should require a 7.2.2 release. Imagine a university > > database server for a course for example - the students would just crash it > > all the time. > > Hey yep, good point. > > Is this the only way that we know of non postgresql-superusers to be > able to take out the server other than by extremely non-optimal, > resource wasting queries? > Check the TODO: You are now connected as new user s. template1=> select cash_out(2); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> \q [swm@laptop a]$ bin/psql template1 psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.3987"? [swm@laptop a]$ --- Gavin ---(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] [SECURITY] DoS attack on backend possible (was: Re:
> Hey yep, good point. > > Is this the only way that we know of non postgresql-superusers to be > able to take out the server other than by extremely non-optimal, > resource wasting queries? > > If we release a 7.2.2 because of this, can we be pretty sure we have a > "no known vulnerabilities" release, or are there other small holes which > should be fixed too? What about that "select cash_out(2) crashes because of opaque" entry in the TODO? That really needs to be fixed. I was talking to a CS lecturer about switching to postgres from oracle when 7.3 comes out and all he said was "how easily is it hacked?". He says their systems are the most constantly bombarded in universities. What could I say? That any unprivileged user can just go 'select cash_out(2)' to DOS the backend? Chris ---(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] [SECURITY] DoS attack on backend possible (was: Re:
Hi Chris, Christopher Kings-Lynne wrote: > > Still, I believe this should require a 7.2.2 release. Imagine a university > database server for a course for example - the students would just crash it > all the time. Hey yep, good point. Is this the only way that we know of non postgresql-superusers to be able to take out the server other than by extremely non-optimal, resource wasting queries? If we release a 7.2.2 because of this, can we be pretty sure we have a "no known vulnerabilities" release, or are there other small holes which should be fixed too? :-) Regards and best wishes, Justin Clift > Chris -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
> Justin Clift <[EMAIL PROTECTED]> writes: > > Am I understanding this right: > > - A PostgreSQL 7.2.1 server can be crashed if it gets passed certain > > date values which would be accepted by standard "front end" parsing? > > AFAIK it's a buffer overrun issue, so anything that looks like a > reasonable date would *not* cause the problem. Still, I believe this should require a 7.2.2 release. Imagine a university database server for a course for example - the students would just crash it all the time. Chris ---(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] python patch
> Not a problem. I would rather them be correct. > > Worth noting that the first patch is what attempts to fix the long -> > int overflow issue. The second patch attempts to resolve "attisdropped" > column use issues with the python scripts. The third patch addresses > issues generated by the implicate to explicate use of "cascade". > > I assume your reservations are only with the second patch and not the > first and third patches? Correct. I'm pretty sure you don't need to exclude attisdropped from the primary key list because all it's doing is finding the column that a primary key is over and that should never be over a dropped column. I can't remember what you said the second query did? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
> Now, in the multibyte case, again in textlen(), I see: > >/* optimization for single byte encoding */ >if (pg_database_encoding_max_length() <= 1) > PG_RETURN_INT32(VARSIZE(t) - VARHDRSZ); > >PG_RETURN_INT32( >pg_mbstrlen_with_len(VARDATA(t), VARSIZE(t) - VARHDRSZ)); > > Three questions here. > 1) In the case of encoding max length == 1, can we treat it the same as > the non-multibyte case (I presume they are exactly the same)? Yes. > 2) Can encoding max length ever be < 1? Doesn't make sense to me. No. It seems just a defensive coding. > 3) In the case of encoding max length > 1, if I understand correctly, > each encoded character can be one *or more* bytes, up to and encluding > encoding max length bytes. Right. > So the *only* way presently to get the length > of the original character string is to loop through the entire string > checking the length of each individual character (that's what > pg_mbstrlen_with_len() does it seems)? Yes. > Finally, if 3) is true, then there is no way to avoid the retrieval and > decompression of the datum just to find out its length. For large > datums, detoasting plus the looping through each character would add a > huge amount of overhead just to get at the length of the original > string. I don't know if we need to be able to get *just* the length > often enough to really care, but if we do, I had an idea for some future > release (I wouldn't propose doing this for 7.3): > > - add a new EXTENDED state to va_external for MULTIBYTE > - any string with max encoding length > 1 would be EXTENDED even if it >is not EXTERNAL and not COMPRESSED. > - to each of the structs in the union, add va_strlen > - populate va_strlen on INSERT and maintain it on UPDATE. > > Now a new function similar to toast_raw_datum_size(), maybe > toast_raw_datum_strlen() could be used to get the original string > length, whether MB or not, without needing to retrieve and decompress > the entire datum. > > I understand we would either: have to steal another bit from the VARHDR > which would reduce the effective size of a valena from 1GB down to .5GB; > or we would need to add a byte or two to the VARHDR which is extra > per-datum overhead. I'm not sure we would want to do either. But I > wanted to toss out the idea while it was fresh on my mind. Interesting idea. I also was thinking about adding some extra infomation to text data types such as character set, collation etc. for 7.4 or later. -- Tatsuo Ishii ---(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] Table Inheritance Discussion
On Sun, 11 Aug 2002, Don Baccus wrote: > It's just type extensibility, really. Yeah. > As to why, again there's an efficiency argument, as I said earlier some > joins can be avoided given PG's implementation of this feature: > [TI and relational examples deleted] What you gave is not the relational equivalant of the TI case as implemented in postgres. Modeled correctly, you should be creating a table for the child, and a view for the parent. Then you will find that the relational definition uses or avoids joins exactly where the TI definition does. > There's also some error checking (using my inherited example): The relational definition doesn't force the dependency, but as you can delete and recreate the view at will without data loss, the amount of safety is the same. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Table Inheritance Discussion
Curt Sampson wrote: > The last question comes up because, during the conversation up to this > point, we seem to have implicitly accepted that table inheritance is > an "object-oriented" way of doing things. Thinking further on this, > however, I've decided that it's not in fact object-oriented at all. It's just type extensibility, really. As to why, again there's an efficiency argument, as I said earlier some joins can be avoided given PG's implementation of this feature: dotlrn=# create table foo(i integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE dotlrn=# create table bar(j integer) inherits (foo); CREATE dotlrn=# explain select * from bar; NOTICE: QUERY PLAN: Seq Scan on bar (cost=0.00..20.00 rows=1000 width=8) EXPLAIN ... dotlrn=# create table foo(i integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE dotlrn=# create table bar(i integer references foo primary key, j integer); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'bar_pkey' for table 'bar' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE dotlrn=# create view foobar as select foo.*, bar.j from foo, bar; CREATE dotlrn=# explain select * from foobar; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..30020.00 rows=100 width=8) -> Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) -> Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4) EXPLAIN There's also some error checking (using my inherited example): dotlrn=# drop table foo; ERROR: Relation "bar" inherits from "foo" dotlrn=# Which doesn't exist in the view approach in PG at least (I'm unclear on standard SQL92 and of course this says nothing about the relational model in theory, just PG and perhaps SQL92 in practice). -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Table Inheritance Discussion
On Thu, 8 Aug 2002, Jordan Henderson wrote: > I think what would be useful is to discuss the theory part. As do I. > - Date has 3, however his most current work is dated 2000, The Third > Manifesto SECOND EDITION. This is actually Date and Darwen. I think we should also add Date's _An Introduction to Database Systems, 7th Edition_, as it covers some relational stuff in more detail than than _The Third Manifesto_. For example, it investigates the details of automatic view updatability, which came up during this discussion, and which most books just completely cop out on. (For example, _Database System Concepts_ just points out a couple of problems with view updatability and says, "Because of problems such as these, modifications are generally not permitted on view relations, except in limited cases.") > - Silberschatz, Korth, Sudarshan, A book I am sure we have all read, > Database System Concepts - Third Edition. > ... > In any case, we should use the current editions of these books, not > something the author has reconsidered, re-written, and published again. In that case we ought to use the fourth edition of this book. Here are some questions I'd like to see people answer or propose answers to: 1. What models of table inheritance have been proposed, and how do they differ? 2. What models of table inheritance are actually implemented in currently available database systems? 3. What are the advantages of describing something using table inheritance rather than an equivalant relational description? 4. If you think table inheritance is "object oriented," why do you think so. 5. How ought we to fix the table inheritance in postgres? The last question comes up because, during the conversation up to this point, we seem to have implicitly accepted that table inheritance is an "object-oriented" way of doing things. Thinking further on this, however, I've decided that it's not in fact object-oriented at all. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [COMMITTERS] pgsql-server/ oc/src/sgml/release.sgml rc/back ...
Tom Lane dijo: > CVSROOT: /cvsroot > Module name: pgsql-server > Changes by: [EMAIL PROTECTED] 02/08/11 17:17:35 > > Log message: > Code review of CLUSTER patch. Clean up problems with relcache getting > confused, toasted data getting lost, etc. Whoa. That's a whole lot of code review. Thank you again. I couldn't have done all that. As Christopher said some time ago, it's humbling to see how much work was still needed. -- Alvaro Herrera () "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains and Indexes
Rod Taylor <[EMAIL PROTECTED]> writes: > However, I have to wonder why GetDefaultOpClass doesn't simply use the > first Binary Compatible opclass. Because that would completely destroy the point of having multiple opclasses for a datatype: you'd only do so if they *act different*. Therefore, having the system choose one at random is a Bad Idea(tm). 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])
[HACKERS] Domains and Indexes
Appears there is a problem finding the opclass when indexing a domain. CREATE DOMAIN newint as int4; CREATE TABLE tab (col newint unique); ERROR: data type newint has no default operator class for access method "btree" You must specify an operator class for the index or define a default operator class for the data type Specifically, GetDefaultOpClass() finds 0 exact matches and 3 binary compatible matches. Fetching getBaseType() of the attribute fixes the problem for domains (see attachment). However, I have to wonder why GetDefaultOpClass doesn't simply use the first Binary Compatible opclass. When there is more than one usable it doesn't do anything useful. Index: src/backend/commands/indexcmds.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/indexcmds.c,v retrieving revision 1.80 diff -c -r1.80 indexcmds.c *** src/backend/commands/indexcmds.c 2002/08/02 18:15:06 1.80 --- src/backend/commands/indexcmds.c 2002/08/11 21:14:13 *** *** 482,487 --- 482,490 Oid exactOid = InvalidOid; Oid compatibleOid = InvalidOid; + /* It could be a domain */ + attrType = getBaseType(attrType); + /* * We scan through all the opclasses available for the access method, * looking for one that is marked default and matches the target type ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
Tatsuo Ishii wrote: >>Any objection if I rework this function to meet SQL92 and fix the bug? > I don't object. One more question on this: how can I generate some characters with > 1 encoding length? I need a way to test the work I'm doing, and I'm not quite sure how to test it. Just making a database that uses a MB encoding doesn't make 0-9/A-Z/a-z into characters of > 1 byte, does it? Sorry, but never having used MB encoding has left me a bit clueless on this ;-) Joe ---(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] python patch
OK, great to have people reviewing them. I will hold on all the python
patches until I hear back from Christopher:
http://candle.pha.pa.us/cgi-bin/pgpatches
---
Greg Copeland wrote:
Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> Not a problem. I would rather them be correct.
>
> Worth noting that the first patch is what attempts to fix the long ->
> int overflow issue. The second patch attempts to resolve "attisdropped"
> column use issues with the python scripts. The third patch addresses
> issues generated by the implicate to explicate use of "cascade".
>
> I assume your reservations are only with the second patch and not the
> first and third patches?
>
> Greg
>
>
> On Sun, 2002-08-11 at 04:43, Christopher Kings-Lynne wrote:
> > I wouldn't apply this _just_ yet Bruce as I'm not certain all the changes
> > are necessary... I intend to look into it but I haven't had the time yet
> > (sorry Greg!)
> >
> > Chris
> >
> >
> > On Sun, 11 Aug 2002, Bruce Momjian wrote:
> >
> > >
> > > Your patch has been added to the PostgreSQL unapplied patches list at:
> > >
> > > http://candle.pha.pa.us/cgi-bin/pgpatches
> > >
> > > I will try to apply it within the next 48 hours.
> > >
> > > ---
> > >
> > >
> > > Greg Copeland wrote:
> > >
> > Checking application/pgp-signature: FAILURE
> > > -- Start of PGP signed section.
> > > > Well, that certainly appeared to be very straight forward. pg.py and
> > > > syscat.py scripts were both modified. pg.py uses it to cache a list of
> > > > pks (which is seemingly does for every db connection) and various
> > > > attributes. syscat uses it to walk the list of system tables and
> > > > queries the various attributes from these tables.
> > > >
> > > > In both cases, it seemingly makes sense to apply what you've requested.
> > > >
> > > > Please find attached the quested patch below.
> > > >
> > > > Greg
> > > >
> > > >
> > > > On Wed, 2002-08-07 at 22:16, Christopher Kings-Lynne wrote:
> > > > > > I don't have a problem looking into it but I can't promise I can get it
> > > > > > right. My python skills are fairly good...my postgres internal skills
> > > > > > are still sub-par IMO.
> > > > > >
> > > > > > From a cursory review, if attisdropped is true then the attribute/column
> > > > > > should be ignored/skipped?! Seems pretty dang straight forward.
> > > > >
> > > > > Basically, yep. Just grep the source code for pg_attribute most likely...
> > > > >
> > > > > I'm interested in knowing what it uses pg_attribute for as well...?
> > > > >
> > > > > Chris
> > > > >
> > > > >
> > > > > ---(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
> > > >
> > >
> > > [ text/x-patch is unsupported, treating like TEXT/PLAIN ]
> > >
> > > > Index: pg.py
> > > > ===
> > > > RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/pg.py,v
> > > > retrieving revision 1.9
> > > > diff -u -r1.9 pg.py
> > > > --- pg.py 2002/03/19 13:20:52 1.9
> > > > +++ pg.py 2002/08/08 03:29:48
> > > > @@ -69,7 +69,8 @@
> > > > WHERE pg_class.oid =
>pg_attribute.attrelid AND
> > > > pg_class.oid =
>pg_index.indrelid AND
> > > > pg_index.indkey[0] =
>pg_attribute.attnum AND
> > > > - pg_index.indisprimary
>= 't'""").getresult():
> > > > + pg_index.indisprimary
>= 't' AND
> > > > +
>pg_attribute.attisdropped = 'f'""").getresult():
> > > > self.__pkeys__[rel] = att
> > > >
> > > > # wrap query for debugging
> > > > @@ -111,7 +112,8 @@
> > > > WHERE pg_class.relname = '%s' AND
> > > > pg_attribute.attnum > 0 AND
> > > > pg_attribute.attrelid =
>pg_class.oid AND
> > > > - pg_attribute.atttypid =
>pg_type.oid"""
> > > > + pg_attribute.atttypid =
>pg_type.oid AND
> > > > + pg_attribute.attisdropped =
>'f'"""
> > > >
> > > > l = {}
> > > > for attname, typname in self.db.query(query % cl).getresult():
> > > > Index: tutorial/syscat.py
> > > > =
Re: [HACKERS] libpqxx
Peter Eisentraut <[EMAIL PROTECTED]> writes: > The problem I see now is that libpqxx has a completely different build > system and documentation system. Unless someone's going to do the work to integrate libpqxx into our build/documentation system, I have to agree with Peter: it should not be part of our core distribution. Since Marc's been moaning about bloat, I'm sure he'll vote for pulling both libpq++ and libpqxx from the core distro and making them separate packages. This would be okay with me. However: making libpq++ buildable standalone would take some work too. Any way you slice it, we need some work here... any volunteers? 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] [SECURITY] DoS attack on backend possible (was: Re:
Hi Florian, Very hard call. If this was even a "fringe case" whereby even only a few places that are doing "the right thing" would be compromisable, then we should probably go for a 7.2.2. Even if it's only 7.2.1 with this one bug fix. However, it sounds like this bug is really only going to affect those places which aren't correctly implementing *proper*, *decent* input validation, and are then passing this not-properly-checked value straight into a SQL string for execution by the server. Doing that (not input checking properly) is a brain damaged concept all by itself. :( Is this scenario of not properly checking the input the only way PostgreSQL could be crashed by this bug In Real Life? Having said this, is this what 7.2.2 here would require doing: - Create an archive of 7.2.1+bugfix, and call it 7.2.2, gzip, md5, etc, as appropriate, put on site - Update CVS appropriately - Create a new press release for 7.2.2, spread that appropriately too - Add an entry to the main website I reckon the only reason for making a 7.2.2 for this would be to help ensure newbie (or very tired) coders don't get their servers taken out by clueful malicious types. Regards and best wishes, Justin Clift Florian Weimer wrote: > > Justin Clift <[EMAIL PROTECTED]> writes: > > > - A PostgreSQL 7.2.1 server can be crashed if it gets passed certain > > date values which would be accepted by standard "front end" parsing? > > So, a web application layer can request a date from a user, do standard > > integrity checks (like looking for weird characters and formatting > > hacks) on the date given, then use the date as part of a SQL query, and > > PostgreSQL will die? > > It depends on the checking. If you just check that the date consists > of digits (and a few additional characters), it's possible to crash > the server. > > -- > Florian Weimer[EMAIL PROTECTED] > University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ > RUS-CERT fax +49-711-685-5898 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] libpqxx
We still haven't really decided what to do about libpqxx. The only argument I've heard so far against distributing it separately is that it would induce users to use libpq++ instead. I think having both libraries in the distribution is going to be even more confusing, especially since one is "old and well-tested" and one is brand new. The problem I see now is that libpqxx has a completely different build system and documentation system. This is also not going to help users find and use it and it's also going to be a maintenance headache. I don't necessarily want libpqxx to change it, but I feel it would be better off maintained separately. I wouldn't mind if we package libpq++ separately as well and tell users that we have these two libraries and they can pick one. And before someone suggests an --enable-libpqxx option: That's not the solution to these problems, it's only a way to hide them. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
Justin Clift <[EMAIL PROTECTED]> writes: > - A PostgreSQL 7.2.1 server can be crashed if it gets passed certain > date values which would be accepted by standard "front end" parsing? > So, a web application layer can request a date from a user, do standard > integrity checks (like looking for weird characters and formatting > hacks) on the date given, then use the date as part of a SQL query, and > PostgreSQL will die? It depends on the checking. If you just check that the date consists of digits (and a few additional characters), it's possible to crash the server. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
Tatsuo Ishii wrote: >>Any objection if I rework this function to meet SQL92 and fix the bug? > I've started working on text_substr() as described in this thread (which is hopefully prep work for the replace() function that started the thread). I haven't really looked at toast or multibyte closely before, so I'd like to ask a couple of questions to be sure I'm understanding the relevant issues correctly. First, in textlen() I see (ignoring multibyte for a moment): text *t = PG_GETARG_TEXT_P(0); PG_RETURN_INT32(VARSIZE(t) - VARHDRSZ); Tom has pointed out to me before that PG_GETARG_TEXT_P(n) incurs the overhead of retrieving and possibly decompressing a toasted datum. So my first question is, can we simply do: PG_RETURN_INT32(toast_raw_datum_size(PG_GETARG_DATUM(0)) - VARHDRSZ); and save the overhead of retrieving and decompressing the whole datum? Now, in the multibyte case, again in textlen(), I see: /* optimization for single byte encoding */ if (pg_database_encoding_max_length() <= 1) PG_RETURN_INT32(VARSIZE(t) - VARHDRSZ); PG_RETURN_INT32( pg_mbstrlen_with_len(VARDATA(t), VARSIZE(t) - VARHDRSZ)); Three questions here. 1) In the case of encoding max length == 1, can we treat it the same as the non-multibyte case (I presume they are exactly the same)? 2) Can encoding max length ever be < 1? Doesn't make sense to me. 3) In the case of encoding max length > 1, if I understand correctly, each encoded character can be one *or more* bytes, up to and encluding encoding max length bytes. So the *only* way presently to get the length of the original character string is to loop through the entire string checking the length of each individual character (that's what pg_mbstrlen_with_len() does it seems)? Finally, if 3) is true, then there is no way to avoid the retrieval and decompression of the datum just to find out its length. For large datums, detoasting plus the looping through each character would add a huge amount of overhead just to get at the length of the original string. I don't know if we need to be able to get *just* the length often enough to really care, but if we do, I had an idea for some future release (I wouldn't propose doing this for 7.3): - add a new EXTENDED state to va_external for MULTIBYTE - any string with max encoding length > 1 would be EXTENDED even if it is not EXTERNAL and not COMPRESSED. - to each of the structs in the union, add va_strlen - populate va_strlen on INSERT and maintain it on UPDATE. Now a new function similar to toast_raw_datum_size(), maybe toast_raw_datum_strlen() could be used to get the original string length, whether MB or not, without needing to retrieve and decompress the entire datum. I understand we would either: have to steal another bit from the VARHDR which would reduce the effective size of a valena from 1GB down to .5GB; or we would need to add a byte or two to the VARHDR which is extra per-datum overhead. I'm not sure we would want to do either. But I wanted to toss out the idea while it was fresh on my mind. Thanks, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
Justin Clift <[EMAIL PROTECTED]> writes: > Am I understanding this right: > - A PostgreSQL 7.2.1 server can be crashed if it gets passed certain > date values which would be accepted by standard "front end" parsing? AFAIK it's a buffer overrun issue, so anything that looks like a reasonable date would *not* cause the problem. 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] [SECURITY] DoS attack on backend possible (was: Re:
Hi Florian, Am I understanding this right: - A PostgreSQL 7.2.1 server can be crashed if it gets passed certain date values which would be accepted by standard "front end" parsing? So, a web application layer can request a date from a user, do standard integrity checks (like looking for weird characters and formatting hacks) on the date given, then use the date as part of a SQL query, and PostgreSQL will die? ? Regards and best wishes, Justin Clift Florian Weimer wrote: > > Justin Clift <[EMAIL PROTECTED]> writes: > > > Is it possible to crash a 7.2.1 backend without having an entry in the > > pg_hba.conf file? > > No, but think of web applications and things like that. The web > frontend might pass in a date string which crashes the server backend. > Since the crash can be triggered by mere data, an attacker does not > have to be able to send specific SQL statements to the server. > > -- > Florian Weimer[EMAIL PROTECTED] > University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ > RUS-CERT fax +49-711-685-5898 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
Michael Meskes <[EMAIL PROTECTED]> writes: > On Tue, Jul 30, 2002 at 11:50:38PM -0400, Bruce Momjian wrote: >> ecpg and bison issues - solved? > Not solved yet. And it's just a matter of time until we run into it with > the main parser grammar file as well. Yeah, I've been worrying about that too. Any idea how close we are to trouble in the main grammar? > Bison upstream is working on > removing all those short ints, but I have yet to receive a version that > compiles ecpg grammar correctly. If no solution is forthcoming, we might have to adopt plan B: find another parser-generator tool. Whilst googling for bison info I came across "Why Bison is Becoming Extinct" http://www.acm.org/crossroads/xrds7-5/bison.html which is a tad amusing at least. Now, it's anyone's guess whether any of the tools he suggests are actually ready for prime time; they might have practical limits much worse than bison's. But I got awfully frustrated yesterday trying (once again) to get bison to allow a schema-qualified type name in the syntax . I'm just about ready to consider alternatives. Plan C would be to devote some work to minimizing the number of states in the main grammar (I assume it's number of states that's the problem). I doubt anyone's ever tried, so there might be enough low-hanging fruit to get ecpg off the hook for awhile. 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] [SECURITY] DoS attack on backend possible (was: Re:
Justin Clift <[EMAIL PROTECTED]> writes: > Is it possible to crash a 7.2.1 backend without having an entry in the > pg_hba.conf file? No, but think of web applications and things like that. The web frontend might pass in a date string which crashes the server backend. Since the crash can be triggered by mere data, an attacker does not have to be able to send specific SQL statements to the server. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] python patch
Not a problem. I would rather them be correct.
Worth noting that the first patch is what attempts to fix the long ->
int overflow issue. The second patch attempts to resolve "attisdropped"
column use issues with the python scripts. The third patch addresses
issues generated by the implicate to explicate use of "cascade".
I assume your reservations are only with the second patch and not the
first and third patches?
Greg
On Sun, 2002-08-11 at 04:43, Christopher Kings-Lynne wrote:
> I wouldn't apply this _just_ yet Bruce as I'm not certain all the changes
> are necessary... I intend to look into it but I haven't had the time yet
> (sorry Greg!)
>
> Chris
>
>
> On Sun, 11 Aug 2002, Bruce Momjian wrote:
>
> >
> > Your patch has been added to the PostgreSQL unapplied patches list at:
> >
> > http://candle.pha.pa.us/cgi-bin/pgpatches
> >
> > I will try to apply it within the next 48 hours.
> >
> > ---
> >
> >
> > Greg Copeland wrote:
> >
> Checking application/pgp-signature: FAILURE
> > -- Start of PGP signed section.
> > > Well, that certainly appeared to be very straight forward. pg.py and
> > > syscat.py scripts were both modified. pg.py uses it to cache a list of
> > > pks (which is seemingly does for every db connection) and various
> > > attributes. syscat uses it to walk the list of system tables and
> > > queries the various attributes from these tables.
> > >
> > > In both cases, it seemingly makes sense to apply what you've requested.
> > >
> > > Please find attached the quested patch below.
> > >
> > > Greg
> > >
> > >
> > > On Wed, 2002-08-07 at 22:16, Christopher Kings-Lynne wrote:
> > > > > I don't have a problem looking into it but I can't promise I can get it
> > > > > right. My python skills are fairly good...my postgres internal skills
> > > > > are still sub-par IMO.
> > > > >
> > > > > From a cursory review, if attisdropped is true then the attribute/column
> > > > > should be ignored/skipped?! Seems pretty dang straight forward.
> > > >
> > > > Basically, yep. Just grep the source code for pg_attribute most likely...
> > > >
> > > > I'm interested in knowing what it uses pg_attribute for as well...?
> > > >
> > > > Chris
> > > >
> > > >
> > > > ---(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
> > >
> >
> > [ text/x-patch is unsupported, treating like TEXT/PLAIN ]
> >
> > > Index: pg.py
> > > ===
> > > RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/pg.py,v
> > > retrieving revision 1.9
> > > diff -u -r1.9 pg.py
> > > --- pg.py 2002/03/19 13:20:52 1.9
> > > +++ pg.py 2002/08/08 03:29:48
> > > @@ -69,7 +69,8 @@
> > > WHERE pg_class.oid =
>pg_attribute.attrelid AND
> > > pg_class.oid =
>pg_index.indrelid AND
> > > pg_index.indkey[0] =
>pg_attribute.attnum AND
> > > - pg_index.indisprimary =
>'t'""").getresult():
> > > + pg_index.indisprimary = 't' AND
> > > + pg_attribute.attisdropped =
>'f'""").getresult():
> > > self.__pkeys__[rel] = att
> > >
> > > # wrap query for debugging
> > > @@ -111,7 +112,8 @@
> > > WHERE pg_class.relname = '%s' AND
> > > pg_attribute.attnum > 0 AND
> > > pg_attribute.attrelid = pg_class.oid
>AND
> > > - pg_attribute.atttypid = pg_type.oid"""
> > > + pg_attribute.atttypid = pg_type.oid AND
> > > + pg_attribute.attisdropped = 'f'"""
> > >
> > > l = {}
> > > for attname, typname in self.db.query(query % cl).getresult():
> > > Index: tutorial/syscat.py
> > > ===
> > > RCS file:
>/projects/cvsroot/pgsql-server/src/interfaces/python/tutorial/syscat.py,v
> > > retrieving revision 1.7
> > > diff -u -r1.7 syscat.py
> > > --- tutorial/syscat.py2002/05/03 14:21:38 1.7
> > > +++ tutorial/syscat.py2002/08/08 03:29:48
> > > @@ -37,7 +37,7 @@
> > > FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> > > WHERE i.indrelid = bc.oid AND i.indexrelid = bc.oid
> > > AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
> > > - AND i.indproc = '0'::oid
> > > +
Re: [HACKERS] pg_stat_reset() weirdness
Christopher Kings-Lynne wrote: > unique_oids script Look in src/include/catalog. You'll find duplicate_oids & unused_oids shell scripts. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
On Tue, Jul 30, 2002 at 11:50:38PM -0400, Bruce Momjian wrote: > ecpg and bison issues - solved? Not solved yet. And it's just a matter of time until we run into it with the main parser grammar file as well. Bison upstream is working on removing all those short ints, but I have yet to receive a version that compiles ecpg grammar correctly. No idea, if this will be fixed in the next month. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] python patch
I wouldn't apply this _just_ yet Bruce as I'm not certain all the changes
are necessary... I intend to look into it but I haven't had the time yet
(sorry Greg!)
Chris
On Sun, 11 Aug 2002, Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> ---
>
>
> Greg Copeland wrote:
>
Checking application/pgp-signature: FAILURE
> -- Start of PGP signed section.
> > Well, that certainly appeared to be very straight forward. pg.py and
> > syscat.py scripts were both modified. pg.py uses it to cache a list of
> > pks (which is seemingly does for every db connection) and various
> > attributes. syscat uses it to walk the list of system tables and
> > queries the various attributes from these tables.
> >
> > In both cases, it seemingly makes sense to apply what you've requested.
> >
> > Please find attached the quested patch below.
> >
> > Greg
> >
> >
> > On Wed, 2002-08-07 at 22:16, Christopher Kings-Lynne wrote:
> > > > I don't have a problem looking into it but I can't promise I can get it
> > > > right. My python skills are fairly good...my postgres internal skills
> > > > are still sub-par IMO.
> > > >
> > > > From a cursory review, if attisdropped is true then the attribute/column
> > > > should be ignored/skipped?! Seems pretty dang straight forward.
> > >
> > > Basically, yep. Just grep the source code for pg_attribute most likely...
> > >
> > > I'm interested in knowing what it uses pg_attribute for as well...?
> > >
> > > Chris
> > >
> > >
> > > ---(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
> >
>
> [ text/x-patch is unsupported, treating like TEXT/PLAIN ]
>
> > Index: pg.py
> > ===
> > RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/pg.py,v
> > retrieving revision 1.9
> > diff -u -r1.9 pg.py
> > --- pg.py 2002/03/19 13:20:52 1.9
> > +++ pg.py 2002/08/08 03:29:48
> > @@ -69,7 +69,8 @@
> > WHERE pg_class.oid =
>pg_attribute.attrelid AND
> > pg_class.oid =
>pg_index.indrelid AND
> > pg_index.indkey[0] =
>pg_attribute.attnum AND
> > - pg_index.indisprimary =
>'t'""").getresult():
> > + pg_index.indisprimary = 't' AND
> > + pg_attribute.attisdropped =
>'f'""").getresult():
> > self.__pkeys__[rel] = att
> >
> > # wrap query for debugging
> > @@ -111,7 +112,8 @@
> > WHERE pg_class.relname = '%s' AND
> > pg_attribute.attnum > 0 AND
> > pg_attribute.attrelid = pg_class.oid
>AND
> > - pg_attribute.atttypid = pg_type.oid"""
> > + pg_attribute.atttypid = pg_type.oid AND
> > + pg_attribute.attisdropped = 'f'"""
> >
> > l = {}
> > for attname, typname in self.db.query(query % cl).getresult():
> > Index: tutorial/syscat.py
> > ===
> > RCS file: /projects/cvsroot/pgsql-server/src/interfaces/python/tutorial/syscat.py,v
> > retrieving revision 1.7
> > diff -u -r1.7 syscat.py
> > --- tutorial/syscat.py 2002/05/03 14:21:38 1.7
> > +++ tutorial/syscat.py 2002/08/08 03:29:48
> > @@ -37,7 +37,7 @@
> > FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
> > WHERE i.indrelid = bc.oid AND i.indexrelid = bc.oid
> > AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
> > - AND i.indproc = '0'::oid
> > + AND i.indproc = '0'::oid AND a.attisdropped = 'f'
> > ORDER BY class_name, index_name, attname""")
> > return result
> >
> > @@ -48,6 +48,7 @@
> > WHERE c.relkind = 'r' and c.relname !~ '^pg_'
> > AND c.relname !~ '^Inv' and a.attnum > 0
> > AND a.attrelid = c.oid and a.atttypid = t.oid
> > +AND a.attisdropped = 'f'
> > ORDER BY relname, attname""")
> > return result
> >
> -- End of PGP section, PGP failed!
>
> --
> Bruce Momjian| http://candle.pha.pa.us
> [EMAIL PROTECTED]
Re: [HACKERS] FUNC_MAX_ARGS benchmarks
> > NAMEDATALEN will be 64 or 128 in 7.3. At this point, we better decide > > which one we prefer. > > > > The conservative approach would be to go for 64 and perhaps increase it > > again in 7.4 after we get feedback and real-world usage. If we go to > > 128, we will have trouble decreasing it if there are performance > > problems. > > I guess I'd also agree with: >FUNC_MAX_ARGS 32 >NAMEDATALEN 64 > and work on the performance issues for 7.4. I agree too. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_stat_reset() weirdness
> Joe Conway <[EMAIL PROTECTED]> writes: > > I guess I should know better than to jump to a conclusion. But I *was* > > under the impression we were supposed to use the unused_oids script to > > get a unique oid for a new function. unique_oids script Chris ---(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
