Re: [HACKERS] Spoofing as the postmaster
On Sun, 23 Dec 2007, Tom Lane wrote: ISTM we have these action items: 1. Improve the code so that SSL authentication can be used across a Unix-socket connection (we can disable encryption though). I've just realised that there's a problem with SSL with disabled encryption on a unix socket / localhost connections for cpu-saving. Any local user using this attack would be able to eavesdrop everything comming through a socket. If an attacker just acts as a tunnel, highjacking a unix-socket and talking to a server using any other interface (or the other way around), then he would not be able to modify information flow, but he would be able to read and save everything going to and from a server. It is again not obvious as normally local connections are not susceptible to eavesdropping. And could go unnoticed for a long time as everything would just work normally. So I think no cpu-saving by turning off encryption should be done. And this would all not help for a denial-of-service attack. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Binary data type with other output method
On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. The answer is surely to fix the PHP driver rather than trying to mangle Postgres. The Perl DBD::Pg driver does not suffer this problem, so it can certainly be worked around (in fact in DBD::Pg you get a choice if 1/0 or t/f values for booleans). This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. As i told earlier, this is really a PHP bug, since libpq knows the type of every returned row in PQftype. DBD::Pg and PHP PDO seems to use this information. Anyway, this does not help in my case. My question was if there is a shorter way to create a boolean type with another output than to recreate all the casts, operators ect. If not, i have to go with this approach but maybe someone else has a more elegant idea. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] duplicate columns with COPY
Am Donnerstag, 20. Dezember 2007 schrieb Tom Lane: I think it's reasonable even for COPY TO, since IMHO the odds that it's a typo, rather than intentional, are probably 100:1. ISTM that with this line of argument we could disable thousands of valid uses of SQL commands. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Unworkable column delimiter characters for COPY
Currently, copy.c rejects newline, carriage return, and backslash as settings for the column delimiter character (in non-CSV mode). These all seem necessary to avoid confusion. However, I just noticed that the letters r, n, t, etc would also not work: on output, data characters matching such a delimiter would get escaped as \r, \n, etc, which on input would be read as C-style control characters. I think at minimum we need to forbid b, f, n, r, t, v, which are the control character representations currently recognized by COPY. But I'm tempted to make it reject all 26 lower-case ASCII letters, as a form of future-proofing. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: The reason for my question is: PHP (yes *grumble*) does not recognize boolean columns but instead makes a simple string from a PG boolean. So every time you select a boolean column in PHP, you cannot use expressions like: if (!$bool) because 't' and 'f' give TRUE in PHP. I was begged many times by our people coding PHP to find a workaround for this problem. The answer is surely to fix the PHP driver rather than trying to mangle Postgres. The Perl DBD::Pg driver does not suffer this problem, so it can certainly be worked around (in fact in DBD::Pg you get a choice if 1/0 or t/f values for booleans). This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. I don't see why it couldn't be switchable behaviour, just as it is in DBD::Pg. Also, earlier you said: I think you'd really need a separate type. But have you considered something simple like CREATE DOMAIN boolint AS int CHECK (value = 0 OR value = 1) i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Sorting Improvements for 8.4
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Mayer Sent: Wednesday, 19 December 2007 19:26 To: Mark Mielke; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Sorting Improvements for 8.4 Or do you mean being able to perform parts of the query plan fully in parallel? If this, then one would need a lot more than ParallelSort... I wouldn't recommend that - it seems like a Hard Problem. My guess is that the best way to use multiple threads in one backend would be to find specific algorithms like sorting that would be easier to isolate. To give my view on this problem: if I'm looking at a competing (commercial) database product, they added some operations called parallize and combine. Basically they split the data across several threads at one point and combine them later. This is basically what you are also implementing for parallelsort, but as a single step in the query exeuction. In my opinion your starting point is too narrow and specific, especially since a fairly simple generalization is possible. Instead, the issue becomes the spill-to-disk code that needs to operate in parallel (which needs to be tackled sooner or later anyways). If you can change the sort into three steps: parallelize, sort (multiple parallel instances) and combine (merge) you still have the same base case. However I believe such a thing is much easier to extend to more operations. Futhermore it seems that cache is a considered a major problem, especially the varying sizes. Wouldn't a cache-oblivious algorithm, like http://erikdemaine.org/papers/BRICS2002/ or http://etd.uwaterloo.ca/etd/afarzan2004.pdf be a good starting point for refinements on sort algorithm itself? I believe you can get a more consistent performance depending on the cache sizes, but it might be slower than a well-tuned quicksort. Just my EUR 0,02... - Joris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Binary data type with other output method
On Thu, 27 Dec 2007 08:52:15 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: On Wed, 26 Dec 2007 20:20:59 -0500 Andrew Dunstan wrote: This PHP driver is in use since years, do you really expect they will fix this bug and make thousands of applications fail? Everyone like Mark who posted an example earlier would have to change the PHP code if the variable is now a true/false boolean instead a 't'/'f' string. No, i don't expect a bugfix here. Newer drivers like PDO seems to behave correct but this will not help if you cannot use this driver. I don't see why it couldn't be switchable behaviour, just as it is in DBD::Pg. Maybe because the PHP folks don't care so much? I don't know. Already discussed this one on IRC but there's not so much love for fixing this issue, as far as i can see. Either i got: MySQL does this right in returning 0/1, so we don't care or i got other drivers fixed this problem, use this one. i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Unworkable column delimiter characters for COPY
Tom Lane wrote: Currently, copy.c rejects newline, carriage return, and backslash as settings for the column delimiter character (in non-CSV mode). These all seem necessary to avoid confusion. However, I just noticed that the letters r, n, t, etc would also not work: on output, data characters matching such a delimiter would get escaped as \r, \n, etc, which on input would be read as C-style control characters. I think at minimum we need to forbid b, f, n, r, t, v, which are the control character representations currently recognized by COPY. But I'm tempted to make it reject all 26 lower-case ASCII letters, as a form of future-proofing. Thoughts? Assuming this is only for non-CSV mode, it seems OK. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. If your code is prepared to emit t/f but not accept it then that seems to violate Postel's admonition: Be *liberal* in what you *accept*, and *conservative* in what you send. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Spoofing as the postmaster
On Mon, Dec 24, 2007 at 12:04:16AM +0100, Tomasz Ostrowski wrote: Not at all, as it won't run as root, it'll just start as root and then give up all root privileges. The only thing it would have after being root is just an open socket. If you think that is complete protection against privilege escalation, I encourage you to read some more bugtraq archives. The answer to MITM attacks is not superuser-reserved ports anyway. The privileged port idea was a bad one in retrospect. The answer is strong authentication. A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Spoofing as the postmaster
On Sun, Dec 23, 2007 at 09:52:14PM +0100, Magnus Hagander wrote: My point is that all these other server products have the exact same issue. And that they deal with it the exact same we do - pretty much leave it up to the guy who configure the server to realize that's just how things work. The problem with that approach is that, in the computer security world, taking that approach is increasingly regarded as negligent. And pointing out that others are similarly negligent is not a response. Note that I am explicitly not subscribing to or disagreeing with that view. A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Spoofing as the postmaster
On Sun, Dec 23, 2007 at 01:45:14AM -0500, Tom Lane wrote: The primary reason things work like that is that there are boatloads of machines that are marginally misconfigured. For instance, userland thinks there is IPv6 support when the kernel thinks not (or vice versa). Not only marginally misconfigured, but broken as shipped, in the case of some OSes. And in those cases, you can't even fix it. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unworkable column delimiter characters for COPY
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I think at minimum we need to forbid b, f, n, r, t, v, which are the control character representations currently recognized by COPY. But I'm tempted to make it reject all 26 lower-case ASCII letters, as a form of future-proofing. Thoughts? Assuming this is only for non-CSV mode, it seems OK. On looking closer, 'x', octal digits, and '.' would also be trouble. So I made it reject a-z, 0-9, and dot. It appears that the CSV mode is a few bricks shy of a load here as well: it will let you do CSV DELIMITER '' resulting in entirely broken output. It seems we ought to forbid delimiter from matching CSV quote or escape characters. I'll let you clean up that case though... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Unworkable column delimiter characters for COPY
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I think at minimum we need to forbid b, f, n, r, t, v, which are the control character representations currently recognized by COPY. But I'm tempted to make it reject all 26 lower-case ASCII letters, as a form of future-proofing. Thoughts? Assuming this is only for non-CSV mode, it seems OK. On looking closer, 'x', octal digits, and '.' would also be trouble. So I made it reject a-z, 0-9, and dot. I take it upper case A-F are safe, even though they are hex digits, because they wouldn't immediately follow the backslash? It appears that the CSV mode is a few bricks shy of a load here as well: it will let you do CSV DELIMITER '' resulting in entirely broken output. It seems we ought to forbid delimiter from matching CSV quote or escape characters. I'll let you clean up that case though... Lucky me. Ok, I'll look at it. Should be simple enough. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Spoofing as the postmaster
Andrew Sullivan wrote: On Sun, Dec 23, 2007 at 09:52:14PM +0100, Magnus Hagander wrote: My point is that all these other server products have the exact same issue. And that they deal with it the exact same we do - pretty much leave it up to the guy who configure the server to realize that's just how things work. The problem with that approach is that, in the computer security world, taking that approach is increasingly regarded as negligent. And pointing out that others are similarly negligent is not a response. Sure. But we *do* provide a way to work around it *if you have to*: use SSL with trusted certificates. In the large number of cases where you *don't* need to worry about it, there's no need to add any extra overhead. And if you're going with SSL already, the extra overhead of TCP vs Unix sockets shouldn't matter *at all*... So I don't really see a motivation for us to support SSL over Unix sockets, if it adds any complexity to the code. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
Mark Mielke wrote: I prefer UNIX sockets with kernel credential passing over TCP/IP with username/password or the more expensive SSL. I do not like storing passwords or private certificates in a place available to the web user, as other web users would then also have access. I do not have evidence, but I am under the impression that the TCP/IP stack incurs additional overhead on connect(), send(), recv(), and close() than UNIX sockets. I think that was one of the original reasons the Unix sockets code was added at all. How expensive would it be to implement a server_user db open parameter that would perform reverse credential passing to validate? dbname=XXX port=5432 server_user=postgres. If the server can't prove it is postgres through UNIX socket credential passing, it fails. Similarly, Probably not very, but you should be able to achieve the same thing by moving the socket to a protected directory, I think? identd may be usable in reverse? I've seen many people claim identd is insecure - but it is secure if I am the one running it, is it not? AFAIK, it's secure if the host that it's running on can be considered secure. It's not secure over the internet, because by definition wherever the client runs is not under your control. But if you fully control the machine that the client runs on, AFAIK, ident should be secure. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Spoofing as the postmaster
Tomasz Ostrowski wrote: On Sun, 23 Dec 2007, Tom Lane wrote: 3. Massive confusion and breakage as various people transition to the new standard at different times. As with any major version. No, it would introduce a client/server incompatibility. Generally, older clients (libpq) will still work fine with newer servers, or the other way around. Lots of attention is paid to maintaining that. 4. Potential to create, rather than remove, spoofing opportunities anyplace there is confusion about which port the postmaster is really listening on. I agree. But because it would just not work it'll be easy to notice and correct. And when corrected it would be no more confusion. It would be a perfect spot to put in the MITM attack that this whole thread has been about... Fundamentally these are man-in-the-middle attacks, and the only real solution is mutual authentication. The problem is not many people expect man-in-the-middle attack on secure lan, localhost or local socket connection, so they'll not try to prevent it. There is no such thing as a secure LAN, unless you control every host and what every user can do on it. (Definition of LAN can be a bit different though. Say you implement proper IPsec isolation on it - in that case, only the machines on the inside of the ipsec cloud need to be trusted) Same thing really does go for the host - it's not a secure host if you can't control what the users are doing on it. So you can't treat it as such if that's the case. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Archiver behavior at shutdown
The problem complained of in bug #3843 was something I'd noticed a few days ago and meant to fix. ISTM the recent change to have the archiver outlive the postmaster was incompletely thought out, and we really need to take two steps back and reconsider, if we want to fix it so it works. As of CVS HEAD, the behavior after the postmaster receives a shutdown request and has seen its last regular-backend child die is: 1. Issue SIGUSR2 to the bgwriter to make it start a shutdown checkpoint. 2. Immediately SIGQUIT the archiver. 3. Back at the main loop, restart the archiver, if it exits before the bgwriter finishes the checkpoint (as is highly likely). 4. After postmaster exits, archiver eventually notices it's gone, but that takes a good while since we are guaranteed to be just starting the delay loop inside the fresh archiver process. This is just plain dumb. Aside from the uselessness of killing a process only to immediately re-fork it, we should not be SIGQUIT'ing the archiver during normal operation --- that might abort an archive copy partway through, and it's anybody's guess whether the archive_command script is smart enough to deal with that situation. ISTM the postmaster should leave the archiver alone at the PM_WAIT_BACKENDS - PM_SHUTDOWN transition, and instead send it a WAKEN signal (SIGUSR1) when it sees normal exit of the bgwriter. That will afford an opportunity to archive anything that was pushed out during the shutdown checkpoint. A possibly better alternative, since the archiver isn't using SIGUSR2, is to send SIGUSR2 which would be defined as archive what you can and then quit. (In that case, the !PostmasterIsAlive exit would be taken only in the event of a true postmaster crash, which is improbable.) Another case that seems not to have been thought about very much is whether the archiver should behave differently in a mode fast shutdown as opposed to mode smart. I would argue that it should not, since both cases are supposed to be equally safe for your data. I notice though that the postmaster suppresses forwarding of WAKEN signals after entering FastShutdown mode; that doesn't seem like a good idea. Another case that needs some revisiting is the archiver's response to SIGTERM, which is currently SIG_IGN. Since the postmaster will never send it SIGTERM, we should assume that receipt of SIGTERM means that init is telling us we have N seconds left before system shutdown. Is it a good idea to continue archiving in that situation? I doubt it --- it seems like we are just asking to get SIGKILL'd partway through a copy step. I suggest that the response to SIGTERM ought to be to finish out the current copy operation (if possible) but then quit without initiating any new ones. And while I'm griping: I see that the pgstats process is SIGQUIT'ed at the entry to PM_SHUTDOWN state, same as the archiver. This likewise seems out of step with current reality, since the bgwriter now sends messages to the stats collector. This step needs to be moved to after bgwriter termination, too. Comments? Anyone see any other bugs here? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Archiver behavior at shutdown
On Thu, 2007-12-27 at 15:29 -0500, Tom Lane wrote: As of CVS HEAD, the behavior after the postmaster receives a shutdown request and has seen its last regular-backend child die is: ...based upon limitations of the existing system. We have been SIGQUIT'ing the archiver, and there is a comment there to say how important it is that we *do not* try to finish processing before we quit. If you think that comment is wrong, thats OK by me: I can't recall the reasoning there, or even if it was my own. ISTM the postmaster should leave the archiver alone at the PM_WAIT_BACKENDS - PM_SHUTDOWN transition, and instead send it a WAKEN signal (SIGUSR1) when it sees normal exit of the bgwriter. That will afford an opportunity to archive anything that was pushed out during the shutdown checkpoint. A possibly better alternative, since the archiver isn't using SIGUSR2, is to send SIGUSR2 which would be defined as archive what you can and then quit. (In that case, the !PostmasterIsAlive exit would be taken only in the event of a true postmaster crash, which is improbable.) Sounds good. Another case that needs some revisiting is the archiver's response to SIGTERM, which is currently SIG_IGN. Since the postmaster will never send it SIGTERM, we should assume that receipt of SIGTERM means that init is telling us we have N seconds left before system shutdown. Is it a good idea to continue archiving in that situation? I doubt it --- it seems like we are just asking to get SIGKILL'd partway through a copy step. I suggest that the response to SIGTERM ought to be to finish out the current copy operation (if possible) but then quit without initiating any new ones. Not sure about that. If there are outstanding files to archive, then it probably is important to try to archive them. Mostly this won't be the case, but if this was, for example a simple switchover between a primary and a warm standby then it might result in data loss. If you see problems with archive_commands that don't correctly reset themselves after an error then we should document how to, rather than just *try* to avoid it. And while I'm griping: I see that the pgstats process is SIGQUIT'ed at the entry to PM_SHUTDOWN state, same as the archiver. This likewise seems out of step with current reality, since the bgwriter now sends messages to the stats collector. This step needs to be moved to after bgwriter termination, too. Sounds good. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Sure. But we *do* provide a way to work around it *if you have to*: use SSL with trusted certificates. In the large number of cases where you *don't* need to worry about it, there's no need to add any extra overhead. And if you're going with SSL already, the extra overhead of TCP vs Unix sockets shouldn't matter *at all*... So I don't really see a motivation for us to support SSL over Unix sockets, if it adds any complexity to the code. Well, the problem with the current behavior is that the client app can require SSL, but the request is silently ignored if the connection is over Unix socket. So you might think you're secure when you aren't. I think that the reason we don't support SSL over Unix socket is mainly that we thought it was useless; but this discussion has exposed reasons to use it. So I'm for just eliminating the asymmetry. I have no problem with that. But it does seem to me that we are going about this all wrong. The OP proposed a solution which was intended to ensure at the server end that an untrusted user could not spoof the postmaster if the postmaster were not running. Putting the onus of this on clients seems wrong. I don't have any experience with SELinux, but my impression is that it can be used to control who or what can open files, sockets etc. On Linux at least this strikes me as a more productive approach to the original problem, as it would put the solution in the SA's hands. Maybe other Unices and Windows have similar capabilities? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Spoofing as the postmaster
Magnus Hagander [EMAIL PROTECTED] writes: Sure. But we *do* provide a way to work around it *if you have to*: use SSL with trusted certificates. In the large number of cases where you *don't* need to worry about it, there's no need to add any extra overhead. And if you're going with SSL already, the extra overhead of TCP vs Unix sockets shouldn't matter *at all*... So I don't really see a motivation for us to support SSL over Unix sockets, if it adds any complexity to the code. Well, the problem with the current behavior is that the client app can require SSL, but the request is silently ignored if the connection is over Unix socket. So you might think you're secure when you aren't. I think that the reason we don't support SSL over Unix socket is mainly that we thought it was useless; but this discussion has exposed reasons to use it. So I'm for just eliminating the asymmetry. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Spoofing as the postmaster
Magnus Hagander wrote: How expensive would it be to implement a server_user db open parameter that would perform reverse credential passing to validate? dbname=XXX port=5432 server_user=postgres. If the server can't prove it is postgres through UNIX socket credential passing, it fails. Similarly, Probably not very, but you should be able to achieve the same thing by moving the socket to a protected directory, I think? What you are ulimately interested in is who runs a given server. Making the inference that if the socket is in a directory that is currently only writable by a certain user implies that the user owns the server that offers that socket doesn't sound like a given to me. And let's forget that it's not really straightforward to find out who has write access to some directory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Archiver behavior at shutdown
Tom Lane wrote: ISTM the postmaster should leave the archiver alone at the PM_WAIT_BACKENDS - PM_SHUTDOWN transition, and instead send it a WAKEN signal (SIGUSR1) when it sees normal exit of the bgwriter. That will afford an opportunity to archive anything that was pushed out during the shutdown checkpoint. What does postmaster do then? Sleep until archiver is done, or exit immediately and hope that the archiver goes away as soon as it finishes? If the former, then we open the possibility that postmaster lives far too long before system shutdown decides to SIGKILL it. If the latter, then a subsequent postmaster start could initiate a second archiver process which would cause issues with whatever the first archiver is doing. I think your proposal to handle SIGTERM could also be used whenever postmaster has been asked for shutdown (except smart shutdown, perhaps?): I suggest that the response to SIGTERM ought to be to finish out the current copy operation (if possible) but then quit without initiating any new ones. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Spoofing as the postmaster
* Tom Lane ([EMAIL PROTECTED]) wrote: Most Linux distros don't have SELinux, AFAIK, so this is probably not a very useful suggestion. Not that I have a problem with Red-Hat-specific solutions ;-) Debian also has SELinux, if one wishes to configure it. I suspect other Debian-derived distributions also have it as a result. It can certainly be a pain to configure but it's far from impossible and if an SA has concerns such as those described, well, I'd be kind of suprised if they weren't considering SELinux (if they're on Linux anyway). ... but since one of the arguments being made against move-the-socket is that it introduces a lot of platform-specific assumptions, we have to apply that same criterion to alternative answers. I don't quite follow how one argues 'against' SELinux in this context as I don't believe upstream changes would be required here. Just a policy configuration whereby only the postgres user can listen on port 5432. As far as ensuring security from the server end, what about extending the pg_hba.conf options to require that the server has both checked a client certificate and presented its own certificate? (I'm not sure whether OpenSSL provides a way to determine that, though.) It'd be really nice to be able to have client-side certificates used for authentication by having a way to associate a certificate (or maybe at least the DN, but you can have dups) to a user. That's a seperate conversation tho, really. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Archiver behavior at shutdown
Simon Riggs [EMAIL PROTECTED] writes: ...based upon limitations of the existing system. We have been SIGQUIT'ing the archiver, and there is a comment there to say how important it is that we *do not* try to finish processing before we quit. If you think that comment is wrong, thats OK by me: I can't recall the reasoning there, or even if it was my own. That comment is clearly wrong --- it applies to the SIGTERM situation. Another case that needs some revisiting is the archiver's response to SIGTERM, which is currently SIG_IGN. Since the postmaster will never send it SIGTERM, we should assume that receipt of SIGTERM means that init is telling us we have N seconds left before system shutdown. Is it a good idea to continue archiving in that situation? I doubt it --- it seems like we are just asking to get SIGKILL'd partway through a copy step. I suggest that the response to SIGTERM ought to be to finish out the current copy operation (if possible) but then quit without initiating any new ones. Not sure about that. If there are outstanding files to archive, then it probably is important to try to archive them. Mostly this won't be the case, but if this was, for example a simple switchover between a primary and a warm standby then it might result in data loss. A simple switchover ought to be done by bringing down the postmaster, not the whole machine. The real question here is whether it's sane to try to do archiving on a machine that is in the midst of shutdown. As an example, it's quite likely that NFS mounts are going to go away sometime between SIGTERM and SIGKILL, if they haven't done so already. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Spoofing as the postmaster
Andrew Dunstan [EMAIL PROTECTED] writes: I have no problem with that. But it does seem to me that we are going about this all wrong. The OP proposed a solution which was intended to ensure at the server end that an untrusted user could not spoof the postmaster if the postmaster were not running. Putting the onus of this on clients seems wrong. I don't have any experience with SELinux, but my impression is that it can be used to control who or what can open files, sockets etc. On Linux at least this strikes me as a more productive approach to the original problem, as it would put the solution in the SA's hands. Maybe other Unices and Windows have similar capabilities? Most Linux distros don't have SELinux, AFAIK, so this is probably not a very useful suggestion. Not that I have a problem with Red-Hat-specific solutions ;-) ... but since one of the arguments being made against move-the-socket is that it introduces a lot of platform-specific assumptions, we have to apply that same criterion to alternative answers. As far as ensuring security from the server end, what about extending the pg_hba.conf options to require that the server has both checked a client certificate and presented its own certificate? (I'm not sure whether OpenSSL provides a way to determine that, though.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Archiver behavior at shutdown
On Thu, 2007-12-27 at 17:29 -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: What does postmaster do then? Sleep until archiver is done, or exit immediately and hope that the archiver goes away as soon as it finishes? I think it can just exit immediately, particularly if we invent the variant signal for archive what you can and then quit. If the former, then we open the possibility that postmaster lives far too long before system shutdown decides to SIGKILL it. If the latter, then a subsequent postmaster start could initiate a second archiver process which would cause issues with whatever the first archiver is doing. That's a problem that the archiver itself should fix (perhaps it needs its own lockfile). http://archives.postgresql.org/pgsql-hackers/2006-05/msg00920.php -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Sorting Improvements for 8.4
Ron Mayer wrote: Or do you mean being able to perform parts of the query plan fully in parallel? If this, then one would need a lot more than ParallelSort... I wouldn't recommend that - it seems like a Hard Problem. Isn't it the case that the implicit unions from processing partitioned data provides a more-or-less-ideal opportunity here? I certainly have sympathy for parallelising expensive queries to bring the best response time down, even if the average under full load goes up slightly, since any implied locks (including pinning of read-ahead ages) will be released sooner. And when load is light, users who are online get more of the hardware they paid for. James ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Archiver behavior at shutdown
Alvaro Herrera [EMAIL PROTECTED] writes: What does postmaster do then? Sleep until archiver is done, or exit immediately and hope that the archiver goes away as soon as it finishes? I think it can just exit immediately, particularly if we invent the variant signal for archive what you can and then quit. If the former, then we open the possibility that postmaster lives far too long before system shutdown decides to SIGKILL it. If the latter, then a subsequent postmaster start could initiate a second archiver process which would cause issues with whatever the first archiver is doing. That's a problem that the archiver itself should fix (perhaps it needs its own lockfile). Consider kill -9 on the postmaster followed by starting a fresh postmaster --- you have the same problem, and there's nothing much the postmaster can do about it. I think your proposal to handle SIGTERM could also be used whenever postmaster has been asked for shutdown (except smart shutdown, perhaps?): I suggest that the response to SIGTERM ought to be to finish out the current copy operation (if possible) but then quit without initiating any new ones. No, because during normal shutdown we'd like the archiver to copy away *all* available segments, not just one. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Spoofing as the postmaster
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I have no problem with that. But it does seem to me that we are going about this all wrong. The OP proposed a solution which was intended to ensure at the server end that an untrusted user could not spoof the postmaster if the postmaster were not running. Putting the onus of this on clients seems wrong. I don't have any experience with SELinux, but my impression is that it can be used to control who or what can open files, sockets etc. On Linux at least this strikes me as a more productive approach to the original problem, as it would put the solution in the SA's hands. Maybe other Unices and Windows have similar capabilities? Most Linux distros don't have SELinux, AFAIK, so this is probably not a very useful suggestion. Not that I have a problem with Red-Hat-specific solutions ;-) ... but since one of the arguments being made against move-the-socket is that it introduces a lot of platform-specific assumptions, we have to apply that same criterion to alternative answers. As far as ensuring security from the server end, what about extending the pg_hba.conf options to require that the server has both checked a client certificate and presented its own certificate? (I'm not sure whether OpenSSL provides a way to determine that, though.) A server has *always* presented its certificate. SSL doesn't work otherwise. What we can't know is if the client *verified* the certificate. But there's no way to control that from server-side anyway... And we do request the client certificate if the server is provided with a root certificate store to verify it against... I'm not sure we gain a lot by adding a second option to do the same thing (which still will need said root certificate store to work) //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
Stephen Frost wrote: It'd be really nice to be able to have client-side certificates used for authentication by having a way to associate a certificate (or maybe at least the DN, but you can have dups) to a user. That's a seperate conversation tho, really. Absolutely, but as you say a completely different thing. And FYI, it's on my list of things I'd like to work on for 8.4. Usual disclaimers about not actually ending up having time to do it applies, of course :-) //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Binary data type with other output method
On Thu, 27 Dec 2007 11:39:29 -0500 Andrew Dunstan wrote: Andreas 'ads' Scherbaum wrote: i considered this one but this would only bring 0/1 as input, not as output values, which is what i want. er, what? This domain would have input and output values of 0/1. Yes, but i will loose 't', 'true', 'f' and 'false', the usual input values in PostgreSQL beside '0' and '1'. It's not that the people don't use true/false, they use this a lot. But they expect boolean variable in PHP working like a bolean column in PG ... without casts, without extra workarounds in the code. If your code is prepared to emit t/f but not accept it then that seems to violate Postel's admonition: Be *liberal* in what you *accept*, and *conservative* in what you send. Andrew: your workaround was to create a domain which would use 0/1 ... this idea will not behave like a boolean, because it does not accept 'true'/'false'. My replacement boolean type does accept all this input values and does emit 0/1 instead of 'f'/'t'. But since i have to create all the casts, operators and classes (like for boolean) again for my data type, i asked if someone know a more elegant way ... if not, i will use my data type. You can see the code here: http://andreas.scherbaum.la/writings/boolean.sql This are some hundred lines of SQL which are almost doubled from the boolean type. So maybe there's a way to avoid all this, but i haven't found one. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Archiver behavior at shutdown
Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2007-12-27 at 17:29 -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: then a subsequent postmaster start could initiate a second archiver process which would cause issues with whatever the first archiver is doing. That's a problem that the archiver itself should fix (perhaps it needs its own lockfile). http://archives.postgresql.org/pgsql-hackers/2006-05/msg00920.php I thought that sounded familiar ;-). What was the outcome of that discussion? No patch for this ever got applied AFAICS. The patch as posted had a few issues, per the thread, and I don't see a followup version. (The alleged replacement patch did something else entirely.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Binary data type with other output method
Andreas 'ads' Scherbaum wrote: Andrew: your workaround was to create a domain which would use 0/1 ... this idea will not behave like a boolean, because it does not accept 'true'/'false'. My replacement boolean type does accept all this input values and does emit 0/1 instead of 'f'/'t'. But since i have to create all the casts, operators and classes (like for boolean) again for my data type, i asked if someone know a more elegant way ... if not, i will use my data type. You can see the code here: http://andreas.scherbaum.la/writings/boolean.sql This are some hundred lines of SQL which are almost doubled from the boolean type. So maybe there's a way to avoid all this, but i haven't found one. Looks to me like this is the way to meet you requirements. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
