[HACKERS] why declare arg as a array in FunctionCallInfoData structure
hi, When I read the postgresql codes, I noticed that the FunctionCallInfoData structure(declared in the src/include/fmgr.h) contains two arrays 'arg' and 'argnull'. Why don't you declare it as a pointer and allocate the memory from heap? It saves more momery if 'arg' and 'argnull' declares as pointer type. Can anyone explain it to me? Thanks in advance. -- 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] why declare arg as a array in FunctionCallInfoData structure
On Mon, Feb 02, 2009 at 03:16:01PM +0800, Tao Ma wrote: hi, When I read the postgresql codes, I noticed that the FunctionCallInfoData structure(declared in the src/include/fmgr.h) contains two arrays 'arg' and 'argnull'. Why don't you declare it as a pointer and allocate the memory from heap? It saves more momery if 'arg' and 'argnull' declares as pointer type. I imagaine it's because most of the time this structure would be allocated on the stack, where allocation is essentially free. Having to allocate two arrays from the heap would be slower. 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] why declare arg as a array in FunctionCallInfoData structure
2009/2/2 Tao Ma feng_e...@163.com: hi, When I read the postgresql codes, I noticed that the FunctionCallInfoData structure(declared in the src/include/fmgr.h) contains two arrays 'arg' and 'argnull'. Why don't you declare it as a pointer and allocate the memory from heap? It saves more momery if 'arg' and 'argnull' declares as pointer type. Can anyone explain it to me? It based on Datum data type, that store short fixed values directly (int, float), and you need second array, that carries info about NULL or NOT NULL. Bigger problem is some non consistency - sometime this is bool array, and sometime array of char. Regards Pavel Stehule Thanks in advance. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] PQinitSSL broken in some use casesf
Bruce Momjian wrote: Andrew Chernow wrote: I am using a library that links with and initializes libcrypto (ie. CRYPTO_set_locking_callback) but not SSL. This causes problems even when using PQinitSSL(FALSE) because things like SSL_library_init(); are not called (unless I manually call them, copy and paste code from fe-secure.c which may change). If libpq does init ssl, it overwrites (and breaks) the other library's crypto. Shouldn't crypto and ssl init be treated as two different things? If not, how does one determine a version portable way of initializing SSL in a manner required by libpq? Lots of apps using encryption but don't necessarily use ssl, so they need to know how to init ssl for libpq. I didn't realize they were could be initialized separately, so we really don't have an answer for you. This is the first time I have heard of this requirement. Just bringing it to everyones attention. I have no idea how common this use case is or if it deserves a patch. From your comments, it sounds uncommon. How we came across this: We have an internal library that links with libcrypto.so but not libssl.so. The library uses digests and ciphers from libcrypto. It initializes libcrypto for thread safety and seeds the PRNG. So, one of our applications is linking with both libpq and this library; which caused the conflict. How we worked around it: We solved it by copying the SSL init sequence from fe-secure.c. Doesn't seem like something that would change very often. So we init_our_library(), PQinitSSL(0) and then do a few lines of SSL init stuff. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] add_path optimization
well, true - but also, statically allocated table, without any predefined size (with #DEFINE) , and no boundary check - is bad as well. I suppose , this code is easy enough to let it be with your changes, but I would still call it not pretty. Well, it might merit a comment. Actually - if you did profile postgresql with bunch of queries, I wouldn't mind to see results of it - I don't know whether it makes sense to send that to the list (I would think it does), but if it is too big, or something - you could send it to me in private. What I'd really like to do is develop some tests based on a publicly available dataset. Any suggestions? ...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] parallel restore
Peter Eisentraut pete...@gmx.net writes: Andrew Dunstan wrote: I didn't know such a thing even existed. What causes it to happen? I agree it should be forbidden. It was the only way to switch users before we had SET SESSION AUTHORIZATION and SET ROLE and such. But the pg_restore man page says that -R/--no-reconnect is obsolete, so I'm not sure what the current behavior really is. Yeah, I think I was remembering ancient history. AFAICT we now never do a reconnect with anything but the originally specified username. I thought for a bit about stripping out the apparent flexibility to use other names, and making these low-level functions just consult ropt-username for themselves. But we might regret that someday. What's probably better is to have them notice whether the argument is ropt-username, and only attempt to cache the password if so. I'm almost done reviewing the patch, and will send along an updated version shortly. 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] parallel restore
I wrote: I'm almost done reviewing the patch, and will send along an updated version shortly. And here 'tis. I didn't commit because I have no way to test whether I broke the Windows code path. Please test, and commit if OK. There is an unfinished TODO item here: we really ought to make it work for tar-format archives. That's probably not hugely difficult, but I didn't look into it, and don't think we should hold up applying the existing patch for it. regards, tom lane binac1hcPdEZj.bin Description: parallel_restore_17.patch.gz -- 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] Space reservation v02
Gregory Stark píše v pá 30. 01. 2009 v 16:56 +: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Zdenek Kotala wrote: Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500: Well, I was thinking the new pg_class column would allow the upgrade to verify the pre-upgrade script was run properly, but a flat file works just as well if we assume we are going to pre-upgrade in one pass. Flat file or special table for pg_upgrade will work fine. Right, there's no difference in what you can achieve, whether you store the additional info in a flat file, special table or extra pg_class columns. If you can store something in pg_class, you can store it elsewhere just as well. Well having a column in pg_class does have some advantages. Like, you could look at the value from an sql session more easily. And if there are operations which we know are unsafe -- such as adding columns -- we could clear it from the server side easily. I think, For pg_upgrade script is more useful to have possibility to registry triggers on metadata change. It is general feature and after that you can do what you want. 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] How to get SE-PostgreSQL acceptable
Robert Haas wrote: IANAC, but that's my impression too. The simplified patch shouldn't assume that row-level security in its current form is going to end up getting put back in. AFAICS, there's no reason why the security ID for tables can't be a regular attribute in pg_class, or why the security attribute for columns can't be a regular attribute in pg_attribute. If it is identifier, it can be compoundable. I dislike it is held as text. It fundamentaly breaks SE-PostgreSQL's architecture, and requires to scrap near future. I think the column in pg_attribute and pg_class can and should be an OID. The issue is whether it's a regular OID column or a new system column. Stephen and I are saying it should be a regular column. pg_security can stick around to map OIDs to text labels. Why an OID? We store acl items now without a lookup table; I think there will be at most the same number of SE-Linux entries. Also, by using text we avoid the problem of cleaning out unreferenced pg_security rows, improve performance (no lookups), and simplify the code. -- 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] [PATCH] Space reservation v02
Heikki Linnakangas píše v so 31. 01. 2009 v 21:56 +0200: Robert Haas wrote: Ofcourse, the simplest way to me for handling type changes seems to be to keep the old type OID reserved and have the new version of the type with a new OID. Then the entire problem vanishes. But it was decided a long time ago not to do that. Why was that decision made? Suppose you have a type called widget and you decide it sucks and you want to reimplement it. So in release N+1, you rename the old type to old_shitty_widget and leave it with the same OID, add the new type under the name widget with a different oid, and document that old_shitty_widget should not be used. Then in release N+2 you remove old_shitty_widget altogether. Yeah, that works. The other approach is to convert the data types along with the new page format. That works too, and avoids having to keep around the old type and all that deprecation and stuff. I don't remember any hard decision on that, and we're not facing any data type changes in this release IIRC. It is something we should consider on a case-by-case basis when we get there. There might be reasons to do it like that, if for example the old format can't be converted to new format in a non-lossy fashion (e.g. float-timestamps - integer-timestamps). And authors of 3rd party data types are naturally free to do what they want. I think there is a confusion, because tuple change size when: 1) on disk structure like tupleheader, varlena, array header ... changed size or 2) datatype representation changed size. We discussed mostly #1 case. It maybe invoked meaning that ALTER TABLE is ignored. But it is not true. I agree with Heikki, data type conversion should be case-by-case and ALTER TABLE is also good solution. 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] LIMIT NULL
David E. Wheeler wrote: Howdy, Is it intentional that `LIMIT NULL` means the same as `LIMIT ALL`? If so, I'd like to submit a patch to document it, because I've found it useful in SQL functions: http://justatheory.com/computers/databases/postgresql/dynamic-limit.html Uh, I figure LIMIT NULL should return no rows. -- 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] LIMIT NULL
On Mon, Feb 2, 2009 at 12:58 PM, Bruce Momjian br...@momjian.us wrote: David E. Wheeler wrote: Howdy, Is it intentional that `LIMIT NULL` means the same as `LIMIT ALL`? If so, I'd like to submit a patch to document it, because I've found it useful in SQL functions: http://justatheory.com/computers/databases/postgresql/dynamic-limit.html Uh, I figure LIMIT NULL should return no rows. Wouldn't that be LIMIT 0? Per SQL spec, doesn't NULL refer to an unknown quantity? Making it mean ALL seems more useful than making it mean 0, since you can already write 0 if you want 0. ...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] LIMIT NULL
On Feb 2, 2009, at 9:58 AM, Bruce Momjian wrote: Is it intentional that `LIMIT NULL` means the same as `LIMIT ALL`? If so, I'd like to submit a patch to document it, because I've found it useful in SQL functions: http://justatheory.com/computers/databases/postgresql/dynamic-limit.html Uh, I figure LIMIT NULL should return no rows. Well, LIMIT 0 does that, and it's useful to have a type-compatible option to LIMIT that allows it to return all rows. And I say type- compatible, because you can't pass ALL as a bare string via a function like COALESCE. Best, David -- 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] LIMIT NULL
Bruce Momjian br...@momjian.us wrote: David E. Wheeler wrote: Is it intentional that `LIMIT NULL` means the same as `LIMIT ALL`? Uh, I figure LIMIT NULL should return no rows. Why? Since the normal meaning of NULL is value unknown or does not apply it would seem to be most reasonable, if it's going to be accepted in a LIMIT clause, to have it mean the LIMIT does not apply. Plus, if it has been accepted with the no limit semantics, wouldn't it require a really good reason to break backwards compatibility? -Kevin -- 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 buildfarm client code feature release
Tested and it looks good. See http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=gothic_mothbr=HEAD thanks Zdenek Andrew Dunstan píše v po 02. 02. 2009 v 11:34 -0500: Zdenek Kotala wrote: Andrew Dunstan píše v ne 01. 02. 2009 v 16:38 -0500: There is a new release of the buildfarm client code. It can be downloaded from http://pgfoundry.org/frs/download.php/2069/build-farm-3_2.tgz I installed it on ghotic_moth and it look likes that there is problem with other locales processing. See: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_mothdt=2009-02-02%2012:49:34 It says failure at stage InstallCheck, but there is not clue what locale is broken :(. And it does not contains log output. All this breakage should now be fixed. The web app is working correctly, and there is a hot fix in CVS for the client app: it can be downloaded from http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pgbuildfarm/client-code/run_build.pl?rev=1.100content-type=text/plain I'll put out a bug fix release when things settle down and we're sure we have most of them. 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] LIMIT NULL
Bruce Momjian br...@momjian.us writes: Uh, I figure LIMIT NULL should return no rows. It's worked the way it does now since 7.1, and no one has complained; in fact we've gotten bug reports when it was broken by the int8-limit patch. So there are people depending on the behavior. 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] parallel restore
Tom Lane wrote: I wrote: I'm almost done reviewing the patch, and will send along an updated version shortly. And here 'tis. Many many thanks. Your edits look very sensible, as always. I didn't commit because I have no way to test whether I broke the Windows code path. Please test, and commit if OK. Will do. There is an unfinished TODO item here: we really ought to make it work for tar-format archives. That's probably not hugely difficult, but I didn't look into it, and don't think we should hold up applying the existing patch for it. Right. Were you thinking this should be done for 8.4? 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] parallel restore
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: There is an unfinished TODO item here: we really ought to make it work for tar-format archives. That's probably not hugely difficult, but I didn't look into it, and don't think we should hold up applying the existing patch for it. Right. Were you thinking this should be done for 8.4? If you have time to look into it, sure. Otherwise we should just put it on the TODO list. 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 buildfarm client code feature release
Andrew Dunstan píše v ne 01. 02. 2009 v 16:38 -0500: There is a new release of the buildfarm client code. It can be downloaded from http://pgfoundry.org/frs/download.php/2069/build-farm-3_2.tgz I installed it on ghotic_moth and it look likes that there is problem with other locales processing. See: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_mothdt=2009-02-02%2012:49:34 It says failure at stage InstallCheck, but there is not clue what locale is broken :(. And it does not contains log output. 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] [NOVICE] LATIN2-UTF8 conversation with dblink
Ruzsinszky Attila ruzsinszky.att...@gmail.com writes: The situation: We've got two machines. The source database (DB) is running on an RHEL 5.x machine with PSQL 8.1.11. The destination DB is running on SuSE 11.0 with PSQL 8.3.x. The mechines are relative far away each other and there is a 2Mbps WAN line between them. The DB is the same except the character coding. Source is LATIN2 and the target DB is UTF8. We wrote a trigger to copy the data from source to target with dblink. The problem is the different DB character coding! PGSQL complains about wrong byte order. Hmm. You can presumably fix this by setting client_encoding in the dblink connection to match the encoding in use in the database it's called in. But I wonder why dblink doesn't just do that for you automatically. 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] LIMIT NULL
On Feb 2, 2009, at 10:17 AM, Tom Lane wrote: It's worked the way it does now since 7.1, and no one has complained; in fact we've gotten bug reports when it was broken by the int8-limit patch. So there are people depending on the behavior. Yeah, it's very useful. Here's a patch for the docs about it. Thanks, David limit-null.patch 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] [NOVICE] LATIN2-UTF8 conversation with dblink
On Feb 2, 2009, at 1:31 PM, Tom Lane wrote:
Ruzsinszky Attila ruzsinszky.att...@gmail.com writes:
The situation:
We've got two machines. The source database (DB) is running on an
RHEL
5.x machine
with PSQL 8.1.11. The destination DB is running on SuSE 11.0 with
PSQL 8.3.x.
The mechines are relative far away each other and there is a 2Mbps
WAN
line between them.
The DB is the same except the character coding. Source is LATIN2 and
the target DB is UTF8.
We wrote a trigger to copy the data from source to target with
dblink.
The problem is the
different DB character coding! PGSQL complains about wrong byte
order.
Hmm. You can presumably fix this by setting client_encoding in the
dblink connection to match the encoding in use in the database it's
called in. But I wonder why dblink doesn't just do that for you
automatically.
regards, tom lane
We did it like this:
INSERT INTO a tbl_datafeed
SELECT
nextval('acc_mkt.tbl_ants_to_ace_feed_row_id_seq'),
convert(project_number::bytea, 'WIN1258'::text, 'UTF8'::text),
convert(project_name::bytea, 'WIN1258'::text, 'UTF8'::text),
...
...
FROM dblink('dbname=mydbname host=removehost user=someuser','SELECT
* FROM tbl_datafeed') AS p
(
...
..
...
.)
Ries
--
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
To summarize where I think we are, release-wise: o Log streaming hold for 8.5 o Hot standby if committable for 8.4, fine, if not, 8.5, Heikki decides o SE-PostgreSQL no row-level security, if committable for 8.4, fine, if not, 8.5 o Others We will focus on all the other items on the commit fest page, and that will determine our time-line for 8.4 beta, i.e. the first three items will not delay our beta release. -- 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] parallel restore
Tom Lane wrote: And here 'tis. I didn't commit because I have no way to test whether I broke the Windows code path. Please test, and commit if OK. Tested and committed. Thanks to the people who reviewed and tested this - it was quite a difficult piece of work, much more difficult than I originally expected. 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] LIMIT NULL
David E. Wheeler da...@kineticode.com writes: On Feb 2, 2009, at 10:17 AM, Tom Lane wrote: It's worked the way it does now since 7.1, and no one has complained; in fact we've gotten bug reports when it was broken by the int8-limit patch. So there are people depending on the behavior. Yeah, it's very useful. Here's a patch for the docs about it. Seems to me that the SELECT reference page is a more appropriate place for this type of detail. I've applied a patch there. 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] LIMIT NULL
On Feb 2, 2009, at 12:43 PM, Tom Lane wrote: Yeah, it's very useful. Here's a patch for the docs about it. Seems to me that the SELECT reference page is a more appropriate place for this type of detail. I've applied a patch there. What about both? The LIMIT page is the first page I'd look for it, and the ALL note is there… Thanks, David -- 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] parallel restore
Andrew Dunstan wrote: Still, that's not a 100% solution because of the cases where we use reconnections to change user IDs --- the required password would (usually) vary. It might be sufficient to forbid that case with parallel restore, though; I think it's mostly a legacy thing anyway. I didn't know such a thing even existed. What causes it to happen? I agree it should be forbidden. It was the only way to switch users before we had SET SESSION AUTHORIZATION and SET ROLE and such. But the pg_restore man page says that -R/--no-reconnect is obsolete, so I'm not sure what the current behavior really is. -- 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] LIMIT NULL
David E. Wheeler da...@kineticode.com writes: On Feb 2, 2009, at 12:43 PM, Tom Lane wrote: Seems to me that the SELECT reference page is a more appropriate place for this type of detail. I've applied a patch there. What about both? We don't really have space to document every little niggling detail in two places; if we did that, the main docs would become unreadably dense. (I think it's justifiable to regard this as a niggling detail because no one's asked about it before.) 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] Compiler warning in ecpglib/execute.c
CVS HEAD is producing execute.c: In function 'ecpg_store_result': execute.c:394: warning: 'act_tuple' may be used uninitialized in this function It looks to me like this is an actual bug, not just the compiler being insufficiently smart to prove the variable is set before use. 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
Josh Berkus wrote: Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? I think we're just not going to work out the headache-inducing issues around row-level security in time for 8.4, and it seems to me that integrated system-level security labels at the table-and-column level are still very useful, even without row-level security. Sorry for the delay in answering, I'm currently on vacation (I haven't been able to catch up on this thread yet either, I'll try to a little later). The answer is yes, at least to get people started using it and make sure there are no practical issues with the security model sans row access control. But as I said earlier row based access control is going to be the most compelling part so hopefully the issues everyone is having can get worked out and the community will agree on the path forward, sooner rather than later. -- 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] add_path optimization
On 2 Feb 2009, at 14:50, Robert Haas wrote: well, true - but also, statically allocated table, without any predefined size (with #DEFINE) , and no boundary check - is bad as well. I suppose , this code is easy enough to let it be with your changes, but I would still call it not pretty. Well, it might merit a comment. :) What I'd really like to do is develop some tests based on a publicly available dataset. Any suggestions? I would say, it wouldn't hurt to do benchmarking/profiling regression tests on real hardware - but someone will have to generate quite substantial amount of data, so we could test it on small queries, up to 20+ join/sort/window function/aggregation queries, with various indexes, and data types. The more real the data, the better. I could make some of my stuff public - but without the lookup tables (id-some real data - like, names, surnames, mac addr, etc). -- 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] LIMIT NULL
On Feb 2, 2009, at 1:10 PM, Tom Lane wrote: David E. Wheeler da...@kineticode.com writes: On Feb 2, 2009, at 12:43 PM, Tom Lane wrote: Seems to me that the SELECT reference page is a more appropriate place for this type of detail. I've applied a patch there. What about both? We don't really have space to document every little niggling detail in two places; if we did that, the main docs would become unreadably dense. What, disk space? What do you mean by “space”? (I think it's justifiable to regard this as a niggling detail because no one's asked about it before.) Sure. Best, David -- 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 1/30/09 5:43 PM, Josh Berkus j...@agliodbs.com wrote: Joshua, Kohei-san, So, for 8.4: *if* we included in 8.4 a version of SEPostgres with all features *except* row-level security, would it still be useful to the SELinux community? Yes, it's definitely still useful. While many of the use cases we've wanted this for require row-level access control, there have been several that did not. It is definitely still useful, especially if it is a path toward row-level access control in a later release. Chad -- 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] LIMIT NULL
We don't really have space to document every little niggling detail in two places; if we did that, the main docs would become unreadably dense. What, disk space? What do you mean by space? Brain space. ...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] mingw check hung
Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: Hiroshi Inoue wrote: Eventually does the crash come from the call SetEnvironemntVariable (.., NULL) on mingw-XP(or older?)? I'm also interested in this issue and want to know the cause. The debugger shows that we actually fail on a popen() call in intdb. However, if we replace the calls to SetEnvironmentVariable(foo,NULL) with calls to SetEnvironmentVariable(foo,) then there is no failure. My theory is that on XP somehow the former is corrupting the environment such that when popen() tries to copy the environment for the new child process, it barfs. Well, XP only does it when it's built with mingw! Or is this actually dependent on if the binary is run under msys or cmd? Even weirder. It has now started working. For no apparent reason. I am seriously confused. This is just strange :S We could #ifdef out that thing on mingw, but I'm still worried that it will not work in all cases. I'd like to think there's a reason that thing was in there in the first place. Hmm. Actually, if I look at how things were before, I think we only called SetEnvironmentVariable() in case we set a variable, and never if we removed one. I'm not sure that's correct behavior, but it's apparently non-crashing behavior. Perhaps we need to restore that one? I'd be in favor of restoring it for both mingw and msvc in that case - that way we keep the platforms as close to each other as possible. Comments? //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] new buildfarm client code feature release
Zdenek Kotala wrote: Andrew Dunstan píše v ne 01. 02. 2009 v 16:38 -0500: There is a new release of the buildfarm client code. It can be downloaded from http://pgfoundry.org/frs/download.php/2069/build-farm-3_2.tgz I installed it on ghotic_moth and it look likes that there is problem with other locales processing. See: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_mothdt=2009-02-02%2012:49:34 It says failure at stage InstallCheck, but there is not clue what locale is broken :(. And it does not contains log output. All this breakage should now be fixed. The web app is working correctly, and there is a hot fix in CVS for the client app: it can be downloaded from http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pgbuildfarm/client-code/run_build.pl?rev=1.100content-type=text/plain I'll put out a bug fix release when things settle down and we're sure we have most of them. 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] why declare arg as a array in FunctionCallInfoData structure
Tao Ma feng_e...@163.com writes: When I read the postgresql codes, I noticed that the FunctionCallInfoData structure(declared in the src/include/fmgr.h) contains two arrays 'arg' and 'argnull'. Why don't you declare it as a pointer and allocate the memory from heap? Speed. We spend enough cycles in palloc already. 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] Psql List Languages
On Mon, Feb 02, 2009 at 11:29:51AM -0200, Fernando Ike wrote: Hi, On Fri, Jan 30, 2009 at 3:03 PM, Fernando Ike f...@midstorm.org wrote: Hi,, My job, I maintainer some postgres server for clients. We have many PL/(Java, Perl, Ruby, Python, R) and to more easy administration, I worked new little psql attribute to list languages com shorcurt/function \dL. [..] I know that this moment is inappropriate to submit patch, with the discussions about features for 8.4. But, if can added for commitfest to 8.5 version. I'm appreciate. I update patch for added gettext fields and change spaces/tab to 4 spaces. :) +1 for adding this :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Psql List Languages
Hi,
On Fri, Jan 30, 2009 at 3:03 PM, Fernando Ike f...@midstorm.org wrote:
Hi,,
My job, I maintainer some postgres server for clients. We have
many PL/(Java, Perl, Ruby, Python, R) and to more easy
administration, I worked new little psql attribute to list languages
com shorcurt/function \dL.
[..]
I know that this moment is inappropriate to submit patch, with the
discussions about features for 8.4. But, if can added for commitfest
to 8.5 version. I'm appreciate.
I update patch for added gettext fields and change spaces/tab to 4 spaces. :)
Cheers,
--
Fernando Ike
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***
*** 375,380 exec_command(const char *cmd,
--- 375,383
case 'l':
success = do_lo_list();
break;
+ case 'L':
+ success = listLanguages(pattern, show_verbose);
+ break;
case 'n':
success = listSchemas(pattern, show_verbose);
break;
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2018,2023 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
--- 2018,2085
return true;
}
+ /*
+ * \dL
+ *
+ * Describes Languages.
+ */
+ bool
+ listLanguages(const char *pattern, bool verbose)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(buf);
+
+ printfPQExpBuffer(buf,
+ SELECT l.lanname as \%s\,\n
+ pg_catalog.pg_get_userbyid(l.lanowner) as \%s\,\n
+ CASE WHEN l.lanispl = 't' THEN \'%s\' WHEN l.lanispl = 'f' THEN \'%s\' END AS \%s\,\n
+ CASE WHEN l.lanpltrusted='t' THEN \'%s\' WHEN lanpltrusted='f' THEN \'%s\' END AS \%s\,\n
+ CASE WHEN p.oid = 0 THEN NULL ELSE p.proname END AS \%s\,\n
+ CASE WHEN q.oid = 0 THEN NULL ELSE q.proname END AS \%s\\n,
+ gettext_noop(Name),
+ gettext_noop(Owner),
+ gettext_noop(Yes),
+ gettext_noop(No),
+ gettext_noop(Procedural Language),
+ gettext_noop(Trusted),
+ gettext_noop(Untrusted),
+ gettext_noop(Trusted),
+ gettext_noop(Call Handler),
+ gettext_noop(Validator));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(buf, ,\n);
+ printACLColumn(buf, l.lanacl);
+ }
+
+ appendPQExpBuffer(buf, FROM pg_catalog.pg_language l\n);
+ appendPQExpBuffer(buf, LEFT JOIN pg_catalog.pg_proc p on l.lanplcallfoid = p.oid\n);
+ appendPQExpBuffer(buf, LEFT JOIN pg_catalog.pg_proc q on l.lanvalidator = q.oid\n);
+
+ processSQLNamePattern(pset.db, buf, pattern, false, false,
+ NULL, l.lanname, NULL, NULL);
+
+ appendPQExpBuffer(buf, ORDER BY 1;);
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _(List of languages);
+ myopt.translate_header = true;
+
+ printQuery(res, myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+
+ }
/*
* \dD
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
***
*** 75,79 extern bool listForeignServers(const char *pattern, bool verbose);
--- 75,81
/* \deu */
extern bool listUserMappings(const char *pattern, bool verbose);
+ /* \dL */
+ extern bool listLanguages(const char *pattern, bool verbose);
#endif /* DESCRIBE_H */
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***
*** 215,220 slashUsage(unsigned short int pager)
--- 215,221
fprintf(output, _( \\dg [PATTERN]list roles (groups)\n));
fprintf(output, _( \\di[S+] [PATTERN]list indexes\n));
fprintf(output, _( \\dl list large objects, same as \\lo_list\n));
+ fprintf(output, _( \\dL list (procedural) languages\n));
fprintf(output, _( \\dn[+] [PATTERN]list schemas\n));
fprintf(output, _( \\do[S] [PATTERN]list operators\n));
fprintf(output, _( \\dp [PATTERN]list table, view, and sequence access privileges\n));
--
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] reloptions with a namespace
Euler Taveira de Oliveira wrote:
Alvaro Herrera escreveu:
New patch attached, with pg_dump support (thanks to Tom for the SQL
heads-up).
Great! We're close. Just two minor gripes:
+ char *validnsps[] = { toast };
Surely, you forgot to add a NULL at the end. Patch is attached.
Right, thanks.
IIRC, my last patch includes a partial validation code for RESET cases. For
example, the last SQL will not be atomic (invalid reloption silently ignored).
So, why not apply the namespace validation code to RESET case too? Patch is
attached too.
No, we must not validate the options passed to RESET, because we want to
be able to reset even options that we do not currently think that are
valid. Consider that we might be trying to clean up after options set
by a previous version of a module.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona (Carlos Duclós)
--
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] add_path optimization
On 1 Feb 2009, at 21:35, Robert Haas wrote: On Sun, Feb 1, 2009 at 3:25 PM, Grzegorz Jaskiewicz g...@pointblue.com.pl wrote: I don't like the fact that you hardcoded that here. I know that you are trying to pass on few calls in one go here, but still... ugly. Well, I think you'll find that using a dynamically sized data structure destroys the possibility of squeezing any additional performance out of this part of the code. The nice thing about fixed-size data structures is that they cost essentially nothing to stack-allocate; you just move the stack pointer and away you go. We should in fact be looking for MORE places where we can avoid the use of constructs like List, since the second-highest CPU hog in my tests was AllocSetAlloc(), beaten out only by add_path(). With this patch applied, AllocSetAlloc() moves up to first. well, true - but also, statically allocated table, without any predefined size (with #DEFINE) , and no boundary check - is bad as well. I suppose , this code is easy enough to let it be with your changes, but I would still call it not pretty. Hmm, well I didn't either, but there's this handy tool called gprof that you might want to try out. I wouldn't be wasting my time patching this part of the code if it didn't make a difference, and in fact if you do 10% of the amount of benchmarking that I did in the process of creating this patch, you will find that it in fact does make a difference. To be honest, I really didn't had a time to run it down with your patch and gprof. I believe that you did that already, hence your suggestion, right ? Actually - if you did profile postgresql with bunch of queries, I wouldn't mind to see results of it - I don't know whether it makes sense to send that to the list (I would think it does), but if it is too big, or something - you could send it to me in private. It's already static to that .c file, so the compiler likely will inline it. In fact, I suspect you will find that removing the static keyword from the implementation of that function in CVS HEAD is itself sufficient to produce a small but measurable slowdown in planning of large join trees, exactly because it will defeat inlining. that depends on many things, including whether optimizations are on or not. Because that function basically consists of two ifs essentially - it could easily be turned into two separate inlines/macros - that would remove any function's specific overhead (stack alloc, etc, etc). -- 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: Hmm. Actually, if I look at how things were before, I think we only called SetEnvironmentVariable() in case we set a variable, and never if we removed one. I'm not sure that's correct behavior, but it's apparently non-crashing behavior. Perhaps we need to restore that one? I'd be in favor of restoring it for both mingw and msvc in that case - that way we keep the platforms as close to each other as possible. Comments? works for me. 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] Controlling hot standby
On Fri, 2009-01-23 at 12:09 -0500, Robert Haas wrote: Could also be something like allow_connections_during_recovery. +1 (should we say continuous recovery?) Rather than a boolean, it seems more useful to specify a parameter that has some additional usefulness, if we are going to have one at all. max_recovery_connections = 0+ If you set it to 0 then we will turn off hot standby. Default value would be to set it to same value as max_connections, though can be overridden if specifically set. This then allows us to control the number of users who get access to the standby, which we might conceivably want to be smaller than max_connections because recovery takes resources also. It also means we don't have a specific name for this feature, we just say what we want: connections. The patch currently sets these parameters in recovery.conf. The above change would only work if set via postgresql.conf, since it must be read by the postmaster. So I would suggest that we put both max_recovery_connections and max_standby_delay into postgresql.conf, which then allows them both to be changed as recovery progresses. -- 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 buildfarm client code feature release
Andrew Dunstan wrote: Zdenek Kotala wrote: Andrew Dunstan píše v ne 01. 02. 2009 v 16:38 -0500: There is a new release of the buildfarm client code. It can be downloaded from http://pgfoundry.org/frs/download.php/2069/build-farm-3_2.tgz I installed it on ghotic_moth and it look likes that there is problem with other locales processing. See: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_mothdt=2009-02-02%2012:49:34 It says failure at stage InstallCheck, but there is not clue what locale is broken :(. And it does not contains log output. Darn. Ok, thanks. will check. I will fix the heading shortly, but you can now see all the log files - the problem was an over-aggressive query insertion filter that disallowed a dot in the stage name. See http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=gothic_mothdt=2009-02-02%20124934stg=install-check-cs_CZ.ISO8859-2 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] new buildfarm client code feature release
Zdenek Kotala wrote: Andrew Dunstan píše v ne 01. 02. 2009 v 16:38 -0500: There is a new release of the buildfarm client code. It can be downloaded from http://pgfoundry.org/frs/download.php/2069/build-farm-3_2.tgz I installed it on ghotic_moth and it look likes that there is problem with other locales processing. See: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_mothdt=2009-02-02%2012:49:34 It says failure at stage InstallCheck, but there is not clue what locale is broken :(. And it does not contains log output. Darn. Ok, thanks. will check. 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] How to get SE-PostgreSQL acceptable
Bruce Momjian wrote: Robert Haas wrote: IANAC, but that's my impression too. The simplified patch shouldn't assume that row-level security in its current form is going to end up getting put back in. AFAICS, there's no reason why the security ID for tables can't be a regular attribute in pg_class, or why the security attribute for columns can't be a regular attribute in pg_attribute. If it is identifier, it can be compoundable. I dislike it is held as text. It fundamentaly breaks SE-PostgreSQL's architecture, and requires to scrap near future. I think the column in pg_attribute and pg_class can and should be an OID. The issue is whether it's a regular OID column or a new system column. Stephen and I are saying it should be a regular column. pg_security can stick around to map OIDs to text labels. Why an OID? We store acl items now without a lookup table; I think there will be at most the same number of SE-Linux entries. Also, by using text we avoid the problem of cleaning out unreferenced pg_security rows, improve performance (no lookups), and simplify the code. The reason why I concern about text formed security context is it has variable length, so it requires to deform/form a HeapTuple again when SE-PostgreSQL assigns a default security context. If a user inserts a new tuple into pg_ without explicit security context, it has to be labeled based on security context. We cannot estimate what string will be given prior to ExecInsert(), it needs to put a security label on the given HeapTuple. If is is a fixed length variable (like oid), it is not necessary to deform/form them. So, I prefer the security identifier. In addition, it also has performance gain. The current architecture does not need to look up pg_security in most cases. SE-PostgreSQL caches results of access controls in userspace to reduce the number of kernel invocation. (In generally, context switch is a heavy one.) All cached entries are tagged by its security identifier, so we can lookup the entry without string comparing. The text form is used only when it could not find the entry on the cache. In this case, SE-PostgreSQL translate security identifier into text form and ask for in-kernel SELinux. It requires a text form due to the protocol. At least, we cannot apply this scheme on the next phase (row-level) due to the storage consumption and others. So, I don't think it is a preferable way to design the first step without ignoring upcoming expandability. 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] How to get SE-PostgreSQL acceptable
KaiGai Kohei wrote: Why an OID? We store acl items now without a lookup table; I think there will be at most the same number of SE-Linux entries. Also, by using text we avoid the problem of cleaning out unreferenced pg_security rows, improve performance (no lookups), and simplify the code. The reason why I concern about text formed security context is it has variable length, so it requires to deform/form a HeapTuple again when SE-PostgreSQL assigns a default security context. If a user inserts a new tuple into pg_ without explicit security context, it has to be labeled based on security context. We cannot estimate what string will be given prior to ExecInsert(), it needs to put a security label on the given HeapTuple. If is is a fixed length variable (like oid), it is not necessary to deform/form them. So, I prefer the security identifier. In addition, it also has performance gain. The current architecture does not need to look up pg_security in most cases. SE-PostgreSQL caches results of access controls in userspace to reduce the number of kernel invocation. (In generally, context switch is a heavy one.) All cached entries are tagged by its security identifier, so we can lookup the entry without string comparing. The text form is used only when it could not find the entry on the cache. In this case, SE-PostgreSQL translate security identifier into text form and ask for in-kernel SELinux. It requires a text form due to the protocol. That is an interesting optimization I had not thought of. At least, we cannot apply this scheme on the next phase (row-level) due to the storage consumption and others. So, I don't think it is a preferable way to design the first step without ignoring upcoming expandability. The big problem is that the security value on system tables controls the _object_ represented by the row, while on user tables the security value represents access to the row. That is just an odd design, and why a regular system table security value makes sense, independent of the row-level security feature. FYI, it is possible we might implement row-level security a different way in 8.5. -- 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] How to get SE-PostgreSQL acceptable
Why an OID? We store acl items now without a lookup table; I think there will be at most the same number of SE-Linux entries. Also, by using text we avoid the problem of cleaning out unreferenced pg_security rows, improve performance (no lookups), and simplify the code. In addition, it also has performance gain. The current architecture does not need to look up pg_security in most cases. SE-PostgreSQL caches results of access controls in userspace I think this is a very compelling point. ...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] add_path optimization
Robert Haas robertmh...@gmail.com wrote: running this 5 times each on several queries, dropping top and bottom results. Running a complex query (posted in previous threads, runs about 300,000 time per day in a production web application), I got these timings on a production quality machine (4 quad CPU chips, that is 16 CPUs like this: Intel(R) Xeon(R) CPU X7350 @ 2.93GHz, 128 GB RAM, big RAID with BBU). I ran explain in each environment 5 times, tossed high and low, and averaged. The 8.4devel was from today's (2008-02-02) snapshot, built the same way we did 8.3.5. 8.3.5, statistics target 10: 36.188 ms 8.4devel without patch, statistics target 100: 109.862 ms 8.4devel with patch, statistics target 100: 104.015 ms After seeing that, I re-analyzed to eliminate the statistics target as the cause of the 8.4 increase. 8.4devel with patch, statistics target 10: 99.421 ms -Kevin -- 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
Bruce Momjian wrote:
At least, we cannot apply this scheme on the next phase (row-level)
due to the storage consumption and others. So, I don't think it is
a preferable way to design the first step without ignoring upcoming
expandability.
The big problem is that the security value on system tables controls the
_object_ represented by the row, while on user tables the security value
represents access to the row. That is just an odd design, and why a
regular system table security value makes sense, independent of the
row-level security feature.
I don't think there is a fundamental differences between ALTER FUNCTION
and UPDATE pg_proc SET ..., for example. It is necessary to apply
same privileges in this case.
(In this case, db_procedure:{setattr} is checked on the object.)
The security label of system catalogs (like pg_class, pg_proc, ...) are
also used when the objects are used as target of user's request, like
a target of SELECT statement, a target of function invocation.
Please note that different permissions are checked in this case.
(db_table:{select} and db_procedure:{execute})
Sorry, it is a bit unclear what is a problem you pointed out.
I guessed you concerned about a tuple (within system catalogs) is handled
as an object when user tries to modify the system catalogs by hand.
However, I cannot understand why it is an odd design.
If we keep free to update system catalogs, it makes a bypassable
route to create/alter/drop objects.
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] More FOR UPDATE/FOR SHARE problems
Kevin Grittner wrote:
Tom Lane t...@sss.pgh.pa.us wrote:
Jeff Davis pg...@j-davis.com writes:
There you see a snapshot of the table that never existed. Either
the
snapshot was taken before the UPDATE, in which case i=3 should be
included, or it was taken after the UPDATE, in which case i=4 should
be
included. So atomicity is broken for WHERE.
This assertion is based on a misunderstanding of what FOR UPDATE in
read-committed mode is defined to do. It is supposed to give you
the
latest available rows.
Well, technically it's violating the Isolation part of ACID, not the
Atomicity, since the UPDATE transaction will either commit or roll
back in its entirety, but another transaction can see it in an
intermediate (partially applied) state.[1]
I guess the issue of whether this violation of ACID properties should
be considered a bug or a feature is a separate discussion, but calling
it a feature seems like a hard sell to me.
In trying to get some closure on this issue, I started investigating
this myself. I realize there is the issue with serializable isolation
level that is already documented:
http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE
Particularly, inserts by two transactions not seeing each other. OK, at
least it is documented.
There is also the problem of queries that add and remove rows from
SELECT FOR UPDATE sets:
http://archives.postgresql.org/pgsql-hackers/2009-01/msg01803.php
I have come up with a simpler example of that behavior:
S1:
test= CREATE TABLE mvcc_test (status BOOLEAN);
CREATE TABLE
test= INSERT INTO mvcc_test VALUES (true), (false);
INSERT 0 2
test= BEGIN;
BEGIN
test= UPDATE mvcc_test SET status = NOT status;
UPDATE 2
S2:
test= SELECT * FROM mvcc_test WHERE status = true FOR UPDATE;
S1:
test= COMMIT;
S2:
status
(0 rows)
As you can see, the S2 SELECT FOR UPDATE returns zero rows, even though
one row would be returned before the UPDATE, and one row after the
update, and at no time were no rows matching its criteria ('true').
So, I thought, this is another SELECT FOR UPDATE problem, but then I was
able to duplicate it with just UPDATEs:
S1:
test= CREATE TABLE mvcc_test (status BOOLEAN);
CREATE TABLE
test= INSERT INTO mvcc_test VALUES (true), (false);
INSERT 0 2
test= BEGIN;
BEGIN
test= UPDATE mvcc_test SET status = NOT status;
UPDATE 2
S2:
test= UPDATE mvcc_test SET status = true WHERE status = false;
UPDATE 0
S1:
test= COMMIT;
S2:
test= SELECT * FROM mvcc_test;
status
t
f
(2 rows)
If the S2 UPDATE was run before or after the S1 UPDATE, it would have
set both rows to true, while you can see the two rows are different.
What is significant about this is that it isn't a serializable failure,
nor is it a SELECT FOR UPDATE failure.
The fundamental behavior above is that the S1 transaction is adding
_and_ removing rows from the S2 query's result set; S2 is seeing the
pre-query values that don't match its criteria and ignoring them and
blocking on a later row that does match its criteria. Once S1 commits,
the new row does not match its criteria and it skips it, making the
SELECT FOR UPDATE return zero rows, and the S2 UPDATE do nothing.
Serializable mode does prevent the problem outlined above.
Is this behavior documented already? If not, where should I add it?
Perhaps section 13.2.1., Read Committed Isolation Level:
http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE
That section vaguely suggests this might happen but doesn't give an
example.
--
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] [PATCHES] GIN improvements
Teodor Sigaev teo...@sigaev.ru writes: I'm very sorry, but v0.24 has a silly bug with not initialized value :(. New version is attached I looked at this a little bit --- it needs proofreading (VACUUME?). Do we really need an additional column in pgstat table entries in order to store something that looks like it can be derived from the other columns? The stats tables are way too big already. Also, I really think it's a pretty bad idea to make index cost estimation depend on the current state of the index's pending list --- that state seems far too transient to base plan choices on. It's particularly got to be nuts to turn off indexscans entirely if the pending list is too full. Having some lossy pages might not be great but I don't believe it can be so bad that you should go to a seqscan all the time. 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] Review: B-Tree emulation for GIN
Looked at this a bit ... do you think it's really a good idea to remove the strategy number argument of comparePartial? The argument given in the docs for it is that it might be needed to determine when to end the scan, and that still seems plausible to me. The description of extractQuery's extra_data parameter seems confusing too. AFAICS it is incorrect, or at least misleading, to describe it as void ** extra_data[]; it is really void ***extra_data, because there is only one object there not an array. 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
Bruce Momjian wrote: KaiGai Kohei wrote: Why an OID? We store acl items now without a lookup table; I think there will be at most the same number of SE-Linux entries. Also, by using text we avoid the problem of cleaning out unreferenced pg_security rows, improve performance (no lookups), and simplify the code. The reason why I concern about text formed security context is it has variable length, so it requires to deform/form a HeapTuple again when SE-PostgreSQL assigns a default security context. If a user inserts a new tuple into pg_ without explicit security context, it has to be labeled based on security context. We cannot estimate what string will be given prior to ExecInsert(), it needs to put a security label on the given HeapTuple. If is is a fixed length variable (like oid), it is not necessary to deform/form them. So, I prefer the security identifier. In addition, it also has performance gain. The current architecture does not need to look up pg_security in most cases. SE-PostgreSQL caches results of access controls in userspace to reduce the number of kernel invocation. (In generally, context switch is a heavy one.) All cached entries are tagged by its security identifier, so we can lookup the entry without string comparing. The text form is used only when it could not find the entry on the cache. In this case, SE-PostgreSQL translate security identifier into text form and ask for in-kernel SELinux. It requires a text form due to the protocol. That is an interesting optimization I had not thought of. Just as an FYI, SELinux does this in general. There is an access vector cache in the kernel that caches the access computations, and there is also a userspace implementation in libselinux that most apps use. KaiGai reimplemented the AVC because he wanted it to work in a shm and be shared by multiple postgres processes. There is also a sidtab which is just a hashtable that maps string contexts to sids. The sidtab is filled at runtime and not persistent across boots, which means the contexts are generally stored as text on the persistent medium (like the xattr's on the filesystem). It doesn't matter from a security perspective whether the contexts are stored as strings or sids, its just an optimization you guys need to work out. At least, we cannot apply this scheme on the next phase (row-level) due to the storage consumption and others. So, I don't think it is a preferable way to design the first step without ignoring upcoming expandability. The big problem is that the security value on system tables controls the _object_ represented by the row, while on user tables the security value represents access to the row. That is just an odd design, and why a regular system table security value makes sense, independent of the row-level security feature. I may not be understanding this but I don't see why. In SELinux everything is an object, and all objects have contexts. No access is specified on the object or in the context, that is all done in the policy currently loaded in the security server. system tables and user tables shouldn't be treated differently implementation wise, they should just have a context and defer the decision making to the policy. In practice the system tables (and rows within the tables) would have a context that restricts access tightly, but this is up to the policy, not the implementation. FYI, it is possible we might implement row-level security a different way in 8.5. -- 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] add_path optimization
On Mon, Feb 2, 2009 at 8:10 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: running this 5 times each on several queries, dropping top and bottom results. Running a complex query (posted in previous threads, runs about 300,000 time per day in a production web application), I got these timings on a production quality machine (4 quad CPU chips, that is 16 CPUs like this: Intel(R) Xeon(R) CPU X7350 @ 2.93GHz, 128 GB RAM, big RAID with BBU). I ran explain in each environment 5 times, tossed high and low, and averaged. The 8.4devel was from today's (2008-02-02) snapshot, built the same way we did 8.3.5. 8.3.5, statistics target 10: 36.188 ms 8.4devel without patch, statistics target 100: 109.862 ms 8.4devel with patch, statistics target 100: 104.015 ms After seeing that, I re-analyzed to eliminate the statistics target as the cause of the 8.4 increase. 8.4devel with patch, statistics target 10: 99.421 ms Yikes! The impact of the patch is about what I'd expect, but the fact that planning time has nearly tripled is... way poor. Can you repost the query and the EXPLAIN output for 8.3.5 and CVS HEAD? ...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] [NOVICE] LATIN2-UTF8 conversation with dblink
Tom Lane wrote: Ruzsinszky Attila ruzsinszky.att...@gmail.com writes: The DB is the same except the character coding. Source is LATIN2 and the target DB is UTF8. We wrote a trigger to copy the data from source to target with dblink. The problem is the different DB character coding! PGSQL complains about wrong byte order. Hmm. You can presumably fix this by setting client_encoding in the dblink connection to match the encoding in use in the database it's called in. But I wonder why dblink doesn't just do that for you automatically. Mainly because I never thought about it myself before, and this is the first time I've seen someone complain ;-) But if you think automatically setting client encoding is appropriate, I will make the change. Would it be classified as a bug (and therefore something to backpatch) or a new feature? Joe -- 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 wrote: The big problem is that the security value on system tables controls the _object_ represented by the row, while on user tables the security value represents access to the row. That is just an odd design, and why a regular system table security value makes sense, independent of the row-level security feature. I may not be understanding this but I don't see why. In SELinux everything is an object, and all objects have contexts. No access is specified on the object or in the context, that is all done in the policy currently loaded in the security server. system tables and user tables shouldn't be treated differently implementation wise, they should just have a context and defer the decision making to the policy. In practice the system tables (and rows within the tables) would have a context that restricts access tightly, but this is up to the policy, not the implementation. FYI, it is possible we might implement row-level security a different way in 8.5. Seeing a pg_attribute row and seeing the column referenced by the row are not the same thing. Also, we are discussing system catalog values, (table, column, function), etc, so I don't see an performance issue. I haven't heard of anyone complaining about our ACL parsing overhead recently. A cache could still be used, but on the text string, not the oid. -- 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] More FOR UPDATE/FOR SHARE problems
Bruce Momjian wrote: The fundamental behavior above is that the S1 transaction is adding _and_ removing rows from the S2 query's result set; S2 is seeing the pre-query values that don't match its criteria and ignoring them and blocking on a later row that does match its criteria. Once S1 commits, the new row does not match its criteria and it skips it, making the SELECT FOR UPDATE return zero rows, and the S2 UPDATE do nothing. Serializable mode does prevent the problem outlined above. To clarify, serializable throws an error, as expected. -- 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] add_path optimization
Running a complex query (posted in previous threads, runs about 300,000 time per day in a production web application), I got these timings on a production quality machine (4 quad CPU chips, that is 16 CPUs like this: Intel(R) Xeon(R) CPU X7350 @ 2.93GHz, 128 GB RAM, big RAID with BBU). I ran explain in each environment 5 times, tossed high and low, and averaged. The 8.4devel was from today's (2008-02-02) snapshot, built the same way we did 8.3.5. 8.3.5, statistics target 10: 36.188 ms 8.4devel without patch, statistics target 100: 109.862 ms 8.4devel with patch, statistics target 100: 104.015 ms After seeing that, I re-analyzed to eliminate the statistics target as the cause of the 8.4 increase. 8.4devel with patch, statistics target 10: 99.421 ms Yikes! The impact of the patch is about what I'd expect, but the fact that planning time has nearly tripled is... way poor. Can you repost the query and the EXPLAIN output for 8.3.5 and CVS HEAD? FYI, I retested my queries on REL8_3_STABLE and the results were not all that different from CVS HEAD. So the problem is apparently specific to something your query is doing that mine isn't., rather than a general slowdown in planning (or else one of us goofed up the testing). ...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] add_path optimization
Robert Haas robertmh...@gmail.com writes: Yikes! The impact of the patch is about what I'd expect, but the fact that planning time has nearly tripled is... way poor. We're going to need to see the test case, because I don't see that in some simple tests here. 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-Level Privileges
Tom, all,
In the attached patch-
* Tom Lane (t...@sss.pgh.pa.us) wrote:
* Some of the information_schema views are specified to respond to
per-column privileges; the column_privileges and columns views
certainly need work now to meet spec, and there might be others.
Done.
* It might be appropriate to let the pg_stats view expose stats for
columns you have select privilege for, even if you haven't got it
across the whole table.
Done.
* We probably ought to invent has_column_privilege SQL functions
analogous to has_table_privilege; this is not just for completeness,
but is probably necessary to finish the above items.
Done.
* ISTM that COPY with a column list should succeed if you have
SELECT or INSERT privilege on just the mentioned columns.
Done.
* Perhaps it would be appropriate to let LOCK TABLE succeed if you have
proper permissions on at least one column of the table. However, it's
bad enough that LOCK TABLE examines permissions before locking the table
now; I don't think it ought to be grovelling through the columns without
lock. So this might be a place to leave well enough alone.
Left alone.
Thanks,
Stephen
Index: src/backend/catalog/information_schema.sql
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v
retrieving revision 1.50
diff -c -r1.50 information_schema.sql
*** src/backend/catalog/information_schema.sql 20 Jan 2009 09:10:20 - 1.50
--- src/backend/catalog/information_schema.sql 3 Feb 2009 03:17:26 -
***
*** 507,523
UNION ALL
SELECT 0::oid, 'PUBLIC'
) AS grantee (oid, rolname),
! (SELECT 'SELECT' UNION ALL
! SELECT 'INSERT' UNION ALL
! SELECT 'UPDATE' UNION ALL
! SELECT 'REFERENCES') AS pr (type)
WHERE a.attrelid = c.oid
AND c.relnamespace = nc.oid
AND a.attnum 0
AND NOT a.attisdropped
AND c.relkind IN ('r', 'v')
! AND aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
AND (pg_has_role(u_grantor.oid, 'USAGE')
OR pg_has_role(grantee.oid, 'USAGE')
--- 507,523
UNION ALL
SELECT 0::oid, 'PUBLIC'
) AS grantee (oid, rolname),
! (VALUES ('SELECT'),
! ('INSERT'),
! ('UPDATE'),
! ('REFERENCES')) AS pr (type)
WHERE a.attrelid = c.oid
AND c.relnamespace = nc.oid
AND a.attnum 0
AND NOT a.attisdropped
AND c.relkind IN ('r', 'v')
! AND aclcontains(a.attacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
AND (pg_has_role(u_grantor.oid, 'USAGE')
OR pg_has_role(grantee.oid, 'USAGE')
***
*** 677,683
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
!OR has_table_privilege(c.oid, 'REFERENCES') );
GRANT SELECT ON columns TO PUBLIC;
--- 677,687
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
!OR has_table_privilege(c.oid, 'REFERENCES')
!OR has_column_privilege(c.oid, a.attnum, 'SELECT')
!OR has_column_privilege(c.oid, a.attnum, 'INSERT')
!OR has_column_privilege(c.oid, a.attnum, 'UPDATE')
!OR has_column_privilege(c.oid, a.attnum, 'REFERENCES'));
GRANT SELECT ON columns TO PUBLIC;
Index: src/backend/catalog/system_views.sql
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.58
diff -c -r1.58 system_views.sql
*** src/backend/catalog/system_views.sql 1 Jan 2009 17:23:37 - 1.58
--- src/backend/catalog/system_views.sql 3 Feb 2009 03:17:26 -
***
*** 137,143
FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
! WHERE has_table_privilege(c.oid, 'select');
REVOKE ALL on pg_statistic FROM public;
--- 137,144
FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
! WHERE has_table_privilege(c.oid, 'select') OR
! has_column_privilege(c.oid, a.attnum, 'select');
REVOKE ALL on pg_statistic FROM public;
Index: src/backend/commands/copy.c
Re: [HACKERS] add_path optimization
* Tom Lane (t...@sss.pgh.pa.us) wrote: Robert Haas robertmh...@gmail.com writes: Yikes! The impact of the patch is about what I'd expect, but the fact that planning time has nearly tripled is... way poor. We're going to need to see the test case, because I don't see that in some simple tests here. A good data set, plus complex queries against it, might be the data from the US Census, specifically the TIGER data and the TIGER geocoder. I've been following this thread with the intention of putting together a large-data test set, but I just havn't found the time to yet. Right now there's alot of dependencies on PostGIS (which aren't really required to just do the queries to pull out the street segment) which I figure people would want ripped out. It'd also be nice to include the other Census data besides just the road data. If people really are interested, I'll see what I can put together. It's *alot* of data (around 23G total in PG), though perhaps just doing 1 state would be enough for a good test, I keep the states split up anyway using CHECK constraints. Don't think that would change this case, though there might be cases where it does affect things.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] add_path optimization
A good data set, plus complex queries against it, might be the data from the US Census, specifically the TIGER data and the TIGER geocoder. I've been following this thread with the intention of putting together a large-data test set, but I just havn't found the time to yet. Right now there's alot of dependencies on PostGIS (which aren't really required to just do the queries to pull out the street segment) which I figure people would want ripped out. It'd also be nice to include the other Census data besides just the road data. If people really are interested, I'll see what I can put together. It's *alot* of data (around 23G total in PG), though perhaps just doing 1 state would be enough for a good test, I keep the states split up anyway using CHECK constraints. Don't think that would change this case, though there might be cases where it does affect things.. I'm interested, but I need maybe a 1GB data set, or smaller. The thing that we are benchmarking is the planner, and planning times are related to the complexity of the database and the accompanying queries, not the raw volume of data. (It's not size that matters, it's how you use it?) In fact, in a large database, one could argue that there is less reason to care about the planner, because the execution time will dominate anyway. I'm interested in complex queries in web/OLTP type applications, where you need the query to be planned and executed in 400 ms at the outside (and preferably less than half of that). ...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] add_path optimization
* Robert Haas (robertmh...@gmail.com) wrote: I'm interested, but I need maybe a 1GB data set, or smaller. The thing that we are benchmarking is the planner, and planning times are related to the complexity of the database and the accompanying queries, not the raw volume of data. (It's not size that matters, it's how you use it?) In fact, in a large database, one could argue that there is less reason to care about the planner, because the execution time will dominate anyway. I'm interested in complex queries in web/OLTP type applications, where you need the query to be planned and executed in 400 ms at the outside (and preferably less than half of that). We prefer that our geocoding be fast... :) Doing 1 state should give you about the right size (half to 1G of data). I'll try to put together a good test set this week. Stephen signature.asc Description: Digital signature
Re: [HACKERS] add_path optimization
Robert Haas robertmh...@gmail.com writes: I'm interested, but I need maybe a 1GB data set, or smaller. The thing that we are benchmarking is the planner, and planning times are related to the complexity of the database and the accompanying queries, not the raw volume of data. In fact, the only reason to care whether there is any data in the DB *at all* is that you need some realistic content in pg_statistic. So it should be possible to set up a planner test DB with very little data bulk, which would surely make testing a lot less painful. 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
Bruce Momjian wrote:
Joshua Brindle wrote:
The big problem is that the security value on system tables controls the
_object_ represented by the row, while on user tables the security value
represents access to the row. That is just an odd design, and why a
regular system table security value makes sense, independent of the
row-level security feature.
I may not be understanding this but I don't see why. In SELinux everything is an
object, and all objects have contexts. No access is specified on the object or
in the context, that is all done in the policy currently loaded in the security
server. system tables and user tables shouldn't be treated differently
implementation wise, they should just have a context and defer the decision
making to the policy.
In practice the system tables (and rows within the tables) would have a context
that restricts access tightly, but this is up to the policy, not the implementation.
FYI, it is possible we might implement row-level security a different
way in 8.5.
Seeing a pg_attribute row and seeing the column referenced by the row
are not the same thing.
Yes, it is quite different. It seems to me we are now confusing.
Are you saying:
a) SELECT attname FROM pg_attribute where attrelid='t'::regclass and
attname='a';
and
b) SELECT a FROM t;
are different, aren't you?
Yes, it is not same thing.
For the query a), SE-PostgreSQL should check db_column:{getattr} permission
on the selected tuples, when row-level security is available.
In this case, it also checks db_column:{select} permission on the
attname column and db_table:{select} on the pg_attribute table.
For the query b), SE-PostgreSQL checks db_column:{select} permission on
the column a, and it also checks db_table:{select} on the table t.
And db_tuple:{select} permission when row-level security is available.
Please note that it checks db_column:* class permission on tuples within
pg_attribute system catalog, although db_tuple:* class ones are applied
on user defined tables.
When it checks permission of column, for example, it requires a label
assigned to the target object. In this case, an object is a row within
pg_attribute system catalog. It needs to be labeled as a column.
Thus, we have to add a field to hold its security label within pg_attribute
system catalog.
My concern is INSERT/UPDATE/DELETE these system catalogs by hand.
When user tries to insert a tuple without explicit security context,
it is necessary to be labeled as default one.
But, if it has variable length form, we have to deform the given
HeapTuple once then form HeapTuple again with text variable.
If it has fixed length oid, we can put it directly, as oid doing
at heap_insert() or heap_update().
Also, we are discussing system catalog values, (table, column,
function), etc, so I don't see an performance issue. I haven't heard of
anyone complaining about our ACL parsing overhead recently. A cache
could still be used, but on the text string, not the oid.
Yes, performance is not the first issue here.
The variable length type makes hard to assign a newly inserted tuple
(into pg_class, etc...) a default security context.
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] How to get SE-PostgreSQL acceptable
If we add a field on pg_ to store security label in text form,
it is necessary to attach a default one at the following points.
* pg_class
- InsertPgClassTuple() at heap.c
* pg_attribute
- InsertPgAttributeTuple() at heap.c
* pg_proc
- ProcedureCreate() at pg_proc.c
* pg_database
- createdb() at dbcommands.c
* for whole of them
- InsertOneTuple() at bootstrap.c
- ExecInsert() at execMain.c
The reason why I prefer security identifier (oid) is that we can put
a hook to assign a default security context inside the simple_heap_insert().
But, if above functions are the all to insert a new tuple into these
issued relations, it may be a reasonable approach for those four system
catalogs. Please point out if I overlooks somewhere.
At the previous message, I noted I'll submit revised patches on thie
Wednesday, but it become impossible due to the change. (T-T)
Please wait for a while.
KaiGai Kohei wrote:
Bruce Momjian wrote:
Joshua Brindle wrote:
The big problem is that the security value on system tables controls
the
_object_ represented by the row, while on user tables the security
value
represents access to the row. That is just an odd design, and why a
regular system table security value makes sense, independent of the
row-level security feature.
I may not be understanding this but I don't see why. In SELinux
everything is an object, and all objects have contexts. No access is
specified on the object or in the context, that is all done in the
policy currently loaded in the security server. system tables and
user tables shouldn't be treated differently implementation wise,
they should just have a context and defer the decision making to the
policy.
In practice the system tables (and rows within the tables) would have
a context that restricts access tightly, but this is up to the
policy, not the implementation.
FYI, it is possible we might implement row-level security a different
way in 8.5.
Seeing a pg_attribute row and seeing the column referenced by the row
are not the same thing.
Yes, it is quite different. It seems to me we are now confusing.
Are you saying:
a) SELECT attname FROM pg_attribute where attrelid='t'::regclass and
attname='a';
and
b) SELECT a FROM t;
are different, aren't you?
Yes, it is not same thing.
For the query a), SE-PostgreSQL should check db_column:{getattr} permission
on the selected tuples, when row-level security is available.
In this case, it also checks db_column:{select} permission on the
attname column and db_table:{select} on the pg_attribute table.
For the query b), SE-PostgreSQL checks db_column:{select} permission on
the column a, and it also checks db_table:{select} on the table t.
And db_tuple:{select} permission when row-level security is available.
Please note that it checks db_column:* class permission on tuples within
pg_attribute system catalog, although db_tuple:* class ones are applied
on user defined tables.
When it checks permission of column, for example, it requires a label
assigned to the target object. In this case, an object is a row within
pg_attribute system catalog. It needs to be labeled as a column.
Thus, we have to add a field to hold its security label within pg_attribute
system catalog.
My concern is INSERT/UPDATE/DELETE these system catalogs by hand.
When user tries to insert a tuple without explicit security context,
it is necessary to be labeled as default one.
But, if it has variable length form, we have to deform the given
HeapTuple once then form HeapTuple again with text variable.
If it has fixed length oid, we can put it directly, as oid doing
at heap_insert() or heap_update().
Also, we are discussing system catalog values, (table, column,
function), etc, so I don't see an performance issue. I haven't heard of
anyone complaining about our ACL parsing overhead recently. A cache
could still be used, but on the text string, not the oid.
Yes, performance is not the first issue here.
The variable length type makes hard to assign a newly inserted tuple
(into pg_class, etc...) a default security context.
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
