Thread: Translate function and strange results ...

Hi,

Can someone can explain me why it's run like this with PostgreSQL v8.3.8 ?

base=# select translate('Hervé', 'é', 'e');
 translate
-----------
 Herve
(1 row)

base=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
 translate
-----------
 Hervai
(1 row)

base=# \encoding
SQL_ASCII

Thanks,
--
Hervé Piedvache

You are actually doing something like:

select translate(E'Herv\xc3\xa9', E'\xc3\xa2\xc3\xa0\xc3\xa4\xc3\xa9\xc3\xa8\xc3\xaa\xc3\xab\xc3\xaf\xc3\xb6\xc3\xb4\xc3\xb9\xc3\xbc\xc3\xbb', E'aaaeeeeioouuu');

Which apparently translates \xc3 -> a and \xa9 -> i.

I don't know why it does that though. Maybe it's the server_encoding. What does SHOW server_encoding; tell you?

Arjen Nienhuis <a.g.nienhuis@gmail.com> writes:
> I don't know why it does that though. Maybe it's the server_encoding. What
> does SHOW server_encoding; tell you?

He said SQL_ASCII.  translate() will definitely not work nicely with
multibyte characters if it doesn't know they are multibyte :-(

            regards, tom lane

Hi Tom,

Thar's mean I need to convert my database in other enconding ?

Regards,

Le dimanche 01 novembre 2009, Tom Lane a écrit :
> Arjen Nienhuis <a.g.nienhuis@gmail.com> writes:
> > I don't know why it does that though. Maybe it's the server_encoding.
> > What does SHOW server_encoding; tell you?
>
> He said SQL_ASCII.  translate() will definitely not work nicely with
> multibyte characters if it doesn't know they are multibyte :-(
>
>             regards, tom lane
>


--
Hervé Piedvache

On Mon, Nov 2, 2009 at 12:07 AM, Hervé Piedvache <bill.footcow@gmail.com> wrote:
> Hi Tom,
>
> Thar's mean I need to convert my database in other enconding ?
>

No you don't. The problem is with the encoding of the query:

test=# \encoding
SQL_ASCII
test=# SELECT convert('Hervé', 'UTF-8', 'LATIN1');
 convert
----------
 Herv\351
(1 row)

test=# SELECT translate(E'Herv\351',
E'\342\340\344\351\350\352\353\357\366\364\371\374\373',
'aaaeeeeioouuu');
 translate
-----------
 Herve
(1 row)

Or you can also change your terminal character encoding to ISO-8859-1 encoding:

test=# \encoding
SQL_ASCII
test=# select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
 translate
-----------
 Herve
(1 row)

Or even iconv can help:

postgres@ub64:~$ cat > test.sql
select translate('Hervé', 'âàäéèêëïöôùüû', 'aaaeeeeioouuu');
postgres@ub64:~$ file test.sql
test.sql: UTF-8 Unicode text
postgres@ub64:~$ psql test < test.sql
 translate
-----------
 Hervai
(1 row)

postgres@ub64:~$ iconv -t iso-8859-1 < test.sql > latin.sql
postgres@ub64:~$ file latin.sql
latin.sql: ISO-8859 text
postgres@ub64:~$ psql test < latin.sql
 translate
-----------
 Herve
(1 row)

Hello,


I want to implement SSL in my Frontend implementation with TCP/IP.

The manual just says, after receiving an S:

"To continue after S, perform an SSL startup handshake (not described
here, part of the SSL specification) with the server."

I can't find it in the manual or in the postgresql web page.

thanks,

regards,


raimon

Raimon Fernandez <coder@montx.com> writes:
> I want to implement SSL in my Frontend implementation with TCP/IP.

You should not be thinking about implementing SSL from scratch --- we don't.
Use OpenSSL or another library.

If you're just a glutton for punishment and creating your own security
holes, you could probably find a spec at www.openssl.org.

            regards, tom lane

Tom Lane wrote:
> Raimon Fernandez <coder@montx.com> writes:
>
>> I want to implement SSL in my Frontend implementation with TCP/IP.
>>
>
> You should not be thinking about implementing SSL from scratch --- we don't.
> Use OpenSSL or another library.
>
> If you're just a glutton for punishment and creating your own security
> holes, you could probably find a spec at www.openssl.org.
>

heck, you have to be a glutton to want to use libssl from openssl...
there's something like 158 APIs and very little documentation on how to
properly use them

Why aren't you using libpq ??!?

On 05/11/2009, at 0:06, John R Pierce wrote:

> Tom Lane wrote:
>> Raimon Fernandez <coder@montx.com> writes:
>>
>>> I want to implement SSL in my Frontend implementation with TCP/IP.
>>>
>>
>> You should not be thinking about implementing SSL from scratch ---
>> we don't.
>> Use OpenSSL or another library.

Of course I'm not going to implement SSL from scratch ...

:-)


Where can I find the steps to start an SSL connection with PostgreSQL ?

The config files, certificates, etc. etc. must follow the same rules
for the libpq specification ?


> heck, you have to be a glutton to want to use libssl from openssl...
> there's something like 158 APIs and very little documentation on how
> to properly use them

Doy you mean there's no 'easy' way to start-up an SSL connection from
a TCP/IP socket to postgresql ????


> Why aren't you using libpq ??!?


I'm doing this as an experiment/hobby, the comunication using TCP/IP
is really fast, I'm accessing servers that are far away and the speed
is really great, I have asynchronous comunication, I can show rows as
they are coming, I don't have to wait before all of them are here,
it's multi-plattform, my code works on OS X, OS 9, Windows, Linux, and
I don't know almost nothing about C, linking C libraries, etc. etc.

:-)

thanks!

regards,

raimon

On Thu, Nov 05, 2009 at 08:48:47AM +0100, Raimon Fernandez wrote:
> Where can I find the steps to start an SSL connection with PostgreSQL ?
>
> The config files, certificates, etc. etc. must follow the same rules for
> the libpq specification ?

You follow the conventions of whatever SSL library you use.

>> heck, you have to be a glutton to want to use libssl from openssl...
>> there's something like 158 APIs and very little documentation on how
>> to properly use them
>
> Doy you mean there's no 'easy' way to start-up an SSL connection from a
> TCP/IP socket to postgresql ????

Sure, open up the documentation for the SSL library you want to use and
find the function that lets you pass a open file descriptior. This
function will handle the SSL startup for you and give you a handle for
further communication.

Personally I find the GnuTLS API to be much saner than openssl, in
which case you just do:

  gnutls_transport_set_ptr (session, (gnutls_transport_ptr_t) FileDescriptor);

  /* Perform the TLS handshake
   */
  ret = gnutls_handshake (session);

See this example:


http://www.gnu.org/software/gnutls/manual/html_node/Simple-client-example-with-anonymous-authentication.html#Simple-client-example-with-anonymous-authentication

Hope this helps,
--
Martijn van Oosterhout   <kleptog@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.

Raimon Fernandez wrote:
>> heck, you have to be a glutton to want to use libssl from openssl...
>> there's something like 158 APIs and very little documentation on how
>> to properly use them
>
> Doy you mean there's no 'easy' way to start-up an SSL connection from
> a TCP/IP socket to postgresql ????
>

When you see that "S", you initialize a TLS/SSL connection, some hints
about how SSL works here...

http://www.mozilla.org/projects/security/pki/nss/ssl/

the *pathetic* official documentation on OpenSSL is here...
http://www.openssl.org/docs/
oops, 214 library functions in libssl, I think I said 148 or something
earlier.
http://www.openssl.org/docs/ssl/ssl.html#API_FUNCTIONS

likely your best bet will be to look at the sources to libpq that deal
with SSL session setup, usage, and teardown, and use the libssl docs as
references for the SSL_xxxxxxx API calls you find there

And you likely will want to get a comprehensive book on programming
SSL/TLS with libssl/openssl

>> Why aren't you using libpq ??!?
>
> I'm doing this as an experiment/hobby, the comunication using TCP/IP
> is really fast, I'm accessing servers that are far away and the speed
> is really great, I have asynchronous comunication, I can show rows as
> they are coming, I don't have to wait before all of them are here,
> it's multi-plattform, my code works on OS X, OS 9, Windows, Linux, and
> I don't know almost nothing about C, linking C libraries, etc. etc.

What are you programming in ?  Does it provide native SSL sockets ?
OpenSSL is pretty much all C library programming.   Certainly, something
like the native SSL SecureSocket mechanismi in Java are much easier to use

On 05/11/2009, at 9:15, John R Pierce wrote:

> Raimon Fernandez wrote:
>>> heck, you have to be a glutton to want to use libssl from
>>> openssl... there's something like 158 APIs and very little
>>> documentation on how to properly use them
>>
>> Doy you mean there's no 'easy' way to start-up an SSL connection
>> from a TCP/IP socket to postgresql ????
>>
>
> When you see that "S", you initialize a TLS/SSL connection, some
> hints about how SSL works here...
>
> http://www.mozilla.org/projects/security/pki/nss/ssl/
>
> the *pathetic* official documentation on OpenSSL is here...
> http://www.openssl.org/docs/
> oops, 214 library functions in libssl, I think I said 148 or
> something earlier.
> http://www.openssl.org/docs/ssl/ssl.html#API_FUNCTIONS
>
> likely your best bet will be to look at the sources to libpq that
> deal with SSL session setup, usage, and teardown, and use the libssl
> docs as references for the SSL_xxxxxxx API calls you find there
>
> And you likely will want to get a comprehensive book on programming
> SSL/TLS with libssl/openssl
>
>>> Why aren't you using libpq ??!?
>>
>> I'm doing this as an experiment/hobby, the comunication using TCP/
>> IP is really fast, I'm accessing servers that are far away and the
>> speed is really great, I have asynchronous comunication, I can show
>> rows as they are coming, I don't have to wait before all of them
>> are here, it's multi-plattform, my code works on OS X, OS 9,
>> Windows, Linux, and I don't know almost nothing about C, linking C
>> libraries, etc. etc.
>
> What are you programming in ?  Does it provide native SSL sockets ?
> OpenSSL is pretty much all C library programming.   Certainly,
> something like the native SSL SecureSocket mechanismi in Java are
> much easier to use

Yes, I have Native TCP/IP SSL Sockets, and I've successfully connected
to other servers in SSL.

I'm going to install a certificate in PostgreSQL and start from
there ...

The port is the same for 'open' connections ?

thanks,

raimon

On Nov 5, 2009, at 2:48 AM, Raimon Fernandez wrote:

> I'm doing this as an experiment/hobby, the comunication using TCP/IP
> is really fast, I'm accessing servers that are far away and the
> speed is really great, I have asynchronous comunication, I can show
> rows as they are coming, I don't have to wait before all of them are
> here, it's multi-plattform, my code works on OS X, OS 9, Windows,
> Linux, and I don't know almost nothing about C, linking C libraries,
> etc. etc.

A much easier secure option is to just tunnel your connection over SSH.

http://pgedit.com/tip/postgresql/ssh_tunneling




John DeSoi, Ph.D.

On 05/11/2009, at 14:22, John DeSoi wrote:

>
> On Nov 5, 2009, at 2:48 AM, Raimon Fernandez wrote:
>
>> I'm doing this as an experiment/hobby, the comunication using TCP/
>> IP is really fast, I'm accessing servers that are far away and the
>> speed is really great, I have asynchronous comunication, I can show
>> rows as they are coming, I don't have to wait before all of them
>> are here, it's multi-plattform, my code works on OS X, OS 9,
>> Windows, Linux, and I don't know almost nothing about C, linking C
>> libraries, etc. etc.
>
> A much easier secure option is to just tunnel your connection over
> SSH.
>
> http://pgedit.com/tip/postgresql/ssh_tunneling


Yes, this is the 'easy way', but it depends if the user has installed
SSH or not, so it's another dependency. On OS X and Linux no problem,
on windows, mmmmm ......

The tool that I'm using has builtin SSL Sockets, so I'm trying to
implement it, I'll see ...

:-)

Now I'm busy with Authentication, I used a trusted user/connection for
an easier startup but now I want to test a real user with psw ...
Maybe a new message to the list in a short time ...

:-)

regards and thanks!


raimon

We want to control from our application how to handle certain exceptions. I
believe that Raise is the functionality that we want to use.  The
documentation is a little light on what happens on the client side.

How does the client get notified and is there a error window that is
displayed when a raise is issued by the server?  What is the communications
method used and is there a way to trap those errors on the client side?

Best Regards
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

On Thu, Nov 05, 2009 at 08:24:24AM -0600, Michael Gould wrote:
> We want to control from our application how to handle certain exceptions. I
> believe that Raise is the functionality that we want to use.  The
> documentation is a little light on what happens on the client side.

That's because it's up to the client to decide what to do.  You'll need
to look at the documentation of whatever library/code you're using
to talk to PG.  PG just aborts the transaction for anything apart
from NOTIFY and hence your client will just see the transaction/query
failing.  How you disentangle this is up to your code and how they with
your drivers.

I'd just write a plpgsql function that raises an error, call it from
your code, and see what happens.

--
  Sam  http://samason.me.uk/

Hello,


More on this ...


To be clear, just after receiving the S confirmation that PostgreSQL
can handle SSL connections, I have to switch my TCPSocket into SSL.

Immediatly, I receive some errors, depending my configuration:


0 - SSLv2: SSL (Secure Sockets Layer) version 2.  ==== ERROR => 102
1 - SSLv23: SSL version 3, but can roll back to 2 if needed.  ====
ERROR => 336031996
2- SSLv3: SSL version 3.  ==== ERROR => 336130315
3- TLSv1: TLS (Transport Layer Security) version 1. ==== ERROR =>
336150773


NavicatPostgreSQL can connect and establish a SSL connection with my
PostgreSQL server.
pgAdminIII can also connect using SSL.

So, the problem must be in my code ?

thanks,

regards,

raimon

On 2009-11-05, Sam Mason <sam@samason.me.uk> wrote:
> On Thu, Nov 05, 2009 at 08:24:24AM -0600, Michael Gould wrote:
>> We want to control from our application how to handle certain exceptions. I
>> believe that Raise is the functionality that we want to use.  The
>> documentation is a little light on what happens on the client side.
>
> That's because it's up to the client to decide what to do.  You'll need
> to look at the documentation of whatever library/code you're using
> to talk to PG.  PG just aborts the transaction for anything apart
> from NOTIFY and hence your client will just see the transaction/query
> failing.  How you disentangle this is up to your code and how they with
> your drivers.

NOTICE, LOG, and DEBUG events are non-terminating, only EXCEPTION cancels the
transaction. NOTIFY is something completely different.
there is an option that must be set to make the events visible, I
forget what it is.

look up PQsetNoticeProcessor

At work we exploit notices to control our application's GUI.