Re: [PATCHES] [HACKERS] Solaris ident authentication using unix domain sockets
Florian, I'd be *very* interested in how they come to that assessment. I'd have thought that the only alternative to getpeereid/getupeercred is password-based or certificate-based authenticated - which seem *less* secure because a) they also rely on the client having the correct uid or gid (to read the password/private key), plus b) the risk of the password/private key getting into the wrong hands. *shrug* don't ask me. I don't agree with the policy, I can hardly defend it. --Josh -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS] [PATCHES] GIN improvements
Neil, I was tasked with reviewing this for the current commit fest, although so far I've just been working on grokking the rest of the GIN code. But if you'd like to review it instead, that's fine with me. I have plenty of other stuff I could assign you if you're not needed on GIN. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] Solaris ident authentication using unix domain sockets
Tom, Indeed. If the Solaris folk feel that getupeercred() is insecure, they had better explain why their kernel is that broken. This is entirely unrelated to the known shortcomings of the ident IP protocol. The Solaris security kernel folks do, actually. However, there's no question that TRUST is inherently insecure, and that's what people are going to use if they can't get IDENT to work. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Tom, I think this patch is plenty complicated enough without adding useless restrictive options. +1 for no additonal GUC options. --Josh Berkus -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
KaiGai, The series of patches are the proposal of Security-Enhanced PostgreSQL (SE-PostgreSQL) for the upstreamed PostgreSQL 8.4 development cycle. Since I'm (Finally!) expecting the TrustedSolaris folks to put some work into PostgreSQL as well this year, I'm going to ask them to look over PGACE to see if this implementation is (still) generic enough to support TS as well. If it is, then it's probably generic enough to be a general building block. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Andreas, Writing to a different area was considered in pg, but there were more negative issues than positive. So imho pg_compresslog is the correct path forward. The current discussion is only about whether we want a more complex pg_compresslog and no change to current WAL, or an increased WAL size for a less complex implementation. Both would be able to compress the WAL to the same archive log size. Huh? As conceived, pg_compresslog does nothing to lower log volume for general purposes, just on-disk storage size for archiving. It doesn't help us at all with the tremendous amount of log we put out for an OLTP server, for example. Not that pg_compresslog isn't useful on its own for improving warm standby managability, but it's completely separate from addressing the we're logging too much issue. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Koichi, Andreas, 1) To deal with partial/inconsisitent write to the data file at crash recovery, we need full page writes at the first modification to pages after each checkpoint. It consumes much of WAL space. We need to find a way around this someday. Other DBs don't do this; it may be becuase they're less durable, or because they fixed the problem. I don't think there should be only one setting. It depend on how database is operated. Leaving wal_add_optiomization_info = off default does not bring any change in WAL and archive log handling. I understand some people may not be happy with additional 3% or so increase in WAL size, especially people who dosn't need archive log at all. So I prefer to leave the default off. Except that, is there any reason to turn this off if we are archiving? Maybe it should just be slaved to archive_command ... if we're not using PITR, it's off, if we are, it's on. 1) is there any throughput benefit for platforms with fast CPU but contrained I/O (e.g. 2-drive webservers)? Any penalty for servers with plentiful I/O? I've only run benchmarks with archive process running, because wal_add_optimization_info=on does not make sense if we don't archive WAL. In this situation, total I/O decreases because writes to archive log decreases. Because of 3% or so increase in WAL size, there will be increase in WAL write, but decrease in archive writes makes it up. Yeah, I was just looking for a way to make this a performance feature. I see now that it can't be. ;-) 3) How is this better than command-line compression for log-shipping? e.g. why do we need it in the database? I don't fully understand what command-line compression means. Simon suggested that this patch can be used with log-shipping and I agree. If we compare compression with gzip or other general purpose compression, compression ratio, CPU usage and I/O by pg_compresslog are all quite better than those in gzip. OK, that answered my question. This is why I don't like Josh's suggested name of wal_compressable eighter. WAL is compressable eighter way, only pg_compresslog would need to be more complex if you don't turn off the full page optimization. I think a good name would tell that you are turning off an optimization. (thus my wal_fullpage_optimization on/off) Well, as a PG hacker I find the name wal_fullpage_optimization quite baffling and I think our general user base will find it even more so. Now that I have Koichi's explanation of the problem, I vote for simply slaving this to the PITR settings and not having a separate option at all. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Hackers, Writing lots of additional code simply to remove a parameter that *might* be mis-interpreted doesn't sound useful to me, especially when bugs may leak in that way. My take is that this is simple and useful *and* we have it now; other ways don't yet exist, nor will they in time for 8.3. How about naming the parameter wal_compressable? That would indicate pretty clearly that the parameter is intended to be used with wal_compress and nothing else. However, I do agree with Andreas that anything which adds to WAL volume, even 3%, seems like going in the wrong direction. We already have higher log output than any comparable database (higher than InnoDB by 3x) and we should be looking for output to trim as well as compression. So the relevant question is whether the patch in its current form provides enough benefit to make it worthwhile for 8.3, or whether we should wait for 8.4. Questions: 1) is there any throughput benefit for platforms with fast CPU but contrained I/O (e.g. 2-drive webservers)? Any penalty for servers with plentiful I/O? 2) Will this patch make attempts to reduce WAL volume in the future significantly harder? 3) How is this better than command-line compression for log-shipping? e.g. why do we need it in the database? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
Simon, OK, different question: Why would anyone ever set full_page_compress = off? The only reason I can see is if compression costs us CPU but gains RAM I/O. I can think of a lot of applications ... benchmarks included ... which are CPU-bound but not RAM or I/O bound. For those applications, compression is a bad tradeoff. If, however, CPU used for compression is made up elsewhere through smaller file processing, then I'd agree that we don't need a switch. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] GUC description cleanup
Neil, Sure, I'll wait for 8.3 to branch. I have some cleanup I want to do for 8.3 too. Josh Berkus PostgreSQL @ Sun San Francisco 415-752-2500 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Updated version of FAQ_Solaris
Zdenek, Bruce, Has anyone updated the Solaris portion of runtime.sgml yet? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] drop if exists remainder
Tom, What's the consensus on this? Nobody else has chimed in, so I'm inclined to do no more on the gounds of insufficient demand. Let's decide before too much bitrot occurs, though. +1 :) +1 We were talking about this on IRC, and I feel that if we're going to do IF EXISTS for any objects, we should do it for all objects. Otherwise we risk a considerable amount of user confusion. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom, all, SELECT: currval UPDATE: nextval, setval USAGE: nextval, currval +1. --Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce, Tom, The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. Uh, how are they different? You mean just UPDATE and none of the others do anything? Yes, it would be nice to have real permissions for sequences, specifically USE (which allows nextval() and currval()) and UPDATE (which would allow setval() ). However, I don't know that the added functionality would justify breaking backwards-compatibility. Oh, and Bruce, I can't imagine needing specific relkind so I think that part's fine. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] small doc patch for postgresql.conf.sample
Bruce, Re-building CVS I noticed that I never submitted this doc-only patch to postgresql.conf.sample. -- Josh Berkus Aglio Database Solutions San Francisco Index: postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.127 diff -c -r1.127 postgresql.conf.sample *** postgresql.conf.sample 31 Aug 2004 04:53:44 - 1.127 --- postgresql.conf.sample 20 Sep 2004 00:58:28 - *** *** 11,16 --- 11,19 # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # + # Please note that re-commenting values does NOT revert them to the + # default values. + # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. *** *** 18,24 # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use ! # pg_ctl reload. #--- --- 21,28 # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use ! # pg_ctl reload. Some settings, such as listen_address, require ! # a full shutdown and restart to take effect. #--- ---(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: [PATCHES] Postgresql.conf Documentation change
Bruce, One issue is that the postgresql.conf file only gets installed as part of initdb so a shutdown/install/restart will replace the sample file, but the server file will retain comments until an initdb. This means some beta people might go into 8.0.0 final with comments, and some will not. I think there's a couple other patches that have the same effect (that is, initdb is required to take care of them). I think people using the beta should expect to re-build several times, anyway -- if they're not, they shouldn't use beta software. Also, this is basically a documentation bug, intended to make postgresql.conf easier to understand, so even if some beta tester doesn't load it, postgresql.conf still works .. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Postgresql.conf Documentation change
Tom, Attached is that patch I talked to you about on hackers. It changes the description at the top and uncomments most (but not all) of the lines in an effort to make it clear that commenting is not a way to restore the defaults. I've tested this on my machine and we end up with the exact same defaults with this patch as without, so no changes to initdb should be required. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Enterprise vertical business[EMAIL PROTECTED] and data analysis solutions(415) 752-2500 and database optimization fax 752-2387 utilizing Open Source technology San Francisco Index: postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.124 diff -c -r1.124 postgresql.conf.sample *** postgresql.conf.sample 8 Aug 2004 19:42:57 - 1.124 --- postgresql.conf.sample 9 Aug 2004 22:26:55 - *** *** 8,23 # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and ! # allowed values can be found in the PostgreSQL documentation. The ! # commented-out settings shown in this file represent the default values. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster ! # receives a SIGHUP. If you edit the file on a running system, you have ! # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. --- 8,25 # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and ! # allowed values can be found in the PostgreSQL documentation. Please ! # note that, unless otherwise indicated, commenting out a line ! # will NOT restore the default value. Also, some options are set at initdb ! # time and are not visible in this file; these are marked. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster ! # receives a SIGHUP. If you edit the file on a running system, you have ! # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. *** *** 25,30 --- 27,35 # CONFIGURATION FILES #--- + # Defaults for all config files are to use the $PGDATA directory of the database cluster + # being run. Commented below are examples of alternate directory assignments. + # Options given on the command line (e.g. -D ) override the below. # pgdata = '/usr/local/pgsql/data' # use data in another directory # hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another directory # ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another directory *** *** 37,62 # - Connection Settings - ! #listen_addresses = 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' = any ! #port = 5432 ! #max_connections = 100 # note: increasing max_connections costs about 500 bytes of shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. ! #superuser_reserved_connections = 2 ! #unix_socket_directory = '' ! #unix_socket_group = '' ! #unix_socket_permissions = 0777 # octal ! #rendezvous_name = '' # defaults to the computer name # - Security Authentication - ! #authentication_timeout = 60 # 1-600, in seconds ! #ssl = false ! #password_encryption = true ! #krb_server_keyfile = '' ! #db_user_namespace = false #--- --- 42,68 # - Connection Settings - ! listen_addresses = 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' = any ! port = 5432 ! max_connections = 100 # note: increasing max_connections costs about 500 bytes of shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. ! superuser_reserved_connections = 2 ! unix_socket_directory = '' ! unix_socket_group = '' ! unix_socket_permissions = 0777 # octal ! rendezvous_name = '' # defaults to the computer name if unset ! # - Security Authentication - ! authentication_timeout = 60 # 1-600, in seconds ! ssl = false ! password_encryption = true ! krb_server_keyfile = '' ! db_user_namespace = false
Re: [PATCHES] [PATCH] Re: [pgsql-advocacy] Why READ ONLY transactions?
Bruce, This seems like a valuable feature, as others have mentioned. However, should it also prevent changes to default_transaction_read_only? What is the use case for this functionality? I thought that this was rejected thouroughly by Tom some months ago. He argued pretty strongly that READ ONLY transactions were *not* a security feature and that trying to make them one would work very poorly. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [PATCH] Re: [pgsql-advocacy] Why READ ONLY transactions?
Sean, Um, why not make it an actual full blown security feature by applying the following patch? This gives PostgreSQL real read only transactions that users can't escape from. Notes about the patch: Way nifty. I vote in favor of this patch (suitably documented debugged) for 7.5. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Runtime.SGML diff ... please expedite!
Bruce, Patch applied. Thanks. Great. 'cause I need to patch the patch, due to a cut-and-paste error. Next patch coming Friday, much less extensive than this one. Is the HAS_RENDEZVOUS GUC still coming? Is there docs for it? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Runtime.SGML diff ... please expedite!
Tom, It should be removed from runtime.sgml then. Also, one of the Red Hat guys pointed out to me that SHOW_BTREE_BUILD_STATS seems to have crept back into runtime.sgml, although it was removed during 7.4 development. Could we have that out of there in the next patch? Will do. Is SHOW_BTREE gone, or is it just a Source Developer Option? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Runtime.SGML diff ... please expedite!
Tom, Also, one of the Red Hat guys pointed out to me that SHOW_BTREE_BUILD_STATS seems to have crept back into runtime.sgml, although it was removed during 7.4 development. Could we have that out of there in the next patch? Accoring to GUC.c this morning, the option is still there ... it's just been re-named to LOG_BTREE_BUILD_STATS. Is this a mistake in GUC.c, or should I put it back in Runtime.SGML under the new name? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
