Please add schema support to the PostgreSQL connector

Our Postgres server(s) are set up using schemas to help filter and control data access. The Postgres connector doesn't allow for this. Instead, it connects to a database and grabs all of the tables and views available in the database.

 

Please add schema support to the connector.

 

As it stands, all of the tables and views show up, even if the user can't use them. So you get a big dropdown with a lot of resources....many of which won't work. The connector can query the table names and columns (not ideal), but won't ever be able to get data as the connection user doesn't have access to the data. Hence the schema.

 

Anyway, I captured the script that the connector runs so that I could see what needs to be added. Here's the script:

SELECT NULL AS
TABLE_CAT,
n.nspname AS TABLE_SCHEM,
C.relname AS TABLE_NAME,
CASE
n.nspname ~ '^pg_'
OR n.nspname = 'information_schema'
WHEN TRUE THEN
CASE

WHEN n.nspname = 'pg_catalog'
OR n.nspname = 'information_schema' THEN
CASE
C.relkind
WHEN 'r' THEN
'SYSTEM TABLE'
WHEN 'v' THEN
'SYSTEM VIEW'
WHEN 'i' THEN
'SYSTEM INDEX' ELSE NULL
END
WHEN n.nspname = 'pg_toast' THEN
CASE
C.relkind
WHEN 'r' THEN
'SYSTEM TOAST TABLE'
WHEN 'i' THEN
'SYSTEM TOAST INDEX' ELSE NULL
END ELSE
CASE
C.relkind
WHEN 'r' THEN
'TEMPORARY TABLE'
WHEN 'p' THEN
'TEMPORARY TABLE'
WHEN 'i' THEN
'TEMPORARY INDEX'
WHEN 'S' THEN
'TEMPORARY SEQUENCE'
WHEN 'v' THEN
'TEMPORARY VIEW' ELSE NULL
END
END
WHEN FALSE THEN
CASE
C.relkind
WHEN 'r' THEN
'TABLE'
WHEN 'p' THEN
'TABLE'
WHEN 'i' THEN
'INDEX'
WHEN 'S' THEN
'SEQUENCE'
WHEN 'v' THEN
'VIEW'
WHEN 'c' THEN
'TYPE'
WHEN 'f' THEN
'FOREIGN TABLE'
WHEN 'm' THEN
'MATERIALIZED VIEW' ELSE NULL
END ELSE NULL
END AS TABLE_TYPE,
d.description AS REMARKS
FROM
pg_catalog.pg_namespace n,
pg_catalog.pg_class
C LEFT JOIN pg_catalog.pg_description d ON ( C.oid = d.objoid AND d.objsubid = 0 )
LEFT JOIN pg_catalog.pg_class dc ON ( d.classoid = dc.oid AND dc.relname = 'pg_class' )
LEFT JOIN pg_catalog.pg_namespace dn ON ( dn.oid = dc.relnamespace AND dn.nspname = 'pg_catalog' )
WHERE
C.relnamespace = n.oid
AND C.relname LIKE'%'
AND (
FALSE
OR ( C.relkind IN ( 'r', 'p' ) AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )
OR ( C.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' )
)
ORDER BY
TABLE_TYPE,
TABLE_SCHEM,
TABLE_NAME;

All  you need is an additional condition in the WHERE clause, down around line 74. In this example, the desired schema, named "api", is hard-coded in. Obviously that would be parameterized, etc.

AND n.nspname = 'api'

Thank yoou.

0
0 votes

· Last Updated

This discussion has been closed.