Jens Sorensen (Intuii | 25 Jul 13:20 2015

PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.

Hello,

 

I’m stuck.

 

I’m trying to import data from PostgreSQL running on an Untangle appliance (Untangle.com) to SQL Server, but can’t seem to overcome an issue with the following error:

 

Msg 7347, Level 16, State 1, Line 7

OLE DB provider 'MSDASQL' for linked server '(null)' returned data that does not match expected data length for column '[MSDASQL].uri'. The (maximum) expected data length is 8000, while the returned data length is 9606.

 

Even after increasing the MaxLongVarcharSize as in the following select statement, I still get this same error with the value of 8000.

 

select * from openrowset('MSDASQL', 'Dsn=PostgreSQL35W;MaxLongVarcharSize=16000;', 'select * from reports.http_events')

 

I’ve also tried this one:

 

select * from openrowset('MSDASQL', 'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=10.5.55.1;port=5432;database=uvm;MaxLongVarChar=16000;',

 

‘SELECT event_id

      ,time_stamp

      ,session_id

      ,client_intf

      ,server_intf

      ,c_client_addr

      ,s_client_addr

      ,c_server_addr

      ,s_server_addr

      ,c_client_port

      ,s_client_port

      ,c_server_port

      ,s_server_port

      ,policy_id

      ,username

      ,hostname

      ,request_id

      ,method

      ,uri

      ,host

      ,c2s_content_length

      ,s2c_content_length

      ,s2c_content_type

      ,adblocker_blocked

      ,adblocker_cookie_ident

      ,adblocker_action

      ,webfilter_reason

      ,webfilter_category

      ,webfilter_blocked

      ,webfilter_flagged

      ,sitefilter_reason

      ,sitefilter_category

      ,sitefilter_blocked

      ,sitefilter_flagged

      ,clam_clean

      ,clam_name

      ,virusblocker_clean

      ,virusblocker_name

from reports.http_events')

 

From ODBC setup:

 

 

 

From a “pg_dump” of schema:

 

--

-- Name: http_events; Type: TABLE; Schema: reports; Owner: postgres; Tablespace:

--

 

CREATE TABLE http_events (

    event_id bigint NOT NULL,

    time_stamp timestamp without time zone,

    session_id bigint,

    client_intf smallint,

    server_intf smallint,

    c_client_addr inet,

    s_client_addr inet,

    c_server_addr inet,

    s_server_addr inet,

    c_client_port integer,

    s_client_port integer,

    c_server_port integer,

    s_server_port integer,

    policy_id bigint,

    username text,

    hostname text,

    request_id bigint,

    method character(1),

    uri text,

    host text,

    c2s_content_length bigint,

    s2c_content_length bigint,

    s2c_content_type text,

    adblocker_blocked boolean,

    adblocker_cookie_ident text,

    adblocker_action character(1),

    webfilter_reason character(1),

    webfilter_category text,

    webfilter_blocked boolean,

    webfilter_flagged boolean,

    sitefilter_reason character(1),

    sitefilter_category text,

    sitefilter_blocked boolean,

    sitefilter_flagged boolean,

    clam_clean boolean,

    clam_name text,

    virusblocker_clean boolean,

    virusblocker_name text

);

 

 

Any advise appreciated!

 

Jens

 

 

 

 

Ramesh Reddy | 24 Jul 19:56 2015
Picon

Re: When will you be adding ISC_REQ_MUTUAL_AUTH to the ODBC dwSSPIFlags variable?

Thanks you Lindsay.

The root of the question after delving little bit more, we did not find any properties to enable "Mutual Authentication" when using GSSAPI with Windows ODBC driver. Can this be added to the code?

Thanks

Ramesh..

I have pg-odbc working with Windows sspi authentication.  There is a guide online [1] that describes the key element: you need to run the postgres service as a domain user that you've registered as a security principal for that machine. If you need a service name other than POSTGRES there is a GUC setting for krbsrvname; set that and a corresponding SPN.

The only weird behaviour I've noticed is that looking at security events in Windows event manager, after some time the client kerberos authentication reverts to NTLMv1. I suspect that might be a problem between the pg domain user and AD though.

Also, connections never seem to pool but there's probably a good security reason for that.

[1] https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows

On 22 Jul 2015 1:12 am, "Ramesh Reddy" <rareddy <at> redhat.com> wrote:
Has any one have working solution that has kerberos authentication working on windows based pg-odbc driver? We believe the below flag is required for it to work correctly, can anybody weigh in options we have in terms of setting this flag with out code modification. We are also looking to build locally to verify the solution.

Thanks

Ramesh..

----- Original Message -----
> We need mutual authentication via ODBC, looking into the psqlODBC driver to
> find where the Kerberos connection was getting created.  Here in sspisvcs.c
> the PerformKerberosEtcClientHandshake contains the set of SSPI flags being
> set on the request (held in the dwSSPIFlags variable).
> This set is missing the flag required for mutual authentication
> (ISC_REQ_MUTUAL_AUTH).  Can this be added to your ODBC driver?
>
> --
> Regards,
> Debbie Steigner
> Red Hat Global Support Services
> Principal Technical Support Engineer
>
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>


--
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Jean-Marc Guazzo | 23 Jul 04:05 2015
Picon

Materialized Views

Hi,

Materialized Views aren't visible when I try to link them with MS Access, MS Excel or LibreOffice Base.

I guess that's because the ODBC driver doesn't return this information...

Can you tell me whether there will be some adjustement in the next version of the odbc driver regarding the MVs ?

Thanks.

JM.
Stefan Viljoen | 22 Jul 10:06 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi Adrian

Thanks for replying!

>> Hi List
>>
>> The plot thickens - I have now resorted to doing a tcpdump of the 
>> postgres port and then trying to connect via isql.
>>
>> The tcpdump command used was
>>
>> ---
>> tcpdump -vvvvv -x -X -s 65535 -i lo 'port 5432' -w post.pcap
>> ---
>>
>> This has revealed that in the post.pcap file (even just viewing the 
>> raw packet data with vi in the terminal) that the REASON postgress 
>> keeps rejecting isql login attempts is that isql apparently passes the 
>> database name only partially, or corrupts it randomly...

>Not sure, but here is what I see.

>First on my machine openSUSE 13.1

>unixODBC 2.3.1-3.1.2

Ok that is much more recent than my unixODBC as provided by the official
Centos 6.5 repositories, mine is unixODBC 2.2.14, even after a 

yum update unixodbc

>psqlodbc 09.03.0400

See below, I have this same 0400 version installed via yum from the
repository URL indicated below.

>odbcinst.ini

>[Postgres]
>Description             =
>Driver          = /usr/local/lib/psqlodbcw.so
>Driver64                = /usr/lib
>Setup           = /usr/lib/unixODBC/libodbcdrvcfg1S.so
>Setup64         = /usr/lib
>UsageCount              = 1
>CPTimeout               =
>CPReuse         =

>[ODBC]
>Trace           = No
>TraceFile               = /tmp/sql.log
>ForceTrace              = No
>Pooling         = No

Ok, I changed my driver to refer to psqlodbcw.so, NOT psqlodbc.so.

>odbc.ini

>[Production]
>Description             = Postgres
>Driver          = Postgres
>Host            = localhost
>Database                = production
>Port            =

Ok, I lacked a "Host" line (having a "ServerName=localhost" line instead) so
I added a "Host" line like this one above...

>aklaver <at> panda:~> isql -v Production
>+---------------------------------------+
>| Connected!                            |
>Where Production is pointing at a 9.4 database.

>I know we are not talking apples to apples, but at least it proves it is
possible.

Agreed.

>Now my observations.

>1) In my odbcinst.ini the Driver being pointed to is psqlodbcw.so instead
of psqlodbc.so in your odbcinst.ini. This leads to

Ok, I changed mine to psqlodbcw.so as well. No change.

>2) In your original post you have:

># yum list installed | grep postgres

>postgresql94.x86_64     9.4.4-1PGDG.rhel6
>postgresql94-contrib.x86_64
>postgresql94-devel.x86_64
>postgresql94-docs.x86_64
>postgresql94-libs.x86_64
>postgresql94-odbc.x86_64
>postgresql94-odbc-debuginfo.x86_64
>postgresql94-server.x86_64

Correct.

>Yet the Postgres yum repo:

>http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/

>has

>postgresql94-odbc-09.03.0300-1PGDG.rhel6.x86_64.rpm

Ok...

>So is it possible you have a version mismatch in the Postgres ODBC driver
and the Postgres server?

Ok, this is completely possible...

>I may have missed it, but do you know what version of psqlodbc you are
using or more to the point where is postgresql94-odbc.x86_64 is coming from?

I have

postgresql94-odbc-09.03.0400-1PGDG.rhel6.x86_64

e. g. I have the 0400 version like you have.

postgresql94-odbc.x86_64 is coming from the above URL you listed -
yum.postgresql.org/9.4/redhat/rhel-6-x86_64/

I know this due to how I installed Postgres 9.4 on my Centos 6.5 box:

- I edited /etc/yum.repos.d/CentOS-Base.repo and added a line

exclude=postgresql*

to the [base] and [updates] sections of CentOS-Base.repo

- I added a file

pgdg-94-centos.repo

to /etc/yum.repos.d

- This file contains

---
[pgdg94]
name=PostgreSQL 9.4 $releasever - $basearch
baseurl=http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94

[pgdg94-source]
name=PostgreSQL 9.4 $releasever - $basearch - Source
failovermethod=priority
baseurl=http://yum.postgresql.org/srpms/9.4/redhat/rhel-$releasever-$basearc
h
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94
---

- I then ran 

yum install postgresql94
yum install postgresql94-odbc
yum install postgresql94-devel
yum install postgresql94-libs
yum install postgresql94-contrib
yum install postgresql94-docs

to install postgres.

So it appears my problem is an old unixODBC installation?

Should I uninstall the yum ODBC package in Centos 6.5 and try to compile and
install the current unixODBC version from source?

Wonder why the latest MariaDB is working fine with the 2.2 version of
unixODBC I have, while postgres 9.4 won't....

Thanks for the help.

Regards

Stefan

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 21 Jul 10:25 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi List

The plot thickens - I have now resorted to doing a tcpdump of the postgres
port and then trying to connect via isql.

The tcpdump command used was

---
tcpdump -vvvvv -x -X -s 65535 -i lo 'port 5432' -w post.pcap
---

This has revealed that in the post.pcap file (even just viewing the raw
packet data with vi in the terminal) that the REASON postgress keeps
rejecting isql login attempts is that isql apparently passes the database
name only partially, or corrupts it randomly...

E. g. I tried this five or six times and each time, the packets sent from
unixODBC / isql binary, specifies the string

asteriskcdrdb

which denotes the database name to postgress (and therefore is critical to
control login) as

&%% <at> %!db
*&#! <at> #drdb
! <at> &&%$idb

etc. and sometimes mixed / along with some other ASCII crud I cannot
duplicate here.

SSL is most definitely off so this cannot be encryption?

The STRANGE thing is I can spot the username (asteriskcdruser) in the packet
data and that is ALWAYS correct and uncorrupted, and is always passed as
"asteriskcdruser".

But the database name is -always- corrupted that is sent to port 5432 from
isql to login to postgress.

So if I do with isql

---
isql -v pgdb-cdr asteriskcdruser pword
---

In effect I try to login to postgress with

*&#! <at> #drdb

(as indicated by the packet data)  which is of course a database that does
not exist, nor is there a role that matches "asteriskcdruser" for such a
database.

From there the error

[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect

which is completely logical as I'm trying to log into non-existent databases
as passed over ODBC from isql, no?

Any thoughts? How can this be fixed?

ODBC connections on the same box over the lo interface to MySQL keep working
100% - wonder why unixODBC is corrupting the database name, but ONLY when
passing it to Psql-odbc?

Or am I completely barking up the wrong tree?

Thanks

Stefan

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 21 Jul 09:31 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi List

With the new increased verbosity I managed to turn on, I've found something
interesting.

This was my definition in /etc/odbc.ini:

---
[ODBC Data Sources]
pgdb=PostgreSQL

[pgdb-cdr]
Driver                  = /usr/pgsql-9.4/lib/psqlodbc.so
ServerName=/tmp
Port=5432
UserName=asteriskcdruser
Password=1ndestruc#
Database=asteriskcdrdb
ReadOnly=No
Protocol=9.4
Trace=Yes
TraceFile=/tmp/odbc.log
ForceTrace=Yes
ConnSettings=
DSN=pgdb
ServerType=Postgres
Debug=1
DebugFile=/var/log/postgresql_debug.log
---

running

---
isql -v pgdb-cdr dbuser pass
---

gives in pg_log/postgresql-Tue.log the relevant error

---
< 2015-07-21 09:04:35.850 SAST >LOCATION:  PerformAuthentication,
postinit.c:258
< 2015-07-21 09:04:35.856 SAST >LOG:  08006: could not send data to client:
Broken pipe
---

However, removing these two lines from /etc/odbc.ini

---
DSN=pgdb
ServerType=Postgres
---

changes the logged output to this

---
< 2015-07-21 09:21:24.018 SAST >LOG:  00000: connection received:
host=[local]
< 2015-07-21 09:21:24.018 SAST >LOCATION:  BackendInitialize,
postmaster.c:4007
< 2015-07-21 09:21:24.019 SAST >LOG:  00000: connection authorized:
user=asteriskcdruser database=asteriskcdrdb
< 2015-07-21 09:21:24.019 SAST >LOCATION:  PerformAuthentication,
postinit.c:258
< 2015-07-21 09:21:24.022 SAST >LOG:  00000: disconnection: session time:
0:00:00.004 user=asteriskcdruser database=asteriskcdrdb host=[local]
< 2015-07-21 09:21:24.022 SAST >LOCATION:  log_disconnections,
postgres.c:4511
---

e. g. now the "broken pipe" error is gone, but I just get a virtually
instant disconnection.

The isql output remains the same:

---
# isql -v pgdb-cdr dbuser dbname
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
---

but it appears that the REASON is now gone, e. g. no "broken pipe" logged by
Postgres?

This mean anything to anybody?

Thanks

Stefan

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 21 Jul 09:11 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi List

The problems continue - I've now raised the debug logging values to maximum
on the 9.4 instance here that I cannot connect to via unixODBC in Centos
6.5.

This is what the log looks like if I run

---
isql -v pgdb-cdr dbuser dbpass
---

:

---
< 2015-07-21 09:05:11.187 SAST >LOG:  00000: connection received:
host=[local]
< 2015-07-21 09:05:11.187 SAST >LOCATION:  BackendInitialize,
postmaster.c:4007
< 2015-07-21 09:05:11.188 SAST >LOG:  00000: connection authorized:
user=dbuser database=dbpass
< 2015-07-21 09:05:11.188 SAST >LOCATION:  PerformAuthentication,
postinit.c:258
< 2015-07-21 09:05:11.193 SAST >LOG:  08006: could not send data to client:
Broken pipe
< 2015-07-21 09:05:11.193 SAST >LOCATION:  internal_flush, pqcomm.c:1368
< 2015-07-21 09:05:11.193 SAST >FATAL:  08006: connection to client lost
< 2015-07-21 09:05:11.193 SAST >LOCATION:  ProcessInterrupts,
postgres.c:2885
< 2015-07-21 09:05:11.193 SAST >LOG:  00000: disconnection: session time:
0:00:00.006 user=dbuser database=dbpass host=[local]
< 2015-07-21 09:05:11.193 SAST >LOCATION:  log_disconnections,
postgres.c:4511
---

This generates on the commandline

---
# isql -v pgdb-cdr dbuser dbpass
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
---

so the real problem is a broken UNIX pipe when isql is trying to connect to
Postgres.

Any idea what I can do with this new information in order to solve the ODBC
connection problem from isql?

Thanks

Stefan

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 20 Jul 15:14 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi Joe

Whoops, type in my message. I just rechecked the .repo file, they do read

exclude

Thanks for replying.

Regards

Stefan

-----Original Message-----
From: Joe Gilmour [mailto:joe <at> gilmours.com.au] 
Sent: Monday,July 20, 2015 03:03 PM
To: Stefan Viljoen
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting
localhost postgres instance with isql ODBC commandline client

On 20/07/15 22:57, Stefan Viljoen wrote:
> - I installed unixODBC via yum on Centos 6.5 and updated it via yum
>
> - I edited /etc/yum.repos.d/CentOSBase.repo and in the [base] section 
> added
> exlucde=postgresql*
>
> - I edited /etc/yum.repos.d/CentOSBase.repo and in the [updates] 
> section added exlucde=postgresql*
>
> - I added a file pdgd-94-centos.repo which contains:
>
Haven't followed this one too much but if the lines above are as they are in
your repos files they should read exclude not exlucde. Hope this helps.
Joe

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 20 Jul 14:07 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi Jeremy

>I think your /etc/hosts file is fine but just to be certain you could try
> psql -h 127.0.0.1 ...
>if this works it is probably NOT the problem.

Ok, tried this - psql -works-?

---
[root <at> jhbasterisk ~]# psql -h 127.0.0.1 -U asteriskcdruser -d asteriskcdrdb
psql (9.4.4)
Type "help" for help.

asteriskcdrdb=>
---

So that blows my theory out of the water - postgres clearly is listening via
IPV4 on 127.0.0.1 and accepts the connection (my hba file currently has
"trust" on all lines, so no password is requested.)

Putting all the lines in the hba.conf to "md5" asks for a password for the
above step, but STILL works, and isql still fails with the exact same error
it constantly fails with.

All I can surmise is that the unixODBC drivers are incapable due to some
protocol difference or mismatch in connecting to the pgsql driver supplied
with Postgres 9.4 as there apparently is no error in my configuration or
installation of either.

BTW MariaDB 10.0.20 ver 15.1 / MySQL works perfectly via unixODBC on the
same physical box I'm finding it impossible to get postgres 9.4 ODBC access
working on.

So I'm reasonably sure the unixODBC infrastructure IS installed correctly
and is working...

Last resort is probably to start tcpdump'ing and try and ferret out just
what isql is "saying" that Postgres 9.4's ODBC drivers don't "understand"?

But that is going to take such an enormous amount of effort it might be
better to just downgrade pg and if that fails abandon postgres entirely and
try to use MySQL (which is slower and IMO much more limited).

In an ideal situation I'd install Centos 7 which has newer unixODBC drivers
(I suppose) but I have to get this working on a deployed machine that can't
just be sunk for several hours.

Thanks,

Stefan

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

dsteigne | 16 Jul 20:38 2015
Picon

When will you be adding ISC_REQ_MUTUAL_AUTH to the ODBC dwSSPIFlags variable?

We need mutual authentication via ODBC, looking into the psqlODBC driver to find where the Kerberos
connection was getting created.  Here in sspisvcs.c the PerformKerberosEtcClientHandshake contains
the set of SSPI flags being set on the request (held in the dwSSPIFlags variable).
This set is missing the flag required for mutual authentication (ISC_REQ_MUTUAL_AUTH).  Can this be added
to your ODBC driver?

-- 
Regards,
Debbie Steigner
Red Hat Global Support Services
Principal Technical Support Engineer

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Stefan Viljoen | 17 Jul 15:02 2015
Picon

Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi Nick

Here is my odbc.ini entry for the pgdb-cdr data source:

---
[pgdb-cdr]
Driver                  = /usr/pgsql-9.4/lib/psqlodbc.so
ServerName              = localhost
Port                    = 5432
UserName                = dbuser
Password                = dbpassword
Database                = dbname
ReadOnly                = No
Protocol                = 9.4
Trace                   = No
TraceFile               = sql.log
ConnSettings       =
---

I have tried to

- Replace ServerName with 127.0.0.1 and ::1
- Changing the protocol to 9.3, 9.2., 9.1
- Making ReadOnly yes
- Deliberately mis-spelling the Database name but the error remains the same

- Omitting the password, error remains the same
- Removing the port, error remains the same.

The only thing I can conclude at this point is that 9.4 Postgres ODBC driver
is in fact incompatible with the Centos 6.5 ODBC package version?

As far as I can tell isql does not get any response from Postgres ODBC
driver - given that I can corrupt the pgdb-cdr entry however I like, even
severe errors or gaps in it is just ignored and the same error is returned
each time.

I have run strace on isql and I can see that it DOES parse the above file
though - so I'm reasonable sure that the 9.4 Postgres driver is completely
incompatible with the ODBC version supplied with Centos 6.5?

Again, THANK YOU for your assistance :)

Kind regards,

>It can only display the error the driver returns, so not more details.

>What is in your odbc.ini for the pgdb-cr entry?

>I would expect a

>database=dbname

>to direct the driver to the correct database.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc


Gmane