Обсуждение: problem connecting via ODBC when unicode (now in correct forum.).

Hi,

I have have a question that annoys me incredibly.

I have a "Login Roles" that include special chars like "ÆØÅ" (same in password) that accesses a view. however this is not possible, as the connector does not recognize the user.

------------------------------

On Friday 21 January 2011 6:29:07 am Kim Mortensen wrote:
> Hi,
>
> I have have a question that annoys me incredibly.
>
> I have a "Login Roles" that include special chars like "ÆØÅ" (same in
> password) that accesses a view. however this is not possible, as the
> connector does not recognize the user.
>
> ------------------------------
> -----
> FATAL: no pg_hba.conf entry for host "127.0.0.1", user "ÆØÅ", database
> "mydb", SSL off.
> -----------------------------------
>
> However if I create another "Login Roles" with the name "abc" (same in
> password), I can successfull connect and access the view.
>
> I have almost tried all combinations of client_encoding, have tried to set
> configuration string on the connector.
>
> Database is build with: "...... --encoding=UNICODE --locale=C
> --lc-collate=C".
>
> If I use pgadmin, everything in the database looks perfect, and the unicode
> chars are represented correctly (Login role ÆØÅ exists).
>
> Any help much appriciated.
>
> Best Regards
> Kim Mortensen

To eliminate possibilities.
Can you connect to the database via means other than ODBC using the Role that
has the special characters?
What are the lines in your pg_hba file?

--
Adrian Klaver
adrian.klaver@gmail.com

On Friday 21 January 2011 7:57:14 am Kim Mortensen wrote:
> Adrian,
>
> ok, but one thing I dont undestand is that then it would not work for the
> role "aaa", but it does.
>
> I use the ODBC driver test application that is in the control
> panel/administrative tools/ODBC . in there you can select the driver a,d
> put in the credentials, and press Test. for user "aaa" it works, but for
> "æøå" is does not (aaa and æøå are both Login Roles that has the exact same
> properties when looking in the database through the pgadmin - And they were
> both inserted the same way using jdbc driver.)
>
> Best Regards
> Kim Mortesnen


CCing the list so more eyes can see it. I was trying to narrow the possible
error source. Now to ODBC questions.
What version of the driver are you using?
When you installed did you install the Unicode version?






--
Adrian Klaver
adrian.klaver@gmail.com

Adrian,

The ODBC driver is the psqlodbc driver version 9 (latest msi installer, just downloaded from the homepage a couple of days ago.). This installation installel both an ANSI and Unicode version, and i have tried to run tests on both, but not gaining any positive results.

Best Regards
Kim Mortensen

On Friday 21 January 2011 8:10:24 am Kim Mortensen wrote:
> Adrian,
>
> The ODBC driver is the psqlodbc driver version 9 (latest msi installer,
> just downloaded from the homepage a couple of days ago.). This installation
> installel both an ANSI and Unicode version, and i have tried to run tests
> on both, but not gaining any positive results.
>
> Best Regards
> Kim Mortensen
>
>

Hmm, I am going to have to think about this. I will be away from a computer for
a while so I will be able to respond for a bit. In the meantime, when you login
with the 'aaa' role through ODBC can you see the correct characters for the
other role in the database?



--
Adrian Klaver
adrian.klaver@gmail.com

Adrian,

Actually I havent tried to view unicode chars through the login that succeeds, but that was a good test to see if its the connector that is messing things up. Unfortunately im now at home, so I dont have access to the system, so the test will have to be conducted Monday.

By the way, when looking at the create statements shown by pgadmin when I click on the Login Role, i do see a difference, but not something that would cause alarm.

the aaa role has a definition like:   create role aaa ....

where the other with unicode have:  create role "æøå"....

so the unicode is in brackets (cant remenmber if its single or double,), but again is just something i notised, and dont think affexts anything, but worth mentioning..

Best Regards
Kim Mortesnen

Thank you for your input,

it may be that only ascii chars is to be used, however it should be stated or a bug report filled out, as you can insert Login Roles with different charakterset as you can actually use.

Best Regards
Kim Mortesnen

Hi.

I think..
your create role "æøå" was saved by pgAdmin by UTF-8 in the database.
pgAdmin uses the input value of connection directly, however, input
value is in coincidence by UTF-8. however, psqlODBC uses the character
code of environmental dependence as it is.
as for japanese windows, It is not in agreement with a database by the
reason for being SJIS. In the client-server protocol of PostgrSQL,
judgment of encoding can't be performed at the time of connection.
therefore, I think that it is difficult to support. however, It may be
useful to evasion of a security risk that it uses understanding this
specification. but, handling is not recommendation in the reason for
being difficult. Probably, only the ASCII character should be used.

I may have misunderstanding.?

Regards,
Hiroshi Saito
(z-saito@ address was lost...)

(2011/01/22 1:48), Kim Mortensen wrote:
> create role "æøå"

On 01/21/2011 10:20 AM, Kim Mortensen wrote:
> Thank you for your input,
>
> it may be that only ascii chars is to be used, however it should be
> stated or a bug report filled out, as you can insert Login Roles with
> different charakterset as you can actually use.
>
> Best Regards
> Kim Mortesnen
>
>

I cranked up a virtual instance of Windows XP to try to figure this out.
Turns out I need  to know more about how encoding works on Windows. I
had no problem creating the role æøå on my Linux server and logging in
using psql. On Windows not so, I get:

Username [postgres]: æøå
Password for user µ°Õ:
psql: FATAL:  password authentication failed for user "æøå"

and through ODBC:

2011-01-21 12:16:32 PSTFATAL:  password authentication failed for user "æøå"

Seems client encoding is getting in the way somehow, just not sure how.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian,

Tje letters you are typing are from Danish, but any will do.

Now im working abit from memory, but when the databse is build, initdb is using UNICODE as encoding, "C" for locale, so it should be independant of the locale settings.

Best Regards
Kim Mortensen

On 01/21/2011 12:35 PM, Kim Mortensen wrote:
> Adrian,
>
> Tje letters you are typing are from Danish, but any will do.
>
> Now im working abit from memory, but when the databse is build, initdb
> is using UNICODE as encoding, "C" for locale, so it should be
> independant of the locale settings.

I believe the problem, as Hiroshi Saito pointed out, is that the client
encoding is being set on Windows before the connection is attempted and
that it is picking the wrong one. Per his post pgAdmin and JDBC are
probably just starting with UTF8 and that is why they succeed. At this
point I don't know how to get around that.

>
> Best Regards
> Kim Mortensen


--
Adrian Klaver
adrian.klaver@gmail.com

But the connector is configures individually and I specify the encoding by the configuration string sat in the JDBC driver. this string sets the encoding, but apparently it does not work.

BEst Regards
Kim Mortesnen

On 01/21/2011 01:28 PM, Kim Mortensen wrote:
> But the connector is configures individually and I specify the encoding
> by the configuration string sat in the JDBC driver. this string sets the
> encoding, but apparently it does not work.
>

It is a chicken and egg problem. The problem is that to set the encoding
you have to connect but to connect the client needs to be using the
correct encoding. In Windows, it seems the ODBC driver is sending the
connection string in the wrong encoding and you never actually connect.


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> I believe the problem, as Hiroshi Saito pointed out, is that the client
> encoding is being set on Windows before the connection is attempted and
> that it is picking the wrong one. Per his post pgAdmin and JDBC are
> probably just starting with UTF8 and that is why they succeed. At this
> point I don't know how to get around that.

AFAIK the only way to get the mentioned error message (about "no
pg_hba.conf entry") from this kind of problem is if the DBA is listing
user names directly in pg_hba.conf.  Since there is no encoding
conversion active this early in backend startup, non-ASCII user names can
only work if the encoding used in pg_hba.conf matches what is arriving
from the client.

Possible solution: make *two* (or more) entries in pg_hba.conf, one in
each encoding that clients might be using.  This is probably
unmaintainable though, particularly when using Windows editors which are
almost certainly going to think they know more than you do about
encodings.

Better idea: refrain from mentioning specific usernames in pg_hba.conf.
Usually, anything you might want to do that way can be done better in
other ways.  For instance, make use of the CONNECT privilege if you're
running a release new enough to have it.

By and large, though, non-ASCII user names are going to be problematic
anyway --- even if you got past the pg_hba.conf check, I think you might
just get a "no such user" failure when the client sends the name in an
encoding that doesn't match what's in the system catalog.  This is an
area that's known to need work.

            regards, tom lane

On Friday 21 January 2011 2:17:00 pm Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
> > I believe the problem, as Hiroshi Saito pointed out, is that the client
> > encoding is being set on Windows before the connection is attempted and
> > that it is picking the wrong one. Per his post pgAdmin and JDBC are
> > probably just starting with UTF8 and that is why they succeed. At this
> > point I don't know how to get around that.
>
> AFAIK the only way to get the mentioned error message (about "no
> pg_hba.conf entry") from this kind of problem is if the DBA is listing
> user names directly in pg_hba.conf.  Since there is no encoding
> conversion active this early in backend startup, non-ASCII user names can
> only work if the encoding used in pg_hba.conf matches what is arriving
> from the client.

In the message(s) that went private was the pg_hba.conf. It did have some
specific usernames.

>
> Possible solution: make *two* (or more) entries in pg_hba.conf, one in
> each encoding that clients might be using.  This is probably
> unmaintainable though, particularly when using Windows editors which are
> almost certainly going to think they know more than you do about
> encodings.
>
> Better idea: refrain from mentioning specific usernames in pg_hba.conf.
> Usually, anything you might want to do that way can be done better in
> other ways.  For instance, make use of the CONNECT privilege if you're
> running a release new enough to have it.

When I tested I used 'all' for users to get around the above problem. It got me
past the no pg_hba.conf entry problem.

>
> By and large, though, non-ASCII user names are going to be problematic
> anyway --- even if you got past the pg_hba.conf check, I think you might
> just get a "no such user" failure when the client sends the name in an
> encoding that doesn't match what's in the system catalog.  This is an
> area that's known to need work.

What I saw, for the record, in the Windows error message was:
FATAL:password authentication failed for user "?"

>
>             regards, tom lane



--
Adrian Klaver
adrian.klaver@gmail.com