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 On Mon, Jan 4, 2021 at 4:39 PM Olle Östlund <[email protected]> wrote: > 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. > > måndag 4 januari 2021 kl. 15:47:59 UTC+1 skrev 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? >> > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/1c401d46-7c21-4b3f-b76d-ea8a50ffb90cn%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/1c401d46-7c21-4b3f-b76d-ea8a50ffb90cn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4fd07Mrg1D7_PGqnnYckY%2B6Xqi784v5WCDzSOb8KTwnQ%40mail.gmail.com.
