Cannot link to postgres 8.0.0 databases using ODBC from Access - Mailing list pgsql-hackers

I have cross posted this issue here following suggestion on the pg_odbc
list

> I'd try turning off log_discconnections, and see if you get any joy.  If
> that works and the log still displays problems
> related to BackendRun, I'd email the pg-hackers list.

We have an Access 97 database front end connected via ODBC (V8.00.00.04)
Windows 2000 Desktop to a Postgres
8.0.0 backend running on Windows 2000 Server. A long running complex
transaction failed possibly because of a timeout. After the failure the
server logging was changed to identify the application fault. We then could
not unable relink to any database on the server from any client. The error
returned is "The database does not exist on the server or user
authentication failed. (#105)".

The advice in other similar posts is to check the hba.conf file and
database user access (snips below). I cannot see anything wrong with the
set up although I see that the postgres server log shows the first two
lines with UNKNOWN  user and client IP address.  The remaining
lines are as I would expect.
Further investigation has identified a fault that I suppose is applicable
to the combination of
Postgres 8.0.0 ODBC driver running on Windows 2000 server and desktop.

I have two postgresql.conf files, one is the default out-of-the-box
postgresql.conf file, the other has many of the logging options turned on
(I have put the diff between the two files at the end)

The default config works fine, the modified config fails in that the
postgres ODBC does not work (tested with Access 97,2000 and Perl ODBC
connections) giving the error "The database does not exist on the server or
user authentication failed. (#105)".

Only ODBC seems to be affected. All other connections from php, pgadmin
work fine.

I have demonstrated this problem consistently on two completely different
and brand new installations of P8.0.0 (W2K server and desktop). I have
tried many (but not all) combinations but have not been able to pin down
exactly what combination of settings generates the
error. Turning off log_disconnections does not in itself cure the problem.

Regards
Laurie


The diff of the two config files is

207a208
> client_min_messages = log
212a214
> log_min_messages=info
214a217
> log_error_verbosity = verbose
218a222
> log_min_error_statement = info
230a235
> log_connections = true
231a237
> log_disconnections = true
232a239
> log_duration = true
240a248,249
> log_line_prefix = '%u %d %r %t %c %x'
>
241a251
> log_statement = 'all'


hba.conf

host  all   all   127.0.0.1/0       trust
host  all         all         10.35.10.248 255.255.240.0    trust

and the sample database used in the ODBC link.

CREATE DATABASE rjl_pilot WITH OWNER = ldb      ENCODING = 'UNICODE'      TABLESPACE = pg_default;
GRANT ALL ON DATABASE rjl_pilot TO public;
GRANT ALL ON DATABASE rjl_pilot TO ldb;

The psqllog file extract:

conn=159252552, PGAPI_DriverConnect( in)='DSN=rjl_pilot;',
fDriverCompletion=1
DSN info: DSN='rjl_pilot',server='za29rugbr',port='5433',dbase
='rjl_pilot',user='ldb',passwd='xxxxx'         onlyread='0',protocol='6.4',showoid='0',fakeoidindex
='0',showsystable='0'         conn_settings='',conn_encoding='OTHER'         translation_dll='',translation_option=''
Global Options: Version='08.00.0002', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=0               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64               extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=159252552, query=' '
NOTICE from backend during send_query: 'LOG:  statement:
'
NOTICE from backend during send_query: 'LOG:  duration: 0.000 ms
'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'           ------------------------------------------------------------
henv=158138320,conn=159252552, status=0, num_stmts=16           sock=158129576, stmts=158109160, lobj_type=-999
 ---------------- Socket Info -------------------------------           socket=908, reverse=0, errornumber=0,
errormsg='(NULL)'          buffer_in=159281008, buffer_out=158112112           buffer_filled_in=30,
buffer_filled_out=0,buffer_read_in=30
 

The mylog file extract:

[6180]**** PGAPI_DriverConnect: fDriverCompletion=1, connStrIn
='DSN=rjl_pilot;'
[6180]our_connect_string = 'DSN=rjl_pilot;'
[6180]attribute = 'DSN', value = 'rjl_pilot'
[6180]copyAttributes: DSN='rjl_pilot',server='',dbase='',user='',passwd
='xxxxx',port='',onlyread='',protocol='',conn_settings
='',disallow_premature=-1)
[6180]globals.extra_systable_prefixes = 'dd_;'
[6180]our_connect_string = 'DSN=rjl_pilot;'
[6180]attribute = 'DSN', value = 'rjl_pilot'
[6180]CopyCommonAttributes:

A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[6180]CC_connect:entering...
[6180]CC_connect(): DSN = 'rjl_pilot', server = 'za29rugbr', port = '5433',
database = 'rjl_pilot', username = 'ldb', password='xxxxx'
[6180]connecting to the server socket...
[6180]connection to the server socket succeeded.
[6180]sizeof startup packet = 292
[6180]sent the authentication block.
[6180]sent the authentication block successfully.
[6180]gonna do authentication
[6180]read 15, global_socket_buffersize=4096
[6180]auth got 'R'
[6180]areq = 0
[6180]auth got 'K'
[6180]auth got 'Z'
[6180]sending an empty query...
[6180]send_query(): conn=159252552, query=' '
[6180]send_query: done sending query
[6180]in QR_Constructor
[6180]exit QR_Constructor
[6180]read 21, global_socket_buffersize=4096
[6180]send_query: got id = 'N'
[6180]~~~ NOTICE: 'LOG:  statement:
'
[6180]read 30, global_socket_buffersize=4096
[6180]send_query: got id = 'I'
[6180]send_query: got id = 'N'
[6180]~~~ NOTICE: 'LOG:  duration: 0.000 ms
'
[6180]send_query: got id = 'Z'
[6180]got no result from the empty query.  (probably database does not
exist)
[6180]QResult: in DESTRUCTOR
[6180]QResult: free memory in, fcount=0
[6180]QResult: free memory out
[6180]QResult: exit DESTRUCTOR
[6180]CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'

...

The postgres server log has the following

[unknown] [unknown]  2005-02-17 18:55:59 4214e8bf.9dc 0LOG:  00000:
connection received: host=10.35.10.248 port=4630
[unknown] [unknown]  2005-02-17 18:55:59 4214e8bf.9dc 0LOCATION:
BackendRun, postmaster.c:2656
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: connection authorized: user=ldb database=rjl_pilot
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION:  BackendRun, postmaster.c:2728
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOG:  00000: statement:
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOCATION:  pg_parse_query, postgres.c:473
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: duration: 16.000 ms
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION:  exec_simple_query, postgres.c:1021
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: disconnection: session time: 0:00:00.21 user=ldb database=rjl_pilot
host=10.35.10.248 port=4630
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION:  log_disconnections, postgres.c:3403


:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.