exasol / postgresql-virtual-schema

Virtual Schema for connecting PostgreSQL as data source to Exasol

PostgreSQL Virtual Schema

Overview

The PostgreSQL Virtual Schema provides an abstraction layer that makes an external PostgreSQL database accessible from an Exasol database through regular SQL commands. The contents of the external PostgreSQL database are mapped to virtual tables which look like and can be queried as any regular Exasol table.

If you want to set up a Virtual Schema for a different database system, please head over to the [Virtual Schemas Repository][virtual-schemas].

Features

• Access a PostgreSQL database using a Virtual Schema.
• Access PostgreSQL compatible databases:
  • Greenplum
  • AWS Aurora

Table of Contents

Information for Users

• Virtual Schema User Guide
• PostgreSQL Dialect User Guide
• List of supported capabilities
• Changelog
• Dependencies

Find all the documentation in the [Virtual Schemas project][vs-doc].

Information for Developers

• Virtual Schema API Documentation
• Remote logging

Additional Resources

• Dependencies
• Changelog

postgresql-virtual-schema's Issues

Update to VSCJDBC 10.0.1 and fix vulnerabilities

• org.postgresql:postgresql:jar:42.4.0 in compile

  • sonatype-2022-4402: 1 vulnerability (7.1)

• org.yaml:snakeyaml:jar:1.29 in test

  • CVE-2022-25857, severity CWE-400: Uncontrolled Resource Consumption ('Resource Exhaustion') (7.5)
  • CVE-2022-38749, severity CWE-787: Out-of-bounds Write (6.5)
  • CVE-2022-38751, severity CWE-787: Out-of-bounds Write (6.5)
  • CVE-2022-38752, severity CWE-787: Out-of-bounds Write (6.5)
  • CVE-2022-38750, severity CWE-787: Out-of-bounds Write (5.5)

Capital letter PostgreSQL column names make Scalar Functions test fail

I did not test if it's a general issue or related to the scalar functions test. But it looked like it's something general.

Create an installer

Create a prototype script that helps users to install Virtual Schema.

Update tests to V8 VSPG

Since 2023-06-02 for version 8.18.1 of Exasol database a Docker image is available on Dockerhub.

The current ticket therefore requests to update the integration tests of VSPG to use version 8.18.1 as latest default version.

Please note sibling-tickets for all JDBC-based virtual schemas.

Check 'current_schema' functionality, re-enable tests after resolution

Check if https://exasol.atlassian.net/browse/SPOT-18510 has a resolution
Then re-enable the 2 disabled tests for current_schema, look for this issue ticket number or spot 'code' in the code base.

Extract generic part from CapabilitiesReport and move to parent repository

extract an abstract class from CapabilitiesReport and move it to virtual-schema-common-java or -jdbc.

Update the postgresql jdbc driver to the latest version

At the moment we are using 42.2.16. There is a newer version available in https://jdbc.postgresql.org/download.html

Fix broken explicit scalar function tests

The following scalar function tests have probably no effect:

• tests for + - * / NEG
• testSysGUID
• testRand
• testFunctionsWithNoParenthesis

Automatically generate report for scalar function capabilities

Automatically generate a report for scalar function capabilities

Test TS(9) PostgreSQL against AWS Aurora

Situation

Aurora is a hosted database on AWS that claims interface compatibility with MySQL and PostgreSQL. After supporting TS(9) in PostgreSQL VS (#48) we should check if that also works with Aurora.

Dependencies

• #48

Acceptance Criteria

• Either a integration test proves that TS(9) works with Aurora too
• or documentation updated that it is not update

Add integration test for scalar functions

Add integration test for all scalar functions.

Charset Conversion Error

The same issue described and solved here:
exasol/mysql-virtual-schema#26
also exists with this adapter.
ETL-3009: [Column=13 Row=2] [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]]

Switching back to 9.0.4-release solved the problem for me.

Document that Greenplum can be used via PostgreSQL VS

Situation

John reported that he was able to connect to Greenplum via PostgreSQL VS.

Acceptance Criteria

1. VS documentation states that the PostgreSQL VS can be used to connect to green plum too.

Refactor ScalarFunctionTests

Refactor the scalar function tests so that they can run I'm parallel.
Right now there are conflicts between the table names of the nested test classes.

Release 2.0.0 version

Update to the latest common-jdbc

Port of exasol/virtual-schemas#401

Fix docker-db issue with database

Currently, the docker-db running the scalar function integration test brings the docker-db sooner or later into a state where it rejects connections due to a lack of free space.
Sometimes this error also occurs during the test-run. In that case, after a certain point in time, all tests-fail.

Unify exceptions handling

Problem

We need to add our new exception builder to the project and unify the exceptions handling

TS(9) support in PostgreSQL VS

Situation

We need to support nanosecond timestamp resolution.

Acceptance Criteria

• An integration test against Exasol 8 proves that TS(9) is supported

Upgrade build system

• Upgrade dependencies and plugins to latest versions
• Migrate from Travis CI to GitHub Actions

Documentation on uploading JDBC driver

With 61c880a User guide in this repo doesn't mention EXAOperation and only refers to EXAOperation-less installations like docker, NGA, SaaS.

Majority of the customers at the moment use installations with EXAOperation and where ExaLoader would not look for drivers in "default/drivers/jdbc/" folder of BucketFS.

Please consider covering the currently most frequently used scenario in the repo documentation.

Refactor PostgreSQLScalarFunctionsIT when test-db-builder Table#truncate() is implemented

When exasol/test-db-builder-java#67 is done we can remove truncateTable(String) from PostgreSQLScalarFunctionsIT

User Guide: Add reference to common adapter properties for JDBC-Based Virtual Schemas

As PostgreSQL Virtual Schema (the corresponding third party JDBC driver) is known for having problems with default IMPORT data type introspection, I suggest to add a remark to the User Guide similar to the one in MySQL VS User Guide.

See PR #70

Fix documentation

1. broken link in file README.md: Find all the documentation in the [Virtual Schemas project][vs-doc].
2. add prefix comment --/ to SQL script ending with slash / (required for DbVisualizer)
3. add documentation of PostgreSQL specifics: remote connections, create table, catalog, upper / lowercase, schema, database

• Gettingstarted
• create table etc.

Make portgres service listen to remote connections, see

• allow remote connection
• fix pg_hba.conf

Edit file /etc/postgresql/10/main/postgresql.conf (sudo vi) and add line

listen_addresses = '*'

Edit file /etc/postgresql/10/main/pg_hba.conf (sudo vi) and add line

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

restart postgres service to read changed configuration

sudo service postgresql restart

Try remote access

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>

upload driver to bucket fs

curl -X PUT -T postgresql-42.4.2.jar  http://w:$BUCKETFS_PASSWORD@localhost:2580/default/postgresql-42.4.2.jar

Driver for ExaLoader

https://github.com/exasol/docker-db/#installing-custom-jdbc-drivers

File settings.cfg:

DRIVERNAME=POSTGRES_JDBC_DRIVER
JAR=postgresql-42.4.2.jar
DRIVERMAIN=org.postgresql.Driver
PREFIX=jdbc:postgresql:
FETCHSIZE=100000
INSERTSIZE=-1

Upload

curl -v -X PUT -T postgresql-42.4.2.jar  http://w:$BUCKETFS_PASSWORD@localhost:$BUCKETFS_PORT/default/drivers/jdbc/postgresql-42.4.2.jar
curl -v -X PUT -T settings.cfg  http://w:$BUCKETFS_PASSWORD@localhost:$BUCKETFS_PORT/default/drivers/jdbc/settings.cfg

or use https://github.com/exasol/bucketfs-client

Fix CVE-2023-42503 in test dependency `org.apache.commons:commons-compress`

 Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project postgresql-virtual-schema: Detected 1 vulnerable components:
Error:    org.apache.commons:commons-compress:jar:1.23.0:test; https://ossindex.sonatype.org/component/pkg:maven/org.apache.commons/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2023-42503] CWE-20: Improper Input Validation (5.5); https://ossindex.sonatype.org/vulnerability/CVE-2023-42503?component-type=maven&component-name=org.apache.commons%2Fcommons-compress&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Upgrade PostgreSQL with fix for CVE-2022-21724

Fix vulnerabilities in org.postgresql:postgresql:jar:42.6.0:compile & org.apache.commons:commons-compress:jar:1.24.0:test

 Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project postgresql-virtual-schema: Detected 2 vulnerable components:
Error:    org.apache.commons:commons-compress:jar:1.24.0:test; https://ossindex.sonatype.org/component/pkg:maven/org.apache.commons/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2024-25710] CWE-835: Loop with Unreachable Exit Condition ('Infinite Loop') (8.1); https://ossindex.sonatype.org/vulnerability/CVE-2024-25710?component-type=maven&component-name=org.apache.commons%2Fcommons-compress&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2024-26308] CWE-770: Allocation of Resources Without Limits or Throttling (7.5); https://ossindex.sonatype.org/vulnerability/CVE-2024-26308?component-type=maven&component-name=org.apache.commons%2Fcommons-compress&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:    org.postgresql:postgresql:jar:42.6.0:compile; https://ossindex.sonatype.org/component/pkg:maven/org.postgresql/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2024-1597] CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (10.0); https://ossindex.sonatype.org/vulnerability/CVE-2024-1597?component-type=maven&component-name=org.postgresql%2Fpostgresql&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Fix failing dependency check

See https://github.com/exasol/postgresql-virtual-schema/actions/runs/5971043356/job/16199507900

Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project postgresql-virtual-schema: Detected 1 vulnerable components:
Error:    org.postgresql:postgresql:jar:42.6.0:compile; https://ossindex.sonatype.org/component/pkg:maven/org.postgresql/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2020-21469] CWE-121: Stack-based Buffer Overflow (3.7); https://ossindex.sonatype.org/vulnerability/CVE-2020-21469?component-type=maven&component-name=org.postgresql%2Fpostgresql&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Lierals lead to exception

Queries like this fail:

SELECT 'test' FROM my_virtual_table.

with:

ETL-5402: JDBC-Client-Error: JDBC SQL Type for column=0 (starting at 0) value=1111 is unknown.

The reason for that seems to be that PostgreSQL sets the column type for columns of just literals to unknown.
( see)

Since in Exasol literal columns have a type, we probably have to add casts in the query rewriting.

Check if Postgres can support some of the recently added capabilities

Situation

We have added a few new functions to the common part recently.
We need to check if Postgres could support them. The list of the new function capabilities to check:

FN_BIT_LROTATE
FN_BIT_RROTATE
FN_BIT_LSHIFT
FN_BIT_RSHIFT
FN_FROM_POSIX_TIME
FN_HOUR
FN_INITCAP
FN_AGG_EVERY
FN_AGG_SOME
FN_AGG_MUL_DISTINCT
FN_PRED_IS_JSON
FN_PRED_IS_NOT_JSON
FN_HASHTYPE_MD5
FN_HASHTYPE_SHA1
FN_HASHTYPE_SHA256
FN_HASHTYPE_SHA512
FN_HASHTYPE_TIGER
FN_AGG_MUL
FN_JSON_VALUE
FN_MIN_SCALE
FN_AGG_LISTAGG
FN_AGG_LISTAGG_DISTINCT
FN_AGG_LISTAGG_SEPARATOR
FN_AGG_LISTAGG_ON_OVERFLOW_ERROR
FN_AGG_LISTAGG_ON_OVERFLOW_TRUNCATE
FN_AGG_LISTAGG_ORDER_BY
FN_AGG_COUNT_TUPLE

Rename error codes from PGVS to VSPG

Rename error codes from PGVS to VSPG

Test is using hard coded host ip address

The hardcoded host IP -address could change at any time or on different systems.

Fix dependabot warning GHSA-673j-qm5f-xpv8

See GHSA-673j-qm5f-xpv8

Fix broken scalar functions

The following scalar functions seem to be broken:

• GREATEST
• LEAST
• ROUND
• CONCAT
• INSTR
• UNICODECHR
• UNICODE
• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_MONTHS
• HOURS_BETWEEN
• ADD_SECONDS
• ADD_WEEKS
• ADD_YEARS
• SECONDS_BETWEEN
• MINUTES_BETWEEN
• SECOND
• TO_CHAR
• POSIX_TIME

Todo for each:

• Remove from PostgreSQLScalarFunctionsIT#KNOWN_BROKEN to re-enable the test.
• Fix function

Use driver from maven

Right now the Postgres driver is shipped as a jar in the test resources.
A more elegant solution is to add it as a maven test dependency and use the copy-dependency plugin to copy it's jar to the target directory at build time.

(see: https://intranet.srv.exasol.com/display/InTeam/HowTo%3A+Add+JDBC+driver+for+integration+tests+as+maven+dependency)

Refactor PostgreSQLSqlDialectIT

• See todo notes in the class
• Refactor like in exasol dialect not to use one table for multiple tests

Update dependencies to remove discontinued repository maven.exaso.com

Failed to collect dependencies at com.exasol:exasol-testcontainers:jar:6.4.1 -> com.exasol:database-cleaner:jar:1.0.2 -> com.exasol:exasol-jdbc:jar:7.1.11: Failed to read artifact descriptor for com.exasol:exasol-jdbc:jar:7.1.11:

Migration from virtual schemas repo

Migrate the implementation of Postgresql dialect from the Virtual Schemas repository.

Move abstract scalar function test to virtual-schema-common-jdbc

Removed `SQL_DIALECT` property

Feature: Special care for partitioned tables

Some partitioning mechanisms in PostgreSQL are implemented as a set of tables with corresponding triggers.
Because of it in fact creation of a partitioned table might end up in several other tables.
That's the case for the customer and they have so many partitions that they even get the error

The size of the list of the selected tables exceeded the default allowed maximum: 1000 Please, use the 'TABLE_FILTER' property to define the list of tables you need.

during Virtual Schema creation.

Obviously, tuning TABLE_FILTER or MAX_TABLE_COUNT parameters would solve the problem at hand.

However, a more elegant and reasonable solution is to add a parameter to PostgreSQL Virtual Schema to exclude tables that are a part of higher-level partitioned tables.
PostgreSQL JDBC driver might already have tooling for it: pgjdbc/pgjdbc#1708

Please consider researching this topic and implementing such functionality.

Link to user guide broken

The link to the user guide at the following link is broken

https://github.com/exasol/postgresql-virtual-schema/blob/main/doc/dialects/postgresql.md