Re: [GENERAL] [pgsql-advocacy] MySQL worm attacks Windows servers
Jan Wieck <[EMAIL PROTECTED]> writes: > No, Peter. > > Posting a vulnerability on a public mailing list "before" there is a known fix > for it means that you put everyone who has that vulnerability into jeopardy. > Vulnerabilities are a special breed of bugs and need to be exterminated a > little different. Many people disagree with this. Posting the vulnerability isn't what puts people into jeopardy, the presence of the vulnerability puts people in jeopardy. Posting it at least allows people to disable the feature or close off access. Or at least monitor for possible intrusions. Not posting it leaves people in jeopardy and in the dark about it. If you think you're the first one to find the vulnerability you're probably wrong. Often malicious hackers who search for vulnerabilities find them and keep them secret long before they're reported. How would you feel if your system was compromised and then you found out later that it was a known security hole in a feature you had no need for and the vulnerability had been kept secret? This is really the wrong place to have such a debate. This is a long-standing debate and one that you should at just recognize exists. Don't present one side as dogma. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Questions about functionality
The world rejoiced as [EMAIL PROTECTED] ("Ignacio Colmenero") wrote:
[Large amount of HTML removed... _Please_ post in plain text...]
There is no direct analogue to Oracle's SQL*Loader product for
PostgreSQL at this time.
That being said...
1. Plenty of scripting languages are available for Unix platforms,
and they are plenty good at "massaging" data.
2. I recall Jan Wieck having created a Tcl-based "load tool"
prototype about a year ago that had capabilities somewhat resembling
SQL*Loader. (Very "somewhat" :-).) It sort of disappeared, which is
a bit of a shame. It might be possible to convince him to put that up
on GBorg or PGFoundry. It's a tool someone else should take on at
this point in time; the ideas, at least, could help in building such a
tool.
3. Pervasive Software seems to have some Windows-based tools they are
releasing for doing ETL. That might be useful.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://linuxdatabases.info/~cbbrowne/languages.html
But what can you do with it? -- ubiquitous cry from Linux-user
partner. -- Andy Pearce, <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Update command too slow
Oops! [EMAIL PROTECTED] (Venkatesh Babu) was seen spray-painting on a wall: > There aren't any triggers but there are 75262 update > statements. The problem is that we have a datatype > called as "Collection" and we are fetching the data > rows into it, modifying the data and call > Collection.save(). This save method generates one > update satement per record present in it. Is that "Collection" in your application the entire table? If it is, then you can get a nice win thus: --> Delete from stbl; --- One fast statement --> COPY stbl from stdin; --- Another fast statement row 1 data row 2 data row 3 data ... row 75262 data \. That update would be REALLY fast! Even if it isn't, consider trying the following transaction: BEGIN; select * into temp table stbl_12341 from stbl limit 0; -- Note that 12341 is the PID of your process, so that should be -- pretty unique copy stbl_12341 from stdin; -- Load your 75262 rows in one fell swoop row 1 data row 2 data ... row 75262 data \. -- Now, delete from stbl all the rows that are in the replacement table... delete from stbl where pkey in (select pkey from stbl_12341); insert into stbl (select * from stbl_12341); COMMIT; Both approaches will be WAY faster than doing the processing row by row. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxfinances.info/info/postgresql.html "Waving away a cloud of smoke, I look up, and am blinded by a bright, white light. It's God. No, not Richard Stallman, or Linus Torvalds, but God. In a booming voice, He says: "THIS IS A SIGN. USE LINUX, THE FREE Unix SYSTEM FOR THE 386." -- Matt Welsh ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Referencing uninitialized variables in plpgsql
"Karl O. Pinc" <[EMAIL PROTECTED]> writes: > AFICT, the docs arn't clear when it comes to referencing unitialized > array elements. I assume you get NULL values here as well even > though other array elements may have been given non-NULL values. There is no such thing as an uninitialized array element. Looking at array_ref, we do return a NULL if you try to fetch from a position outside the current array subscript range. But that's not "uninitialized" in any normal sense of the word, it's more like "nonexistent". (I think you're right that it's undocumented behavior, too.) > Can I count on this? Until someone makes an argument to change it ;-). 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: [GENERAL] Referencing uninitialized variables in plpgsql
On 02/05/2005 10:57:45 PM, John DeSoi wrote: Yes, exactly. If you don't assign a value to a declared pspgsql variable, it is NULL. Thanks, just what I needed. If you are concerned about this, then always assign a value when you declare it. This does not really address my concern. See below. Also, you can specify NOT NULL in your declaration to ensure a runtime error is generated if the variable is null. See: http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html I don't know why I sometimes can't find this stuff when I need it. I kept scanning for 'variable'. Some quibbles. (Where the devils lurk. ;) AFICT, the docs arn't clear when it comes to referencing unitialized array elements. I assume you get NULL values here as well even though other array elements may have been given non-NULL values. Can I count on this? Initializing all varaibles, whether in declarations, automatically, or by assignment, is not a substiute for throwing an exception at runtime when an unitialized variable is referenced. When a program is written so that varaibles are given values before those values are expected to be used, and not given values otherwise, then runtime exceptions thrown when unitialized variables are referenced are alerts that the program is operating in an unexpected manner. Initializing all variables regardless of whether the initial values are expected to be used does nothing more than assure these sorts of alerts will not be raised, increasing the likelyhood that unexpected program behavior will go unnoticed and uncorrected. So, it would be cool of plpgsql declarations could declare a variable NOT NULL without having to assign a default value, and then raise an 'illegal NULL' exception should the variable be referenced before a value is assigned to it. This sounds like it could be complicated to impliment, except that something similar must already be happening with unitialized array elements to produce NULL values when these are referenced. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [pgsql-advocacy] MySQL worm attacks Windows servers
Jan Wieck wrote: On 1/30/2005 10:18 AM, Peter Eisentraut wrote: Dawid Kuroczko wrote: I think it is in good taste that when you find a bug/vulnerability/etc first you contact the author (in this case: core), leave them some time to fix the problem and then go on announcing it to the world. In this case, core is not the author of the object in question. And of course, to report a "bug/vulnerability/etc" you would write to pgsql-bugs, not core. No, Peter. Posting a vulnerability on a public mailing list "before" there is a known fix for it means that you put everyone who has that vulnerability into jeopardy. Vulnerabilities are a special breed of bugs and need to be exterminated a little different. Jan ain't that the truth. if a vulnerability is found, try to find a fix, or work around, post it privately to the developer, give them an opportunity to get it fixed before going public. when dealing with open souurce, this system works great. when dealing with proprietary / closed source [ specifically microsoft ] expect that it's the public announcement that's going to start them doing something about it. I personally would only give ms a week at most to fix the problem before going public. since open source if usually fixed in that time frame. Jaqui ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] MySQL worm attacks Windows servers
On 1/30/2005 10:18 AM, Peter Eisentraut wrote: Dawid Kuroczko wrote: I think it is in good taste that when you find a bug/vulnerability/etc first you contact the author (in this case: core), leave them some time to fix the problem and then go on announcing it to the world. In this case, core is not the author of the object in question. And of course, to report a "bug/vulnerability/etc" you would write to pgsql-bugs, not core. No, Peter. Posting a vulnerability on a public mailing list "before" there is a known fix for it means that you put everyone who has that vulnerability into jeopardy. Vulnerabilities are a special breed of bugs and need to be exterminated a little different. 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 8: explain analyze is your friend
Re: [GENERAL] Help with access check
Alvaro Herrera wrote: On Sun, Feb 06, 2005 at 03:28:31PM +0100, Thomas Hallgren wrote: Hi, From code inside a C-function, given the AclId of a user and the Oid of a Schema, what's the most efficient way to find out if the user has been granted CREATE on the schema? See src/backend/catalog/aclchk.c, function pg_namespace_aclcheck(). Great. Thanks! Why would you want to do that directly anyway? In PLJava, I use a function to set a schema specific Java classpath that functions declared in that schema will use. The setting is permanent (i.e. not per session). I want to prevent all users but those that have create permission on the schema to set this classpath. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Help with access check
On Sun, Feb 06, 2005 at 03:28:31PM +0100, Thomas Hallgren wrote: Hi, > From code inside a C-function, given the AclId of a user and the Oid of > a Schema, what's the most efficient way to find out if the user has been > granted CREATE on the schema? See src/backend/catalog/aclchk.c, function pg_namespace_aclcheck(). Why would you want to do that directly anyway? -- Alvaro Herrera (<[EMAIL PROTECTED]>) "La virtud es el justo medio entre dos defectos" (Aristóteles) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] security
On Sat, Feb 05, 2005 at 11:00:28PM -0800, David Fetter wrote: > On Sat, Feb 05, 2005 at 09:08:00PM -0500, Ron Peterson wrote: > > I would like to be able to assert that the security of data stored > > as a value in a PostgreSQL table can be as high as the security of > > saving that same piece of data to a file on disk. Would that be > > correct? > > I hate to put it so bluntly, but "security" isn't a product that you > buy or a service that you use. It's not even a rigid set of > procedures, however well-thought-out such a set might be. > > Instead, it's a large and by its nature flexible set of processes that > you must implement and keep up to date. What distinguishes security > in the computer field from other kinds of things involving computers > is the existence of one or more attackers. In re: how to do security, > I'll quote Bruce Schneier's 5-step security evaluation: > >1. What assets are you trying to protect? >2. What are the risks to those assets? >3. How well does the security solution mitigate those risks? >4. What other risks does the security solution cause? >5. What costs and tradeoffs does the security solution impose? > > Until you have answered questions 1 and 2, you can't even start on an > implementation. Sure, I agree with all of that. Those are useful abstractions which basically say that at some point, you need to stop thinking in abstractions, and get down to brass tacks. Using the term "secure" in a loose sense, clearly I hope we can consider it's possible to create secure database applications in PostgreSQL. Otherwise we can tell our clients that PostgreSQL is an inappropriate place to consider storing sensitive information. It's the brass tacks stuff I'd like feel I have a really firm grip on. I've searched, but have not yet found anything resembling a "security best practices" howto for PostgreSQL. I think I have a reasonable grasp of what needs to be done to secure a database application, but I'm worried about that one thing I'm overlooking that leaves me wide open (memory scrubbing?, etc.). As others have said, the additional complications PostgreSQL introduces are the most probable cause of weakness. On the other hand, I don't feel that PostgreSQL is *so* complicated, that those complications can't be dealt with. For example, I agree with Steve Atkins' point that running the database on a separate host eliminates or at least reduces the potential impact of a whole host of issues. Does anyone know of a best practices security checklist for PostgreSQL? Or any other resources in that vein? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(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
[GENERAL] Help with access check
Hi, From code inside a C-function, given the AclId of a user and the Oid of a Schema, what's the most efficient way to find out if the user has been granted CREATE on the schema? Thanks, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgpool simple feature request
> Hello, > > Command Prompt develops a lot of web based applications. > One of the problems with the web is you can't really > pre determine a lot of tasks because of the statelessness. > > It would be nice if you could tell pgpool to perform certain > queries on initialization of a new connection. This would > allow web based applications to take advantange of several > nice features including: > > cursors for more than just the single session > global variables > preloaded indexes for performance in ram Yes, I thought about that too. Probably we need two kinds of initializations: 1) initilization for each new connection (as you requested) 2) initilization for each new client connection I think pgpool could do both of them since it has already done series of queries in closing client connections. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Help with sorting (ie. ORDER BY expression)
On Sat, Feb 05, 2005 at 17:25:06 -0500, "Reuben D. Budiardja" <[EMAIL PROTECTED]> wrote: > > Hello, > I am running postgres-7.3. I have a query like this: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5); > > But I want the output to be sorted in the way I give the question_id, > something like: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5) > ORDER BY question_id (2,10,3,6,4,5) > > Is there any way I can do that, so that the output of the query is You could create a table with the question ids and their rank and join that table to the above query and order by the rank. ---(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: [GENERAL] pgpool 2.5b2 released
# [EMAIL PROTECTED] / 2005-02-06 09:34:53 -: > Since I posted my original question, I realized that pgpool notes a > failure of either master or slave in its log. Would we want something > more proactive? snmp? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgpool 2.5b2 released
Tatsuo Ishii wrote: Pgpool 2.5b2 supports "master slave mode" which can cope with master/slave replication softwares such as Slony-I. In this mode pgpool sends non SELECT queries to master only. SELECTs are load balanced by pgpool. First of all, thanks so much Tatsuo for such a great project! This is incredible! I definitely did wait for this and some people at the Solutions Linux here in Paris did ask about it. Too bad that I didn't read this earlier, it would have made their day. Don't worry! On Feb 2nd, when we saw both Tatsuo did move his pgpool to pgfoundry, I downloaded the tarball, and played all the night with it, so on Feb 3rd (yep, last day, too bad), I gave back the info all the day on it to the people with the kind of problems pgpool and/or slony can resolve. Cheers, -- Jean-Paul Argudo www.PostgreSQLFr.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pgpool 2.5b2 released
> After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] ("Julian
Scarfe") belched out:
> > So all I'm looking for is a way for pgpool to shout if it detects a
> > failure. That could initiate the investigation of the other
> > criteria required for failover.
>
> _There_ lies the one change that is needed. Given that, some outside
> 'oracle' can be used to decide if it's appropriate to do a FAILOVER.
>
> It's quite important for this not to be deeply embedded in pgpool...
I think we have a consensus on that. So what's the most sensible mechanism
for the "shout". Since I posted my original question, I realized that
pgpool notes a failure of either master or slave in its log. Would we want
something more proactive?
Julian
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
