Minimum PostgreSQL privileges for code-generation?

[Olle Östlund]

Hi!

I'm new to Jooq and have been playing around with the Jooq code-generation for a while now. My initial attempts with the code-genration worked just fine, using the Postgres role owning the database in question.

Since the code-generation eventually will end up in source code, I did not like the idea of exposing the credentials of a Postgres role having privileges to modify the database (or even the rights to look at table contents) though. I'd like to run the code-generation with a role restricted to read-only access to Postgres table meta-data only.

This sounds like a natural and basic requirement to me, but I have a really hard time finding any kind of information "in the right direction". I found nothing in the Jooq documentation. I tried to run the code-generation using a Postgres role having no access to the database in question (yes, such a role can see the table meta-data but not the table contents -- you can try it with psql), but the code-generation acted like there was nothing in the database at all (no "access denied" message or similar).

How do you all handle this? Are you happy exposing fully privileged roles?

[Olle Östlund]

I just want to add what the Jooq user-guide says regarding this:

    Replace the username with whatever user has the appropriate privileges to query the database meta data.

Given that a typical user has no idea of how the Jooq database adapter code (PostgreSQL in my case) is querying the database for the table meta-data, it is not an easy task to find out the minimal appropriate privileges. I've had a quick go at it, looking at the PostgreSQL specific Jooq code, but all I can say it would not be a simple and quick task to do. I would advise that the authors of the Jooq database-specific adapter code spend some time on this issue and give users some advise on how to set up suitable privileges for a code-generation role/user.

[Lukas Eder]

Hi Olle

Thanks for your message. We have a pending feature request to document the required grants for each dialect:

https://github.com/jOOQ/jOOQ/issues/10820

It's a bit of work. There are weird details like for example in PostgreSQL, you can query quite a lot of things in the pg_catalog without even having any privileges to the objects, whereas some of the information_schema tables are quite useless if you don't have excessive privileges. For example, you cannot see anything in information_schema.table_constraints, unless you have some write privilege (!):

https://github.com/jOOQ/jOOQ/issues/10824

This is required by the SQL standard and implemented "correctly" in PostgreSQL, even if I completely fail to see the logic behind this. So, you might have run into such a case. It's a painful tradeoff between:

1. Being able to rely on backwards compatible information_schema, which might not contain the desired data

2. Rely on the frequently breaking pg_catalog, which contains all the desired data, but in a very low level format

Obviously, we'd also like to offer being able to generate code with the least amount of privileges possible. When the above issue #10820 is implemented, we'll be able to make formal guarantees as we'll be integration testing things with the lowest possible set of privileges rather than working with elevated users right now. Until then, there's no such guarantee, and you need to add more privileges.

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1c401d46-7c21-4b3f-b76d-ea8a50ffb90cn%40googlegroups.com.