It seems I cannot connect to a local instance of postgres 13.
Im getting the following message:
"The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver."
Hi,
I think the first thing is to check that version of the JDBC driver you have installed with Matillion. I did a test with a Bash Script on my instance liek this:
find / -name "*.jar" -print 2>/dev/null | grep -i postg || echo Not found
It showed two files, the second is a symbolic link to the first. So then in the same Bash Script component:
java -cp /usr/share/tomcat8/lib/postgresql-jdbc.jar org.postgresql.util.PSQLDriverVersion
java -cp /usr/share/emerald/WEB-INF/lib/postgresql-9.4-1206-jdbc41.jar org.postgresql.util.PSQLDriverVersion
Mine showed PostgreSQL 9.4 JDBC4.1 (build 1206), which is definitely way behind Postgres 13, and that's maybe why the new auth method is not supported.
I'm pretty sure you can't update that driver within Matillion. But one thing you could try is downloading the latest one and adding it as a new JDBC driver in Matillion. They have this document which shows you how to do it.
CK
Hi,
I've tried making a new entry in the Driver section according to @Cey111 reply.
Using his command in the post above
‘find / -name “*.jar” -print 2>/dev/null | grep -i postg || echo Not found’
gave me the following result:
However I'm still getting an authentication error in the database query component:
We run Matillion on an Azure VM. I've also tried connection through the terminal with 'PSQL' after installing it ofcourse (yum install postgresql-devel).
The connection to the postgres database works fine through psql, which is version 10.17 btw (PSQL -V).
So this concludes there is not anything wrong in the pga_hba_conf files on the database server.
The only thing I might be missing has something to do with the classpath?
See the following link from the postgress documentation https://jdbc.postgresql.org/documentation/head/classpath.html .
If anyone could help me out how to do this?
Hi,
I think you are on the right lines there. I believe that "authentication type 10" means the SCRAM-SHA-256 password option which they added in version 10, and which is supported in versions 42.2.0 and higher of the JDBC driver.
You definitely don't need to make any manual changes to your CLASSPATH to begin using a new JDBC driver in Matillion. In your screenshot you named it "PostgreSQL2" so if you open the settings of your Database Query component and set the Database Type to PostgreSQL2 then it will use your newly added driver. I think you should always reboot Matillion after adding a new JDBC driver.
According to this post there's nothing else you should need to change. As long as you supply your username and password (recommend using a managed password) then the JDBC driver will negotiate the authentication with the PostgreSQL server.
CK
To add onto this.
When I remove the driver in the "Manage database drivers' section, it somehow still recognizes that there is a driver available.
This makes me think it isnt even using the driver I had uploaded.
Hey CK,
Thanks for the reply once again. You're giving me better support then Matillion does 😀 .
However I did what you stated.
I can select the PostgresSQL2 driver in the database query component.
After rebooting the system I still get the same error.
I'm almost 100% sure it's not due to ip not being accepted since I can ping/ telnet from the VM terminal in CENTOS just fine. Also i'm able to $ PSQL -h <server>-p <port> -U <User> <databasename> -c <command> to the server which gives me replies the terminal:
Pictues for more context:
I was advised to install/migrate to the latest version of Matillion, as since 1.64 (not 1.61) the PostgreSQL driver v42.3.3 has been rolled out. I will give it a try.
i found a workaround to this problem. On investigation it seems that matillion uses its own driver for postgresql and does not pay attention to any jdbc drivers uploaded. What I did that seems to be working so far was to ssh into the instance and locate the directory where matillion holds the drivers, in my case it was
/usr/share/emerald/WEB-INF/lib
the driver they used was postgresql-9.4-1206-jdbc41.jar which does not seem to support scram-sha-256. I renamed this to postgresql-9.4-1206-jdbc41.jar.original (incase i need to restore it later on) and copied over the new driver to this directory and subsequently renamed that to postgresql-9.4-1206-jdbc41.jar in case it was referenced.
I highly recommend that if you are to follow these steps, make a backup of your instance first incase it breaks something down the line.
Just tried uploading a random driver in the PostgresSQL2 database driver config.
Pressing test gave me an error due to the com.xxxx.Driver is not found in the jar.
However my theory was that this would break the database query component using the PostgresSQL as 'Database type'. But it did not, still give me the authenticating type error. Having evaluated this i've only come to the conclusion that it is NOT using the .jar driver i've uploaded.
to add to this, when you upload a driver, for me it was saved in /usr/share/tomcat/Drivers/. This is the directory i copied the driver from
Hi @j.ouwehand1606738637880 ,
did you ever find a solution for the authentication problems? I currently see the same error message.
Thanks
Michael
