Re: 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(at)cybertec(dot)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(at)cybertec(dot)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(at)cybertec(dot)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(dot)m(dot)guerrero(at)usit(dot)uio(dot)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(dot)m(dot)guerrero(at)usit(dot)uio(dot)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(dot)m(dot)guerrero(at)usit(dot)uio(dot)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(at)postgresql(dot)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(at)pgedit(dot)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

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 ?

thanks,

regards,

raimon

On Fri, Nov 6, 2009 at 9:58 AM, Raimon Fernandez <coder(at)montx(dot)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