Hello all,
Thank you for your replies!
but everything proposed, I have already tried and checked before I wrote
to the list...
To summarize:
When I want to connect from Computer A as user B to Database C via
PGAdminIII everything works smoothely -
When I want to connect from Computer A as user B to Database C via
Access or PGAdminII it fails... :-(
So I came to the conclusion that ODBC is the first to suspect...
and pg_hba.conf (which I edited by hand for the new setup) can't
be the problem...
But here (a little boiled down) is my pg_hba.conf:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all postgres trust
local all +dba md5
local zuecht zuecht_odbc md5
local gruenland gruen_odbc md5
local basisdaten versuch md5
host all postgres 127.0.0.1 255.255.0.0 md5
host all postgres 192.168.0.0 255.255.0.0 md5
host all albin 192.168.0.0 255.255.0.0 md5
host all +dba 192.168.0.0 255.255.0.0 md5
Remark:
user "albin" (It's me;-) is a member of the group dba, as you see below, I
tried to connect in this way ...
PostgreSQL loggs the following during I try to connect via Access
(via Syslog):
May 25 10:44:58 postgres[4528]: [2-1] LOG: 00000: Verbindung empfangen:
Host=192.168.0.110 Port=1086
May 25 10:44:58 postgres[4528]: [2-2] ORT: BackendFork, postmaster.c:2395
May 25 10:44:58 postgres[4528]: [3-1] LOG: 00000: Verbindung authorisiert:
Benutzer=albin Datenbank=zuecht
May 25 10:44:58 postgres[4528]: [3-2] ORT: BackendFork, postmaster.c:2457
May 25 10:44:58 postgres[4528]: [4-1] LOG: 00000: Anweisung:
May 25 10:44:58 postgres[4528]: [4-2] ORT: pg_parse_query, postgres.c:464
May 25 10:44:58 postgres[4528]: [5-1] LOG: 00000: Dauer: 0.297 ms
May 25 10:44:58 postgres[4528]: [5-2] ORT: exec_simple_query,
postgres.c:960
I am working in Austria, so the output is partly in German, a short
(rough) translation:
Verbindung empfangen = connection received
Verbindung authorisiert = connection authorized
Benutzer = user
Datenbank = database
Thanks for all!
Albin
Database: PostgreSQL 7.4.2, self-compiled
Clients: W2K, Access from Office XP
ODBC: 7.03.02.00
Here is the output of the psqlodbc-log:
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205,
errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=136060816, conn=136066856, status=0, num_stmts=16
sock=136060872, stmts=136060944, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=136077856, buffer_out=136081960
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=136066856, PGAPI_DriverConnect( in)='DSN=testing7.4.2;',
fDriverCompletion=1
DSN info:
DSN='testing7.4.2',server='balappserver',port='5432',dbase='zuecht',user='al
bin',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='07.03.0200', 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='pg_;', conn_settings=''
conn_encoding='OTHER'
conn=136066856, query=' '
NOTICE from backend during send_query: 'LOG: Anweisung:
'
NOTICE from backend during send_query: 'LOG: Dauer: 0.246 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=136060816, conn=136066856, status=0, num_stmts=16
sock=136060872, stmts=136060944, lobj_type=-999
---------------- Socket Info -------------------------------
socket=964, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=136077856, buffer_out=136081960
buffer_filled_in=48, buffer_filled_out=0, buffer_read_in=48
And here from the mylog-file:
[1244]globals.extra_systable_prefixes = 'dd_;'
[1244][[SQLAllocHandle]][1244]**** in PGAPI_AllocEnv **
[1244]** exit PGAPI_AllocEnv: phenv = 136060816 **
[1244][[SQLSetEnvAttr]] att=200,2
[1244][[SQLAllocHandle]][1244]PGAPI_AllocConnect: entering...
[1244]**** PGAPI_AllocConnect: henv = 136060816, conn = 136066856
[1244]EN_add_connection: self = 136060816, conn = 136066856
[1244] added at i =0, conn->henv = 136060816, conns[i]->henv =
136060816
[1244][SQLGetInfo(30)][1244]PGAPI_GetInfo: entering...fInfoType=77
[1244]PGAPI_GetInfo: p='03.00', len=0, value=0, cbMax=12
[1244][[SQLSetConnectAttr]] 103
[1244]PGAPI_SetConnectAttr 103
[1244]PGAPI_SetConnectOption: entering fOption = 103 vParam = 20
[1244][[SQLGetConnectAttr]] 30002
[1244]PGAPI_GetConnectAttr 30002
[1244]PGAPI_GetConnectOption: entering...
[1244]CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002',
errnum=205, errmsg='Unknown connect option (Get)'
[1244][[SQLSetConnectAttr]] 30002
[1244]PGAPI_SetConnectAttr 30002
[1244]PGAPI_SetConnectOption: entering fOption = 30002 vParam = 122428760
[1244]Microsoft Jet !!!!
[1244][SQLDriverConnect][1244]PGAPI_DriverConnect: entering...
[1244]**** PGAPI_DriverConnect: fDriverCompletion=1,
connStrIn='DSN=testing7.4.2;'
[1244]our_connect_string = 'DSN=testing7.4.2;'
[1244]attribute = 'DSN', value = 'testing7.4.2'
[1244]copyAttributes:
DSN='testing7.4.2',server='',dbase='',user='',passwd='xxxxx',port='',onlyrea
d='',protocol='',conn_settings='',disallow_premature=-1)
[1244]globals.extra_systable_prefixes = 'pg_;'
[1244]our_connect_string = 'DSN=testing7.4.2;'
[1244]attribute = 'DSN', value = 'testing7.4.2'
[1244]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;C
0=1;C1=0;C2=pg_;[1244]CC_connect: entering...
[1244]CC_connect(): DSN = 'testing7.4.2', server = 'balappserver', port =
'5432', database = 'zuecht', username = 'albin', password='xxxxx'
[1244]connecting to the server socket...
[1244]connection to the server socket succeeded.
[1244]sizeof startup packet = 292
[1244]sent the authentication block.
[1244]sent the authentication block successfully.
[1244]gonna do authentication
[1244]read 9, global_socket_buffersize=4096
[1244]auth got 'R'
[1244]areq = 5
[1244]in AUTH_REQ_MD5
[1244]read 15, global_socket_buffersize=4096
[1244]auth got 'R'
[1244]areq = 0
[1244]auth got 'K'
[1244]auth got 'Z'
[1244]sending an empty query...
[1244]send_query(): conn=136066856, query=' '
[1244]send_query: done sending query
[1244]in QR_Constructor
[1244]exit QR_Constructor
[1244]read 48, global_socket_buffersize=4096
[1244]send_query: got id = 'N'
[1244]~~~ NOTICE: 'LOG: Anweisung:
'
[1244]send_query: got id = 'I'
[1244]send_query: got id = 'N'
[1244]~~~ NOTICE: 'LOG: Dauer: 0.246 ms
'
[1244]send_query: got id = 'Z'
[1244]got no result from the empty query. (probably database does not
exist)
[1244]QResult: in DESTRUCTOR
[1244]QResult: free memory in, fcount=0
[1244]QResult: free memory out
[1244]QResult: exit DESTRUCTOR
[1244]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.'
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=1[1244]**** PGAPI_ConnectError:
hdbc=136066856 <0>
[1244]enter CC_get_error
[1244]enter CC_create_errormsg
[1244]msg = 'The database does not exist on the server
or user authentication failed.'
[1244]exit CC_create_errormsg
[1244]exit CC_get_error
[1244]CC_get_error: status = 105, msg = #The database does not exist on the
server
or user authentication failed.#
[1244] szSqlState = 'S1000',len=72, szError='(null)'
[1244]PGAPI_GetDiagRec exiting 1
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=1[1244]**** PGAPI_ConnectError:
hdbc=136066856 <74>
[1244]enter CC_get_error
[1244]exit CC_get_error
[1244]CC_get_error: status = 105, msg = #The database does not exist on the
server
or user authentication failed.#
[1244] szSqlState = 'S1000',len=72, szError='The database does not
exist on the server
or user authentication failed.'
[1244]PGAPI_GetDiagRec exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=8
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=9
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=10
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=11
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=2[1244]**** PGAPI_ConnectError:
hdbc=136066856 <0>
[1244]PGAPI_GetDiagRec exiting 100
[1244][[SQLFreeHandle]][1244]PGAPI_FreeConnect: entering...
[1244]**** in PGAPI_FreeConnect: hdbc=136066856
[1244]enter CC_Destructor, self=136066856
[1244]in CC_Cleanup, self=136066856
[1244]after CC_abort
[1244]SOCK_Destructor
[1244]after SOCK destructor
[1244]exit CC_Cleanup
[1244]after CC_Cleanup
[1244]after free statement holders
[1244]exit CC_Destructor
[1244]PGAPI_FreeConnect: returning...
[1244][[SQLFreeHandle]][1244]**** in PGAPI_FreeEnv: env = 136060816 **
[1244]in EN_Destructor, self=136060816
[1244]exit EN_Destructor: rv = 1
[1244] ok