Re: [HACKERS] 8.4 release planning
Greg Smith wrote: Where I suspect this is all is going to settle down into is that if 1) the SE GUC is on and 2) one of the tables in a join has rows filtered, then you can expect that a) it's possible that the result will leak information, which certainly need to be documented, As far as I can tell this is the case however you hide the information. If you implemented it with views you'll have the same issue. If you hide the existence of project p_id=TOPSECRET01 and people can run inserts then they can spot it. Likewise, it you have fkey references to the row then deletions can be used to spot it. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log_duration_sample config option patch
Euler Taveira de Oliveira wrote: IIRC pgFouine shows exact percentage of query by type; this new GUC implies we can not rely on it anymore. Also, you could skip us from logging long (and bad) running queries. Another statistics data in pgFouine will suffer from the same problem. I see your point in reducing amount of log data but I think it will limit the tool usability (you can always start/stop statement collection at runtime). The accuracy of numbers suffers - yes. But this option basically gives you the choice of logging all queries for a short time (leaving out queries before and after that time period) or some queries for a long time (leaving out randomly selected queries in between). If you're interested in a bigger picture, the numbers will be more meaningful. (Also, if the number of queries is high enough for someone to use this option, it means that there will be a lot of samples anyway.) For example, it might be more useful to log 1% of queries during the peak hours of each day of the month, than all queries during the peak hours of one day. Or you could log 0.01% of all queries always... Especially queries which are executed many times can be found just as well with this approach, and chronically slow queries will eventually pop up also. But if someone wants to specifically track any and all long queries, then there's the option to not enable log_duration_sample but to set log_min_duration_statement to something appropriate instead. Another point is that during the time of the biggest load (= the most interesting time) full logging would increase I/O load and bias the results (and degrade the service quality). By spreading out the load, the bias is smaller and the logging can actually be enabled at all during peak hours. Thanks for the feedback. timo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Greg Smith wrote: PostgreSQL advocacy point, one of the questions Tom asked about a bit upthread is still a bit hazy here. There are commercial database offerings selling into the trusted space already. While the use-cases you describe make perfect sense, I don't think it's clear to everyone yet if there's a unique draw to a PostgreSQL + selinux solution that the class of customers you're talking about would prefer it to purchasing one of those products. Is the cost savings the main driver here, or is there something else about a secure LAPP stack that makes it particularly compelling? According to the data available to me, it is a combination of doing it better than the other guys (e.g., a SELinux type interface instead of something handcrafted) and the usual cost savings. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Heikki Linnakangas píše v st 28. 01. 2009 v 09:24 +0200: Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: That implies a fairly robust and configurable system for adding to and rewriting system catalogs, which today we haven't got. And we won't ever have, because it's unnecessary and would be impossibly complex. We know how to do the catalog update: basically, dump, initdb, reload, then move the data in. There are some corner case issues like how to preserve toast table OIDs, but the idea that we are going to invent a special process for each catalog change is just not reasonable. Right, the dump+initdb+reload approach works quite well in both pg_upgrade and pg-migrator. I believe the biggest issue with that ATM is supporting dropped columns, and maybe there's something else, but it's fairly robust and works across any versions. It works but it is not ideal. Supporting dropped column requires lot of magic which probably will decrease robustness. When you have a tablespace then there is another shuffling magic which does not seems like something robust. And very important thing is that you need old version of postgreSQL installed, which is something what packagers does not want. Look on Oracle how does it. I have idea how to do it without old PostgreSQL version and with bootstrap process extension which should not be invasive and easily maintainable. I will send idea latter ... stay tuned ;-) Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot standby, recovery infra
I've been reviewing and massaging the so called recovery infra patch. To recap, the goal is to: - start background writer during (archive) recovery - skip the shutdown checkpoint at the end of recovery. Instead, the database is brought up immediately, and the bgwriter performs a normal online checkpoint, while we're already accepting connections. - keep track of when we reach a consistent point in the recovery, where we could let read-only backends in. Which is obviously required for hot standby The 1st and 2nd points provide some useful functionality, even without the rest of the hot standby patch. I've refactored the patch quite heavily, making it a lot simpler, and over 1/3 smaller than before: The signaling between the bgwriter and startup process during recovery was quite complicated. The startup process periodically sent checkpoint records to the bgwriter, so that bgwriter could perform restart points. I've replaced that by storing the last seen checkpoint in a shared memory in xlog.c. CreateRestartPoint() picks it up from there. This means that bgwriter can decide autonomously when to perform a restart point, it no longer needs to be told to do so by the startup process. Which is nice in a standby. What could happen before is that the standby processes a checkpoint record, and decides not to make it a restartpoint because not enough time has passed since last one. If we then get a long idle period after that, we'd really want to make the previous checkpoint record a restart point after all, after some time has passed. That is what will happen now, which is a usability enhancement, although the real motivation for this refactoring was to make the code simpler. The bgwriter is now always responsible for all checkpoints and restartpoints. (well, except for a stand-alone backend). Which makes it easier to understand what's going on, IMHO. There was one pretty fundamental bug in the minsafestartpoint handling: it was always set when a WAL file was opened for reading. Which means it was also moved backwards when the recovery began by reading the WAL segment containing last restart/checkpoint, rendering it useless for the purpose it was designed. Fortunately that was easy to fix. Another tiny bug was that log_restartpoints was not respected, because it was stored in a variable in startup process' memory, and wasn't seen by bgwriter. One aspect that troubles me a bit is the changes in XLogFlush. I guess we no longer have the problem that you can't start up the database if we've read in a corrupted page from disk, because we now start up before checkpointing. However, it does mean that if a corrupt page is read into shared buffers, we'll never be able to checkpoint. But then again, I guess that's already true without this patch. I feel quite good about this patch now. Given the amount of code churn, it requires testing, and I'll read it through one more time after sleeping over it. Simon, do you see anything wrong with this? (this patch is also in my git repository at git.postgresql.org, branch recoveryinfra.) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index bd6035d..30fea49 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -119,12 +119,26 @@ CheckpointStatsData CheckpointStats; */ TimeLineID ThisTimeLineID = 0; -/* Are we doing recovery from XLOG? */ +/* + * Are we doing recovery from XLOG? + * + * This is only ever true in the startup process, when it's replaying WAL. + * It's used in functions that need to act differently when called from a + * redo function (e.g skip WAL logging). To check whether the system is in + * recovery regardless of what process you're running in, use + * IsRecoveryProcessingMode(). + */ bool InRecovery = false; /* Are we recovering using offline XLOG archives? */ static bool InArchiveRecovery = false; +/* + * Local copy of shared RecoveryProcessingMode variable. True actually + * means not known, need to check the shared state + */ +static bool LocalRecoveryProcessingMode = true; + /* Was the last xlog file restored from archive, or local? */ static bool restoredFromArchive = false; @@ -133,16 +147,22 @@ static char *recoveryRestoreCommand = NULL; static bool recoveryTarget = false; static bool recoveryTargetExact = false; static bool recoveryTargetInclusive = true; -static bool recoveryLogRestartpoints = false; static TransactionId recoveryTargetXid; static TimestampTz recoveryTargetTime; static TimestampTz recoveryLastXTime = 0; +/* + * log_restartpoints is stored in shared memory because it needs to be + * accessed by bgwriter when it performs restartpoints + */ /* if recoveryStopsHere returns true, it saves actual stop xid/time here */ static TransactionId recoveryStopXid; static TimestampTz recoveryStopTime; static bool recoveryStopAfter;
[HACKERS] How to get SE-PostgreSQL acceptable
I have re-reviewed the SE-PostgreSQL patch set. I don't want to talk about here whether the security model is appropriate, how foreign keys are to be handled etc. I want to discuss that I really don't like the architecture of this patch. I have said this before in previous review rounds, but let me make it a little clearer here. Steps to get your patch accepted: One feature at a time - By my count, your patch set implements at least three or four major features: 1a. System-wide consistency in access control 1b. Mandatory access control 2. Row-level security 3. Additional privileges (permission to alter tables, modify large objects, etc.) You may object and say, these morally belong together in a proper/professional/adequate implementation of this feature you have planned. But realistically, they can be separated. And if a feature is controversial, difficult, or complicated, it would be in your interest to deal with one feature at a time. Deal means the whole round: discuss design, write patch, review, test, commit, relax. If I had to do this, I would first write a patch for #1: A patch that additionally executes existing privilege checks against an SELinux policy. Existing privilege checks are a well-defined set: they mostly happen through pg_xxx_aclcheck() functions. Hook your checks in there. This patch would already provide useful functionality, but it would be much easier to review and verify, because we know how permission checking works in the existing system, so we can compare them. And it would build confidence among developers and users about the whole idea, about SELinux integration etc. Then you can tackle #3: Place permission checks in more places. This patch would be simple to review and verify, because at this point we already know how SELinux integration works, and making more use of it is not such a big step. At this point we could also discuss whether some of these additional checks are useful enough to also expose via the traditional SQL ACLs, which would further simplify the patch. Row-level security should also be developed as a completely separate feature, without any SELinux tie-in initially. This is not only important to make review and verification simpler, but also because we really need a wider test community for such a tricky feature. And the set of SELinux users is quite limited, and the intersection with PostgreSQL developers is almost empty. This was already previously discussed at length. No in-code frameworks - Write your code so that it is fully integrated with the existing code. Or write a plugin interface and then write a plugin. But don't invent a framework because you are afraid to integrate the new code with the old code. As mentioned above, permission checks are done through pg_xxx_aclcheck() functions, which is enough of a framework. I wouldn't want yet another framework that does more permission checking at other times and places. If the existing interfaces are not adequate for your purpose, by all means, extend, refactor, or rewrite them. But don't just avoid it because you don't want to interfere with the existing code. So scrap the whole PGACE thing. If you need to refactor the aclcheck interfaces, that's another separate patch, which can easily be reviewed and verified, simplifying the following patches even further. These things are not going to get done within two weeks. But if you start producing small, self-contained patches along the above lines, you are much more likely to make progress over the coming development cycle. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
Peter Eisentraut wrote: On Tuesday 27 January 2009 23:59:46 Magnus Hagander wrote: Marko Kreen wrote: On 1/27/09, Peter Eisentraut pete...@gmx.net wrote: On Tuesday 27 January 2009 15:51:02 Marko Kreen wrote: Such app already exists: http://ozlabs.org/~jk/projects/patchwork/ So it's a matter of just setting it up. I was in fact in the process of setting that up just now. :-) Nice to know. :) I feel that even if we decide to do our own solution it would be good to try existing solution first. IIRC, we already installed and tried this a while ago. I don't remember exactly what it failed on, but there was something pretty clear. But maybe it's been fixed by now. Details? I find no public record of this. I don't recall specifically :-( Which in itself might mean it's worthwhile to make another try. But i recall trying that one and reviewboard, and none of them was what we needed. If you look at Berkus' list of required features (if you haven't seen it, I'm sure he'll be happy to send you a copy), you will see that it doesn't come close. We can always argue if his list is reasonable :-), but that's just a fact. It has nothing about round-robin reviewers. It has no keep-track-of-nagging features. It has no integration with our mail archives. At least it didn't then - it also appears to have no online documentation, so I can't easily check now :-P //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Magnus Hagander a écrit : Peter Eisentraut wrote: On Tuesday 27 January 2009 23:59:46 Magnus Hagander wrote: Marko Kreen wrote: On 1/27/09, Peter Eisentraut pete...@gmx.net wrote: On Tuesday 27 January 2009 15:51:02 Marko Kreen wrote: Such app already exists: http://ozlabs.org/~jk/projects/patchwork/ So it's a matter of just setting it up. I was in fact in the process of setting that up just now. :-) Nice to know. :) I feel that even if we decide to do our own solution it would be good to try existing solution first. IIRC, we already installed and tried this a while ago. I don't remember exactly what it failed on, but there was something pretty clear. But maybe it's been fixed by now. Details? I find no public record of this. I don't recall specifically :-( Which in itself might mean it's worthwhile to make another try. But i recall trying that one and reviewboard, and none of them was what we needed. If you look at Berkus' list of required features (if you haven't seen it, I'm sure he'll be happy to send you a copy), Josh, can you please give the link to this list of feature ? you will see that it doesn't come close. We can always argue if his list is reasonable :-), but that's just a fact. It has nothing about round-robin reviewers. It has no keep-track-of-nagging features. It has no integration with our mail archives. At least it didn't then - it also appears to have no online documentation, so I can't easily check now :-P //Magnus - -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkmAOAMACgkQo/dppWjpEvxn6ACg2F5to39Q9fW9vvm25E9fW2Zl GAAAoOP9yMO3WuT5Rj98s7OyHhDYK4Ui =rP62 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Column privileges for system catalogs
Is it now acceptable to use column privileges for system catalogs? For the new SQL/MED catalogs we have used the old system of revoking all permissions and having a filtered view on top of it (tradition since pg_shadow), but I figured we could do this properly now by just revoking permissions on a specific column. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Richard Huxton wrote: Greg Smith wrote: Where I suspect this is all is going to settle down into is that if 1) the SE GUC is on and 2) one of the tables in a join has rows filtered, then you can expect that a) it's possible that the result will leak information, which certainly need to be documented, As far as I can tell this is the case however you hide the information. If you implemented it with views you'll have the same issue. If you hide the existence of project p_id=TOPSECRET01 and people can run inserts then they can spot it. Likewise, it you have fkey references to the row then deletions can be used to spot it. It is a covert channel discussion. At least, SE-PostgreSQL does not care about hiding its existence, so it does not prevent user to infer the existence of a tuple with same key value, using PK confliction. (Please note that he must have a info about PK value or lucky to make a key confliction.) But, it enables to prevent unclassified user to read the tuple, and him to know an info the tuple contains p_id=TOPSECRET01 as a result of this read action. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add Windows 7 support
Dave Page wrote: I don't think it's enough that we need to care about it really. I'm thinking we could perhaps even just never set that, and not bother with the version check... That was how I originally coded it, but figured we might as well set it if we can - it's not like it's expensive to do. But perhaps we should set it only when launching as a service, and not when running from the commandline? We could. I'm not sure there's a great deal of need - most people will run as a service, and it won't make any difference for those that start the postmaster directly. I have applied this updated patch. It simplifies the if branches a bit (imho, that is), and also adds the switch to only make the change when starting as a service. //Magnus *** a/src/bin/pg_ctl/pg_ctl.c --- b/src/bin/pg_ctl/pg_ctl.c *** *** 121,127 static void pgwin32_SetServiceStatus(DWORD); static void WINAPI pgwin32_ServiceHandler(DWORD); static void WINAPI pgwin32_ServiceMain(DWORD, LPTSTR *); static void pgwin32_doRunAsService(void); ! static int CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo); static SERVICE_STATUS status; static SERVICE_STATUS_HANDLE hStatus = (SERVICE_STATUS_HANDLE) 0; --- 121,127 static void WINAPI pgwin32_ServiceHandler(DWORD); static void WINAPI pgwin32_ServiceMain(DWORD, LPTSTR *); static void pgwin32_doRunAsService(void); ! static int CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo, bool as_service); static SERVICE_STATUS status; static SERVICE_STATUS_HANDLE hStatus = (SERVICE_STATUS_HANDLE) 0; *** *** 385,391 start_postmaster(void) snprintf(cmd, MAXPGPATH, CMD /C SYSTEMQUOTE \%s\ %s%s \%s\ 21 SYSTEMQUOTE, postgres_path, pgdata_opt, post_opts, DEVNULL); ! if (!CreateRestrictedProcess(cmd, pi)) return GetLastError(); CloseHandle(pi.hProcess); CloseHandle(pi.hThread); --- 385,391 snprintf(cmd, MAXPGPATH, CMD /C SYSTEMQUOTE \%s\ %s%s \%s\ 21 SYSTEMQUOTE, postgres_path, pgdata_opt, post_opts, DEVNULL); ! if (!CreateRestrictedProcess(cmd, pi, false)) return GetLastError(); CloseHandle(pi.hProcess); CloseHandle(pi.hThread); *** *** 1210,1216 pgwin32_ServiceMain(DWORD argc, LPTSTR * argv) /* Start the postmaster */ pgwin32_SetServiceStatus(SERVICE_START_PENDING); ! if (!CreateRestrictedProcess(pgwin32_CommandLine(false), pi)) { pgwin32_SetServiceStatus(SERVICE_STOPPED); return; --- 1210,1216 /* Start the postmaster */ pgwin32_SetServiceStatus(SERVICE_START_PENDING); ! if (!CreateRestrictedProcess(pgwin32_CommandLine(false), pi, true)) { pgwin32_SetServiceStatus(SERVICE_STOPPED); return; *** *** 1313,1319 typedef BOOL(WINAPI * __QueryInformationJobObject) (HANDLE, JOBOBJECTINFOCLASS, * automatically destroyed when pg_ctl exits. */ static int ! CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo) { int r; BOOL b; --- 1313,1319 * automatically destroyed when pg_ctl exits. */ static int ! CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo, bool as_service) { int r; BOOL b; *** *** 1449,1454 CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo) --- 1449,1455 JOBOBJECT_BASIC_LIMIT_INFORMATION basicLimit; JOBOBJECT_BASIC_UI_RESTRICTIONS uiRestrictions; JOBOBJECT_SECURITY_LIMIT_INFORMATION securityLimit; + OSVERSIONINFO osv; ZeroMemory(basicLimit, sizeof(basicLimit)); ZeroMemory(uiRestrictions, sizeof(uiRestrictions)); *** *** 1459,1466 CreateRestrictedProcess(char *cmd, PROCESS_INFORMATION * processInfo) _SetInformationJobObject(job, JobObjectBasicLimitInformation, basicLimit, sizeof(basicLimit)); uiRestrictions.UIRestrictionsClass = JOB_OBJECT_UILIMIT_DESKTOP | JOB_OBJECT_UILIMIT_DISPLAYSETTINGS | ! JOB_OBJECT_UILIMIT_EXITWINDOWS | JOB_OBJECT_UILIMIT_HANDLES | JOB_OBJECT_UILIMIT_READCLIPBOARD | JOB_OBJECT_UILIMIT_SYSTEMPARAMETERS | JOB_OBJECT_UILIMIT_WRITECLIPBOARD; _SetInformationJobObject(job, JobObjectBasicUIRestrictions, uiRestrictions, sizeof(uiRestrictions)); securityLimit.SecurityLimitFlags = JOB_OBJECT_SECURITY_NO_ADMIN | JOB_OBJECT_SECURITY_ONLY_TOKEN; --- 1460,1482 _SetInformationJobObject(job, JobObjectBasicLimitInformation, basicLimit, sizeof(basicLimit)); uiRestrictions.UIRestrictionsClass = JOB_OBJECT_UILIMIT_DESKTOP | JOB_OBJECT_UILIMIT_DISPLAYSETTINGS | ! JOB_OBJECT_UILIMIT_EXITWINDOWS | JOB_OBJECT_UILIMIT_READCLIPBOARD | JOB_OBJECT_UILIMIT_SYSTEMPARAMETERS | JOB_OBJECT_UILIMIT_WRITECLIPBOARD; + + if (as_service) + { + osv.dwOSVersionInfoSize = sizeof(osv); + if (!GetVersionEx(osv) || + osv.dwMajorVersion 6 || + (osv.dwMajorVersion == 6 osv.dwMinorVersion
Re: [HACKERS] Index Scan cost expression
Amit Gupta amit.pc.gu...@gmail.com writes: Moreover it only models a single index scan. It assumes nothing is cached prior to the index scan which is very much not true if we're repeatedly scanning similar ranges of keys. It's reasonable to assume that nothing is cached for estimating the cost. Not really, but doing otherwise is just hard. There's nothing in the query to give Postgres a hint about which tables are more heavily used and more likely to be in cache than others. 1 block !/O per index probe is a considerable cost. Well it's closer to 0 than n... Well they're all different but I suspect the root of what you're observing are all the same thing. Cache doesn't affect any of these nodes unless we start with something in the cache from previous queries and we don't model that. We assume each query and even each plan node is run on a cold cache. Cost of evaluating operators depend heavily on available cache size, which is not considered by the Postgre optimizer at many places. For instance, - # I/O for sorting = T log_M T/M, where T is size of relation, and M is available memory. However, postgre assumes constant avaliable memory of 1M for sorting. Since sort is a blocking operator, which means that exeuction of other operators should halt when sorting is in progress, it should be able to hog all the available memory. Well you're free to raise work_mem. Also, while sorting all happens in one step the memory remains used after the sort is done. It's true that if work_mem is set low but there's lots of cache available it will speed up spilled hashes and on-disk merge sorts. But you would be better off raising work_mem in those cases. If they're spilling because they don't fit in RAM at all then will cache still have an effect? I'm also not clear what kinds of formulas work for this. It has to be something that can be composed effectively. That is, even if a plan node doesn't want to discount itself at all for repetitions it has to include the discount that any subplans have asked for. For example a large sequential scan which expects to overflow effective_cache_size might not want to be discounted at all, but if it has a subquery which scans a small table it will want to discount that 100% for any repetitions since it'll be cached after the first scan. We also need robust statistics to feed into these complex cost expression for accurate estimation. For instance, Oracle lets user to analyze each column to create distribution graph using histograms. These histograms is used by the optimizer to figure out exact number of rows (and their values ) output by an operator. Well we certainly compute histograms and use them for selectivity estimates. The challenge in this case is that you need to combine the distribution from the outer node with the distribution in the inner node to estimate how much overlap in disk accesses will result. So you need more than histograms, you need some kind of cross-table statistics. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
* Zdenek Kotala (zdenek.kot...@sun.com) wrote: And very important thing is that you need old version of postgreSQL installed, which is something what packagers does not want. Look on Oracle how does it. Just as a counter-point, Debian handles multiple concurrently installed versions of PostgreSQL just fine, in large part to specifically deal with the smooth migration challenge (though also because we realize people may want to continue using the old version while others may want to install the new version). Not sure if that's something the community wants to encourage other packagers to do or if we should look at making it easier to do, but it's at least possible and has been done for a pretty large distribution. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Hot standby, recovery infra
On Wed, 2009-01-28 at 12:04 +0200, Heikki Linnakangas wrote: I've been reviewing and massaging the so called recovery infra patch. Thanks. I feel quite good about this patch now. Given the amount of code churn, it requires testing, and I'll read it through one more time after sleeping over it. There's nothing major I feel we should discuss. The way restartpoints happen is a useful improvement, thanks. Simon, do you see anything wrong with this? Few minor points * I think we are now renaming the recovery.conf file too early. The comment says We have already restored all the WAL segments we need from the archive, and we trust that they are not going to go away even if we crash. We have, but the files overwrite each other as they arrive, so if the last restartpoint is not in the last restored WAL file then it will only exist in the archive. The recovery.conf is the only place where we store the information on where the archive is and how to access it, so by renaming it out of the way we will be unable to crash recover until the first checkpoint is complete. So the way this was in the original patch is the correct way to go, AFAICS. * my original intention was to deprecate log_restartpoints and would still like to do so. log_checkpoints does just as well for that. Even less code than before... * comment on BgWriterShmemInit() refers to CHECKPOINT_IS_STARTUP, but the actual define is CHECKPOINT_STARTUP. Would prefer the is version because it matches the IS_SHUTDOWN naming. * In CreateCheckpoint() the if test on TruncateSubtrans() has been removed, but the comment has not been changed (to explain why). * PG_CONTROL_VERSION bump should be just one increment, to 844. I deliberately had it higher to help spot mismatches earlier, and to avoid needless patch conflicts. So it looks pretty much ready for commit very soon. We should continue to measure performance of recovery in the light of these changes. I still feel that fsyncing the control file on each XLogFileRead() will give a noticeable performance penalty, mostly because we know doing exactly the same thing in normal running caused a performance penalty. But that is easily changed and cannot be done with any certainty without wider feedback, so no reason to delay code commit. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] posix_fadvise v22
Tom Lane t...@sss.pgh.pa.us writes: What I intend to do over the next day or so is commit the prefetch infrastructure and the bitmap scan prefetch logic, but I'm bouncing the indexscan part back for rework. I think that it should be implemented in or near index_getnext() and pay attention to effective_io_concurrency. The biggest question I have here is about doing it at the index_* abstraction level. I've looked pretty hard at how to do this and run into some pretty major problems. I don't see any way to do it without changing the access method api. The index_* methods cannot start fiddling with the current scan position without messing up things like CURRENT OF and mark/restore irrecoverably. But if we're going to change the index am api then we lose all of the advantages of putting the logic in indexam.c in the first place. It won't help any other index am without special code in each one The best plan I came up with at this level is to add an am method amgetprefetchbitmap(IndexScanDesc scan, ScanDirection direction, TIDBitmap *bitmap, int nblocks) Which returns up to nblocks worth of bitmap starting from the current scan position in the specified direction based on whatever's convenient to the internal representation. I think it would be best if it stopped at the end of the page at least if the next index page isn't in shared buffers. Then nodeIndexscan.c would keep track of how the value of target_prefetch just like nodeBitmapHeapScan, incrementing it whenever the caller continues the scan and resetting it to zero if the direction changes. However the getprefetchbitmap() call would only remove duplicates from the upcoming blocks. It wouldn't know which blocks have already been prefetched from previous calls. So nodeIndexscan would also have to remove duplicates itself. This splitting the work between three layers of abstraction is pretty messy and creates a lot of duplicated work and doesn't seem to buy us anything. It *still* wouldn't help any non-btree index types until they add the new method -- and if they add the new method they might as well just add the USE_PREFETCH code anyways. I don't see how the new method is useful for anything else. I have another plan which would be a lot simpler but is a much more brute-force approach. If we add a new scan pointer in addition to the current position and the mark and add a slew of new methods like getnext_prefetch() and reset_prefetch() to reset it to the current position. Also, add a hash table internal to PrefetchBuffer and have it return a boolean indicating whether it actually did a prefetch. Then index_prefetch would reset the prefetch pointer and scan forward using it calling PrefetchBuffer on *every* pointer counting how many trues returns it sees from PrefetchBuffer.* *[Hm, not quite, we want to count recent prefetches that probably are still queued separately from old ones that are probably in cache. And we also have to think about how long to treat prefetches as probably still being in cache. But with some additional thought I think this could be made to work.] -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Silence compiler warning on win32.
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Tom Lane wrote: Surely this patch is wrong. It is suppressing, not fixing, a critical warning about a datatype mismatch. You mean the signed vs unsigned part? Other than that, int and dword are always the same on win32... Hmm, need more caffeine I guess. I was thinking dword == long. But in any case, I'd feel a lot more comfortable if the patch ifdef'd the declaration of exit_status to match, rather than forcing a cast of the pointer value. Just a couple weeks ago I wasted a great deal of time finding a bug that was created by someone overriding this exact type of compiler warning with a cast to something that *wasn't* binary compatible. (It worked fine on the author's machine, of course, but not so much on one with a different sizeof long...) Hmm. I looked at that, but that kind of just moves things around. If i change that variable to be DWORD, it still stuffs it into statuses[i] three lines further down, which then means that the whole definition of the function wait_for_tests need to be #ifdefed. I guess the proper solution in that case is to #define a datatype used for return codes. Is it really worth that for this, though? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Sorry for long description again. Peter Eisentraut wrote: I have re-reviewed the SE-PostgreSQL patch set. I don't want to talk about here whether the security model is appropriate, how foreign keys are to be handled etc. I want to discuss that I really don't like the architecture of this patch. I have said this before in previous review rounds, but let me make it a little clearer here. Steps to get your patch accepted: One feature at a time - By my count, your patch set implements at least three or four major features: 1a. System-wide consistency in access control 1b. Mandatory access control 2. Row-level security 3. Additional privileges (permission to alter tables, modify large objects, etc.) You may object and say, these morally belong together in a proper/professional/adequate implementation of this feature you have planned. But realistically, they can be separated. And if a feature is controversial, difficult, or complicated, it would be in your interest to deal with one feature at a time. Deal means the whole round: discuss design, write patch, review, test, commit, relax. I can't afford not to make clear these issues. In this case, (1a) and (1b) are indivisible, because I want to apply SELinux as a security server of (1a), SELinux has to be MAC feature. However, I don't deny a (1b) without (1a) feature like Oracle Label Security, which is not a facility I want to make. I guess (1b) also contains a feature to manage security label. Please note that I don't really want to (1b) only feature. The system-wide consistency in access control is the soul. We can consider (2) as a separated issue. In fact, I already provide a GUC: sepostgresql_row_level=on/off. It also means whether users accept a set of benefit(row-level granularity) and demerit(cover channel of PK/FK), or not. OK, I don't discuss about covert channel here. The (3) is involved to (1b). As a basic assumption, MAC system need to check *any actions* come from clients, even if they have superuser privileges. We already have SQL-level privileges, like ACL_SELECT and so on. However, some of operations implicitly assume request come from superuser is safe. For example, superuser is allowed to load a discretionary shared library file, but it also means he is trusted. If security design (which is defined by (1a) and (1b) primarily) does not allow unconditionally trusted user, it is quite natural to apply additional privileges, even if vanilla one unconditionally trust superuser. Unfortunatelly, we don't have any access controls on large object, but SELinux does not want to provide an information store without mandatory access controls. So, SE-PostgreSQL applies its access controls on large object. Thus, (3) is also indivisible from (1a). If I had to do this, I would first write a patch for #1: A patch that additionally executes existing privilege checks against an SELinux policy. Existing privilege checks are a well-defined set: they mostly happen through pg_xxx_aclcheck() functions. Hook your checks in there. I had a concern about pg_xxx_aclcheck(). When we accesses a table via a view, pg_xxx_aclcheck() checkes view's ACLs and table's ACLs are left for unchecked, even if it accesses same object. SELinux always makes its decision based on the attribute of object itself. In other word, the route to access taget object does not make any affect on its decision making. For example, a filesystem object (inode) can have multiple pathname using hard link on operating system. SELinux always makes ite decision based on inode's attribute (called as security context), independent from its pathname. Do you know a previous hot discussion between SELinux and AppArmor in Linux developers? Please note that I don't deny the benefit of view. It has various effective usages so we cannot ignore them. However, it mismatched with the security design, so I needed to put security hooks on different strategic points. Row-level security should also be developed as a completely separate feature, without any SELinux tie-in initially. This is not only important to make review and verification simpler, but also because we really need a wider test community for such a tricky feature. And the set of SELinux users is quite limited, and the intersection with PostgreSQL developers is almost empty. This was already previously discussed at length. It would be possible. However, note that we should not implement the first step ignoring upcoming facility. Eventually I would implement SE-PostgreSQL 1st edition with paying attention for the upcoming row level security. No in-code frameworks - Write your code so that it is fully integrated with the existing code. Or write a plugin interface and then write a plugin. But don't invent a framework because you are afraid to integrate the new code with the old code. At least, PGACE is not a purpose for me. If fully integrated archtencture is
Re: [HACKERS] 8.4 release planning
On Wed, Jan 28, 2009 at 4:28 AM, Peter Eisentraut pete...@gmx.net wrote: Greg Smith wrote: PostgreSQL advocacy point, one of the questions Tom asked about a bit upthread is still a bit hazy here. There are commercial database offerings selling into the trusted space already. While the use-cases you describe make perfect sense, I don't think it's clear to everyone yet if there's a unique draw to a PostgreSQL + selinux solution that the class of customers you're talking about would prefer it to purchasing one of those products. Is the cost savings the main driver here, or is there something else about a secure LAPP stack that makes it particularly compelling? According to the data available to me, it is a combination of doing it better than the other guys (e.g., a SELinux type interface instead of something handcrafted) and the usual cost savings. I don't know about better, but I would definitely say that it's a more integrated (with the OS) solution. Can you get Oracle to use SELinux policies? Sure. But it would take a combination of Label Security, Fine Grained Access Control tweaks, custom C functions, and custom policies to handle the access control. And, it would cost a helluva lot of money. In short, this would make Postgres quite a bit more appetizing to those who need this functionality, those who prefer SELinux-based policies, and those who don't have the time/money to do it in systems like Oracle. How many people is that? Based on my consulting experience and questions from DoD/DoE people specifically, I think the number of people needing this feature is fairly small right now. But, it wouldn't hurt us to have it. Just to make it clear, this feature wouldn't make Postgres a trusted database in any certification sense. So, using that term would likely cause confusion and get people who used it thinking it had an EAL certification into trouble. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] 8.4 release planning
Robert Treat wrote: The problem is that the pain point is extremely high for missing a given release cycle. If you don't make a specific release, you have a 12-14 month wait for feature arrival. Given that choice, someone who deperately need (aka wants) HS/SEPostgres/Win32/HOT/IPU/etc... will likely be willing to push a release 3-6 months for that one feature. There will always be some features that people are willing to push a release for, if it's a feature they want. At least I hope so - because that means we keep adding new features that people want. But as long as there is some overlap in development timelines - which there will *always* be - there will always be some patch that is not quite ready on time. If the release is pushed back, maybe some other patch could also have been finished by the new deadline - should that also be included? What about a completely new feature that isn't even started yet, but that could easily be finished before the new deadline? What makes those less worthy? The question is, how long do we make people wait for *other* features. It delays this version *and* the next. Incidentally, this is probably why people didnt worry about making a given commitfest. The pain point was low, so there was no percieved need to rework a patch for a specific commit, since there was another one just a couple months away. However, we still see a rush of patches at the final freeze because people know that there is no tommorrow at that point. A problem at this point is that we are basically serializing the project over one or a couple of patches. All those people who aren't qualified to review/work on HS or SEPG are left in a position where they can't get anything done. Sure, they can work on patches offline, and add them to a hypothetical future commitfest that they have no clue when it's going to happen, so they don't know when they need to be available to deal with feedback. And we're back to ending up with a lot of conflicting patches simply because they sit in the queue for too long. That's a lot of developer talent wasted. The commitfests were designed in part to get around this - to get developers quick feedback so they can get on with more features. The final commitfest being much longer than the others by design already makes this hard. Dragging it out even longer makes it an even bigger failure in this way. We can't just say that everybody should help with these patches. Not everybody is qualified to do so, or has an interest to, while they're still both qualified and interested in working on other things for 8.5. In the third place, unless we get an upgrade-in-place process that works all the time, we would be looking at maintaining twice as many back branches in order to provide the same kind of release lifespan we have today. We are at the limit of what we can realistically do in back-branch maintenance already :-( Yeah, I can't argue with that. I'm not sure it's an unsolvable problem though; if odd/even release maintenance doesn't sound good, we could do something like ubuntus LTS, where we pick 1 release every 3 years to make a long-term support commitment (I think 5 years is our current max), and keep other releases on a shorter lifespan (1 or 2 years). Certainly having IPU (or is that UIP?) would make that an easier decision. We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. The PostgreSQL tree tends to be a lot more stable than others. In many cases, you can just snapshot CVS HEAD and get more or less the same things. Perhaps if someone were to simply maintain a bunch of tags or branches against known feature-points in the system in a separate SCM somewhere that'd be enough for people who desperately need the features - or who just want to test them out earlier. That would be close to zero cost for the core project to maintain. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Josh Berkus wrote: That's modest. I've talked to several oracle and db2 shops that want a standby for reporting that has relatively easy setup/maintenance (handling ddl is a big part of this) and the HS feature your working on will give them something as good as what they are getting now. So yeah, HS appeals to future users as well. I've talked to some of my clients, and while they *want* synch or near-synch HS, even slow HS is useful to them *now*. One client is planning on deploying a rather complex FS cloning infrastructure just to have a bunch of reporting, testing and read-only search databases they need. They'd be thrilled with an HS feature which produced DBs which were an hour out of date (or even 6 hours out of date), but ran read-only queries. I have a lot of clients who would be thrilled to have stuff that's been in our tree for half a year by now, and they'd be thrilled to have it *now*. How much extra should we have them wait for the needs of your clients? (Yes, I have clients now who would very much like HS as well, of course, but that's not the point) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Robert Haas wrote: I think the best thing we could do overall is to set release dates and stick to them. If your patch is not ready, well, at least it will get out in a defined amount of time. Right now, the *real* problem with it being pushed to the next release is you don't know how successful some other guy will be at persuading us to delay the next release. +1, LOL. Let's not forget that we've already got CTE, window functions, partial vacuums, and column-level permissions, all of which are major features that should benefit a lot of people. I hope Hot Standby gets committed but even if it doesn't, I'm still going to get a lot of benefit out of this release, so I'd like it to happen on some sort of reasonable time scale. Agreed. Even without HS, this will be one of the biggest releases we've had in a while, IMHO. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
However, we have to make clear whether the PGACE architecture is incorrect, or not, at first. I think the name of PGACE is not important, but it is necessary to make SELinux's decision in similar strategic point in finally. I've been thinking about this issue as well. I think a framework of some kind could be acceptable, but only if we're convinced that the framework is really general enough to handle all of the cases that we care about. For example, can we back-port our existing DAC infrastructure to use PGACE? Is it really true that SE-PostgreSQL interacts through the rest of the system ONLY through PGACE? If the answers to both questions are YES, then it's a framework. If the answer to either question is NO, then it's just a bunch of places where you needed to stick code to make SE-PostgreSQL work. I haven't read the code, but from reading the docs, I have a feeling that right now the answer to both questions are NO, which means it doesn't really have a lot of value. One example of this is the pg_security system catalog. The catalog representation you're proposing is probably just fine for associating OIDs to SELinux security labels. But trying to present it as a general thing that some other security implementation could reuse just doesn't seem realistic. Who is to say that the next person who writes an enhanced security feature will want to use text as the representation for their security domains? It could just as easily be two integers, or an array of booleans. This is after all a database product, so the chances that someone would want to do something with structured data seem non-negligible. In the end, you're going to have to be the one who makes the decision on which way to go. In some ways, I think that a plugin architecture would be better for everyone: we worry about the things on our side of the abstraction boundary, and you worry about the things on your side. Potentially you or someone else can release enhanced security plugins without needing any changes to core, and potentially on a different release cycle. On the other hand, a plugin architecture is probably going to be a lot of work. It seems that to install the plugin you would need to make system catalog changes as well as stuff additional system attributes into every table, which are relatively invasive changes. And you'll need to convince everyone that it's really a plug-in architecture and not just a special case for SE-PostgreSQL, so you'll need to prove that there are multiple viable clients, perhaps by backporting our existing DAC. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Magnus Hagander mag...@hagander.net writes: Josh Berkus wrote: One client is planning on deploying a rather complex FS cloning infrastructure just to have a bunch of reporting, testing and read-only search databases they need. They'd be thrilled with an HS feature which produced DBs which were an hour out of date (or even 6 hours out of date), but ran read-only queries. I have a lot of clients who would be thrilled to have stuff that's been in our tree for half a year by now, and they'd be thrilled to have it *now*. How much extra should we have them wait for the needs of your clients? I really am unconvinced by the argument that delaying existing features is a big deal. Logically it's less of a big deal than delaying HS a whole release cycle which I already said I think isn't a big deal either. This is purely a question of latency between development and release; we still get just as much in each release, it's just 6-12 months later than it might have been. What bothers me is delaying work on things like Bitmap Indexes which won't really start in earnest until Gianni can get feedback from the lists after the release. Or Join Removal which Simon isn't going to look at until after HS is committed (not *released* -- once it's *committed* he'll be free to go on to other things). This would impact *bandwidth* of development which I think is a much bigger deal. It reduces the amount of new features in each release, not just which release they fall in. I'm a bit shocked by how long Tom expects the release cycle to take even if we froze the code today. I guess I forget how long it takes and how many steps there are from past releases. If it's going to be 9+ months between Nov 1st and the first commitfest I'm worried about how many patches will be languishing in the queue with their authors having moved on to other more fruitful pastures in the mean time. If we delay further we're talking about close to a year with developers left hanging. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, recovery infra
Hi, On Wed, Jan 28, 2009 at 7:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I've been reviewing and massaging the so called recovery infra patch. Great! I feel quite good about this patch now. Given the amount of code churn, it requires testing, and I'll read it through one more time after sleeping over it. Simon, do you see anything wrong with this? I also read this patch and found something odd. I apologize if I misread it.. @@ -507,7 +550,7 @@ CheckArchiveTimeout(void) pg_time_t now; pg_time_t last_time; - if (XLogArchiveTimeout = 0) + if (XLogArchiveTimeout = 0 || !IsRecoveryProcessingMode()) The above change destroys archive_timeout because checking the timeout is always skipped after recovery is done. @@ -355,6 +359,27 @@ BackgroundWriterMain(void) */ PG_SETMASK(UnBlockSig); + BgWriterRecoveryMode = IsRecoveryProcessingMode(); + + if (BgWriterRecoveryMode) + elog(DEBUG1, bgwriter starting during recovery); + else + InitXLOGAccess(); Why is InitXLOGAccess() called also here when bgwriter is started after recovery? That is already called by AuxiliaryProcessMain(). @@ -1302,7 +1314,7 @@ ServerLoop(void) * state that prevents it, start one. It doesn't matter if this * fails, we'll just try again later. */ - if (BgWriterPID == 0 pmState == PM_RUN) + if (BgWriterPID == 0 (pmState == PM_RUN || pmState == PM_RECOVERY)) BgWriterPID = StartBackgroundWriter(); Likewise, we should try to start also the stats collector during recovery? @@ -2103,7 +2148,8 @@ XLogFileInit(uint32 log, uint32 seg, unlink(tmppath); } - elog(DEBUG2, done creating and filling new WAL file); + XLogFileName(tmppath, ThisTimeLineID, log, seg); + elog(DEBUG2, done creating and filling new WAL file %s, tmppath); This debug message is somewhat confusing, because the WAL file represented as tmppath might have been already created by previous XLogFileInit() via InstallXLogFileSegment(). regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Wed, Jan 28, 2009 at 11:31:25AM +0900, KaiGai Kohei wrote: As I noted before, there is a symmetrical structure between OS and DBMS. Well, you said that before. I think your analogy is awful. I don't think the similarities are nearly as great as you think, and I also think there are significant differences that _make_ the difference in these cases. In particular, In operating system, a process accesses objects (like file, socket, ...) managed by operating system via system calls. Its security system (filesystem permission, SELinux, ...) acquires the request and applies its access control rules. In DBMS, a client accesses database objects managed by DBMS via SQL queries. Its security system (Database ACL, SE-PostgreSQL, ...) aquires the request and applies its access control rules. the difference here is that in the OS, a process accessing the object has few to no guarantees about concurrency. In RDBMS, a very significant reason even to use the DBMS is the ACID guarantees, which make a number of claims about concurrency that simply aren't there in most filesystems. It's at exactly this architectural point that most of the in-principle design questions have been aimed. My personal view is that those questions haven't really been answered very well, but as I've already said I mostly stopped paying attention to this work several months ago; so maybe I overlooked something. I note that Peter and Bruce seem to have been satisfied, so maybe they understood something I don't (that's quite likely). The most significant feature is centralized access control policy between OS and DBMS. Right, I get that; but all the discussion I've seen on this suggest that, to get the benefit of the centralised access control, I trade away certain well-understood assumptions of a relational environment, but without much indication that I've done so. I talked here we should consider the value of information asset is independent from the way to store them. Yes, I know that was your premise. I am not entirely sure I agree with it, is the thing. Needless to say, the value of information asset is decided by its contents. Nonsense. The value of an information asset is determined only partly by its contents. I'd argue that the value of an information asset is a function of its use-value. If the information asset is completely unusable, then it isn't worth anything at all. If your credit card number is recorded on a paper, do you think it has lesser value than recorded on database? Yes. The database makes the credit card number available to other applications, which can then use that data to charge the credit card with other purchases. For me, therefore, the piece of paper, correctly handled, imposes less risk than the database; in addition, the piece of paper offers a smaller advantage, because it cannot be leveraged to make other interactions more convenient. Finally, the piece of paper offers a different kind of risk, because if it is mishandled and then becomes the basis on which the number ends up in a database, I have a new problem for which I was not prepared. I believe my fundamental objection was that, as far as I was able to tell, SELinux simply didn't have anything useful to say about concurrent actions on data under SE controls; that's because it was aimed at a fairly primitive database (a filesystem) without the rich concurrency support of RDBMS. I still don't see anywhere in your discussions an extension of the SELinux model to account for that concurrency richness, so I think there's something wrong with the principles from which your're starting. I'm totally prepared to admit I've missed something, however. Also, since this isn't really my problem any more, I'm unlikely to spend much time reading more design notes or anything of the sort. Finally, It finally enables to apply centralized access control policy on whole of application stack. Please note that 95% of attacks in 2008 targeted to web system, so it gives a nightmare for security folks. this argument gets to the heart of what you seem to want, which is a centralized system that guarantees the controls you want. I'm actually dubious that such centralization is actually the benefit that its proponents seem to think it is; but if it is, then the centralised system needs to be exactly as rich as the richest system under control. By starting with SELinux, I argue that the approach starts with a too-poor model. (See above.) More fundamentally, the premise that the database is just a part of an application stack is, in my view, exactly _why_ these systems are so vulnerable to attack. Database management systems are not designed to be dumb storage for a single application, and they're actually very poorly adapted to such a role. My impression is that SEPostgres is an attempt to finally force the database system under such controls, as though it were a glorified filesystem. I have no idea whether it
Re: [HACKERS] How to get SE-PostgreSQL acceptable
* KaiGai Kohei (kai...@kaigai.gr.jp) wrote: So, I cannot believe refactoring pg_xxx_aclcheck() is not acceptable. If vanilla PostgreSQL become to check ACLs on tables, independent from views, do you think it is acceptable? Well, just to be clear, ACLs are checked on tables under views, but they're checked using the privileges of the view owner rather than the privileges of the current user. I've run into that empirically because I've gotten 'permission denied' errors when using a view that I've clearly got full rights on but was owned by someone else (who didn't have rights on the table underneath). That being said, I'd think that if we do need different semantics from that for SE-PostgreSQL, we could implement it using a GUC or similar to keep the current behavior as well allow the SE-PostgreSQL behavior. However, we have to make clear whether the PGACE architecture is incorrect, or not, at first. It really bothers me that it seems like these kinds of reviews of the larger patches don't happen until it's time to decide about the next release. Perhaps these issues were all brought up seperately in prior threads, or they weren't articulated as requirements or show-stoppers, and if so then I apologize for not following those more closely. If the approach Peter outlined is what core wants to see and is willing to go along with to get SE-PostgreSQL included then let's please decide that now and agree that unless some serious problem comes up we'll stick to it and not require the whole thing be rewritten again later. I'm not sure about KaiGai's feelings on this, but it strikes me that adding SELinux support for the existing levels of access control in PG might be straight-forward and small enough to include for 8.4 and would show some commitment to this approach of do it for PG, add SELinux checks for it. Alternatively, maybe a progression-towards-SE-PostgreSQL wiki/webpage that outlines the plan, current work, what's been committed, etc, that everyone reviews and agrees to? As a side-note, I've gotten some extremely positive feedback about SE-PostgreSQL from folks in my organization who run systems where it would be used. I'm going to be having a more detailed discussion later today. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How to get SE-PostgreSQL acceptable
That being said, I'd think that if we do need different semantics from that for SE-PostgreSQL, we could implement it using a GUC or similar to keep the current behavior as well allow the SE-PostgreSQL behavior. I don't think a GUC is what you need because you need both behaviors simultaneously, one for MAC and the other for DAC. But I am sure there must be some way to code around the problem. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Robert Haas wrote: I haven't read the code, but from reading the docs, I have a feeling that right now the answer to both questions are NO, which means it doesn't really have a lot of value. One example of this is the pg_security system catalog. The catalog representation you're proposing is probably just fine for associating OIDs to SELinux security labels. But trying to present it as a general thing that some other security implementation could reuse just doesn't seem realistic. Who is to say that the next person who writes an enhanced security feature will want to use text as the representation for their security domains? It could just as easily be two integers, or an array of booleans. This is after all a database product, so the chances that someone would want to do something with structured data seem non-negligible. Text represented security attribute is the most common format for any other security mechanism also. (For example, T-SOL also have its text representation.) In addition, TEXT is the most flexible format. If any other feature want to handle it as an array, it can be stored as a text representation. In the end, you're going to have to be the one who makes the decision on which way to go. In some ways, I think that a plugin architecture would be better for everyone: we worry about the things on our side of the abstraction boundary, and you worry about the things on your side. Potentially you or someone else can release enhanced security plugins without needing any changes to core, and potentially on a different release cycle. On the other hand, a plugin architecture is probably going to be a lot of work. It seems that to install the plugin you would need to make system catalog changes as well as stuff additional system attributes into every table, which are relatively invasive changes. And you'll need to convince everyone that it's really a plug-in architecture and not just a special case for SE-PostgreSQL, so you'll need to prove that there are multiple viable clients, perhaps by backporting our existing DAC. Please make clear the meaning of terms. The 'plugin' means a loadable module which provides its own security policy, doesn't it? There is fundamental difference between built-in and plug-in. The most important factor is where the hooks are deployed, and what facility enables to manage security attribute. Even if I implement SE-PostgreSQL as a loadable module, core PostgreSQL has to provide proper hooks in strategic points and facilities to manage security attribute (pg_security system catalog and security_label system column). If you require to implement it without these facilities, I think it is impossible and prefer scraping PGACE and integrate SE- code into core. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, recovery infra
On Wed, 2009-01-28 at 23:19 +0900, Fujii Masao wrote: @@ -355,6 +359,27 @@ BackgroundWriterMain(void) */ PG_SETMASK(UnBlockSig); + BgWriterRecoveryMode = IsRecoveryProcessingMode(); + + if (BgWriterRecoveryMode) + elog(DEBUG1, bgwriter starting during recovery); + else + InitXLOGAccess(); Why is InitXLOGAccess() called also here when bgwriter is started after recovery? That is already called by AuxiliaryProcessMain(). InitXLOGAccess() sets the timeline and also gets the latest record pointer. If the bgwriter is started in recovery these values need to be reset later. It's easier to call it twice. @@ -1302,7 +1314,7 @@ ServerLoop(void) * state that prevents it, start one. It doesn't matter if this * fails, we'll just try again later. */ - if (BgWriterPID == 0 pmState == PM_RUN) + if (BgWriterPID == 0 (pmState == PM_RUN || pmState == PM_RECOVERY)) BgWriterPID = StartBackgroundWriter(); Likewise, we should try to start also the stats collector during recovery? We did in the previous patch... @@ -2103,7 +2148,8 @@ XLogFileInit(uint32 log, uint32 seg, unlink(tmppath); } - elog(DEBUG2, done creating and filling new WAL file); + XLogFileName(tmppath, ThisTimeLineID, log, seg); + elog(DEBUG2, done creating and filling new WAL file %s, tmppath); This debug message is somewhat confusing, because the WAL file represented as tmppath might have been already created by previous XLogFileInit() via InstallXLogFileSegment(). I think those are just for debugging and can be removed. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Text represented security attribute is the most common format for any other security mechanism also. (For example, T-SOL also have its text representation.) In addition, TEXT is the most flexible format. If any other feature want to handle it as an array, it can be stored as a text representation. Right, but that sort of misses the point of using a DATABASE. I could store everything as text if I wanted to and skip using a database altogether, but I don't want to. That's why I use a database. Please make clear the meaning of terms. The 'plugin' means a loadable module which provides its own security policy, doesn't it? That is what I meant, yes. Even if I implement SE-PostgreSQL as a loadable module, core PostgreSQL has to provide proper hooks in strategic points and facilities to manage security attribute (pg_security system catalog and security_label system column). If you require to implement it without these facilities, I think it is impossible and prefer scraping PGACE and integrate SE- code into core. I am not in a position to require anything since I am not a committer, but I would think that you would need to convince people that the facilities which your plugin requires were pretty much the same as the facilities that any other future plugin might require - that the plugin framework was client-agnostic. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, recovery infra
Hi, On Wed, Jan 28, 2009 at 11:47 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-01-28 at 23:19 +0900, Fujii Masao wrote: @@ -355,6 +359,27 @@ BackgroundWriterMain(void) */ PG_SETMASK(UnBlockSig); + BgWriterRecoveryMode = IsRecoveryProcessingMode(); + + if (BgWriterRecoveryMode) + elog(DEBUG1, bgwriter starting during recovery); + else + InitXLOGAccess(); Why is InitXLOGAccess() called also here when bgwriter is started after recovery? That is already called by AuxiliaryProcessMain(). InitXLOGAccess() sets the timeline and also gets the latest record pointer. If the bgwriter is started in recovery these values need to be reset later. It's easier to call it twice. Right. But, InitXLOGAccess() called during main loop is enough for that purpose. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Column privileges for system catalogs
Peter Eisentraut escreveu: Is it now acceptable to use column privileges for system catalogs? For the new SQL/MED catalogs we have used the old system of revoking all permissions and having a filtered view on top of it (tradition since pg_shadow), but I figured we could do this properly now by just revoking permissions on a specific column. +1. What about pg_authid, pg_roles, pg_user, and pg_shadow? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Stephen Frost wrote: * KaiGai Kohei (kai...@kaigai.gr.jp) wrote: So, I cannot believe refactoring pg_xxx_aclcheck() is not acceptable. If vanilla PostgreSQL become to check ACLs on tables, independent from views, do you think it is acceptable? Well, just to be clear, ACLs are checked on tables under views, but they're checked using the privileges of the view owner rather than the privileges of the current user. I've run into that empirically because I've gotten 'permission denied' errors when using a view that I've clearly got full rights on but was owned by someone else (who didn't have rights on the table underneath). That being said, I'd think that if we do need different semantics from that for SE-PostgreSQL, we could implement it using a GUC or similar to keep the current behavior as well allow the SE-PostgreSQL behavior. I think it is not reasonable. If there are different philosophies, one for one seems to me straight forward approach, for security especially. However, we have to make clear whether the PGACE architecture is incorrect, or not, at first. It really bothers me that it seems like these kinds of reviews of the larger patches don't happen until it's time to decide about the next release. Perhaps these issues were all brought up seperately in prior threads, or they weren't articulated as requirements or show-stoppers, and if so then I apologize for not following those more closely. If the approach Peter outlined is what core wants to see and is willing to go along with to get SE-PostgreSQL included then let's please decide that now and agree that unless some serious problem comes up we'll stick to it and not require the whole thing be rewritten again later. As I noted, PGACE is not my goal. I don't tremble to integrate SELinux related code into the core. I'm not sure about KaiGai's feelings on this, but it strikes me that adding SELinux support for the existing levels of access control in PG might be straight-forward and small enough to include for 8.4 and would show some commitment to this approach of do it for PG, add SELinux checks for it. Alternatively, maybe a progression-towards-SE-PostgreSQL wiki/webpage that outlines the plan, current work, what's been committed, etc, that everyone reviews and agrees to? Are you saying enlargement step-by-step, aren't you? At least, it is far preferable to a death punishment. I would like to here Joshua's opinion also. adding SELinux support for the existing levels of access control in PG is - table/column level access controls - permission checks on database login - permission checks on function invocation - they need a facility to manage security label - I want permission checks on loading a library, though existing PG checks superuser() only. and - removing PGACE, integrate SEPG code into core - permission checks on largeobjects is postponed - row level security is postponed (NOT REJECTED!) - so, writable system column is also postponed If summary is necessary, I'll post it tommorow JST. Because it is not a zero-based implementation, so I believe it can be minimized within acceptable timescale. As a side-note, I've gotten some extremely positive feedback about SE-PostgreSQL from folks in my organization who run systems where it would be used. I'm going to be having a more detailed discussion later today. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Silence compiler warning on win32.
Magnus Hagander mag...@hagander.net writes: Tom Lane wrote: Hmm, need more caffeine I guess. I was thinking dword == long. But in any case, I'd feel a lot more comfortable if the patch ifdef'd the declaration of exit_status to match, rather than forcing a cast of the pointer value. Hmm. I looked at that, but that kind of just moves things around. If i change that variable to be DWORD, it still stuffs it into statuses[i] three lines further down, Sure, but that's a plain old assignment that can cope with the two variables being of different widths, so long as the value to be assigned fits in both. (And if it doesn't, I trust you'll agree that the code is broken...) Casting at the call is simply going to misbehave, very nastily, if somehow the variable isn't of the width the function is expecting. My concern here, ultimately, is that a cast used in that fashion is guaranteed to silence a compiler warning even when the warning is telling you about serious trouble. Because of that, I consider such a cast to be bad style, no matter how certain you are that it's okay in a given case. Any C programmer who's dealt with portability issues will stop and look twice or three times at it, and so you are distracting and irritating the reader. I guess the proper solution in that case is to #define a datatype used for return codes. Is it really worth that for this, though? Probably not, although I seem to recall we have done that elsewhere (pg_ctl maybe?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
KaiGai Kohei wrote: I'm not sure about KaiGai's feelings on this, but it strikes me that adding SELinux support for the existing levels of access control in PG might be straight-forward and small enough to include for 8.4 and would show some commitment to this approach of do it for PG, add SELinux checks for it. Alternatively, maybe a progression-towards-SE-PostgreSQL wiki/webpage that outlines the plan, current work, what's been committed, etc, that everyone reviews and agrees to? Are you saying enlargement step-by-step, aren't you? At least, it is far preferable to a death punishment. I would like to here Joshua's opinion also. s/here/hear/g Sorry, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Wed, 2009-01-28 at 14:55 +0100, Magnus Hagander wrote: If the release is pushed back, maybe some other patch could also have been finished by the new deadline - should that also be included? What about a completely new feature that isn't even started yet, but that could easily be finished before the new deadline? What makes those less worthy? Committers have always added features after freeze... For example, Virtual TransactionIds were added to 8.3 almost exactly 5 months after feature freeze. Not even suggested until about 5 months after, in fact. I argued against such a change late in the cycle, but we did it. It's a great feature and I'm glad we did. If we try to shorten the release cycle, we just end up missing out on tuning opportunities that emerge in beta. IIRC 8.2 was delayed while we changed index cost models. Thankfully. 8.0 was shipped with a completely ineffective bgwriter, so the above changes seem like common sense in comparison. The only way to keep the dev window open longer is to overlap the start of the next cycle with the previous one. i.e. branch new version before final release. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mingw check hung
Andrew Dunstan wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: I've installed drmingw to handle exceptions instead, so we'll see if that gives us useful info. If not, I'll see what I can do with gdb. Hadn't heard of drwmingw, I see how that can be useful :-) report from DrMingw is below Further data point: The suspect patch is quite definitely the source of the problem. I undid the configure changes and surrounded the additions to port/win32.h with #ifdef WIN32_ONLY_COMPILER ... #endif. Result: the problem disappeared, and make check completed perfectly. Per discussion I looked at just reverting that part, but that won't work. If we do that, the call to SetEnvironmentVariable() will not be run, which certainly isn't right.. The problem has to be in win32env.c. I originally thought we accidentally called the putenv function twice in this case, but that code seems properly #ifdef:ed to MSVC. I'm not sure I trust the crash point at all - is this compiled with debug info enabled? It seems like a *very* strange line to crash on... I can't spot the error right off :-( Can you try to see if it's the putenv() or the unsetenv() that gets broken? (by making sure just one of them get replaced) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, recovery infra
On Wed, 2009-01-28 at 23:54 +0900, Fujii Masao wrote: Why is InitXLOGAccess() called also here when bgwriter is started after recovery? That is already called by AuxiliaryProcessMain(). InitXLOGAccess() sets the timeline and also gets the latest record pointer. If the bgwriter is started in recovery these values need to be reset later. It's easier to call it twice. Right. But, InitXLOGAccess() called during main loop is enough for that purpose. I think the code is clearer the way it is. Otherwise you'd read AuxiliaryProcessMain() and think the bgwriter didn't need xlog access. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Even if I implement SE-PostgreSQL as a loadable module, core PostgreSQL has to provide proper hooks in strategic points and facilities to manage security attribute (pg_security system catalog and security_label system column). If you require to implement it without these facilities, I think it is impossible and prefer scraping PGACE and integrate SE- code into core. I am not in a position to require anything since I am not a committer, but I would think that you would need to convince people that the facilities which your plugin requires were pretty much the same as the facilities that any other future plugin might require - that the plugin framework was client-agnostic. We (as a security folks) know any MAC facility have similar architecture called as reference monitor, so I believe it is quite possible to implement them as same basis. But it is a hard request to take an evidence immediately. IMO, the framework is purely implementation matter, so it is not late when the second one appeared. As I noted to another message, I can accept to integrate limited functional SE-PostgreSQL without any PGACE. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, recovery infra
Fujii Masao wrote: On Wed, Jan 28, 2009 at 7:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I feel quite good about this patch now. Given the amount of code churn, it requires testing, and I'll read it through one more time after sleeping over it. Simon, do you see anything wrong with this? I also read this patch and found something odd. Many thanks for looking into this! @@ -507,7 +550,7 @@ CheckArchiveTimeout(void) pg_time_t now; pg_time_t last_time; - if (XLogArchiveTimeout = 0) + if (XLogArchiveTimeout = 0 || !IsRecoveryProcessingMode()) The above change destroys archive_timeout because checking the timeout is always skipped after recovery is done. Yep, good catch. That obviously needs to be IsRecoveryProcessingMode(), without the exclamation mark. @@ -355,6 +359,27 @@ BackgroundWriterMain(void) */ PG_SETMASK(UnBlockSig); + BgWriterRecoveryMode = IsRecoveryProcessingMode(); + + if (BgWriterRecoveryMode) + elog(DEBUG1, bgwriter starting during recovery); + else + InitXLOGAccess(); Why is InitXLOGAccess() called also here when bgwriter is started after recovery? That is already called by AuxiliaryProcessMain(). Oh, I didn't realize that. Agreed, it's a bit disconcerting that InitXLOGAccess() is called twice (there was a 2nd call within the loop in the original patch as well). Looking at InitXLOGAccess, it's harmless to call it multiple times, but it seems better to remove the InitXLOGAccess call from AuxiliaryProcessMain(). InitXLOGAccess() needs to be called after seeing that IsRecoveryProcessingMode() == false, because it won't get the right timeline id before that. @@ -1302,7 +1314,7 @@ ServerLoop(void) * state that prevents it, start one. It doesn't matter if this * fails, we'll just try again later. */ - if (BgWriterPID == 0 pmState == PM_RUN) + if (BgWriterPID == 0 (pmState == PM_RUN || pmState == PM_RECOVERY)) BgWriterPID = StartBackgroundWriter(); Likewise, we should try to start also the stats collector during recovery? Hmm, there's not much point as this patch stands, but I guess we should in the hot standby patch, where we let backends in. @@ -2103,7 +2148,8 @@ XLogFileInit(uint32 log, uint32 seg, unlink(tmppath); } - elog(DEBUG2, done creating and filling new WAL file); + XLogFileName(tmppath, ThisTimeLineID, log, seg); + elog(DEBUG2, done creating and filling new WAL file %s, tmppath); This debug message is somewhat confusing, because the WAL file represented as tmppath might have been already created by previous XLogFileInit() via InstallXLogFileSegment(). I don't quite understand what you're saying, but I think I'll just revert that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Silence compiler warning on win32.
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Tom Lane wrote: Hmm, need more caffeine I guess. I was thinking dword == long. But in any case, I'd feel a lot more comfortable if the patch ifdef'd the declaration of exit_status to match, rather than forcing a cast of the pointer value. Hmm. I looked at that, but that kind of just moves things around. If i change that variable to be DWORD, it still stuffs it into statuses[i] three lines further down, Sure, but that's a plain old assignment that can cope with the two variables being of different widths, so long as the value to be assigned fits in both. (And if it doesn't, I trust you'll agree that the code is broken...) Casting at the call is simply going to misbehave, very nastily, if somehow the variable isn't of the width the function is expecting. Ok. Seems reasonble to change it to a cast in that place instead - will do. I guess the proper solution in that case is to #define a datatype used for return codes. Is it really worth that for this, though? Probably not, although I seem to recall we have done that elsewhere (pg_ctl maybe?) Yeah, we have done it in one or two places. I'll just go with the cast per above for this time. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Zdenek Kotala zdenek.kot...@sun.com writes: Heikki Linnakangas pÃÅ¡e v st 28. 01. 2009 v 09:24 +0200: Right, the dump+initdb+reload approach works quite well in both pg_upgrade and pg-migrator. I believe the biggest issue with that ATM is supporting dropped columns, and maybe there's something else, but it's fairly robust and works across any versions. It works but it is not ideal. Sure, but the other way is just a complete non-starter. It's enormous amounts of work even for simple changes, and it's not hard to think of potentially-desirable catalog rearrangements for which it would be completely unworkable. I have idea how to do it without old PostgreSQL version and with bootstrap process extension which should not be invasive and easily maintainable. I will send idea latter ... stay tuned ;-) New ideas welcome of course. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
KaiGai Kohei wrote: Stephen Frost wrote: * KaiGai Kohei (kai...@kaigai.gr.jp) wrote: So, I cannot believe refactoring pg_xxx_aclcheck() is not acceptable. If vanilla PostgreSQL become to check ACLs on tables, independent from views, do you think it is acceptable? Well, just to be clear, ACLs are checked on tables under views, but they're checked using the privileges of the view owner rather than the privileges of the current user. I've run into that empirically because I've gotten 'permission denied' errors when using a view that I've clearly got full rights on but was owned by someone else (who didn't have rights on the table underneath). That being said, I'd think that if we do need different semantics from that for SE-PostgreSQL, we could implement it using a GUC or similar to keep the current behavior as well allow the SE-PostgreSQL behavior. I think it is not reasonable. If there are different philosophies, one for one seems to me straight forward approach, for security especially. When we talk about mandatory access control we are generally very clear that for it to be mandatory and for the policy to be analyzable you must identify objects unambiguously. For SE-Postgres this is an absolute necessity. At the same time, if view-based ACL's are there, and people want them and use them then that doesn't affect us (the people that want to use sepostgres). They must be orthogonal and sepostgres must have a way of unambiguously labeling objects. However, we have to make clear whether the PGACE architecture is incorrect, or not, at first. It really bothers me that it seems like these kinds of reviews of the larger patches don't happen until it's time to decide about the next release. Perhaps these issues were all brought up seperately in prior threads, or they weren't articulated as requirements or show-stoppers, and if so then I apologize for not following those more closely. As a new-comer to this discussion (and indeed this list/community) I find it baffling that a patchset that has been around this long is just now having basic architectural questions asked about it. If the approach Peter outlined is what core wants to see and is willing to go along with to get SE-PostgreSQL included then let's please decide that now and agree that unless some serious problem comes up we'll stick to it and not require the whole thing be rewritten again later. As I noted, PGACE is not my goal. I don't tremble to integrate SELinux related code into the core. Flask (from which SELinux is derived) has the basic architecture of separating enforcement from policy. This generally means that a security server (or backend) knows what the policy is and how to calculate access vectors and it passes those answers to the enforcement points. Flask itself is a framework. In Linux and Xorg a more general framework was built that interfaces to flask, because the flask security server wasn't seen as something that could implement any kind of security policy (whether that is correct or not is a point of contention, as the selinux security server already implements a kind of RBAC, type enforcement and multilevel security). PCACE is, architecturally, similar to LSM in the linux kernel and XACE in in Xorg but is not an absolute necessity for SELinux integration. I'm not sure about KaiGai's feelings on this, but it strikes me that adding SELinux support for the existing levels of access control in PG might be straight-forward and small enough to include for 8.4 and would show some commitment to this approach of do it for PG, add SELinux checks for it. Alternatively, maybe a progression-towards-SE-PostgreSQL wiki/webpage that outlines the plan, current work, what's been committed, etc, that everyone reviews and agrees to? If you look at the patches you'll see that the bulk of them are adding the SELinux infrastructure. An access vector cache, the label translation functions, the libselinux interface and so on. Over 2/3's of the patch is in src/backend/security. I'm not sure how much it would cut to remove row level access controls, but I do have some points here. To me, row level access controls are the most important part, this is the feature that lets us put secret and top secret data in the same table and use the clients selinux context to decide what they can see, transparently and without modification to the application. Without it you have to use seperate postgres instances, modify the application to select from different sets of tables depending on their label and so on. That said, SELinux didn't start out as fine grained as it is today. A fair number of object classes were present back in '99 when it started but more have been added over time, the policy infrastructure allows that to happen, in fact the policy allows object class/permissions to be added, removed and modified if the access controls were inadequate or unnecessary. Are you saying enlargement step-by-step,
Re: [HACKERS] mingw check hung
Magnus Hagander mag...@hagander.net writes: Andrew Dunstan wrote: The suspect patch is quite definitely the source of the problem. I can't spot the error right off :-( Can you try to see if it's the putenv() or the unsetenv() that gets broken? Are we sure pgwin32_unsetenv works in this environment? (Or worse, maybe it's trying to use port/unsetenv.c?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Column privileges for system catalogs
Peter Eisentraut pete...@gmx.net writes: Is it now acceptable to use column privileges for system catalogs? Sure, to the same extent that table privileges work for them (ie, don't expect the C code to pay any attention ;)). For the new SQL/MED catalogs we have used the old system of revoking all permissions and having a filtered view on top of it (tradition since pg_shadow), but I figured we could do this properly now by just revoking permissions on a specific column. I don't have any objection to changing the catalog's own permissions that way, but the filtered view still has a usability advantage: you can just go select * from Is it reasonable to change the catalog permissions and keep the view too? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Stephen Frost sfr...@snowman.net writes: * Zdenek Kotala (zdenek.kot...@sun.com) wrote: And very important thing is that you need old version of postgreSQL installed, which is something what packagers does not want. Look on Oracle how does it. Just as a counter-point, Debian handles multiple concurrently installed versions of PostgreSQL just fine, in large part to specifically deal with the smooth migration challenge (though also because we realize people may want to continue using the old version while others may want to install the new version). Not sure if that's something the community wants to encourage other packagers to do or if we should look at making it easier to do, but it's at least possible and has been done for a pretty large distribution. The Red Hat/Fedora brigade has also been thinking seriously about that, though we've not gotten further than thinking yet. Of course, if pg_upgrade becomes a reality we'd likely stop thinking about it. IMHO, it would not by any means be a disaster for pg_upgrade to require a copy of the older-version postmaster. The way I'd foresee packaging it is to build a separate postgresql-upgrade RPM containing pg_upgrade itself and a version-N-minus-1 postmaster that gets installed in a nonstandard location (that pg_upgrade knows about). After you've finished the upgrade you can remove that RPM and get the extra disk space back. Most of the possible alternatives mean a *permanent* disk space hit, because the postmaster will have to contain one-time-use upgrade code that can't be dropped afterwards. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] using composite types in insert/update
IMO, composite types on insert/update should work as they do on select: SELECT foo FROM foo; -- works, returning type foo INSERT INTO foo VALUES '(something)'::foo -- fails, but we have a workaround: INSERT INTO foo SELECT ('(something)'::foo).* -- expands foo into foo columns however no such workaround exists for update. ideally, UPDATE foo SET foo = foo; would be valid. Aside from fixing a surprising behavior, it would greatly aid in writing triggers that do things like ship updates over dblink _much_ easier (in fact...the dblink_build_xxx family would become obsolete). e.g. perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo); I call the existing behavior of insert/update of composite types broken to the point of almost being a bug. Fixing the above to work would close the loop on a broad new set of things you can do with composite types. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Wednesday 28 January 2009 08:55:56 Magnus Hagander wrote: We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. As I pointed out to Tom, by percentage the additional beta/release cycles wouldn't be very different than what we have now; the more churn you have during development, the longer it takes to beta/release. I'm pretty sure that if we had pushed everything not committed on December 1st, we would be very close to release right now, and that's with more dev cycles than I'm talking about for 8.5. And I think most people (aka not the patch authors :-) would have been willing to push the stuff we're dealing with now if they knew the next release would be closer to 6 months than 14 months. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Robert Treat xzi...@users.sourceforge.net writes: On Wednesday 28 January 2009 08:55:56 Magnus Hagander wrote: We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. As I pointed out to Tom, by percentage the additional beta/release cycles wouldn't be very different than what we have now; the more churn you have during development, the longer it takes to beta/release. I don't believe that thesis in itself, because it ignores economies of scale and parallelism for beta testing. And in any case it's complete nonsense in respect to back-branch maintenance costs. If we double the frequency of releases we are going to be pretty much forced to halve the support lifetime, and ain't nobody going to be happy with us. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Joshua Brindle met...@manicmethod.com writes: I'm not sure how much it would cut to remove row level access controls, but I do have some points here. To me, row level access controls are the most important part, this is the feature that lets us put secret and top secret data in the same table and use the clients selinux context to decide what they can see, For me, the row-level access controls are really the sticking point. There is absolutely nothing you can say that will convince me that they don't break SQL in fundamental ways, and I also don't believe that it's going to be possible to implement them without a constant stream of bugs of omission and commission. (Those two points are not unrelated.) Now that you've admitted that you aren't trying to accomplish the equivalent sort of data hiding within the filesystem, the argument that you have to have them seems fairly weak, certainly not strong enough to justify the costs. We have already touched on some ways that you can accomplish similar goals with just table- and column-level access permissions, which are well understood and don't violate anyone's expectations. There's probably a need to twiddle our permissions rules for inheritance/partitioning cases, but that was already on the table anyway for other reasons. I could be persuaded to get behind a patch that does Peter's step #1 (ie, use SELinux permissions as an additional filter for existing SQL permission checks). I don't believe I will ever think that row-level checks are a good idea; as long as those are in the patch I will vote against applying it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
On Wed, Jan 28, 2009 at 10:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Zdenek Kotala zdenek.kot...@sun.com writes: Heikki Linnakangas píše v st 28. 01. 2009 v 09:24 +0200: Right, the dump+initdb+reload approach works quite well in both pg_upgrade and pg-migrator. I believe the biggest issue with that ATM is supporting dropped columns, and maybe there's something else, but it's fairly robust and works across any versions. It works but it is not ideal. Sure, but the other way is just a complete non-starter. It's enormous amounts of work even for simple changes, and it's not hard to think of potentially-desirable catalog rearrangements for which it would be completely unworkable. Well, the goal of coding is to make such things easier. Already the solution you're advocating has one huge wart: the need to represent dropped columns in pg_dump output. It seems optimistic to assume that there won't be any more, and it seems possible that some change might happen that requires the wart without the necessary wart actually getting inserted. We'll end up testing after the fact to see whether in-place upgrade has gotten broken, and given that no one was under any pressure to give it some forethought in advance, the answer will probably be yes, at least sometimes... I'm not dead set against doing it this way, I'm just not sold on it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Tom Lane wrote: Joshua Brindle met...@manicmethod.com writes: I'm not sure how much it would cut to remove row level access controls, but I do have some points here. To me, row level access controls are the most important part, this is the feature that lets us put secret and top secret data in the same table and use the clients selinux context to decide what they can see, For me, the row-level access controls are really the sticking point. There is absolutely nothing you can say that will convince me that they don't break SQL in fundamental ways, and I also don't believe that it's going to be possible to implement them without a constant stream of bugs of omission and commission. (Those two points are not unrelated.) This isn't going to be something that the vast majority of your users use. The people that need them understand the ramifications of row filtering and the absence of inaccessible rows won't be surprising. Now that you've admitted that you aren't trying to accomplish the equivalent sort of data hiding within the filesystem, the argument that We apply access controls on reading and writing files, this is equivalent (in my mind) to applying access controls on tuples, as they are the structured object holding data (just like files). you have to have them seems fairly weak, certainly not strong enough to justify the costs. We have already touched on some ways that you can The costs are nil for people who don't want this feature. accomplish similar goals with just table- and column-level access permissions, which are well understood and don't violate anyone's I've already pointed out how table and column level access control don't provide enough. Holding data of multiple classifications in a single table is something of great concern. Unmodified applications need to be able to query out of a table and get the data they are cleared to see. Column level access control doesn't help because those same applications, of course, will need access to the data in those columns. Splitting data into different tables isn't an option in many cases because applications aren't always configurable wrt to the database or tables they use. Further, clients of multiple clearances should be able to use the same application, and we can not trust the application to make the decision as to which database or table is correct. And even further, maintaining multiple sets of databases or tables that hold the same kinds of information (and therefore have the same schema) is a maintenance, backup and restoration issue. expectations. There's probably a need to twiddle our permissions rules for inheritance/partitioning cases, but that was already on the table anyway for other reasons. partitions don't help because, once again, the application would be making the determination about which partition to query. For mandatory access control that we need in the kind of environments to work the application doesn't get to make security relevant decisions, the database holds the data and needs to say who can access it. Further, partitioning isn't fine grained. I can't say user X can read secret rows and read/write top secret rows and get that data out in a transparent way (the applications would have to be aware of the partitions). Relabeling of data also looks like a challenge with partitions (if I correctly understand how they work). I could be persuaded to get behind a patch that does Peter's step #1 (ie, use SELinux permissions as an additional filter for existing SQL permission checks). I don't believe I will ever think that row-level checks are a good idea; as long as those are in the patch I will vote against applying it. We've already used postgresql in sensitive environments and had to make compromises because of the lack of fine grained access control. We've been telling customers for years that we hope postgresql will have said access controls in the future and that it will help them solve many of the problems they have. We've been enthusiastic about the work KaiGai has been doing with respect to the environments we operate in and it would be a shame for all that to be discarded. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Output filter for psql
On Thu, 8 Jan 2009 17:22:04 -0500 D'Arcy J.M. Cain da...@druid.net wrote: So, I guess psql should pass XML to the user's filter and simply dump its output to the screen. Is XML the best format? It would allow us to pass meta information. For example, we could pass the border setting so that the filter could change its output based on it. Pardon me while I argue with myself. :-) I wonder if XML isn't overkill for this. I think that we only need three pieces of information: - The column headings - The column types (in case the user wants it) - The data XML would be difficult from both the server as well as the user. Perhaps the simplest thing for both sides is simply CSV with an extra row for type. For example: person_id,person_name,person_email int,text,text 1,darcy,D'Arcy Cain 2,tom,Tom Lane 3,bruce,Bruce Momjian ...etc. The user could choose to ignore the second line if he doesn't need it. I suppose we could define another line with options that we could define for meta information such as the border setting and the table name and whatever we define later. E.g: table:person,border:3,funky:option with \:\ -- D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
I skimmed through the Hot Standby patch for a preliminary review. I noted the following things, some minor tweaks, some just questions. None of the things I noted are big issues unless some of the questions uncover issues. 1) This code is obviously a cut-pasto: + else if (strcmp(tok1, max_standby_delay) == 0) + { + errno = 0; + maxStandbyDelay = (TransactionId) strtoul(tok2, NULL, 0); + if (errno == EINVAL || errno == ERANGE) + ereport(FATAL, +(errmsg(max_standby_delay is not a valid number: \%s\, +tok2))); Have you ever tested the behaviour with max_standby_delay = -1 ? Also, the default max_standby_delay is currently 0 -- ie, kill queries immediately upon detecting any conflicts at all -- which I don't really think anyone would be happy with. I still *strongly* feel the default has to be the non-destructive conservative -1. 2) This hard coded constant of 500ms seems arbitrary to me. If your use case is a heavily-used reporting engine you might get this message all the time. I think this either has to be configurable (warn_standby_delay?) or based on max_standby_delay or some other parameter somehow. + /* +* log that we have been waiting for a while now... +*/ + if (!logged standbyWait_ms 500) 3) These two blocks of code seem unsatisfactory: ! /* !* Keep track of the block number of the lastBlockVacuumed, so !* we can scan those blocks as well during WAL replay. This then !* provides concurrency protection and allows btrees to be used !* while in recovery. !*/ ! if (lastBlockVacuumed vstate-lastBlockVacuumed) ! vstate-lastBlockVacuumed = lastBlockVacuumed; ! + /* +* XXXHS we don't actually need to read the block, we +* just need to confirm it is unpinned. If we had a special call +* into the buffer manager we could optimise this so that +* if the block is not in shared_buffers we confirm it as unpinned. +*/ + buffer = XLogReadBufferExtended(xlrec-node, MAIN_FORKNUM, blkno, RBM_NORMAL); + if (BufferIsValid(buffer)) + { + LockBufferForCleanup(buffer); + UnlockReleaseBuffer(buffer); + } Aside from the XXX comment (I thought we actually had such a call now, but if not shouldn't we just add one instead of carping?) I'm not convinced this handles all the cases that can arise. Notable, what happens if a previous vacuum died in the middle of the scan? I think we have a vacuum id which we use already with btrees to the same issue. It seems to me this be more robust if you stamped the xlog record with that id number and ensured it matched the id of the lastBloockVacuumed? Then you could start from 0 if the id changes. 4) Why is this necessary? + if (IsRecoveryProcessingMode() + locktag-locktag_type == LOCKTAG_OBJECT + lockmode AccessShareLock) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), +errmsg(cannot acquire lockmode %s on database objects while recovery is in progress, + lockMethodTable-lockModeNames[lockmode]), +errhint(Only AccessShareLock can be acquired on database objects during recovery.))); + Obviously we can't lock records (SELECT FOR UPDATE) since that requires write access. But shouldn't we be able to do manual LOCK TABLE calls? I hope this isn't the only interlock we have against trying to do write i/o or DDL against tables...? 5) The code for killing off conflicting transactions looks odd to me but I haven't really traced through it to see what it's doing. It seems to kill off just one process? What if there are several holding the lock in question? Also, it doesn't seem to take into account how long the various transactions have held the lock? Is there a risk of, for example, if you have a long report running against a table and lots of OLTP requests against the same table it seems the conflict resolution code would fire randomly into the crowd and hit mostly innocent OLTP transactions until eventually it found the culprit report? Also, it kills of backends using SIGINT which I *think* Tom went through and made safe earlier this release, right? In any case if the backend doesn't die off promptly we wait forever with no further warnings or log messages. I would think we should at least print some sort of message here, even if it's a can't happen elog. The doubling thing is probably unnecessary too in this case. + if (!XLogRecPtrIsValid(conflict_LSN)) + { + /* wait awhile for it to die */ + pg_usleep(wontDieWait * 5000L); + wontDieWait *= 2; + } + } 6) I still don't
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Joshua Brindle met...@manicmethod.com writes: partitions don't help because, once again, the application would be making the determination about which partition to query. Not necessarily since the database can be programmed to automatically put the records into the right partition. Right now it's a pain but we're definitely headed in that direction. Further, partitioning isn't fine grained. I can't say user X can read secret rows and read/write top secret rows and get that data out in a transparent way (the applications would have to be aware of the partitions). Relabeling of data also looks like a challenge with partitions (if I correctly understand how they work). I think the transparent is the source of the problem. The application should issue a query for the data it wants. It shouldn't transparently get magic extra clauses attached to the query. That's where the SQL semantics are being violated. Row-level security isn't inherently a problem. It's just that the security is affecting the data returned that's causing a problem. I don't think partitioning is really the same thing as row-level security. But I wonder if some of the same infrastructure could be used for both -- once we have it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Robert Haas robertmh...@gmail.com writes: On Wed, Jan 28, 2009 at 10:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sure, but the other way is just a complete non-starter. Well, the goal of coding is to make such things easier. Already the solution you're advocating has one huge wart: the need to represent dropped columns in pg_dump output. Dropped columns are certainly an issue, and TOAST pointers are another, but they seem to me to be soluble with relatively limited impact (I don't think pg_dump necessarily needs to be involved; rather I'd imagine pg_upgrade itself adjusting the new catalog entries after it's used pg_dump to do most of the work). Trying to do catalog upgrade in-place is going to be a complete mess. I'd be interested to know, for example, how you imagine rearranging the contents of pg_class would work. You don't get to modify pg_class if you can't even find it, which you can't because you can't read it. And in the time it takes you to think of an approach for that, I will be able to think of a dozen more that are all equally nasty. There are probably some thousands of places in the backend where we expect the system catalogs to have layout matching what the code expects. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Output filter for psql
D'Arcy J.M. Cain wrote: On Thu, 8 Jan 2009 17:22:04 -0500 D'Arcy J.M. Cain da...@druid.net wrote: So, I guess psql should pass XML to the user's filter and simply dump its output to the screen. Is XML the best format? It would allow us to pass meta information. For example, we could pass the border setting so that the filter could change its output based on it. Pardon me while I argue with myself. :-) I wonder if XML isn't overkill for this. I think that we only need three pieces of information: - The column headings - The column types (in case the user wants it) - The data XML would be difficult from both the server as well as the user. Perhaps the simplest thing for both sides is simply CSV with an extra row for type. For example: person_id,person_name,person_email int,text,text 1,darcy,D'Arcy Cain 2,tom,Tom Lane 3,bruce,Bruce Momjian ...etc. The user could choose to ignore the second line if he doesn't need it. I suppose we could define another line with options that we could define for meta information such as the border setting and the table name and whatever we define later. E.g: table:person,border:3,funky:option with \:\ Why do you want to add this extra information? psql doesn't output it now, and IIRC your original proposal didn't do it either. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I skimmed through the Hot Standby patch for a preliminary review. I noted the following things, some minor tweaks, some just questions. None of the things I noted are big issues unless some of the questions uncover issues. Thanks for your attention. 1) This code is obviously a cut-pasto: + else if (strcmp(tok1, max_standby_delay) == 0) + { + errno = 0; + maxStandbyDelay = (TransactionId) strtoul(tok2, NULL, 0); + if (errno == EINVAL || errno == ERANGE) + ereport(FATAL, +(errmsg(max_standby_delay is not a valid number: \%s\, +tok2))); Have you ever tested the behaviour with max_standby_delay = -1 ? Also, the default max_standby_delay is currently 0 -- ie, kill queries immediately upon detecting any conflicts at all -- which I don't really think anyone would be happy with. Agreed. As explained when I published that patch it is deliberately severe to allow testing of conflict resolution and feedback on it. I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. 2) This hard coded constant of 500ms seems arbitrary to me. If your use case is a heavily-used reporting engine you might get this message all the time. I think this either has to be configurable (warn_standby_delay?) or based on max_standby_delay or some other parameter somehow. + /* +* log that we have been waiting for a while now... +*/ + if (!logged standbyWait_ms 500) Greg, that's a DEBUG5 message. 3) These two blocks of code seem unsatisfactory: ! /* !* Keep track of the block number of the lastBlockVacuumed, so !* we can scan those blocks as well during WAL replay. This then !* provides concurrency protection and allows btrees to be used !* while in recovery. !*/ ! if (lastBlockVacuumed vstate-lastBlockVacuumed) ! vstate-lastBlockVacuumed = lastBlockVacuumed; ! + /* +* XXXHS we don't actually need to read the block, we +* just need to confirm it is unpinned. If we had a special call +* into the buffer manager we could optimise this so that +* if the block is not in shared_buffers we confirm it as unpinned. +*/ + buffer = XLogReadBufferExtended(xlrec-node, MAIN_FORKNUM, blkno, RBM_NORMAL); + if (BufferIsValid(buffer)) + { + LockBufferForCleanup(buffer); + UnlockReleaseBuffer(buffer); + } Aside from the XXX comment (I thought we actually had such a call now, but if not shouldn't we just add one instead of carping?) We should add many things. The equivalent optimisation of VACUUM in normal running has not been done either. Considering we have both HOT and visibility map enhanced VACUUM, its not a priority. I'm not convinced this handles all the cases that can arise. Notable, what happens if a previous vacuum died in the middle of the scan? Nothing, because it won't then have removed any heap rows. I think we have a vacuum id which we use already with btrees to the same issue. It seems to me this be more robust if you stamped the xlog record with that id number and ensured it matched the id of the lastBloockVacuumed? Then you could start from 0 if the id changes. 4) Why is this necessary? + if (IsRecoveryProcessingMode() + locktag-locktag_type == LOCKTAG_OBJECT + lockmode AccessShareLock) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), +errmsg(cannot acquire lockmode %s on database objects while recovery is in progress, + lockMethodTable-lockModeNames[lockmode]), +errhint(Only AccessShareLock can be acquired on database objects during recovery.))); + Obviously we can't lock records (SELECT FOR UPDATE) since that requires write access. But shouldn't we be able to do manual LOCK TABLE calls? Read the rest of the comments on the locking code section. I hope this isn't the only interlock we have against trying to do write i/o or DDL against tables...? No it's not. 5) The code for killing off conflicting transactions looks odd to me but I haven't really traced through it to see what it's doing. It seems to kill off just one process? No, why do you think that? What if there are several holding the lock in
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: Agreed. As explained when I published that patch it is deliberately severe to allow testing of conflict resolution and feedback on it. I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Not to mention we have already lost them as a user because they will blame postgresql regardless of reality as evidenced by their inability to RTFM (or have a vendor that RTFMs) in the first place. I got to vote with Greg on this one. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mingw check hung
Magnus Hagander wrote: Per discussion I looked at just reverting that part, but that won't work. If we do that, the call to SetEnvironmentVariable() will not be run, which certainly isn't right.. The problem has to be in win32env.c. I originally thought we accidentally called the putenv function twice in this case, but that code seems properly #ifdef:ed to MSVC. I'm not sure I trust the crash point at all - is this compiled with debug info enabled? It seems like a *very* strange line to crash on... I can't spot the error right off :-( Can you try to see if it's the putenv() or the unsetenv() that gets broken? (by making sure just one of them get replaced) //Magnus Hi guys, Don't know if this is relevant at all, but it reminds me of a problem I had with environment variables in PostGIS with MingW. It was something along the lines of environment variables set in a MingW program using putenv() for PGPORT, PGHOST etc. weren't visible to a MSVC-compiled libpq but were to a MingW-compiled libpq. It's fairly easy to knock up a quick test program in C to verify this. I eventually gave up and just built a connection string instead - for reference the final patch is here http://postgis.refractions.net/pipermail/postgis-commits/2008-January/000199.html. I appreciate it may not be 100% relevant, but I thought I'd flag it up as possibly being a fault with the MingW putenv implementation. HTH, Mark. -- Mark Cave-Ayland Sirius Corporation - The Open Source Experts http://www.siriusit.co.uk T: +44 870 608 0063 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Gregory Stark st...@enterprisedb.com writes: I don't think partitioning is really the same thing as row-level security. Of course not, but it seems to me that it can be used to accomplish most of the same practical use-cases. The main gripe about doing it via partitioning is that the user's nose gets rubbed in the fact that there can't be an enormous number of different security classifications in the same table (since he has to explicitly make a partition for each one). But the proposed implementation of row-level security would poop out pretty darn quick for such a case, too, and frankly I'm not seeing an application that would demand it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Gregory Stark wrote: 6) I still don't understand why you need unobserved_xids. We don't need this in normal running, an xid we don't know for certain is committed is exactly the same as a transaction we know is currently running or aborted. So why do you need it during HS? In normal operation, any transaction that's in-progress has an entry in ProcArray. GetSnapshot() gathers the xids of all those in-progress transactions, so that they're seen as not-committed even when the they're later marked as committed in clog. In HS, we might see the first WAL record of transaction 10 before we see the first WAL record of transaction 9. Without unobserved_xids, if you take a snapshot in the standby between those two WAL records, xid 10 is included in the snapshot, but 9 is not. If xact 9 later commits, it's marked in clog as committed, and it will suddenly appear as visible to the snapshot. To avoid that, when we replay the first WAL record of xact 10, we also add 9 to the unobserved xid array, so that it's included in snapshots. So, you can think of the unobserved xids array as an extension of ProcArray. The entries are like light-weight PGPROC entries. In fact I proposed earlier to simply create dummy PGPROC entries instead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
On Wed, Jan 28, 2009 at 01:49:21PM -0500, Joshua Brindle wrote: use. The people that need them understand the ramifications of row filtering and the absence of inaccessible rows won't be surprising. I wish there was even a little bit of evidence that users of a security system may be relied upon to understand its implications and effects. In my experience, however, they often don't. you have to have them seems fairly weak, certainly not strong enough to justify the costs. We have already touched on some ways that you can The costs are nil for people who don't want this feature. That's also false, because developers who don't care about the feature have to continue to maintain it as part of the system. If maintenance were free, I suspect nobody would be objecting to the feature. But this feature will in fact constrain future development and will impose maintenance requirements on the programmers of the system. Those maintenance requirements in turn amount to a cost that every user has to pay, because time spent addressing issues that result from this feature (or accommodating it in new development) is time that is not spent on other problems users might face. If I imagined I were project manager of the PostgreSQL project (a preposterous supposition, let me be clear), then I'd be very worried that this feature, which is apparently poorly understood by at least one big contributor to the project, would amount to a significant drag on future development work. In that case, I'd have to ask why having this feature as part of the main line of PostgreSQL is a good trade-off. Happily, I'm not someone who has to make that determination, so I can't say whether it _is_ a good trade-off. But I think that's what the resistance to the feature is all about, so you'll need to make the case that the trade-off is a good one. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I still don't understand why you need unobserved_xids. We don't need this in normal running, an xid we don't know for certain is committed is exactly the same as a transaction we know is currently running or aborted. So why do you need it during HS? All running transactions need to be part of the snapshot. This is true on both standby and primary. On primary we allocate new xids from a single counter, so there are no gaps. Newly assigned xids go straight into the procarray and then are removed later when they commit/abort. In standby we only know what is in WAL. Xid assignment is not currently WAL logged, so either we choose to WAL log each new xid and face the performance consequences (IMHO, unacceptable), or we choose a different strategy. UnobservedXids is that different strategy. Florian Pflug had a different strategy, but he did have a strategy. The comment says: + * This is very important because if we leave + * those xids out of the snapshot then they will appear to be already complete. + * Later, when they have actually completed this could lead to confusion as to + * whether those xids are visible or not, blowing a huge hole in MVCC. + * We need 'em. But that doesn't sound rational to me. I'm not sure what confusion this would cause. If they later actually complete then any existing snapshots would still not see them. And any later snapshots wouldn't be confused by the earlier conclusions. If a xact is running when we take a snapshot, yet we do not include it then bad things will happen, but not til later. If a xact is not in snapshot *and* less than xamx then we presume it completed prior to the snapshot. If that xact did subsequently commit *after* we took the snapshot, then it's absence from the snapshot will make that data visible if the xact committed, making it look like it committed *before* the snapshot was taken. So the confusion results in an MVCC violation and so we must handle this case correctly, or die. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
* Tom Lane t...@sss.pgh.pa.us [090128 15:02]: Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. That's what we currently have without HS, but people aren't completely satisfied with it either ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Hot Standby (v9d)
(sorry for top posting -- blame apple) I don't see anything dangerous with either setting. For use cases where the backup is the primary purpose then killing queries is fine. For use cases where the maching is a reporting machine then saving large amounts of archived logs is fine. Engineering is about tradeoffs and these two use cases are intrinsically in conflict. The alternative is postponing vacuuming on the master which is imho even worse than the disease. -- Greg On 28 Jan 2009, at 19:56, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Put another way: your characterization is no more true than claiming there's no safe setting for statement_timeout since a large value means clog could overflow your disk and your tables could bloat. (I note we default statement_timeout to off though) -- Greg On 28 Jan 2009, at 19:56, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 21:41 +0200, Heikki Linnakangas wrote: So, you can think of the unobserved xids array as an extension of ProcArray. The entries are like light-weight PGPROC entries. In fact I proposed earlier to simply create dummy PGPROC entries instead. Which we don't do because we don't know whether we are dealing with top-level xids or subtransactions of already observed top-level xids. Either way we have to rearrange things when we move from unobserved to observed. A major difference is that what we have now works and what we might have instead may not, which is being illustrated by recent testing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres The same could be said for warm standby right now. Or Slony-I, for that matter. I think that we can reasonably expect anyone implementing asynchronous replication for HA to properly monitor the lag time. There are many sources of latency in the process, so I don't think anyone can expect 10 seconds without actually monitoring to verify what the actual lag time is. I apologize if my post is based on ignorance, I haven't followed your patch as closely as others involved in this discussion. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. We've gone through that already. Different ideas were hashed out around September. There's four basic feasible approaches to what to do when an incoming WAL record conflicts with a running read-only query: 1. Kill the query. (max_standby_delay=0) 2. Wait for the query to finish before continuing (max_standby_delay=-1) 3. Have a feedback loop from standby to master, feeding an OldestXmin to the master, preventing it from removing tuples that are still needed in the standby. 4. Allow the query to continue, knowing that it will return wrong results. I don't consider 4 to be an option. Option 3 has its own set of drawbacks, as a standby can then cause bloat in the master, and in any case we're not going to have it in this release. And then there's some middle ground, like wait a while and then kill the query (max_standby_delay 0). I don't see any way around the fact that when a tuple is removed, it's gone and can't be accessed by queries. Either you don't remove it, or you kill the query. I think the max_standby_delay setting is fairly easy to explain. It shouldn't be too hard for a DBA to set it correctly. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Output filter for psql
On Wed, 28 Jan 2009 14:08:54 -0500 Andrew Dunstan and...@dunslane.net wrote: D'Arcy J.M. Cain wrote: I suppose we could define another line with options that we could define for meta information such as the border setting and the table name and whatever we define later. E.g: table:person,border:3,funky:option with \:\ Why do you want to add this extra information? psql doesn't output it now, and IIRC your original proposal didn't do it either. It mentioned including meta information. I'm not sure that I really care if I get it but we may want it in general, if not now then later, and it would be something that would be extremely hard to retrofit. If we start out with just the table and perhaps the border setting we can always add more later if we find we need it. If we don't start with something then adding an extra line later will cause all sorts of heartache. -- D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 11:33 -0800, Joshua D. Drake wrote: On Wed, 2009-01-28 at 19:27 +, Simon Riggs wrote: On Wed, 2009-01-28 at 18:55 +, Gregory Stark wrote: Agreed. As explained when I published that patch it is deliberately severe to allow testing of conflict resolution and feedback on it. I still *strongly* feel the default has to be the non-destructive conservative -1. I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. It was never intended to be 0, that was just for testing, as I said. But a smallish integer number of seconds, say 10, 60, 300 or at most 600 is reasonable. Crash barriers can be moved, falling off a cliff is permanent. It's easy to change the parameter if you don't like it, and too late to change it if we set the default wrong. My name is on the patch and I take responsibility for such failures. I'm not going to turn round and say but Josh said, kinda like Stan Laurel, if it fails. I've never called any user stupid and never will, not while they use Postgres, at least... If we get the default wrong then its down to us. Never cancelling queries is definitely a wrong default choice for an HA server. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mingw check hung
Mark Cave-Ayland wrote: Magnus Hagander wrote: Per discussion I looked at just reverting that part, but that won't work. If we do that, the call to SetEnvironmentVariable() will not be run, which certainly isn't right.. The problem has to be in win32env.c. I originally thought we accidentally called the putenv function twice in this case, but that code seems properly #ifdef:ed to MSVC. I'm not sure I trust the crash point at all - is this compiled with debug info enabled? It seems like a *very* strange line to crash on... I can't spot the error right off :-( Can you try to see if it's the putenv() or the unsetenv() that gets broken? (by making sure just one of them get replaced) //Magnus Hi guys, Don't know if this is relevant at all, but it reminds me of a problem I had with environment variables in PostGIS with MingW. It was something along the lines of environment variables set in a MingW program using putenv() for PGPORT, PGHOST etc. weren't visible to a MSVC-compiled libpq but were to a MingW-compiled libpq. It's fairly easy to knock up a quick test program in C to verify this. That's the reason for this patch to go in in the first place. That has been fixed. It also seems to have caused crashes on mingw, which was not expected :-) It's not actually a fault with mingw putenv, it's just that those go into the cached environment only. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 14:56 -0500, Tom Lane wrote: Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. I see the opposite: We don't know what tradeoffs, if any, the user is willing to put up with, so we need input. It is about resource prioritisation and not for us to decide, since these reflect business objectives not internal twangy things like join_collapse_limit. The essential choice is What would you like the max failover time to be?. Some users want one server with max 5 mins behind, some want two servers, one with 0 seconds behind, one with 12 hours behind -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Simon Riggs wrote: The essential choice is What would you like the max failover time to be?. Some users want one server with max 5 mins behind, some want two servers, one with 0 seconds behind, one with 12 hours behind It's not quite that simple. Setting max_standby_delay=5mins means that you're willing to wait 5 minutes for each query to die. Which means that in worst case you have to stop for 5 minutes at every single vacuum record, and fall behind much more than 5 minutes. You could make it more like that by tracking the timestamps in commit records, and/or having some sort of a moving average logic in the timeout, where you allow more waiting if you haven't waited for a long time, and kill queries more aggressively if you've had to wait a lot recently. It should also be noted that the control functions allow you to connect to the database and manually pause/resume the replay. So you can for example set max_standby_delay=0 during the day, but pause the replay manually before starting a nightly report. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 22:47 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: The essential choice is What would you like the max failover time to be?. Some users want one server with max 5 mins behind, some want two servers, one with 0 seconds behind, one with 12 hours behind It's not quite that simple. In this case, yes it is. Setting max_standby_delay=5mins means that you're willing to wait 5 minutes for each query to die. Which means that in worst case you have to stop for 5 minutes at every single vacuum record, and fall behind much more than 5 minutes. That's not how this patch works. You could make it more like that by tracking the timestamps in commit records Which is what we do. It should also be noted that the control functions allow you to connect to the database and manually pause/resume the replay. So you can for example set max_standby_delay=0 during the day, but pause the replay manually before starting a nightly report. Yes, thank you for bringing balance to the discussions. Please everybody read this before commenting further. http://wiki.postgresql.org/wiki/Hot_Standby#Usage -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
On Wed, 2009-01-28 at 22:47 +0200, Heikki Linnakangas wrote: It's not quite that simple. Setting max_standby_delay=5mins means that you're willing to wait 5 minutes for each query to die. Which means that in worst case you have to stop for 5 minutes at every single vacuum record, and fall behind much more than 5 minutes. Just trying to follow along: are you talking about the situation where there are (for example) a continuous stream of select pg_sleep(600) on the standby, and a series of rapid VACUUMs on the primary? This situation might be more likely now that we have partial VACUUMs. It should also be noted that the control functions allow you to connect to the database and manually pause/resume the replay. So you can for example set max_standby_delay=0 during the day, but pause the replay manually before starting a nightly report. That's a very cool feature. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Andrew Sullivan a...@crankycanuck.ca writes: On Wed, Jan 28, 2009 at 01:49:21PM -0500, Joshua Brindle wrote: The costs are nil for people who don't want this feature. That's also false, because developers who don't care about the feature have to continue to maintain it as part of the system. If maintenance were free, I suspect nobody would be objecting to the feature. But this feature will in fact constrain future development and will impose maintenance requirements on the programmers of the system. Right. The major implementation problem I have with row-level security is that it will require sticking its hands into every part of the backend; at least if you want it to be actually *secure* with no holes, and if not I guess I'm failing to grasp the point. Every future patch will have to be vetted to ensure that it's not accidentally breaking that security. This stems directly from the fact that you're trying to impose behavior that's fundamentally at odds with SQL, and therefore there isn't any well-defined choke point at which you could apply the checks and be done with it. The system simply isn't modularized that way. (Of course we could throw it all away and start over...) BTW, in regard to the upthread question about how much of the patch could be discarded if we removed row-level security: having now taken another look through it, I'd put the fraction at well north of 90%. (That's exclusive of the security policy file, which I don't understand at all and so can't tell how much might be specific to row security.) What's worse, the current patch footprint is conservative because the placement of hooks is simply wrong. You can't usefully apply checks in simple_heap_insert, for example, since it has no idea who called it or why. It's got to be done at a higher level and therefore in a lot more places. And I don't see any attempt at all to restrict system-driven fetches, yet surely there has to be some control over that (otherwise why are we worrying about system-driven updates?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
I don't. Primarily, we must support high availability. It is much better if we get people saying I get my queries cancelled and we say RTFM and change parameter X, than if people say my failover was 12 hours behind when I needed it to be 10 seconds behind and I lost a $1 million because of downtime of Postgres and we say RTFM and change parameter X. If the person was stupid enough to configure it for such as thing they deserve to the lose the money. Not to mention we have already lost them as a user because they will blame postgresql regardless of reality as evidenced by their inability to RTFM (or have a vendor that RTFMs) in the first place. I got to vote with Greg on this one. I vote with Simon. The thing is that if you get some queries cancelled, you'll realize you have a problem. Now you have several options for what to do to fix it. Having your failover be 12 hours behind (or 12 months behind) is something that it would be much easier to not realize. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Sorry for top-posting and avoiding to paste online doc URL. Joshua, you sound like you're missing an important point. Sorry for misunderstanding if that's not true. Partitioning is supported in a way that a query does not need to know about it, be it a SELECT, INSERT, UPDATE or DELETE. And 8.4 onwards, some more. What Tom is talking about is adding support for discarding partitions based on GRANTed rights rather than WHERE clauses. No application rewrite. Columns obfuscating remains to be cared of: what about allowing views as partitions? HTH, regards, -- dim Le 28 janv. 09 à 19:49, Joshua Brindle met...@manicmethod.com a écrit : Tom Lane wrote: Joshua Brindle met...@manicmethod.com writes: I'm not sure how much it would cut to remove row level access controls, but I do have some points here. To me, row level access controls are the most important part, this is the feature that lets us put secret and top secret data in the same table and use the clients selinux context to decide what they can see, For me, the row-level access controls are really the sticking point. There is absolutely nothing you can say that will convince me that they don't break SQL in fundamental ways, and I also don't believe that it's going to be possible to implement them without a constant stream of bugs of omission and commission. (Those two points are not unrelated.) This isn't going to be something that the vast majority of your users use. The people that need them understand the ramifications of row filtering and the absence of inaccessible rows won't be surprising. Now that you've admitted that you aren't trying to accomplish the equivalent sort of data hiding within the filesystem, the argument that We apply access controls on reading and writing files, this is equivalent (in my mind) to applying access controls on tuples, as they are the structured object holding data (just like files). you have to have them seems fairly weak, certainly not strong enough to justify the costs. We have already touched on some ways that you can The costs are nil for people who don't want this feature. accomplish similar goals with just table- and column-level access permissions, which are well understood and don't violate anyone's I've already pointed out how table and column level access control don't provide enough. Holding data of multiple classifications in a single table is something of great concern. Unmodified applications need to be able to query out of a table and get the data they are cleared to see. Column level access control doesn't help because those same applications, of course, will need access to the data in those columns. Splitting data into different tables isn't an option in many cases because applications aren't always configurable wrt to the database or tables they use. Further, clients of multiple clearances should be able to use the same application, and we can not trust the application to make the decision as to which database or table is correct. And even further, maintaining multiple sets of databases or tables that hold the same kinds of information (and therefore have the same schema) is a maintenance, backup and restoration issue. expectations. There's probably a need to twiddle our permissions rules for inheritance/partitioning cases, but that was already on the table anyway for other reasons. partitions don't help because, once again, the application would be making the determination about which partition to query. For mandatory access control that we need in the kind of environments to work the application doesn't get to make security relevant decisions, the database holds the data and needs to say who can access it. Further, partitioning isn't fine grained. I can't say user X can read secret rows and read/write top secret rows and get that data out in a transparent way (the applications would have to be aware of the partitions). Relabeling of data also looks like a challenge with partitions (if I correctly understand how they work). I could be persuaded to get behind a patch that does Peter's step #1 (ie, use SELinux permissions as an additional filter for existing SQL permission checks). I don't believe I will ever think that row-level checks are a good idea; as long as those are in the patch I will vote against applying it. We've already used postgresql in sensitive environments and had to make compromises because of the lack of fine grained access control. We've been telling customers for years that we hope postgresql will have said access controls in the future and that it will help them solve many of the problems they have. We've been enthusiastic about the work KaiGai has been doing with respect to the environments we operate in and it would be a shame for all that to be discarded. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To
Re: [HACKERS] Hot Standby (v9d)
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-01-28 at 14:56 -0500, Tom Lane wrote: Well, those unexpectedly cancelled queries could have represented critical functionality too. I think this argument calls the entire approach into question. If there is no safe setting for the parameter then we need to find a way to not have the parameter. I see the opposite: We don't know what tradeoffs, if any, the user is willing to put up with, so we need input. Well, if you see it that way then it seems to me you should be arguing for making max_standby_delay a mandatory parameter. Without it don't start allow connections. I hadn't considered that and am not exactly sure where I would stand on it. The essential choice is What would you like the max failover time to be?. Some users want one server with max 5 mins behind, some want two servers, one with 0 seconds behind, one with 12 hours behind Sure. But if they don't configure one then we shouldn't impose one. You're thinking of precisely one use case and taking positive action to interrupt the user's requests on the basis of it. But there are plenty of other use cases. I claim the default has to be to do as the user instructed without intervention. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby (v9d)
Robert Haas robertmh...@gmail.com writes: I vote with Simon. The thing is that if you get some queries cancelled, you'll realize you have a problem. ... or if you don't, they couldn't have been all that critical. Having your failover be 12 hours behind (or 12 months behind) is something that it would be much easier to not realize. Okay, I'm sold, positive max_standby_delay should be the default. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Le 28 janv. 09 à 16:22, Simon Riggs si...@2ndquadrant.com a écrit : The only way to keep the dev window open longer is to overlap the start of the next cycle with the previous one. i.e. branch new version before final release. This is the second time the idea is raised and I like it. Do we have anywhere near enough resources for this to happen? That would mean first 8.5 commit-fest begins e.g. April 1st, while hopefully 8.4 enters beta or get ready for it. If no commiter is available for reviewed patch they just get postponed as ready to commit on next commit fest. This way our Round Robin Reviewers team is still at work while in beta, and more importantly developpers still get feedback. -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Column privileges for system catalogs
* Tom Lane (t...@sss.pgh.pa.us) wrote: I don't have any objection to changing the catalog's own permissions that way, but the filtered view still has a usability advantage: you can just go select * from Is it reasonable to change the catalog permissions and keep the view too? I've fine with that, I don't see any drawback to it, personally.. Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade project status
Tom Lane píše v st 28. 01. 2009 v 14:06 -0500: Trying to do catalog upgrade in-place is going to be a complete mess. I'd be interested to know, for example, how you imagine rearranging the contents of pg_class would work. You don't get to modify pg_class if you can't even find it, which you can't because you can't read it. It is relatively easy. There are very high design: 1) upgrade move old catalog table into save location. We can create for example special tablespace for it 2) bootstrap creates catalog + create records for old catalog structure 3) data will be copied like INSERT INTO pg_class select from pg_upgrade.pg_class where oid16... In reality it will be more complicated command but pure SQL. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Dimitri Fontaine dfonta...@hi-media.com writes: Le 28 janv. 09 à 16:22, Simon Riggs si...@2ndquadrant.com a écrit : The only way to keep the dev window open longer is to overlap the start of the next cycle with the previous one. i.e. branch new version before final release. This is the second time the idea is raised and I like it. Do we have anywhere near enough resources for this to happen? No. The key committers are overstressed already. It would also pretty much guarantee that we get *no* help during review and beta, because everyone else will find it more interesting/fun to work on new patches instead. The current system at least gives non-committer developers some motivation to help with that stuff, because they know their patches won't be looked at until beta is over. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
On Wed, Jan 28, 2009 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Sullivan a...@crankycanuck.ca writes: On Wed, Jan 28, 2009 at 01:49:21PM -0500, Joshua Brindle wrote: The costs are nil for people who don't want this feature. That's also false, because developers who don't care about the feature have to continue to maintain it as part of the system. If maintenance were free, I suspect nobody would be objecting to the feature. But this feature will in fact constrain future development and will impose maintenance requirements on the programmers of the system. Right. The major implementation problem I have with row-level security is that it will require sticking its hands into every part of the backend; at least if you want it to be actually *secure* with no holes, and if not I guess I'm failing to grasp the point. Every future patch will have to be vetted to ensure that it's not accidentally breaking that security. This stems directly from the fact that you're trying to impose behavior that's fundamentally at odds with SQL, and therefore there isn't any well-defined choke point at which you could apply the checks and be done with it. The system simply isn't modularized that way. (Of course we could throw it all away and start over...) BTW, in regard to the upthread question about how much of the patch could be discarded if we removed row-level security: having now taken another look through it, I'd put the fraction at well north of 90%. (That's exclusive of the security policy file, which I don't understand at all and so can't tell how much might be specific to row security.) What's worse, the current patch footprint is conservative because the placement of hooks is simply wrong. You can't usefully apply checks in simple_heap_insert, for example, since it has no idea who called it or why. It's got to be done at a higher level and therefore in a lot more places. And I don't see any attempt at all to restrict system-driven fetches, yet surely there has to be some control over that (otherwise why are we worrying about system-driven updates?) I'm not clear that I understand why it would be necessary for row-level security to touch every part of the code. If the current implementation requires that, then maybe that's a defect in the implementation rather than an inherent problem with row-level security. It seems to me that the crucial decision is Where are we trying to erect the security wall?. If we're trying to put it between the client and the postgres backend, then maybe the right thing to do is apply some sort of processing step to each query that is submitted, essentially rewriting SELECT * FROM a, b as SELECT * FROM a, b WHERE you_can_see(a.securityid) AND you_can_see(b.securityid). Maybe you (Tom) still won't like this because it breaks SQL semantics, but it seems like it would at least centralize the code. Unless I'm totally off base here? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Zdenek Kotala wrote: Bruce Momjian p??e v po 26. 01. 2009 v 23:02 -0500: OK, time for me to chime in. I think the outstanding commit-fest items can be broken down into four sections: o Log streaming o Hot standby o SE-PostgreSQL o Others You omit pg_upgrade. Does it mean that this project is already killed for 8.4? I considered pg_upgrade one of the others on the list; it is not as complex as the previous three. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
On Wed, Jan 28, 2009 at 5:00 PM, Zdenek Kotala zdenek.kot...@sun.com wrote: Tom Lane píše v st 28. 01. 2009 v 14:06 -0500: Trying to do catalog upgrade in-place is going to be a complete mess. I'd be interested to know, for example, how you imagine rearranging the contents of pg_class would work. You don't get to modify pg_class if you can't even find it, which you can't because you can't read it. It is relatively easy. There are very high design: 1) upgrade move old catalog table into save location. We can create for example special tablespace for it 2) bootstrap creates catalog + create records for old catalog structure 3) data will be copied like INSERT INTO pg_class select from pg_upgrade.pg_class where oid16... In reality it will be more complicated command but pure SQL. I really like this idea, assuming I understand it. Basically, I think you're proposing that we move the old system catalogs out of the way, bootstrap a new catalog, and then using SQL (running inside a standalone backend?) to migrate data from the old catalog to the new one. That sounds really good to me. Even a relatively complicated catalog reorganization should be able to be handled this way without too much work or a lot of special-purpose code. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
I considered pg_upgrade one of the others on the list; it is not as complex as the previous three. LOL. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
* Tom Lane (t...@sss.pgh.pa.us) wrote: For me, the row-level access controls are really the sticking point. There is absolutely nothing you can say that will convince me that they don't break SQL in fundamental ways, and I also don't believe that it's going to be possible to implement them without a constant stream of bugs of omission and commission. (Those two points are not unrelated.) And, just to go full circle, row-level access controls are exactly what the other enterprise RDBMSs have and is what is used in these security circles today. One of the major issues, as I understand it, is to be able to use stock applications with multiple security levels where the application doesn't know (or care about) the security level. Doing that through views and partitions and triggers and whatnot for each and every application that is run on these systems will be a big hurdle to those users, if it ends up being workable at all. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Joshua Brindle wrote: Tom Lane wrote: Joshua Brindle met...@manicmethod.com writes: I'm not sure how much it would cut to remove row level access controls, but I do have some points here. To me, row level access controls are the most important part, this is the feature that lets us put secret and top secret data in the same table and use the clients selinux context to decide what they can see, Help me understand this. It seems to me exactly as easy/hard to make sure that the secret and top-secret rows are put into their appropriate partitions, as it is to make sure that the secret and top-secret rows are tagged with the right row-level-access-info. If the idea is that the top-secret-row-inserter magically forces the row-level tag top-secret it seems just as easy if the top-secret row-inserter only has write permission to the top-secret partition and not the others. If the idea is that the less-secret-reader can't read rows tagged top-secret it seems just as easy if the less-secret-reader has no read access on the top-secret partition. At first glance, partition level seems quite a bit easier to manage in all the cases I can think of immediately. partitions don't help because, once again, the application would be making the determination about which partition to query. For mandatory I think that's not true. I would hope that the application queries the master table, and the SEPostgres ACLs prevent any data coming from the inappropriate partitions. access control that we need in the kind of environments to work the application doesn't get to make security relevant decisions, the database holds the data and needs to say who can access it. Further, partitioning isn't fine grained. I can't say user X can read secret rows and read/write top secret rows and get that data out in a Why not? It seems one can define the user with read access on the partition with secret rows and read/write on top-secret rows. Queries done on the master partition should get the data out in a transparent way. transparent way (the applications would have to be aware of the partitions). Relabeling of data also looks like a challenge with partitions (if I correctly understand how they work). ISTM we need to have a discussion of how partitioned tables work - and what the postgres roadmap is. If they can't yet, I think they should. I could be persuaded to get behind a patch that does Peter's step #1 (ie, use SELinux permissions as an additional filter for existing SQL permission checks). I don't believe I will ever think that row-level checks are a good idea; as long as those are in the patch I will vote against applying it. We've already used postgresql in sensitive environments and had to make compromises because of the lack of fine grained access control. We've been telling customers for years that we hope postgresql will have said access controls in the future and that it will help them solve many of the problems they have. We've been enthusiastic about the work KaiGai has been doing with respect to the environments we operate in and it would be a shame for all that to be discarded. AFACT there's nowhere near a consensus that it should be discarded (or accepted); but rather that if the project can be split into two phases parts of it could go in sooner. There seems to be much less debate about the column/table/partition level MAC parts. Once those get in, I think the next valuable discussion would be whether the fine-grained access control is best achieved through improving the partition system or by adding the row-level acls as proposed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
Stephen Frost wrote: And, just to go full circle, row-level access controls are exactly what the other enterprise RDBMSs have and is what is used in these security circles today. One of the major issues, as I understand it, is to be able to use stock applications with multiple security levels where the application doesn't know (or care about) the security level. Doing that through views and partitions and triggers and whatnot for each and every application that is run on these systems will be a big hurdle to those users, if it ends up being workable at all. That seems to me to be a shortcoming of the partition system and a good TODO for the future partitioning improvements. Why shouldn't be just as easy to make sure a row ends up in the right partition as opposed to making sure it's tagged with right row-level ACLs. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Wednesday 28 January 2009 12:35:42 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: On Wednesday 28 January 2009 08:55:56 Magnus Hagander wrote: We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. As I pointed out to Tom, by percentage the additional beta/release cycles wouldn't be very different than what we have now; the more churn you have during development, the longer it takes to beta/release. I don't believe that thesis in itself, because it ignores economies of scale and parallelism for beta testing. And in any case it's complete nonsense in respect to back-branch maintenance costs. If we double the frequency of releases we are going to be pretty much forced to halve the support lifetime, and ain't nobody going to be happy with us. Yes, back branch maintanance is an issue, but I'd bet that as long as we occasionally designate specific releases as long term support releases (my guess is 1 every 4 releases, though I haven't done the math), people would be comfortable with this. We've already had short maintainance windows for win32 support, and that has gone over without significant uproar. Also other projects (some much larger than ours) have implemented similar schemes, and it's been fairly well recieved. People understand the trade-offs of new features verses stability, and as long as you give them a long term option, they're happy. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jan 27, 2009 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right, but you expect that to be a small and predictable cost, say in the single-digits-percentage range. Plan optimizations that suddenly stop happening can cost you multiple orders of magnitude. Well, look at it another way. If we don't accept row-level security into PostgreSQL, then people will have to implement it themselves. In fact, I currently have a real application that does exactly this. The row-filtering is done, in essence, by having the web application add certain conditions to the WHERE clause of certain queries depending on which user is making the request. And if those WHERE clauses happen to mention columns from table X, then table X won't be a candidate for join removal. The only difference is that the logic is in my app rather than in the database itself. To put that another way, row-level permissions are just another attribute of a table that could potentially affect the query result, and the impact of referring to that attribute will be exactly the same as the impact of referring to any other attribute in that table. The flaw in that argument is that as you are doing it, the de-optimization only happens on queries that actually need the behavior. As the SEPostgres patch is constructed, the planner could *never* trust an FK for optimization since it would have no way to know whether row level permissions might be present (perhaps only for some rows) at execution time. You could only get back the optimization in builds with SEPostgres compiled out. That's pretty nasty, especially for packagers who have to decide which build setting will displease fewer users. I am afraid that SQL-level row permissions would also cause that problem, and I thought they were enabled by default. (The configure flag --enable-selinux only controls SE-Linux support.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Output filter for psql
D'Arcy J.M. Cain wrote: On Wed, 28 Jan 2009 14:08:54 -0500 Andrew Dunstan and...@dunslane.net wrote: D'Arcy J.M. Cain wrote: I suppose we could define another line with options that we could define for meta information such as the border setting and the table name and whatever we define later. E.g: table:person,border:3,funky:option with \:\ Why do you want to add this extra information? psql doesn't output it now, and IIRC your original proposal didn't do it either. It mentioned including meta information. I'm not sure that I really care if I get it but we may want it in general, if not now then later, and it would be something that would be extremely hard to retrofit. If we start out with just the table and perhaps the border setting we can always add more later if we find we need it. If we don't start with something then adding an extra line later will cause all sorts of heartache. For CSV it's likely to cause a headache right at the start. CSV with a single header line is a well known and mostly understood format. I don't know of any CSV processor (including ours) that handles more than one header line. This is actually an example of why XML is not such a bad choice. It is rich enough that a processor is unlikely to be confused by metadata, and capable of being turned into almost any other format you might want with little difficulty. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Simon Riggs wrote: On Tue, 2009-01-27 at 14:18 -0500, Tom Lane wrote: Joshua Brindle met...@manicmethod.com writes: Tom Lane wrote: Right, which is why it's bad for something like a foreign key constraint to expose the fact that the row does exist after all. Once again, this is not an issue for us. Yes it is an issue The question of whether there is a covert channel is only a small part of my complaint here. If it's the judgement of security experts that that's an acceptable thing, that's fine, it's their turf. But SQL behavior is my turf, and I'm not happy with discarding fundamental semantic properties. Why did we bother to invite Joshua here if we aren't going to listen to him? Thanks for coming to help Joshua, much appreciated. I agree. This is exactly the type of feedback I was hoping for. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Robert Haas wrote: The flaw in that argument is that as you are doing it, the de-optimization only happens on queries that actually need the behavior. As the SEPostgres patch is constructed, the planner could *never* trust an FK for optimization since it would have no way to know whether row level permissions might be present (perhaps only for some rows) at execution time. You could only get back the optimization in builds with SEPostgres compiled out. That's pretty nasty, especially for packagers who have to decide which build setting will displease fewer users. OK, I think I am starting to understand your concern now. My understanding of how the world works is SE-PostgreSQL would always be compiled in but could be turned off at run-time with a GUC. I know that the original design called for a compile-time switch, but everyone hated it and I am pretty sure KaiGai changed it. If he hasn't, he will. :-) There was also talk of having a table-level option to include/exclude the security ID (I'm not sure if it's currently implemented that way). Obviously that wouldn't be relevant for row-level MAC (because presumably you would need/want that turned on for all tables) but it would be very relevant for row-level DAC (because it's easy, at least for me, to imagine that you would only turn this on for a subset, possibly quite a small subset, of your tables where you knew that it was really needed). If, by default, we make sepostgresql disabled, MAC security IDs on newly created tables off, and DAC security IDs on newly created tables off, then the pain will be confined to people who explicitly request sepostgresql or row-level DAC. Yes, if there is concern about row-level security turning off optimizations, some flag would have to be checked so the optimization would be possible for sites not using row-level security; ideally there would be a table-level flag, and I think the current patch implements it that way. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: As the SEPostgres patch is constructed, the planner could *never* trust an FK for optimization since it would have no way to know whether row level permissions might be present (perhaps only for some rows) at execution time. You could only get back the optimization in builds with SEPostgres compiled out. That's pretty nasty, especially for packagers who have to decide which build setting will displease fewer users. I am afraid that SQL-level row permissions would also cause that problem, and I thought they were enabled by default. (The configure flag --enable-selinux only controls SE-Linux support.) So they would. However, I've already determined that I'm against row-level permissions of either flavor ;-) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers