Jooq: Add code generation support for PostgreSQL foreign data wrappers

[ahoulgrave]

Hello!

Just opening this issue to add some information on the matter, as i could not find a lot online. Hopefully it'll be somewhat helpful to someone having the same issue.

So, currently jooq does not support foreign tables. The generator cannot see them.

[lukaseder]

Thanks, makes sense.

[lukaseder]

Is there any kind of FDW that is very simple for testing? I'd love to use a CSV, for example, and hard code it in the FDW definition, rather than pointing to a file on disk...

[lukaseder]

... or even better, a "loop back FDW", which points back to a table in the same PostgreSQL instance...

[lukaseder]

Found it: https://www.postgresql.org/docs/current/postgres-fdw.html

[lukaseder]

This seems to suffice:

create extension if not exists postgres_fdw;
create server loopback foreign data wrapper postgres_fdw;
create foreign table t_fdw (id int, title text) server loopback options (table_name 't_book');
create user mapping for postgres server loopback options (user 'postgres', password 'test');
select * from t_fdw;

A quick query on the information_schema reveals:

select * 
from information_schema.tables
where table_name = 't_fdw'
;

... result:

table_catalog|table_schema|table_name|table_type|
-------------|------------|----------|----------|
postgres     |public      |t_fdw     |FOREIGN   |

This seems to work out of the box. I'm getting a generated table like this:

public class TFdw extends TableImpl<TFdwRecord> {

    private static final long serialVersionUID = -1599415199;

    /**
     * The reference instance of <code>public.t_fdw</code>
     */
    public static final TFdw T_FDW = new TFdw();

    /**
     * The class holding records for this type
     */
    @Override
    public Class<TFdwRecord> getRecordType() {
        return TFdwRecord.class;
    }

    /**
     * The column <code>public.t_fdw.id</code>.
     */
    public final TableField<TFdwRecord, Integer> ID = createField(DSL.name("id"), org.jooq.impl.SQLDataType.INTEGER, this, "");

    /**
     * The column <code>public.t_fdw.title</code>.
     */
    public final TableField<TFdwRecord, String> TITLE = createField(DSL.name("title"), org.jooq.impl.SQLDataType.CLOB, this, "");

    // ...

What's missing?

[wulczer]

We figured it out, sorry - it seems that information_schema.tables filters out tables where the user has no privileges:

\d+ information_schema.tables

(...)

View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,

(...)

AND (pg_has_role(c.relowner, 'USAGE'::text) OR
     has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
     has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

They still show up in \d which is a bit confusing. It also means that we could get the table structure from internal Postgres catalogs instead of information_schema if we wanted to, but I don't think it's worth it.

[lukaseder]

I see, thanks for the feedback. Privileges are generally a requirement for jOOQ's code generation to work. If a table is invisible in the information_schema, but visible in pg_catalog, I'd even think this is a bug in PostgreSQL worth reporting.

[lukaseder]

I'd even think this is a bug in PostgreSQL worth reporting.

Perhaps there's a privilege to see tables, but not select from them, though? I'm not 100% sure about PG's privilege model...

[wulczer]

There's a usage privilege on schemas, but I'm not sure if it's worth going into details about it. The information_schema.tables only showing objects the user has access to is intentional and documented (I wouldn't expect otherwise from Postgres ;) ).

Thank you for answering that quickly and sorry for the false report.

[lukaseder]

There's a usage privilege on schemas, but I'm not sure if it's worth going into details about it.

Well, the ideal user to use for code generation is the user that will be used at runtime, so I don't think the distinction here is worth it, indeed.

Thank you for answering that quickly and sorry for the false report.

No worries :)