Unable to Delete db on CR removal

[aroundthecode]

Hi all

I'm trying to use CR to create/delete database/users over a pre-generated Google PostreSQL instance.

Prerequisites (done manually outside operator)

• Create database instance
• create default database postgresql-operator
• add a devops-operator user
• configured secret to connect to default database using such user

Operator resource

---
apiVersion: db.movetokube.com/v1alpha1
kind: Postgres
metadata:
  name: operator-test-devops
  namespace: devops-operators
spec:
  database: test-devops-db # Name of database created in PostgreSQL
  dropOnDelete: true # Set to true if you want the operator to drop the database and role when this CR is deleted (optional) 
  masterRole: devops-operator # must match DB creator's role

Applying CR sucessfylly connects and creates test-devops-db database into my postrgresql It also creates test-devops-db-reader and test-devops-db-writer roles But once I try to remove the CR the operator fails with

[ext-postgres-operator-5d58bdc75-dhpx5 ext-postgres-operator]  {"level":"error","ts":1648634579.2694623,"logger":"controller-runtime.controller","msg":"Reconciler error","controller":"postgres-controller","request":"devops-operators/operator-test-devops","error":"pq: current user cannot be dropped","stacktrace":"github.com/go-logr/zapr.(*zapLogger).Error
/go/pkg/mod/github.com/go-logr/[email protected]/zapr.go:128\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:258\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:232\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).worker
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.Until
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:90"}

Not sure if this is linked to masterRole: devops-operator

but omitting such line or assigning it to a different role other then the one configured for connection in operator secrets results in

[ext-postgres-operator-5d58bdc75-rbf26 ext-postgres-operator]  {"level":"error","ts":1648623901.489596,"logger":"controller-runtime.controller","msg":"Reconciler error","controller":"postgres-controller","request":"devops-operators/operator-test-devops","error":"Internal error occurred: pq: must be member of role \"test-devops-db-group\"","stacktrace":"github.com/go-logr/zapr.(*zapLogger).Error
/go/pkg/mod/github.com/go-logr/[email protected]/zapr.go:128\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:258\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:232\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).worker
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.Until
/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:90"}

Which is the proper way to have Users and DB properly removed upon CR deetion? thanks in advance!

[aroundthecode]

Tested 1.1.0 version on GCP and noticed there is a typo in queries preventing them to work properly, just opened #87 to resolve the problem.