clickhouse cloud: The grant operation was successful but it didn't create the expected entry in system.grants table

[daruom-md]

When using the provider on ClickHouse Cloud with >1 replica, terraform apply intermittently fails on clickhousedbops_grant_privilege even though the GRANT succeeds server-side.

The error shown is:

Error: Error Creating ClickHouse Privilege Grant

The grant operation was successful but it didn't create the expected entry
in system.grants table. This normally means there is an already granted
privilege to the same grantee that already includes the one you tried to apply.

On a single-replica service, the issue does not occur.

From reading the provider code:

• After issuing the GRANT, the provider reads system.grants to verify creation:

https://github.com/ClickHouse/terraform-provider-clickhousedbops/blob/main/internal/dbops/grantprivilege.go#L95

• With multiple replicas, that post-write read can hit a replica that hasn’t replicated the new row yet (replication lag on system.grants). The lookup then returns no row.

• The provider subsequently enters the overlap/duplicate detection branch:

https://github.com/ClickHouse/terraform-provider-clickhousedbops/blob/main/pkg/resource/grantprivilege/grantprivilege.go#L293

• That logic can conclude there is an “already included” permission and raise the error above, even though the GRANT actually succeeded.

This explains why the same plan succeeds reliably on a single-replica service but fails intermittently on multi-replica services.

Steps to Reproduce

• Use ClickHouse Cloud with ≥2 replicas.

• Provider version : 1.3.1

• Apply a plan that creates role privilege grants (global and/or per-DB).

• Observe intermittent failures on clickhousedbops_grant_privilege.

• On a 1-replica service, the same plan succeeds consistently.

Fix / Ideas

• When verifying the grant, query all replicas (e.g. via clusterAllReplicas('cluster', system.grants) ) for clickhouse cloud
• Pass a flag to use clusterAllReplicas

Many thanks

[monometa]

@rsickles Hey! Could you please share if this provider is still being actively maintained? Our Ops team has been running into several issues with it (including the one mentioned here) and is suggesting we might need to consider moving away from it 😮

We’re using a similar Terraform provider for PostgreSQL and are quite happy with it, but in this case, we’ve noticed a few major bugs. The last release was in late August, while previous updates used to come out regularly - so we just wanted to check whether there’s been any change in maintenance status.

When adopting it, we based our decision on the ClickHouse blog post that announced the general availability of the new Terraform provider:
https://clickhouse.com/blog/new-terraform-provider-manage-clickhouse-database-users-roles-and-privileges-with-code

[rsickles]

Hi @monometa and @daruom-md - thank you for your feedback here and my apologies for not following up sooner. The team is aware of this TF issue with the GRANT operation but we first need to make changes to the core clickhouse DB before we can fix this in TF (targeting in the next couple of months).

@monometa we are still actively maintaining this and our other TF provider and so I will be sure to update you here as we have more to share on our progress with this work. What other issues are you and your team facing with this provider?

[daruom-md]

Hi @rsickles
thank you for the update.

there is also another issue which appeared recently.

In ClickHouse 25.7, the way S3 privileges are stored and granted has changed.
Previously, we used:
GRANT S3 ON *.* TO role;

Starting with 25.7, this is replaced by:

GRANT READ ON S3 TO role;
GRANT WRITE ON S3 TO role;

The old S3 privilege no longer exists in system.grants. Instead, privileges are now stored as READ or WRITE on S3.

As a result, the provider currently fails because it still runs a query like:
SELECT ... FROM system.grants WHERE access_type = 'S3'

which returns no rows on 25.7 and causes privilege-related operations to break.

Impact

This is a breaking change for the provider:

The same Terraform configuration no longer works across environments running different ClickHouse versions (e.g. 25.6 vs 25.7).

We had to manually adjust our grants for 25.7, which breaks compatibility with 25.6.

Expected behaviour

The provider should detect or handle both privilege models:

On 25.6 and below
-> GRANT S3 ON . TO role

On 25.7 and above:
-> GRANT READ/WRITE ON S3 TO role

thank you

[rsickles]

Ok thanks for reporting this bug @daruom-md - I have created another ticket for us to fix this and will provide an update once we do.

[slawomir-b]

Hey @rsickles are there any updates on the S3 grant issue? We just run into the same problem at my company.

[rsickles]

Hi @slawomirbiernacki - no update yet other than the team is still planning to fix this in the next 1-2 months.

[rsickles]

@daruom-md @slawomirbiernacki the fix for the S3 grant issue is done and in the 1.6.0-alpha1 version of this provider. Please feel to try it out and give feedback! The initial request of this ticket is still being worked on.

[jorgeparavicini]

Any update on this @rsickles, it is quite frustrating working with the provider atm

[rsickles]

Hi @jorgeparavicini - we’ve resolved the S3 GRANT issue and we’re still actively working on a fix for the original GRANT issue reported by @daruom-md. Thanks for your patience here - I’ll make sure to keep this issue updated as soon as we have more progress to share.

[nunoadrego]

@daruom-md we added retries to some resources in v1.8.0-alpha1. Can you check whether they have resolved some of the intermittent issues?