[PGSQL] Extend Grant kind to support more than database object

[Bastichou]

Support schema,objects,objectType specifications on Grant kind to grand permission on other object than the database.

This feature request will cover the following issues:

• Creating readonly role and schemas in PostgreSQL #161
• Grants to schema functions / custom SQL queries #72
• How to do "GRANT ALL ON SCHEMA public TO demo;" in postgres grant? #145

What problem are you facing?

Currently the GRANT object only support granting privileges to the Database object: GRANT %s ON DATABASE %s TO %s %s %s

Grant kind only supports granting permissions on Database object.
The Grant kind doest allow fine-grained permission on sub-object such as schemas, extensions, tables, columns etc.

How could Crossplane help solve your problem?

Introducing Schema, ObjectType and Objects to better qualify the grant specification is probably the way to do (this is how this Terraform provider is taking care of this) but this would be a breaking change (unless we set object database as default maybe).

Slack discussion link: https://crossplane.slack.com/archives/C0384KWFD52/p1739825314454329

[unni-facets]

@Bastichou I'd like to contribute to this issue. please let me know if I can work on this

[Bastichou]

I started working on this PR (realy stage draft), feel free to comment.
We can sync on Slack if you wanna give me more inputs or recommandations :)

[Bastichou]

Guys, I'm sorry but I really miss out of time and I wont be able to work as much I as would like on this. If anyone is keen to take over please do :'(

I do have a PR in draft if you want to keep going.

[benoittgt]

I started working on something similar but for the moment it's postponed at work. Love what you started @Bastichou. It's quite evolved.