Grants to schema functions / custom SQL queries

[petteja]

What problem are you facing?

We're using RDS for PostgreSQL and after using provider-sql to install aws_s3 extension we have to grant usage to the extension manually.
grant all on all functions in schema aws_s3 to <user>;

How could Crossplane help solve your problem?

Does it make sense to implement functionallity in extension.postgresql to give grants? Or have possibility to define custom queries which is run when reconciling? I know a few other use-cases where running custom SQL makes sense, like revoke all on database <database> from public after creating a database.

[jmalloc]

We are facing the same issue with the aws_s3 extension. I have not found a way to grant schema-specific privileges.

I think I had much the same kind of solution I'm mind:

• Grant MR gains ability to target objects other than the database (such as schemas, tables, etc)
• Add separate SchemaGrant MR (and by extension TableGrant, etc, potentially)

I also thought executing arbitrary SQL would be useful, but probably doesn't really fit the notion of a reconciliation loop particularly well. The most robust approach would probably be to execute the queries on each reconciliation, which would be fine if the queries were guaranteed to be idempotent, but could lead to some pretty expected results otherwise.

EDIT: Linking to some Issues in the same vein / that might also solve the aws_s3 problem:

• How to do "GRANT ALL ON SCHEMA public TO demo;" in postgres grant? #145
• Create a Database and give the ownership to a new role (ALL PRIVILEGES) #164

[jmalloc]

I believe the following queries grant the minimal permissions required to use the aws_s3 extension, so ideally the solution would allow us to grant this and nothing more.

GRANT USAGE ON schema aws_s3 TO <user>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA aws_s3 TO <user>;

[Bastichou]

Many duplicated on this topic, merged on #217
This issue will be closed