Thread: How can I pass an array to SPI_execute_with_args()?
Hi, I would like to execute the code below. I SELECTed a bigint[] from the database into "Datum ids", I need to insert a new bigint ID in the middle. Datum ids; int n_ids; int idx_min, idx_max, idx_mid; ArrayType *ids_arr; Datum *ids_data; ArrayType *array_prefix, *array_postfix; ... ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull); n_ids = DatumGetInt32(DirectFunctionCall2(array_length, ids, Int32GetDatum(1))); ids_arr = DatumGetArrayTypeP(ids); ids_data = (Datum *) ARR_DATA_PTR(ids_arr); ... At this point it's already ensured that 0 < idx_min < n_ids - 1, idx_min is the index in the array where I need to split: get_typlenbyvalalign(INT8OID, &typlen, &typbyval, &typalign); /* Split the array and UPDATE */ /* ids[0 ... idx_min - 1] || new_id || ids[idx_min ... n_ids - 1] */ array_prefix = construct_array(ids_data, idx_min, INT8OID, typlen, typbyval, typalign); array_postfix = construct_array(&ids_data[idx_min], n_ids - idx_min, INT8OID, typlen, typbyval, typalign); oids[0] = ANYARRAYOID; values[0] = PointerGetDatum(array_prefix); nulls[0] = false; >>>>>>>> oids[1] = INT8OID; /* ANYELEMENTOID; */ values[1] = id; /* really an int8 Datum */ nulls[1] = false; oids[2] = ANYARRAYOID; values[2] = PointerGetDatum(array_postfix); nulls[2] = false; oids[3] = TEXTOID; values[3] = lex; nulls[3] = false; ret = SPI_execute_with_args( "UPDATE product.t_product_inv SET ids = array_append($1, $2) || $3 WHERE word = $4", 4, oids, values, nulls, false, 1); If the above marked line sets oids[1] = INT8OID, I get this error: ERROR: function array_append(anyarray, bigint) does not exist LINE 1: UPDATE product.t_product_inv SET ids = array_append($1, $2) ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: UPDATE product.t_product_inv SET ids = array_append($1, $2) || $3 WHERE word = $4 If I use ANYELEMENTOID there, I get this error: ERROR: argument declared "anyarray" is not an array but type anyarray CONTEXT: SQL statement "UPDATE product.t_product_inv SET ids = array_append($1, $2) || $3 WHERE word = $4" I am stuck here. Can someone help me? Thanks in advance, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/
Boszormenyi Zoltan <zb@cybertec.at> writes: > I would like to execute the code below. Since you're apparently deconstructing and reconstructing the array anyway, why don't you do the insertion at the C-array stage, and do just one construct_array() that yields the final result? > If the above marked line sets oids[1] = INT8OID, I get this error: > ERROR: function array_append(anyarray, bigint) does not exist > LINE 1: UPDATE product.t_product_inv SET ids = array_append($1, $2) ... > ^ You'd need to use the types int8[] and int8, explicitly. No ANYARRAY. regards, tom lane
Tom Lane írta: > Boszormenyi Zoltan <zb@cybertec.at> writes: > >> I would like to execute the code below. >> > > Since you're apparently deconstructing and reconstructing the array > anyway, why don't you do the insertion at the C-array stage, and > do just one construct_array() that yields the final result? > Okay, that's a good idea. :-) >> If the above marked line sets oids[1] = INT8OID, I get this error: >> > > >> ERROR: function array_append(anyarray, bigint) does not exist >> LINE 1: UPDATE product.t_product_inv SET ids = array_append($1, $2) ... >> ^ >> > > You'd need to use the types int8[] and int8, explicitly. No ANYARRAY. > OK, so the OID for int8[] is (looking at pg_type.h...) 1016, i.e. for type "_int8"? It worked this way. Thank you very much. A question, though: why are there symbolic names for certain array types (FLOAT4ARRAYOID, etc) but not for the most? Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/
Boszormenyi Zoltan <zb@cybertec.at> writes: > A question, though: why are there symbolic names for certain > array types (FLOAT4ARRAYOID, etc) but not for the most? It's just historical ... we've only bothered to add #defines for the array types that are explicitly referenced somewhere in the core code. regards, tom lane
Hello again ... I'm trying to Authenticate to some PostgreSQL using MD5. I can get the salt, for example: A7 2E DB 2F Docs: AuthenticationMD5Password (B) ---------------------------------------------------------------------------------------- - Byte1(’R’) Identifies the message as an authentication request. - Int32(12) Length of message contents in bytes, including self. - Int32(5) Specifies that an MD5-encrypted password is required. - Byte4 The salt to use when encrypting the password. I'm not sure if I have to use 4 bytes (A7 2E DB 2F) or only the fourth byte (2F) And, where I have to add the salt, before the password or after the password ? Despite I've been trying all possibilities, but postgresql answers with a FATAL error ... :-) I'm sure my MD5 rutines are working OK as they give me the same results as other tools. Maybe my problem is in sending the correct values, but once I'm sure how the format is, I'll be sure where to lookfor ... thanks again, regards, raimon
On Thu, Nov 05, 2009 at 04:32:51PM +0100, Raimon Fernandez wrote: > Maybe my problem is in sending the correct values, but once I'm sure > how the format is, I'll be sure where to lookfor ... Not sure if would help, but it may help running something like psql under strace and seeing what it puts out onto the network and gets back. There are other more specialized tools for seeing what's going on, but strace is pretty easy and generally available. For example: strace -s 8192 -o out psql template1 -c 'select 1;' egrep '^(send|recv)' out Hope that helps! -- Sam http://samason.me.uk/
Raimon Fernandez wrote: > Hello again ... > > > I'm trying to Authenticate to some PostgreSQL using MD5. > > I can get the salt, for example: > > A7 2E DB 2F > Hello The md5 authentication in PostgreSQL works this way: server -------------- 4-byte token ---------------------------> client server <--- "md5" + md5(md5(password + username) + token)" ---- client regards - -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On 05/11/2009, at 16:49, Rafael Martinez wrote: > Raimon Fernandez wrote: >> Hello again ... >> >> >> I'm trying to Authenticate to some PostgreSQL using MD5. >> >> I can get the salt, for example: >> >> A7 2E DB 2F >> > > Hello > > The md5 authentication in PostgreSQL works this way: > > server -------------- 4-byte token ---------------------------> client > server <--- "md5" + md5(md5(password + username) + token)" ---- client Where did gou get this ? I can't find it in the docs ... AuthenticationMD5Password The frontend must now send a PasswordMessage containing the password encrypted via MD5, using the 4-character salt specified in the AuthenticationMD5Password message. If this is the correct password, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. Also, for what I see, I have to send the first byte with 'p' and then the MD5 of the psw using the salt, I cna't see the specification where I have to send the 'md5' string ... And, I have to make two MD5 ... I'll give it a try ... thanks, raimon
Raimon Fernandez wrote: > > On 05/11/2009, at 16:49, Rafael Martinez wrote: > >> >> The md5 authentication in PostgreSQL works this way: >> >> server -------------- 4-byte token ---------------------------> client >> server <--- "md5" + md5(md5(password + username) + token) ---- client > > Where did gou get this ? > > I can't find it in the docs ... > From an e-mail from Tom Lane about this: http://archives.postgresql.org/pgsql-novice/2003-05/msg00305.php regards, -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On 05/11/2009, at 18:02, Rafael Martinez wrote: > Raimon Fernandez wrote: >> >> On 05/11/2009, at 16:49, Rafael Martinez wrote: >> >>> >>> The md5 authentication in PostgreSQL works this way: >>> >>> server -------------- 4-byte token ---------------------------> >>> client >>> server <--- "md5" + md5(md5(password + username) + token) ---- >>> client >> >> Where did gou get this ? >> >> I can't find it in the docs ... >> > > > From an e-mail from Tom Lane about this: > http://archives.postgresql.org/pgsql-novice/2003-05/msg00305.php ok, thanks ... what's not clear to me if: for example: user: postgre psw: postgres salt: 1234 first md5: md5("postgrepostgres") ==> 44965a835f81ec252d83961d2cc9f3e1 second: md5("44965a835f81ec252d83961d2cc9f3e1"+&H01+&H02+&H03+&H04) ==> 85c0fde09d577cce6286869467f9f50e send "md585c0fde09d577cce6286869467f9f50e" as a psw this is not working for me ... yet ... :-) thanks, raimon > regards, > -- > Rafael Martinez, <r.m.guerrero@usit.uio.no> > Center for Information Technology Services > University of Oslo, Norway > > PGP Public Key: http://folk.uio.no/rafael/ > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 05/11/2009, at 18:24, Raimon Fernandez wrote: > > On 05/11/2009, at 18:02, Rafael Martinez wrote: > >> Raimon Fernandez wrote: >>> >>> On 05/11/2009, at 16:49, Rafael Martinez wrote: >>> >>>> >>>> The md5 authentication in PostgreSQL works this way: >>>> >>>> server -------------- 4-byte token ---------------------------> >>>> client >>>> server <--- "md5" + md5(md5(password + username) + token) ---- >>>> client >>> >>> Where did gou get this ? >>> >>> I can't find it in the docs ... >>> >> >> >> From an e-mail from Tom Lane about this: >> http://archives.postgresql.org/pgsql-novice/2003-05/msg00305.php > > ok, thanks ... at least, my first md5 (psw+user) is the same as the pg_shadow (wihtout the 'md5') ... should I md5 the first md5 as I get it as string (like username) or byte by byte ? thanks, regards, r.
On Nov 5, 2009, at 12:35 PM, Raimon Fernandez wrote: > at least, my first md5 (psw+user) is the same as the pg_shadow > (wihtout the 'md5') ... > > should I md5 the first md5 as I get it as string (like username) or > byte by byte ? As far as I know, a string. But it is unclear to me what happens when the user or database name has non-ascii characters. The client encoding is not established until after authentication. I asked about that a while ago but did not get any responses. http://archives.postgresql.org/pgsql-general/2008-12/msg00808.php John DeSoi, Ph.D.
John DeSoi <desoi@pgedit.com> writes: > ... But it is unclear to me what happens when > the user or database name has non-ascii characters. The client > encoding is not established until after authentication. No encoding conversion will happen on those names. If you consistently use the same encoding in all clients as in the database, there's no problem in using non-ASCII user or DB names. If not, I'd recommend sticking to ASCII. regards, tom lane
I'm blocked ....... On 06/11/2009, at 6:27, John DeSoi wrote: > > On Nov 5, 2009, at 12:35 PM, Raimon Fernandez wrote: > >> at least, my first md5 (psw+user) is the same as the pg_shadow >> (wihtout the 'md5') ... >> >> should I md5 the first md5 as I get it as string (like username) or >> byte by byte ? > > As far as I know, a string. But it is unclear to me what happens > when the user or database name has non-ascii characters. The client > encoding is not established until after authentication. > > I asked about that a while ago but did not get any responses. After reading all the emails about it, I'm blocked, maybe someone can see where the error is and shade some light on it ... user: postgres (test values) psw:postgres (test values) first md5("postgrepostgres") ==> 44965A835F81EC252D83961D2CC9F3E1 salt: A6B76060 second md5("44965A835F81EC252D83961D2CC9F3E1"+"A6B76060") ==> 34F74BEF877202D4399092F97EFE8712 send to server: header + length + "md5"+"34F74BEF877202D4399092F97EFE8712" ==> Fatal error, password Authentication failed for user postgres ... thanks, regards, raimon
On 06/11/2009, at 8:48, Raimon Fernandez wrote: > I'm blocked ....... > > > On 06/11/2009, at 6:27, John DeSoi wrote: > >> >> On Nov 5, 2009, at 12:35 PM, Raimon Fernandez wrote: >> >>> at least, my first md5 (psw+user) is the same as the pg_shadow >>> (wihtout the 'md5') ... >>> >>> should I md5 the first md5 as I get it as string (like username) >>> or byte by byte ? >> >> As far as I know, a string. But it is unclear to me what happens >> when the user or database name has non-ascii characters. The client >> encoding is not established until after authentication. >> >> I asked about that a while ago but did not get any responses. > > After reading all the emails about it, I'm blocked, maybe someone > can see where the error is and shade some light on it ... > > user: postgres (test values) > psw:postgres (test values) > > first md5("postgrepostgres") ==> 44965A835F81EC252D83961D2CC9F3E1 > > salt: A6B76060 > > > second md5("44965A835F81EC252D83961D2CC9F3E1"+"A6B76060") ==> > 34F74BEF877202D4399092F97EFE8712 > > > send to server: header + length + > "md5"+"34F74BEF877202D4399092F97EFE8712" ==> Fatal error, password > Authentication failed for user postgres ... I've created a tcpdump with all information: server => 52 (R) 00 00 00 0C (12 length) 00 00 00 05 (5 => md5) C8 C3 57 17 (token) psql sends => 70 00 00 00 28 6D 64 35 33 38 38 35 30 37 37 39 31 39 64 38 30 63 39 35 62 33 32 34 65 39 63 36 38 65 39 64 37 66 64 63 00 => binary p
On Fri, Nov 6, 2009 at 9:58 AM, Raimon Fernandez <coder@montx.com> wrote: > > On 06/11/2009, at 8:48, Raimon Fernandez wrote: > >> I'm blocked ....... >> >> >> On 06/11/2009, at 6:27, John DeSoi wrote: >> >>> >>> On Nov 5, 2009, at 12:35 PM, Raimon Fernandez wrote: >>> >>>> at least, my first md5 (psw+user) is the same as the pg_shadow (wihtout >>>> the 'md5') ... >>>> >>>> should I md5 the first md5 as I get it as string (like username) or byte >>>> by byte ? >>> >>> As far as I know, a string. But it is unclear to me what happens when the >>> user or database name has non-ascii characters. The client encoding is not >>> established until after authentication. >>> >>> I asked about that a while ago but did not get any responses. >> >> After reading all the emails about it, I'm blocked, maybe someone can see >> where the error is and shade some light on it ... >> >> user: postgres (test values) >> psw:postgres (test values) >> >> first md5("postgrepostgres") ==> 44965A835F81EC252D83961D2CC9F3E1 >> >> salt: A6B76060 >> >> >> second md5("44965A835F81EC252D83961D2CC9F3E1"+"A6B76060") ==> >> 34F74BEF877202D4399092F97EFE8712 >> >> >> send to server: header + length + >> "md5"+"34F74BEF877202D4399092F97EFE8712" ==> Fatal error, password >> Authentication failed for user postgres ... > > I've created a tcpdump with all information: > > server => > > 52 (R) > 00 00 00 0C (12 length) > 00 00 00 05 (5 => md5) > C8 C3 57 17 (token) > > > > psql sends => > > 70 00 00 00 28 6D 64 35 33 38 38 35 30 37 37 39 31 39 64 38 30 63 39 35 62 > 33 32 34 65 39 63 36 38 65 39 64 37 66 64 63 00 => binary > p (md53885077919d80c95b324e9c68e9d7fdc => string > > > user: postgres > psw: postgre > > I can't create an identical HASH with those values, because: > > the first md5 is easy: 44965a835f81ec252d83961d2cc9f3e1c8c35717 > > Now we have to MD5 this one with the token: > > 1. 44965a835f81ec252d83961d2cc9f3e1c8c35717C8C35717 (uppercase and > lowercase) > 2. 44965a835f81ec252d83961d2cc9f3e1c8c35717c8c35717 (lowercase) > 3. 44965a835f81ec252d83961d2cc9f3e1c8c35717 + &HC8 + &HC3 + &H57 + &H17 > 4. ?????????? > > wich one is the correct ? > md5("44965a835f81ec252d83961d2cc9f3e1" + &HC8 + &HC3 + &H57 + &H17) in python: >>> hashlib.md5('44965a835f81ec252d83961d2cc9f3e1' + '\xc8\xc3\x57\x17').hexdigest() '3885077919d80c95b324e9c68e9d7fdc'
On 06/11/2009, at 12:24, Arjen Nienhuis wrote: >> wich one is the correct ? >> > > md5("44965a835f81ec252d83961d2cc9f3e1" + &HC8 + &HC3 + &H57 + &H17) > > in python: > >>>> hashlib.md5('44965a835f81ec252d83961d2cc9f3e1' + >>>> '\xc8\xc3\x57\x17').hexdigest() > '3885077919d80c95b324e9c68e9d7fdc' thanks! finally I can reproduce it, and it's working now ... :-) regards, raimon
Raimon Fernandez wrote: > > I'm blocked ....... [...] > > user: postgres (test values) > > psw:postgres (test values) You mean, "psw: postgre", right? > > > > first md5("postgrepostgres") ==> 44965A835F81EC252D83961D2CC9F3E1 > > > > salt: A6B76060 > > > > > > second md5("44965A835F81EC252D83961D2CC9F3E1"+"A6B76060") ==> > > 34F74BEF877202D4399092F97EFE8712 > > > > > > send to server: header + length + > > "md5"+"34F74BEF877202D4399092F97EFE8712" ==> Fatal error, password > > Authentication failed for user postgres ... > > I've created a tcpdump with all information: > > server => > > 52 (R) > 00 00 00 0C (12 length) > 00 00 00 05 (5 => md5) > C8 C3 57 17 (token) > > > > psql sends => > > 70 00 00 00 28 6D 64 35 33 38 38 35 30 37 37 39 31 39 64 38 30 63 39 > 35 62 33 32 34 65 39 63 36 38 65 39 64 37 66 64 63 00 => binary > p(md53885077919d80c95b324e9c68e9d7fdc => string > > > user: postgres > psw: postgre > > I can't create an identical HASH with those values, because: > > the first md5 is easy: 44965a835f81ec252d83961d2cc9f3e1c8c35717 > > Now we have to MD5 this one with the token: > > 1. 44965a835f81ec252d83961d2cc9f3e1c8c35717C8C35717 (uppercase and > lowercase) > 2. 44965a835f81ec252d83961d2cc9f3e1c8c35717c8c35717 (lowercase) > 3. 44965a835f81ec252d83961d2cc9f3e1c8c35717 + &HC8 + &HC3 + > &H57 + &H17 > 4. ?????????? I think you got confused. This is the password, MD5-hashed: SELECT 'md5' || md5('postgrepostgres'::bytea) AS md5pwd; md5pwd ------------------------------------- md544965a835f81ec252d83961d2cc9f3e1 (1 row) Now let's encrypt the part without "md5" it with the four bytes "C8 C3 57 17" appended: SELECT 'md5' || md5(E'44965a835f81ec252d83961d2cc9f3e1\\310\\303\\127\\027'::bytea) AS double_md5; double_md5 ------------------------------------- md53885077919d80c95b324e9c68e9d7fdc (1 row) That is what psql sends. You can see all that by looking at the code: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c?rev=1.142 (function pg_password_sendauth) Yours, Laurenz Albe
On 06/11/2009, at 14:56, Albe Laurenz wrote: > Raimon Fernandez wrote: >>> user: postgres (test values) >>> psw:postgres (test values) > > You mean, "psw: postgre", right? yes ... >> Now we have to MD5 this one with the token: >> >> 1. 44965a835f81ec252d83961d2cc9f3e1c8c35717C8C35717 (uppercase and >> lowercase) >> 2. 44965a835f81ec252d83961d2cc9f3e1c8c35717c8c35717 (lowercase) >> 3. 44965a835f81ec252d83961d2cc9f3e1c8c35717 + &HC8 + &HC3 + >> &H57 + &H17 >> 4. ?????????? > > I think you got confused. > > This is the password, MD5-hashed: > > SELECT 'md5' || md5('postgrepostgres'::bytea) AS md5pwd; > > md5pwd > ------------------------------------- > md544965a835f81ec252d83961d2cc9f3e1 > (1 row) > > Now let's encrypt the part without "md5" it with the four bytes "C8 > C3 57 17" appended: > > SELECT 'md5' || md5(E'44965a835f81ec252d83961d2cc9f3e1\\310\\303\ > \127\\027'::bytea) AS double_md5; > > double_md5 > ------------------------------------- > md53885077919d80c95b324e9c68e9d7fdc > (1 row) > > That is what psql sends. > > You can see all that by looking at the code: > > http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/libpq/fe-auth.c?rev=1.142 > (function pg_password_sendauth) thanks for the info, it's working now ... regards, raimon