SSL error: access denied when connection to PostgreSQL 13 from Exasol 7.0.4 on Gougle Cloud

[bakka]

Hi, we just moved our Google Cloud environment to Frankfurt, and I am trying to restablish the setup.

One of the things is to let Exasol access a postgres database.

Exasol is on one VPC network peered to another network with Postgres. We haven't yet been able to access using interal IP, so we are using external IP. This worked in our previous setup in another region.

When trying to connect I get this error message. As I understand it, it sounds like the exasol session is denied access to an internal exasol resource. The session is running as SYS, so it has as much permission to everything as possible.

Any ideas as to what I can try to make this work.

SQL Error [ETL-5]: JDBC-Client-Error: Connecting to 'jdbc:postgresql://<IP>:5432/metadata?currentSchema=analyticaldata' as user='postgres' failed: SSL error: access denied ("java.io.FilePermission" "/home/exasolution/.postgresql/postgresql.crt" "read") (Session: 1690951028142374912)

Any help is greatly appreciated.

Exasol is installed as a community edition using the Exasol BYOL option in GCP marketplace. Postgres driver came with the installation.

Postgres is a CloudSQL.

[exa-Nico · Accepted solution]

I think you are right, the driver needs additional permissions. Go into Exaoperation -> Software -> JDBC Drivers. Click on your postgres driver and check the box to disable the security manager. See if that solves the problem.

[bakka · Accepted solution]

Giving the added driver the same prefix as the existing did the job:

[exa-Franz]

Hi bakka, just for your information:
you can explicitely select one of the two postgresql drivers if you want to use - using the "driver option":
https://docs.exasol.com/sql/import.htm
"Some JDBC drivers are already delivered as default (visible in EXAoperation) and can be used within the connection string (for example, jdbc:mysql, jdbc:postgres). You can additionally configure JDBC drivers in EXAoperation and choose them through the DRIVER option if its prefix is ambiguous."

For the example, search for "driver=" on that page.

In your case:

select * from (import from jdbc DRIVER='POSTGRESQL2' at beacon_metadata statement 'select ''Connection beacon_metadata works'' ');

Hope that helps a bit!

[jwarlander]

Finally, you can also upload the new driver using the same name ("POSTGRESQL") as the default system driver, effectively overriding it -- if you know that's what you want to do of course!

Then you don't have to worry about specifying the driver when using IMPORT.

[bakka]

Thanks - I was not aware of the DRIVER= option. 

[bakka]

Added another postgres driver:

But it seems like Exasol does not recognize the driver:

CREATE OR REPLACE CONNECTION postconn to 'jdbc:hulla://1.2.3.4:5432/mydata?currentSchema=analyticaldata' USER 'postgres' IDENTIFIED BY ..

select * from (import from jdbc at beacon_metadata statement 'select ''Connection beacon_metadata works'' ');

Results in the error:

SQL Error [ETL-5]: JDBC-Client-Error: Connecting to 'jdbc:hulla://1.2.3.4:5432/mydata?currentSchema=analyticaldata' as user='postgres' failed: No suitable driver found for jdbc:hulla://1.2.3.4:5432/mydata?currentSchema=analyticaldataengine (Session: 1690971833132449792)

The driver is the JDBC 4.1 driver downloaded from this site: https://jdbc.postgresql.org/download.html#current

[bakka]

Thanks. It seems like I cannot change the preloaded driver. But I try to add another postgres driver. The newest jdbc 4.1 driver shoul be fine ?

[exa-Nico · Accepted solution]

I think you are right, the driver needs additional permissions. Go into Exaoperation -> Software -> JDBC Drivers. Click on your postgres driver and check the box to disable the security manager. See if that solves the problem.