crossplane-contrib / crossplane-contrib/provider-sql
Extension's schema field is defined in the API but never used - extensions always install into the connection's default schema
- Dominant language
- Go
- Stars
- 154
- Forks
- 119
- Avg merge
- 6d 17h
- Merged PRs (30d)
- 8
Description
## What happened
`ExtensionParameters.Schema` is documented as `// Schema for extension install.` and is a valid, accepted field on the `Extension` resource (`postgresql.sql.crossplane.io/v1alpha1`, both cluster and namespaced variants). However, setting it has no effect: the extension is always installed into whatever schema is first in the connecting role's `search_path` (typically `public`), regardless of what `schema` is set to.
## Root cause
`pkg/controller/cluster/postgresql/extension/reconciler.go`'s `Create()` never references `mg.Spec.ForProvider.Schema`:
```go
func (c *external) Create(ctx context.Context, mg *v1alpha1.Extension) (managed.ExternalCreation, error) {
var b strings.Builder
b.WriteString("CREATE EXTENSION IF NOT EXISTS ")
b.WriteString(pq.QuoteIdentifier(mg.Spec.ForProvider.Extension))
if mg.Spec.ForProvider.Version != nil {
b.WriteString(" WITH VERSION ")
b.WriteString(pq.QuoteIdentifier(*mg.Spec.ForProvider.Version))
}
return managed.ExternalCreation{}, errors.Wrap(c.db.Exec(ctx, xsql.Query{String: b.String()}), errCreateExtension)
}
```
No `SCHEMA` clause is ever appended, even though `CREATE EXTENSION ... SCHEMA ` is valid PostgreSQL syntax. The field is likewise unused in `Observe()`, `Update()`, `upToDate()`, and `lateInit()` - it plays no role anywhere in the reconciler.
This isn't a recent regression: the field has been unused since the very first commit that introduced the `Extension` resource (`3495d110`, April 2021), across every release since, including current `v0.16.1`. Same in the namespaced variant (`pkg/controller/namespaced/postgresql/extension/reconciler.go`).
## Reproduction
```yaml
apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Extension
metadata:
name: postgis-example
spec:
forProvider:
extension: postgis
schema: my_schema
database: mydb
databaseSelector: ...
providerConfigRef:
name: default
```
Expected: `postgis` installs into `my_schema`.
Actual: `postgis` installs into `public` (or whatever the connection's default search_path resolves to). `\dx+ postgis` shows `extnamespace = public`.
## Why this matters (and why `ALTER ... SET SCHEMA` isn't a workaround)
For non-relocatable extensions (e.g. `postgis`, which sets `relocatable = false` in its control file), there is no way to fix this after the fact - `ALTER EXTENSION postgis SET SCHEMA ...` fails with `extension "postgis" does not support SET SCHEMA`. The schema can only be set at `CREATE EXTENSION` time, which means this field's being unimplemented makes schema-scoped installs of non-relocatable extensions impossible via this provider today.
## Suggested fix
In `Create()` (`pkg/controller/cluster/postgresql/extension/reconciler.go` and the namespaced equivalent), append `SCHEMA ` to the `CREATE EXTENSION` statement when `mg.Spec.ForProvider.Schema != nil`, mirroring how `Version` is handled just below it. `Observe()`/`upToDate()` would also need to compare the installed extension's actual `extnamespace` against the desired schema (e.g. via `SELECT extnamespace::regnamespace FROM pg_extension WHERE extname = $1`) to detect drift and support `lateInit()`.
## Versions checked
- v0.14.0 - same, unimplemented
- v0.16.1 (latest) - same, unimplemented
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with Create() and Observe() in pkg/controller/cluster/postgresql/extension/reconciler.go, then compare the namespaced equivalent at pkg/controller/namespaced/postgresql/extension/reconciler.go. Trace how Schema and Version are handled and inspect the related tests. Done means the requested schema is used at install time and the reconciler can detect when the installed extension's schema differs from the desired one.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100