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

Open
#440 0 comments 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.