Re: [HACKERS] Re: [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.
Heikki Linnakangas wrote: Magnus Hagander wrote: Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Tom Lane wrote: What makes more sense to me is to add a table to encnames.c that provides the gettext name of every encoding that we support. Do you mean a separate table there, or should we add a new column to one of the existing tables? Whichever seems to make more sense is fine with me. I just don't want add-an-encoding maintenance requirements spread across N different source files. I was about to start looking at this when that other thread (http://archives.postgresql.org//pgsql-hackers/2009-03/msg01270.php) started about related issues on other platforms. Seems we should have a coordinated fix for this, so I'm going to want and see what come sout of that one. Unless I'm misunderstanding thigns and they're not related? I've committed a fairly trivial patch per Peter's suggestion to fix the other thread's issue. I left the table as is, so whatever refactorings were planned can now be applied. Here's a patch that moves the table over to encnames.c, and renames it to look like the others. I don't know what it should be doing if it can't find a match, so I haven't changed that behavior. Comments? //Magnus *** a/src/backend/utils/mb/encnames.c --- b/src/backend/utils/mb/encnames.c *** *** 431,436 pg_enc2name pg_enc2name_tbl[] = --- 431,478 }; /* -- + * These are encoding names for gettext. + * -- + */ + pg_enc2gettext pg_enc2gettext_tbl[] = + { + {PG_UTF8, UTF-8}, + {PG_LATIN1, LATIN1}, + {PG_LATIN2, LATIN2}, + {PG_LATIN3, LATIN3}, + {PG_LATIN4, LATIN4}, + {PG_ISO_8859_5, ISO-8859-5}, + {PG_ISO_8859_6, ISO_8859-6}, + {PG_ISO_8859_7, ISO-8859-7}, + {PG_ISO_8859_8, ISO-8859-8}, + {PG_LATIN5, LATIN5}, + {PG_LATIN6, LATIN6}, + {PG_LATIN7, LATIN7}, + {PG_LATIN8, LATIN8}, + {PG_LATIN9, LATIN-9}, + {PG_LATIN10, LATIN10}, + {PG_KOI8R, KOI8-R}, + {PG_KOI8U, KOI8-U}, + {PG_WIN1250, CP1250}, + {PG_WIN1251, CP1251}, + {PG_WIN1252, CP1252}, + {PG_WIN1253, CP1253}, + {PG_WIN1254, CP1254}, + {PG_WIN1255, CP1255}, + {PG_WIN1256, CP1256}, + {PG_WIN1257, CP1257}, + {PG_WIN1258, CP1258}, + {PG_WIN866, CP866}, + {PG_WIN874, CP874}, + {PG_EUC_CN, EUC-CN}, + {PG_EUC_JP, EUC-JP}, + {PG_EUC_KR, EUC-KR}, + {PG_EUC_TW, EUC-TW}, + {PG_EUC_JIS_2004, EUC-JP} + }; + + + /* -- * Encoding checks, for error returns -1 else encoding id * -- */ *** a/src/backend/utils/mb/mbutils.c --- b/src/backend/utils/mb/mbutils.c *** *** 890,936 cliplen(const char *str, int len, int limit) return l; } - #if defined(ENABLE_NLS) - static const struct codeset_map { - int encoding; - const char *codeset; - } codeset_map_array[] = { - {PG_UTF8, UTF-8}, - {PG_LATIN1, LATIN1}, - {PG_LATIN2, LATIN2}, - {PG_LATIN3, LATIN3}, - {PG_LATIN4, LATIN4}, - {PG_ISO_8859_5, ISO-8859-5}, - {PG_ISO_8859_6, ISO_8859-6}, - {PG_ISO_8859_7, ISO-8859-7}, - {PG_ISO_8859_8, ISO-8859-8}, - {PG_LATIN5, LATIN5}, - {PG_LATIN6, LATIN6}, - {PG_LATIN7, LATIN7}, - {PG_LATIN8, LATIN8}, - {PG_LATIN9, LATIN-9}, - {PG_LATIN10, LATIN10}, - {PG_KOI8R, KOI8-R}, - {PG_KOI8U, KOI8-U}, - {PG_WIN1250, CP1250}, - {PG_WIN1251, CP1251}, - {PG_WIN1252, CP1252}, - {PG_WIN1253, CP1253}, - {PG_WIN1254, CP1254}, - {PG_WIN1255, CP1255}, - {PG_WIN1256, CP1256}, - {PG_WIN1257, CP1257}, - {PG_WIN1258, CP1258}, - {PG_WIN866, CP866}, - {PG_WIN874, CP874}, - {PG_EUC_CN, EUC-CN}, - {PG_EUC_JP, EUC-JP}, - {PG_EUC_KR, EUC-KR}, - {PG_EUC_TW, EUC-TW}, - {PG_EUC_JIS_2004, EUC-JP} - }; - #endif /* ENABLE_NLS */ - void SetDatabaseEncoding(int encoding) { --- 890,895 *** *** 969,980 pg_bind_textdomain_codeset(const char *domainname) return; #endif ! for (i = 0; i lengthof(codeset_map_array); i++) { ! if (codeset_map_array[i].encoding == encoding) { if (bind_textdomain_codeset(domainname, ! codeset_map_array[i].codeset) == NULL) elog(LOG, bind_textdomain_codeset failed); break; } --- 928,939 return; #endif ! for (i = 0; pg_enc2gettext_tbl[i].name != NULL; i++) { ! if (pg_enc2gettext_tbl[i].encoding == encoding) { if (bind_textdomain_codeset(domainname, ! pg_enc2gettext_tbl[i].name) == NULL) elog(LOG, bind_textdomain_codeset failed); break; } *** a/src/include/mb/pg_wchar.h --- b/src/include/mb/pg_wchar.h *** *** 262,267 typedef struct pg_enc2name --- 262,278 extern pg_enc2name pg_enc2name_tbl[]; /* + * Encoding names for gettext + */ + typedef struct pg_enc2gettext + { + pg_enc encoding; + const char *name; + } pg_enc2gettext; + + extern pg_enc2gettext pg_enc2gettext_tbl[]; + + /* * pg_wchar stuff */ typedef int (*mb2wchar_with_len_converter) (const unsigned char *from, -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make
Re: [HACKERS] New trigger option of pg_standby
Fujii Masao wrote: On Tue, Apr 14, 2009 at 2:41 PM, Fujii Masao masao.fu...@gmail.com wrote: I'd like to propose another simple idea; pg_standby deletes the trigger file *whenever* the nextWALfile is a timeline history file. A timeline history file is restored at the end of recovery, so it's guaranteed that the trigger file is deleted whether nextWALfile exists or not. A timeline history file is restored also at the beginning of recovery, so the accidentally remaining trigger file is deleted in early warm-standby as a side-effect of this idea. Here is the revised patch as above. I think we have gone off to an overly complicated solution. pg_standby shouldn't need to special-case history files, or know what order the server will ask for them. What's wrong with just this: (ignoring the missing fast option) --- a/contrib/pg_standby/pg_standby.c +++ b/contrib/pg_standby/pg_standby.c @@ -552,8 +552,6 @@ main(int argc, char **argv) break; case 't': /* Trigger file */ triggerPath = optarg; - if (CheckForExternalTrigger()) - exit(1);/* Normal exit, with non-zero */ break; case 'w': /* Max wait time */ maxwaittime = atoi(optarg); ie. only check and delete the trigger file once the server requests a file that doesn't exist. -- 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] WITH inconsistency
On Monday 20 April 2009 01:35:25 Robert Grabowski wrote: I found some inconsistency on WITH keyword: Yes, there are lots of those. Deal with it. ;-) -- 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 for 8.5, transformationHook
On Sunday 19 April 2009 20:47:37 Pavel Stehule wrote: 2009/4/19 Peter Eisentraut pete...@gmx.net: On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote: There are lot of things, that should be done with current grammar only on transformation stage. Currently pg do it now. There are lot of pseudo functions, that are specially transformed: least, greatest, coalesce. After hooking we should do some similar work from outer libraries. There are surely other ways to accomplish this than an expression transformation hook. Adding a property or two to the function definition to do what you want could do it. should you describe it little bit more? The question we should be asking is, what is it that prevents us from implementing least, greatest, and coalesce in user space now? And then design a solution for that, if we wanted to pursue this. Instead of writing transformation hooks and then force every problem to fit that solution. -- 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] Unicode support
On Sunday 19 April 2009 18:54:45 Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On Monday 13 April 2009 20:18:31 - - wrote: 1) Functions like char_length() or length() do NOT return the number of characters (the manual says they do), instead they return the number of code points. I have added a Todo item about possibly fixing this. I thought the conclusion of the thread was that this wasn't wrong? The only consensus I saw was that the normal form of an existing Unicode string shouldn't be altered by PostgreSQL. That's pretty clear. However, no one was entirely clear on the matter of how combining characters are supposed to be processed. And even if we think that the current interfaces give the right answer, there should possibly be other interfaces that give the other right answer. It needs more research first of all. -- 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 for 8.5, transformationHook
2009/4/20 Peter Eisentraut pete...@gmx.net: On Sunday 19 April 2009 20:47:37 Pavel Stehule wrote: 2009/4/19 Peter Eisentraut pete...@gmx.net: On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote: There are lot of things, that should be done with current grammar only on transformation stage. Currently pg do it now. There are lot of pseudo functions, that are specially transformed: least, greatest, coalesce. After hooking we should do some similar work from outer libraries. There are surely other ways to accomplish this than an expression transformation hook. Adding a property or two to the function definition to do what you want could do it. should you describe it little bit more? The question we should be asking is, what is it that prevents us from implementing least, greatest, and coalesce in user space now? And then design a solution for that, if we wanted to pursue this. Instead of writing transformation hooks and then force every problem to fit that solution. I don't believe so is possible to find other general solution. (or better I didn't find any other solution). Tom has true, transformationHook on expression is expensive. I thing, so hook on function should be simple and fast - not all transformation's should be simple defined via property - classic sample is decode like functions, it needs procedural code. regards Pavel Stehule -- 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] unalias of ACL_SELECT_FOR_UPDATE
Heikki Linnakangas wrote: KaiGai Kohei wrote: Tom Lane wrote: KaiGai Kohei kai...@kaigai.gr.jp writes: The vanilla access control mechanism switches the current userid, and it enables to run SELECT FOR SHARE without ACL_UPDATE, but SELinux's security model does not have a concept of ownership. Should I not read that as SELinux's security model is so impoverished that it cannot be useful for monitoring SQL behavior? If you don't understand current user and ownership, it's hopeless. Trying to distinguish SELECT FOR UPDATE instead of that is a workaround that is only going to fix one symptom (if it even works for this, which I doubt). There will be many more. It is a difference between two security designs, characteristics and philosophies, not a competitive merit and demerit. SELinux makes its decision based on the security policy and the security context of client and objects accessed. Here, user identifier and object ownership don't appear. Meanwhile, the vanilla PostgreSQL makes its decision based on the user identifier and database ACLs of objects accessed. It does not use the security context, needless to say. Can't you have a SE-PostgreSQL policy like disallow ACL_UPDATE on table X for user Y, except when current user is owner of X? It seems to me a quite ad-hoc idea. At least, it can prevents several users (with individual security contexts) to share a common read-writable table, even if both of the kernel security policy and vanilla database ACL allow it. Now we can discriminate them using rte-modifiedCols. I don't find out any problem in this approach. It can solve my headach without any changes in the vanilla database ACLs. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] [PATCH] Borland C Compiler compatibility issues
On Mon, Apr 20, 2009 at 1:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Pavel Golub pa...@microolap.com writes: Here the patch to /src/include/pg_config_os.h attached improving Borland C++ Compiler compatibility. Applied along with your other two patches. Please note in future that pg_config_os.h is a derived file --- this patch should have been against src/include/port/win32.h. regards, tom lane Oops. My bad. Thanks. -- Nullus est in vitae sensus ipsa vera est sensus. -- 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] unalias of ACL_SELECT_FOR_UPDATE
KaiGai Kohei wrote: Tom Lane wrote: KaiGai Kohei kai...@kaigai.gr.jp writes: The vanilla access control mechanism switches the current userid, and it enables to run SELECT FOR SHARE without ACL_UPDATE, but SELinux's security model does not have a concept of ownership. Should I not read that as SELinux's security model is so impoverished that it cannot be useful for monitoring SQL behavior? If you don't understand current user and ownership, it's hopeless. Trying to distinguish SELECT FOR UPDATE instead of that is a workaround that is only going to fix one symptom (if it even works for this, which I doubt). There will be many more. It is a difference between two security designs, characteristics and philosophies, not a competitive merit and demerit. SELinux makes its decision based on the security policy and the security context of client and objects accessed. Here, user identifier and object ownership don't appear. Meanwhile, the vanilla PostgreSQL makes its decision based on the user identifier and database ACLs of objects accessed. It does not use the security context, needless to say. Can't you have a SE-PostgreSQL policy like disallow ACL_UPDATE on table X for user Y, except when current user is owner of X? -- 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] [GENERAL] Performance of full outer join in 8.3
On Sat, 2009-04-18 at 08:32 -0400, Tom Lane wrote: The issues that I think would be worth having tests for are questions like will the planner push comparisons to constants down through a full join? (which was the bug that started this thread). Yes, that sounds good. With a test methodology like the above, it wouldn't be enough to write a test case that exercised the behavior; you'd have to make sure that any alternative plan was an order of magnitude worse. I'm inclined to think that some sort of fuzzy examination of EXPLAIN output (in this example, are there constant-comparison conditions in the relation scans?) might do the job, but I'm not sure how we'd go about that. We can compose unit tests that have plans where the presence/absence of the optimizer action is critical to a good plan. i.e. if the constant-comparison is *not* pushed down it will be unable to use an index created for it and so run cost will be much greater. We can then define success in terms of a reduction in plan cost below a threshold. So for each test we specify * SQL * a success threshold for cost e.g. For a piece of SQL we have cost = 60002.2 without optimisation or 12.45 with optimisation, so we make the threshold 20.0. Enough slack to allow for changes in plan costs on platforms/over time, yet sufficient to discriminate between working/non-working optimisation. -- 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] New trigger option of pg_standby
Hi, On Mon, Apr 20, 2009 at 6:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: On Tue, Apr 14, 2009 at 2:41 PM, Fujii Masao masao.fu...@gmail.com wrote: I'd like to propose another simple idea; pg_standby deletes the trigger file *whenever* the nextWALfile is a timeline history file. A timeline history file is restored at the end of recovery, so it's guaranteed that the trigger file is deleted whether nextWALfile exists or not. A timeline history file is restored also at the beginning of recovery, so the accidentally remaining trigger file is deleted in early warm-standby as a side-effect of this idea. Here is the revised patch as above. I think we have gone off to an overly complicated solution. pg_standby shouldn't need to special-case history files, or know what order the server will ask for them. What's wrong with just this: (ignoring the missing fast option) --- a/contrib/pg_standby/pg_standby.c +++ b/contrib/pg_standby/pg_standby.c @@ -552,8 +552,6 @@ main(int argc, char **argv) break; case 't': /* Trigger file */ triggerPath = optarg; - if (CheckForExternalTrigger()) - exit(1); /* Normal exit, with non-zero */ break; case 'w': /* Max wait time */ maxwaittime = atoi(optarg); ie. only check and delete the trigger file once the server requests a file that doesn't exist. Thanks for the suggestion! I have three comments. 1) Though some users want to save the fast failover mode, this solution doesn't provide it. You think that that mode is unnecessary? 2) This solution would go wrong when there are the WAL files in pg_xlog; If pg_xlog of the primary server can be read at failover (it's not node failure but process failure case), the WAL files in it may be copied to pg_xlog of the standby server in order to prevent the transaction loss. On the other hand, we can copy such files to the archival storage instead of pg_xlog. In this case, your simple solution goes well, but recovery would unexpectedly end without the trigger file because those WAL files have the invalid record which means the end of WAL. I'd like to control when the standby will come up as a normal server. So, I think that pg_standby should cope with also the above situation which I described. What is your opinion? 3) This solution has a race condition; some transactions would be lost if WAL file is shipped from the primary server and the trigger file is created, while pg_standby is sleeping, because pg_standby checks previously whether a trigger file exists. 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] Warm Standby restore_command documentation
Andreas Pflug wrote: I've been following the thread with growing lack of understanding why this is so hardly discussed, and I went back to the documentation of what the restore_command should do ( http://www.postgresql.org/docs/8.3/static/warm-standby.html ) While the algorithm presented in the pseudocode isn't dealing too good with a situation where the trigger is set while the restore_command is sleeping (this should be handled better in a real implementation), the code says Restore all wal files. If no more wal files are present, stop restoring if the trigger is set; otherwise wait for a new wal file. Since pg_standby is meant as implementation of restore_command, it has to follow the directive stated above; *anything else is a bug*. pg_standby currently does *not* obey this directive, and has that documented, but a documented bug still is a bug. I think you're interpreting the chapter too strongly. The provided pseudo-code is just an example of a suitable restore_command, it doesn't say that pg_standby behaves exactly like that. I agree we should change the default behavior, though. -- 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] Warm Standby restore_command documentation
Heikki Linnakangas wrote: Andreas Pflug wrote: I've been following the thread with growing lack of understanding why this is so hardly discussed, and I went back to the documentation of what the restore_command should do ( http://www.postgresql.org/docs/8.3/static/warm-standby.html ) While the algorithm presented in the pseudocode isn't dealing too good with a situation where the trigger is set while the restore_command is sleeping (this should be handled better in a real implementation), the code says Restore all wal files. If no more wal files are present, stop restoring if the trigger is set; otherwise wait for a new wal file. Since pg_standby is meant as implementation of restore_command, it has to follow the directive stated above; *anything else is a bug*. pg_standby currently does *not* obey this directive, and has that documented, but a documented bug still is a bug. I think you're interpreting the chapter too strongly. The provided pseudo-code is just an example of a suitable restore_command, it doesn't say that pg_standby behaves exactly like that. After reading that chapter, I assumed that pg_standby actually does work like this, and skipped reading the pg_standby specific doc The pgsql doc tries hard to give best advice for common situations, especially for integrity and safety issues. IMHO it's best to have the warm-standby chapter as reference how things should work for typical use-cases. Regards, Andreas -- 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 for 8.5, transformationHook
2009/4/18 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2009/4/11 Tom Lane t...@sss.pgh.pa.us: No, I was complaining that a hook right there is useless and expensive. transformExpr() is executed multiple times per query, potentially a very large number of times per query; so even testing to see if a hook exists is not a negligible cost. I did some tests based on pgbench. The queries done by pgbench are completely trivial and do not stress parser performance. Even if they did (consider cases likw an IN with a few thousand list items), the parser is normally not a bottleneck compared to transaction overhead, network round trips, and pgbench itself. I though about different position of hook, but only in this place the hook is useful (because expressions are recursive). As I keep saying, a hook there is useless, at least by itself. You have no control over the grammar and no ability to modify what the rest of the system understands. The only application I can think of is to fool with the transformation of FuncCall nodes, which you could do in a much lower-overhead way by hooking into transformFuncCall. Even that seems pretty darn marginal for real-world problems. Hello I am sending modified patch - it hooking parser via transformFuncCall regards Pavel Stehule regards, tom lane transformHook.dif Description: Binary data -- 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 for 8.5, transformationHook
On Monday 20 April 2009 09:52:05 Pavel Stehule wrote: I don't believe so is possible to find other general solution. (or better I didn't find any other solution). Tom has true, transformationHook on expression is expensive. I thing, so hook on function should be simple and fast - not all transformation's should be simple defined via property - classic sample is decode like functions, it needs procedural code. I find this all a bit premature, given that you haven't clearly defined what sort of user-visible functionality you hope to end up implementing. Which makes it hard to argue why this or that approach might be better. -- 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] Re: [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.
Magnus Hagander mag...@hagander.net writes: Tom Lane wrote: What makes more sense to me is to add a table to encnames.c that provides the gettext name of every encoding that we support. Here's a patch that moves the table over to encnames.c, and renames it to look like the others. I think you forgot to include the NULL terminating entry that the loop seems to be expecting. Also, why isn't the array const? I don't know what it should be doing if it can't find a match, so I haven't changed that behavior. As things stand, it should throw error, except in the case of SQL_ASCII; there is no excuse for any other database encoding to not be in the table. However, what seems more worrisome to me is the prospect already discussed that the codeset name we have in the table is not actually recognized by gettext/iconv. Did we have a solution for that? Anyway, this fixes my immediate concern about where the info is located, so you may as well apply it with the array-terminator fix. 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] Patch for 8.5, transformationHook
Peter Eisentraut pete...@gmx.net writes: I find this all a bit premature, given that you haven't clearly defined what sort of user-visible functionality you hope to end up implementing. That sums up my reaction too --- this looks like a solution in search of a problem. The hook itself might be relatively harmless as long as it's not in a performance-critical place, but I think people would tend to contort their thinking to match what they can do with the hook rather than think about what an ideal solution might be. I'm also concerned that a hook like this is not usable unless there are clear conventions about how multiple shared libraries should hook into it simultaneously. The other hooks we have mostly aren't intended for purposes that might need concurrent users of the hook, but it's hard to argue that the case won't come up if this hook actually gets used. 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] New trigger option of pg_standby
Fujii Masao wrote: On Mon, Apr 20, 2009 at 6:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: What's wrong with just this: (ignoring the missing fast option) --- a/contrib/pg_standby/pg_standby.c +++ b/contrib/pg_standby/pg_standby.c @@ -552,8 +552,6 @@ main(int argc, char **argv) break; case 't': /* Trigger file */ triggerPath = optarg; - if (CheckForExternalTrigger()) - exit(1);/* Normal exit, with non-zero */ break; case 'w': /* Max wait time */ maxwaittime = atoi(optarg); ie. only check and delete the trigger file once the server requests a file that doesn't exist. Thanks for the suggestion! I have three comments. 1) Though some users want to save the fast failover mode, this solution doesn't provide it. You think that that mode is unnecessary? No I just left it out to keep it simple. The patch was only meant to make the point, I didn't intend it to be applied as is. 2) This solution would go wrong when there are the WAL files in pg_xlog; If pg_xlog of the primary server can be read at failover (it's not node failure but process failure case), the WAL files in it may be copied to pg_xlog of the standby server in order to prevent the transaction loss. On the other hand, we can copy such files to the archival storage instead of pg_xlog. In this case, your simple solution goes well, but recovery would unexpectedly end without the trigger file because those WAL files have the invalid record which means the end of WAL. I'd like to control when the standby will come up as a normal server. So, I think that pg_standby should cope with also the above situation which I described. What is your opinion? Hmm, the user manual instructs to copy any unarchived files directly into pg_xlog, but I guess you could copy them to the archive instead. At the end of archive recovery, the server always probes for the timeline by requesting history files until it fails to find one. That probing should remove the trigger file if it hasn't been removed by then. It's a bit coincidental to rely on that, but at least it's simple. The assumption we're making is that the server won't exit recovery before asking restore_command for a file that doesn't exist. 3) This solution has a race condition; some transactions would be lost if WAL file is shipped from the primary server and the trigger file is created, while pg_standby is sleeping, because pg_standby checks previously whether a trigger file exists. Yeah, it should sleep only after checking for the trigger file. That doesn't completely eliminate the race condition though: I think it should check again that the WAL file doesn't exist after reading the trigger file but before deleting it. -- 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
[HACKERS] springy migration of moths (buildfarm)
Just for information due to server lab reorganization we (Me Jorgen) are going to consolidate and transfer our Solaris'es buildfarms member (moth_*) to a another hardware (another lab). It should be finished during next couple of weeks. During this time, results could be confusing. Thanks for understanding. 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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
On Mon, Apr 20, 2009 at 3:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: It seems to me a quite ad-hoc idea. That's rather a silly charge to be leveling when your own proposal is such a horrid kluge as this one. As near as I can tell, you intend that SELinux will be unable to prohibit SELECT FOR UPDATE because it cannot tell the difference between that and a foreign key reference. If that isn't a hack, I don't know what is. I think we're talking at cross purposes here. I think Kai Gai's descriptions make sense if you start with a different set of assumptions. The idea behind SELinux is that each individual object is access controlled and each user has credentials which grant access to specific operations on specific objects. As I understand it part of the goal is to eliminate situations where setuid or other forms of privilege escalation is required. So in this situation -- I suspect, if any SELinux people want to pipe up to tell me whether I'm on the right track -- the idea is that you should be able to examine a user superficially and know for certain whether he has the ability to lock a record or whether that privilege has been denied him. It shouldn't be possible for him to gain the privilege by going through a view or trigger which runs as another user. If that's right then the previous suggestion that we take only the part of SELinux which maps to the existing POstgres model really falls down. SEPostgres won't map to the Postgres model just as SELinux doesn't map directly to the traditional Unix security model. It'll be a whole new security model which may be internally consistent but won't make sense to think about together with the Postgres model. That would be a hard sell because as demonstrated in this thread, we don't really understand the SE security model and it would probably be quite invasive to support. If on the other hand I'm wrong and this isn't a fundamental feature but just an implementation question then I think the right solution is to fix the problems that make it hard to implement the Postgres security model in SELinux. The consensus earlier was that the first version of the patch to land would just be a minimal patch which implements the existing security model using SELinux without making any changes to the model. Playing around with new privileges and how we distinguish referential integrity checks wouldn't be part of that. -- greg -- 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] Re: [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.
Tom Lane wrote: However, what seems more worrisome to me is the prospect already discussed that the codeset name we have in the table is not actually recognized by gettext/iconv. Did we have a solution for that? You get English. -- 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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
Greg Stark st...@enterprisedb.com writes: I think we're talking at cross purposes here. I think Kai Gai's descriptions make sense if you start with a different set of assumptions. The idea behind SELinux is that each individual object is access controlled and each user has credentials which grant access to specific operations on specific objects. As I understand it part of the goal is to eliminate situations where setuid or other forms of privilege escalation is required. Well, if so, the idea is a miserable failure. SELinux has just as many setuid programs as any other Unix, and absolutely zero hope of removing them. I am not going to take the idea of remove setuid seriously when they haven't been able to accomplish it anywhere else. 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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
Tom Lane t...@sss.pgh.pa.us writes: Greg Stark st...@enterprisedb.com writes: I think we're talking at cross purposes here. I think Kai Gai's descriptions make sense if you start with a different set of assumptions. The idea behind SELinux is that each individual object is access controlled and each user has credentials which grant access to specific operations on specific objects. As I understand it part of the goal is to eliminate situations where setuid or other forms of privilege escalation is required. Well, if so, the idea is a miserable failure. SELinux has just as many setuid programs as any other Unix, and absolutely zero hope of removing them. I am not going to take the idea of remove setuid seriously when they haven't been able to accomplish it anywhere else. But can you remove privileges from users to make these programs ineffective? So even if you obtain root privileges you're missing the SE privilege which the program expects to use? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] missing auto_explain contrib in 8.4beta1
Hi, sorry for sending this one to this list but i can't send it to pginstaller-de...@pgfoundry.org and don't know where else to send it The new auto_explain contrib is missing in the windows installer, is that intentional? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] missing auto_explain contrib in 8.4beta1
On Mon, Apr 20, 2009 at 4:48 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, sorry for sending this one to this list but i can't send it to pginstaller-de...@pgfoundry.org and don't know where else to send it The new auto_explain contrib is missing in the windows installer, is that intentional? It shouldn't be - I remember adding it. iirc, there's no SQL script for that one though, so no checkbox to select. -- Dave Page EnterpriseDB UK: 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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
On Mon, Apr 20, 2009 at 03:48:11PM +0100, Greg Stark wrote: So in this situation -- I suspect, if any SELinux people want to pipe up to tell me whether I'm on the right track -- the idea is that you should be able to examine a user superficially and know for certain whether he has the ability to lock a record or whether that privilege has been denied him. It shouldn't be possible for him to gain the privilege by going through a view or trigger which runs as another user. My (admittedly superficial) research into the topic suggests to me that it's because SELinux is entirely into protecting the data. It doesn't really care whether you're accessing it via a view or function or what. If you don't have permissions you can't get it and no-one within postgresql can grant you access either (that's why it's MAC). The way I understood the specific problem here is that SELECT FOR UPDATE doesn't semantically change any data so you don't really need UPDATE permissions to do it. That's just an artifact of the Postgres implementation. If on the other hand I'm wrong and this isn't a fundamental feature but just an implementation question then I think the right solution is to fix the problems that make it hard to implement the Postgres security model in SELinux. The consensus earlier was that the first version of the patch to land would just be a minimal patch which implements the existing security model using SELinux without making any changes to the model. Playing around with new privileges and how we distinguish referential integrity checks wouldn't be part of that. ISTM that limiting the patch to doing what can already be done with standard postgresql is silly. SE-Postgres is not trying to supplant the Pg model, it's trying to do things that the Pg model can't do. Namely, label stuff secret and be sure no-one without clearence can read it, even if someone makes a setuid function for it. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Patch for 8.5, transformationHook
2009/4/20 Peter Eisentraut pete...@gmx.net: On Monday 20 April 2009 09:52:05 Pavel Stehule wrote: I don't believe so is possible to find other general solution. (or better I didn't find any other solution). Tom has true, transformationHook on expression is expensive. I thing, so hook on function should be simple and fast - not all transformation's should be simple defined via property - classic sample is decode like functions, it needs procedural code. I find this all a bit premature, given that you haven't clearly defined what sort of user-visible functionality you hope to end up implementing. Which makes it hard to argue why this or that approach might be better. a) it allows procedural setting for parameter's transformation and checking like fce(int, varchar, int, varchar, ), fce(int, int, int, varchar, varchar, varchar) ... there should be hundred patterns b) it allows constructors for data types (ANSI SQL) datatype(typefield1[, typefiedl2[, typefiedl3[, ...]]]) returns type c) it allows named parameters with different syntax like Oracle fcecall(a = 10, b = 30), like Informix fcecall(a = 10, b = 30) d) with patch that allows named parameters with PostgreSQL syntax (value AS name) it allows smart parameters - name isn't name of variable, but label like SQL/XML xmlforest(user_name, user_name AS user name) I hope so this is enough :) Regards Pavel -- 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 for 8.5, transformationHook
2009/4/20 Tom Lane t...@sss.pgh.pa.us: Peter Eisentraut pete...@gmx.net writes: I find this all a bit premature, given that you haven't clearly defined what sort of user-visible functionality you hope to end up implementing. That sums up my reaction too --- this looks like a solution in search of a problem. The hook itself might be relatively harmless as long as it's not in a performance-critical place, but I think people would tend to contort their thinking to match what they can do with the hook rather than think about what an ideal solution might be. see mail to Peter, please I'm also concerned that a hook like this is not usable unless there are clear conventions about how multiple shared libraries should hook into it simultaneously. The other hooks we have mostly aren't intended for purposes that might need concurrent users of the hook, but it's hard to argue that the case won't come up if this hook actually gets used. I though about it. The first rule is probably - handler have to work as filter, and should be (if is possible) independent on order. It is very similar to triggers. regards Pavel Stehule 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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
KaiGai Kohei kai...@ak.jp.nec.com writes: Heikki Linnakangas wrote: Can't you have a SE-PostgreSQL policy like disallow ACL_UPDATE on table X for user Y, except when current user is owner of X? It seems to me a quite ad-hoc idea. That's rather a silly charge to be leveling when your own proposal is such a horrid kluge as this one. As near as I can tell, you intend that SELinux will be unable to prohibit SELECT FOR UPDATE because it cannot tell the difference between that and a foreign key reference. If that isn't a hack, I don't know what is. 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.4b1 regression?
On Apr 20, 2009, at 2:27 PM, Eric B. Ridge wrote: Some investigation showed that the use of non-IMMUTABLE PL/PGSQL functions as view columns, when these views are joined with other views, cause the query to be planned poorly. I'm sorry. I should have said VOLATILE functions. Which is the default if nothing is specified (and that's true for 8.1 and 8.4) eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4b1 regression?
I loaded a copy of a production database into PG 8.4b1 and immediately saw that all of our queries were significantly slower compared to v8.1. Some investigation showed that the use of non-IMMUTABLE PL/PGSQL functions as view columns, when these views are joined with other views, cause the query to be planned poorly. Attached are the two different plans. Literally, the only difference is changing the definition of the custom PL/PGSQL to be IMMUTABLE. I spent some time coming up with a reproduce-able schema, but it's almost 500k gzipped. Is that too big to attach to -hackers? The function in the test schema is simply: CREATE FUNCTION make_it_slow(id bigint) RETURNS text LANGUAGE plpgsql AS $$begin return 'non-immutable functions make it slow'; end;$$; In our case, the suspect functions *can* be declared IMMUTABLE, and we should have done that in the first place, but I thought it was worth mentioning that v8.1 did a much better job planning in this particular case. If my test schema will be beneficial, please let me know. Thanks! eric foo=# explain analyze select count(*) from c where tab_id = 2; QUERY PLAN Aggregate (cost=2014181.55..2014181.56 rows=1 width=0) (actual time=524.034..524.035 rows=1 loops=1) - Hash Join (cost=2316.92..2014180.53 rows=407 width=0) (actual time=436.223..524.026 rows=1 loops=1) Hash Cond: (item.id = folder.id) - Hash Join (cost=2311.61..2013055.97 rows=81398 width=1313) (actual time=51.783..508.441 rows=81398 loops=1) Hash Cond: (perms.owner_id = user.id) - Hash Join (cost=2310.45..5678.39 rows=81398 width=1281) (actual time=50.485..204.430 rows=81398 loops=1) Hash Cond: (perms.item_id = item.id) - Seq Scan on perms (cost=0.00..1332.98 rows=81398 width=17) (actual time=0.041..37.544 rows=81398 loops=1) Filter: (NOT deleted) - Hash (cost=1292.98..1292.98 rows=81398 width=1272) (actual time=50.389..50.389 rows=81398 loops=1) - Seq Scan on item (cost=0.00..1292.98 rows=81398 width=1272) (actual time=0.038..22.298 rows=81398 loops=1) - Hash (cost=1.07..1.07 rows=7 width=40) (actual time=0.017..0.017 rows=7 loops=1) - Seq Scan on user (cost=0.00..1.07 rows=7 width=40) (actual time=0.013..0.014 rows=7 loops=1) SubPlan 1 - Aggregate (cost=24.39..24.40 rows=1 width=0) (never executed) - Seq Scan on comments (cost=0.00..24.38 rows=3 width=0) (never executed) Filter: (read AND (item_id = $0)) - Hash (cost=5.29..5.29 rows=1 width=16) (actual time=0.157..0.157 rows=1 loops=1) - Nested Loop (cost=0.00..5.29 rows=1 width=16) (actual time=0.152..0.154 rows=1 loops=1) - Seq Scan on collection (cost=0.00..1.01 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1) Filter: (tab_id = 2) - Index Scan using folder_pkey on folder (cost=0.00..4.27 rows=1 width=8) (actual time=0.125..0.126 rows=1 loops=1) Index Cond: (folder.id = collection.id) Total runtime: 525.447 ms (24 rows) foo=# explain analyze select count(*) from c where tab_id = 2; QUERY PLAN - Aggregate (cost=14.15..14.16 rows=1 width=0) (actual time=0.070..0.071 rows=1 loops=1) - Nested Loop (cost=0.00..14.14 rows=1 width=0) (actual time=0.048..0.053 rows=1 loops=1) - Nested Loop (cost=0.00..9.86 rows=1 width=24) (actual time=0.042..0.045 rows=1 loops=1) - Nested Loop (cost=0.00..9.58 rows=1 width=32) (actual time=0.038..0.040 rows=1 loops=1) - Nested Loop (cost=0.00..5.30 rows=1 width=24) (actual time=0.030..0.032 rows=1 loops=1) - Seq Scan on collection (cost=0.00..1.01 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) Filter: (tab_id = 2) - Index Scan using perms_pkey on perms (cost=0.00..4.27 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=1) Index Cond: (perms.item_id = collection.id) Filter: (NOT perms.deleted) - Index Scan using item_pkey on item
Re: [HACKERS] Postgres SQL specification (tests)
On Apr 16, 10:52 am, mito milos.ors...@gmail.com wrote: By table structure i mean table definition options. ...which includes columns, right? Sorry, I don't think I can picture what you're trying to do. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GEQO: ERX
Hello, I was digging through the optimizer code and have a question regarding the edge recombination crossover (ERX) of the GEQO. It might be completely stupid and therefore I apologize for this in advance. As far as I understand it, the idea of the ERX is the minimization of edge failures. When reading in geqo_main.c and geqo_erx.c, it seams that every iterative round (generation) it is checked by gimme_tour() if there where any edge failures. When I understand the algorithm right, there should be no edge failures. Therefore I think about NOT checking for edge failures anymore, to save some time. (If it just to make sure, it could be done only once in the end.) Might that work or do I have some errors in my thoughts? Thanks in advance, Tobias -- 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] unalias of ACL_SELECT_FOR_UPDATE
On Mon, Apr 20, 2009 at 12:48 PM, Martijn van Oosterhout klep...@svana.org wrote: On Mon, Apr 20, 2009 at 03:48:11PM +0100, Greg Stark wrote: So in this situation -- I suspect, if any SELinux people want to pipe up to tell me whether I'm on the right track -- the idea is that you should be able to examine a user superficially and know for certain whether he has the ability to lock a record or whether that privilege has been denied him. It shouldn't be possible for him to gain the privilege by going through a view or trigger which runs as another user. My (admittedly superficial) research into the topic suggests to me that it's because SELinux is entirely into protecting the data. It doesn't really care whether you're accessing it via a view or function or what. If you don't have permissions you can't get it and no-one within postgresql can grant you access either (that's why it's MAC). The way I understood the specific problem here is that SELECT FOR UPDATE doesn't semantically change any data so you don't really need UPDATE permissions to do it. That's just an artifact of the Postgres implementation. If on the other hand I'm wrong and this isn't a fundamental feature but just an implementation question then I think the right solution is to fix the problems that make it hard to implement the Postgres security model in SELinux. The consensus earlier was that the first version of the patch to land would just be a minimal patch which implements the existing security model using SELinux without making any changes to the model. Playing around with new privileges and how we distinguish referential integrity checks wouldn't be part of that. ISTM that limiting the patch to doing what can already be done with standard postgresql is silly. SE-Postgres is not trying to supplant the Pg model, it's trying to do things that the Pg model can't do. Namely, label stuff secret and be sure no-one without clearence can read it, even if someone makes a setuid function for it. Not really, because SE-PostgreSQL introduces its own analogue of setuid/security definer, which happens to be called trusted procedure, and you can do the same darn thing. The issue at hand is foreign key constraints. Standard PostgreSQL checks those constraints as the table owner using the table owner's credentials. The question is whether there's some reason why SE-PostgreSQL shouldn't do the same. ...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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
Gregory Stark wrote: Tom Lane t...@sss.pgh.pa.us writes: Greg Stark st...@enterprisedb.com writes: I think we're talking at cross purposes here. I think Kai Gai's descriptions make sense if you start with a different set of assumptions. The idea behind SELinux is that each individual object is access controlled and each user has credentials which grant access to specific operations on specific objects. As I understand it part of the goal is to eliminate situations where setuid or other forms of privilege escalation is required. Well, if so, the idea is a miserable failure. SELinux has just as many setuid programs as any other Unix, and absolutely zero hope of removing them. I am not going to take the idea of remove setuid seriously when they haven't been able to accomplish it anywhere else. But can you remove privileges from users to make these programs ineffective? So even if you obtain root privileges you're missing the SE privilege which the program expects to use? It is also too radical goal for SELinux. :-) SELinux intends to prevent unexpected privilege escalation, but it does not mean to eliminate setuids. The unexpected means the actions are not explicitly allowed in the security policy. The SELinux privileges mechanism works orthogonally with DAC mechanism. If a user runs a root-setuid program, he can get full-controllable privileges in the DAC rules, but SELinux checks his privileges from different aspect to mask unallowed privilges due to the MAC rules. Thus, SELinux makes its decision based on only security contexts, independent from user identifier and other factors. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] [PATCH] unalias of ACL_SELECT_FOR_UPDATE
Robert Haas wrote: On Mon, Apr 20, 2009 at 12:48 PM, Martijn van Oosterhout klep...@svana.org wrote: On Mon, Apr 20, 2009 at 03:48:11PM +0100, Greg Stark wrote: So in this situation -- I suspect, if any SELinux people want to pipe up to tell me whether I'm on the right track -- the idea is that you should be able to examine a user superficially and know for certain whether he has the ability to lock a record or whether that privilege has been denied him. It shouldn't be possible for him to gain the privilege by going through a view or trigger which runs as another user. My (admittedly superficial) research into the topic suggests to me that it's because SELinux is entirely into protecting the data. It doesn't really care whether you're accessing it via a view or function or what. If you don't have permissions you can't get it and no-one within postgresql can grant you access either (that's why it's MAC). The way I understood the specific problem here is that SELECT FOR UPDATE doesn't semantically change any data so you don't really need UPDATE permissions to do it. That's just an artifact of the Postgres implementation. If on the other hand I'm wrong and this isn't a fundamental feature but just an implementation question then I think the right solution is to fix the problems that make it hard to implement the Postgres security model in SELinux. The consensus earlier was that the first version of the patch to land would just be a minimal patch which implements the existing security model using SELinux without making any changes to the model. Playing around with new privileges and how we distinguish referential integrity checks wouldn't be part of that. ISTM that limiting the patch to doing what can already be done with standard postgresql is silly. SE-Postgres is not trying to supplant the Pg model, it's trying to do things that the Pg model can't do. Namely, label stuff secret and be sure no-one without clearence can read it, even if someone makes a setuid function for it. Not really, because SE-PostgreSQL introduces its own analogue of setuid/security definer, which happens to be called trusted procedure, and you can do the same darn thing. The issue at hand is foreign key constraints. Standard PostgreSQL checks those constraints as the table owner using the table owner's credentials. The question is whether there's some reason why SE-PostgreSQL shouldn't do the same. It is an idea to be worth considering, I think. The current foreign-key implementation internally invokes secondary queries to check whether the given tuples satisfies the constraints, or not. SE-PostgreSQL checks any given queries and permissions on the required database objects, so the secondary queries are also checked. However, the way to achive foreign-key feature is purely depending on the implementation of DBMS, so we might be able to consider it as a part of system internal stuff. For example, if PostgreSQL implemented the foreign-key feature using hard-wired functions, we don't need to apply checks here because of it is not a query. One possible compromise is to skip SE- checks during foreign-key checks. I'll consider the idea a bit more. BTW, as we have discussed many times, SE-PostgreSQL does not intend to prevent unpriv users to infer existence of invisible tuples. So, this design changes will not be a headach for me. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers