Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Gevik Babakhani wrote: Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. I am sending this patch to check if I am on the right track. So please take a look at this if possible. Step 1: don't use c++ style comments like this: + //TODO: Check here C89 is basically our standard. gcc -std=c89 will check that it complies. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Noted. Thank you. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: Friday, November 02, 2007 4:19 PM To: Gevik Babakhani Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name. Gevik Babakhani wrote: Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. I am sending this patch to check if I am on the right track. So please take a look at this if possible. Step 1: don't use c++ style comments like this: + //TODO: Check here C89 is basically our standard. gcc -std=c89 will check that it complies. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] krb_match_realm
Henry B. Hotz wrote: On Nov 1, 2007, at 1:40 PM, Magnus Hagander wrote: Henry B. Hotz wrote: Thank you very much. This helps, but I'm still evaluating how much. I *can* point at one problem though: you do a strchr(gbuf.value, '@') and then error out if there isn't a Kerberos realm there. In fact that is exactly the default username of at least one of the GSSAPI implementations I've tested if the realm is the same as the local default realm. Eh, so how do we then determine the difference between local realm and no realm given? Well, what I've seen is: no realm given if and only if the default local realm matches the realm for the GSSAPI username. I don't think that's guaranteed. Irrk. Very much irrk. I'm not entirely sure what the intended semantics of krb_match_realm are, but if you're trying to match the GSSAPI-authenticated name against value_of(PGUSER)@value_of(krb_match_realm) then you need to construct that string, gss_import_name() it, and then gss_compare_name() the imported name with the authenticated name that GSSAPI already gave you. I know the API overhead of doing that is a PITA, but that's what's going to work. Why? Because if we're using the GSSAPI then we need to use the properties defined by the GSSAPI, and not depend on observed behavior of specific implementations of specific mechanisms. Otherwise things will be non-portable or unreliable in ways that may be non-obvious. In particular gss_display_name() produces a character string intended for display to a human being. It is *NOT* intended for access control. As another example, Heimdal gss_display_name() puts '\' escapes in front of special characters in the username. I don't think it's worth writing special case code for that either. Ok. I can see that point. However, if you have those characters in your username, you may have other problems as well :-) Is there some other way to actually get the username from gss? I mean, if we *didn't* get it from the startup packet, how would we ever be able to determine what user logged in? The standard defines two ways to do comparisons for access control. We should use one of them. Anything else is going to be more work and less reliable. What's the other way then? Last I checked there was no way to do case insensitive matching on gss_compare_name() but I could be on the wrong docs? Finding any kind of consistent docs for this stuff isn't exactly easy. Because we *must* have the ability to do case insensitive matching, or it *will* break on Windows. Well, it's not a high priority for me, but there is a GSSAPI mechanism called SPKM which uses X500-syle names (X509 certificate subject names to be precise). If we use gss_name_compare() properly then it should just work. I'm unsure if PostgreSQL in general is prepared to deal with such usernames. You'd certainly have to verify that stuff before anything would just work. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. I am sending this patch to check if I am on the right track. So please take a look at this if possible. What does this patch do? As discussed in thread: http://archives.postgresql.org/pgsql-hackers/2007-10/msg01490.php, this patch adds an additional parameter (char **argnames) to pg_parse_and_rewrite and pg_analyze_and_rewrite and ParseState. When transformColumnRef is about to report an error for a non existing column,a final match is performed to see if the non existing column is a parameter name. (argnames) If true, then a new node is created by transformParamRef NOTE: - This patch is created using MSVC++ ! - Nothing is done yet for polymorphic arguments. My test where: create table tbl1(id serial,field1 integer,field2 varchar); insert into tbl1 (field1,field2) values(11,''); insert into tbl1 (field1,field2) values(22,''); create or replace function func1(par1 integer,par2 integer,par3 varchar) returns setof record as $$ select par1::text, par2, par1+par2, par2+par1, par1+field1, (field1+par2)::varchar, par3, field2 || ' ' || par3 from tbl1; $$ language sql; select func1(2,4,''); select * from func1(5,16,'') as (a text ,b int ,c int, e int, f int,g varchar,h varchar,i text); results: (2,4,6,6,13,15,, ) (2,4,6,6,24,26,, ) And 5;16;21;21;16;27;; 5;16;21;21;27;38;; Regards, Gevik func-name-args-v0.1.patch Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Gevik Babakhani [EMAIL PROTECTED] writes: I am sending this patch to check if I am on the right track. So please take a look at this if possible. You seem not to have understood my recommendation to use a callback function. This patch might work nicely for SQL functions but there will be no good way to use it for plpgsql, or probably any other PL function language. If we're going to change the parser API then I'd like to have a more general solution. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. It can be more wide used: * named params in prepared statements * named params in SPI * .. Regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Gevik Babakhani [EMAIL PROTECTED] writes: what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. Please explain. Perhaps I am wrong, but plpgsql handles arsgument names before it passes the query to be executed. Which is actually the Wrong Thing to do: really the parameters should be seen as being in a name scope that's outside that of the query (and thus ambiguous names should be resolved first as column names of the query). The proposed patch does this in the right order and so I think that Pavel's concern is without foundation. One point here is that it would be good to be able to qualify the argument names with the function name, for example create function myfunc(x int) ... select ... from t where t.x = myfunc.x If t has a column named x then this will be the only way that the function parameter x can be referenced within that query. We are partway to that point with plpgsql but haven't bitten the bullet of changing the lookup order. Note that this consideration is another reason for having a callback function that's responsible for trying to resolve unresolved names. I certainly wouldn't like to have a notion of function name wired into the parser API, and if we did do that it still wouldn't be sufficient for plpgsql which can have multiple block-label namespaces accessible at once. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
On 02/11/2007, Gevik Babakhani [EMAIL PROTECTED] wrote: Hi, what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. Please explain. Perhaps I am wrong, but plpgsql handles arsgument names before it passes the query to be executed. Please see: plpgsql/pl_comp.c/do_compile(...)/line: 393 Regards, Gevik. it's one from mystic bugs: create table t(a integer, b integer); insert into t values(10,20); create function foo(a integer) returns integer as $$ select a from t where a b and a = 10; $$ languge sql; select foo(20); output? expected 10, but you will get NULL! Regards Pavel Stehule so some prefixes can help create function foo(a integer) returns integer as $$ select a from t where :a b and a = 10; $$ languge sql; Oracle use symbol ':' I don't know what others databases has. Regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hi, what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. Please explain. Perhaps I am wrong, but plpgsql handles arsgument names before it passes the query to be executed. Please see: plpgsql/pl_comp.c/do_compile(...)/line: 393 Regards, Gevik. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] krb_match_realm
On Nov 1, 2007, at 6:33 AM, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Also the elog message texts need a bit of copy-editing. Probably ;-) Got any specific hints, so I don't have to go through the iteration twice? The one that caught my eye was SSPI domain (%s) does and configured domain (%s) don't match, regards, tom lane s/does // I assume that's your point? The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] krb_match_realm
On Nov 2, 2007, at 8:38 AM, Magnus Hagander wrote: Henry B. Hotz wrote: On Nov 1, 2007, at 1:40 PM, Magnus Hagander wrote: Henry B. Hotz wrote: Thank you very much. This helps, but I'm still evaluating how much. I *can* point at one problem though: you do a strchr (gbuf.value, '@') and then error out if there isn't a Kerberos realm there. In fact that is exactly the default username of at least one of the GSSAPI implementations I've tested if the realm is the same as the local default realm. Eh, so how do we then determine the difference between local realm and no realm given? Well, what I've seen is: no realm given if and only if the default local realm matches the realm for the GSSAPI username. I don't think that's guaranteed. Irrk. Very much irrk. I'm not entirely sure what the intended semantics of krb_match_realm are, but if you're trying to match the GSSAPI-authenticated name against value_of(PGUSER)@value_of(krb_match_realm) then you need to construct that string, gss_import_name() it, and then gss_compare_name() the imported name with the authenticated name that GSSAPI already gave you. I know the API overhead of doing that is a PITA, but that's what's going to work. Why? Because if we're using the GSSAPI then we need to use the properties defined by the GSSAPI, and not depend on observed behavior of specific implementations of specific mechanisms. Otherwise things will be non-portable or unreliable in ways that may be non-obvious. In particular gss_display_name() produces a character string intended for display to a human being. It is *NOT* intended for access control. As another example, Heimdal gss_display_name() puts '\' escapes in front of special characters in the username. I don't think it's worth writing special case code for that either. Ok. I can see that point. However, if you have those characters in your username, you may have other problems as well :-) Yeah. Not many people put spaces inside usernames. Is there some other way to actually get the username from gss? I mean, if we *didn't* get it from the startup packet, how would we ever be able to determine what user logged in? gss_export_name(), but what it returns is supposed to be an opaque binary blob. It's guaranteed to produce a unique, canonicalized name based on the specific mechanism in use. It is suitable for memcmp(). The exported name will re-import. Section 3.10 of rfc 2744 describes all this, and appears to be clearer than the Sun document I pointed you at. Certainly it's more concise. YMMV. memcmp() on exported names will only be true if everyone uses the same gss mechanism. (OK, the only one we care about is kerberos.) In contrast it's possible that gss_compare_name() would say that uid=smith,ou=People,dc=example,dc=com is the same as [EMAIL PROTECTED] The standard defines two ways to do comparisons for access control. We should use one of them. Anything else is going to be more work and less reliable. What's the other way then? Last I checked there was no way to do case insensitive matching on gss_compare_name() but I could be on the wrong docs? Finding any kind of consistent docs for this stuff isn't exactly easy. Because we *must* have the ability to do case insensitive matching, or it *will* break on Windows. No gss_compare_name() is case sensitive. I think the way to do it is to know what case Microsoft is going to use and pre-map everything to that case (before you do a gss_import_name()). I *think* Microsoft will use upper case for the service name so we will need to change from postgres to POSTGRES as the default name in service principals. I've seen places where they may be using lower case realm names (which makes *NO* sense to me). Absent an environment where I can actually look at all these things, my only point of reference is mod_auth_kerb, and the issues reported with it. I know an upper case HTTP is needed to interoperate with windows clients. An upper case realm name seems to be OK, as is a lower case server name in the second component. The actual usernames seem to be lower case, but that's not the concern of the mod_auth_kerb developers since the deployer just needs to put in whatever matches. I assume in AD you can't create both smith and Smith, but can you create the latter at all? If you do, does AD remember the case for display purposes? Here at JPL usernames are lower case, and I don't think we allow anything special but hyphens in them, so I'm not likely to see a lot of the possible corner cases. I think you can upper case the service name, lower case the server name, upper case the realm name, and lower case the user name. If you can create Smith in AD and the user gets authenticated as [EMAIL PROTECTED] at the protocol level then that won't work though. I'm actually trying to write some Kerberos principal to X500
Re: [PATCHES] krb_match_realm
Henry B. Hotz wrote: On Nov 1, 2007, at 6:33 AM, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Also the elog message texts need a bit of copy-editing. Probably ;-) Got any specific hints, so I don't have to go through the iteration twice? The one that caught my eye was SSPI domain (%s) does and configured domain (%s) don't match, regards, tom lane s/does // I assume that's your point? Yup, thanks. I fixed that per Toms earlier comment. I'll get back to you on the other email tomorrow, it's becoming late friday evening here now :-) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
