pq: must be member of role

[michalschott]

Trying to use this operator (version 1.1.1) against AWS Aurora (postgres):

apiVersion: db.movetokube.com/v1alpha1
kind: Postgres
metadata:
  name: my-db
spec:
  database: test-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: test-db-group # (optional)
  schemas: # List of schemas the operator should create in database (optional)
  - stores
  - customers
  extensions: # List of extensions that should be created in the database (optional)
  - fuzzystrmatch
  - pgcrypto
---
apiVersion: db.movetokube.com/v1alpha1
kind: PostgresUser
metadata:
  name: my-db-user
spec:
  role: username
  database: my-db       # This references the Postgres CR
  secretName: my-secret
  privileges: OWNER     # Can be OWNER/READ/WRITE

Logs from operator:

ext-postgres-operator-65bc584d44-rgqzc ext-postgres-operator {"level":"error","ts":1657720145.3735487,"logger":"controller_postgres","msg":"Could not create schema stores","Request.Namespace":"developer","Request.Name":"my-db","error":"pq: must be member of role \"test-db-group\"","stacktrace":"github.com/go-logr/zapr.(*zapLogger).Error\n\t/go/pkg/mod/github.com/go-logr/[email protected]/zapr.go:128\ngithub.com/movetokube/postgres-operator/pkg/controller/postgres.(*ReconcilePostgres).Reconcile\n\t/go/src/github.com/movetokube/postgres-operator/pkg/controller/postgres/postgres_controller.go:212\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:256\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:232\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).worker\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1\n\t/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil\n\t/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.Until\n\t/go/pkg/mod/k8s.io/[email protected]/pkg/util/wait/wait.go:90"}

[michalschott]

An update - looks like c.user is not being added properly to newly created owner role thus its unable to create schemas.

[michalschott]

This still happens in 1.2.1

[vdmkenny]

Having the same issue on 1.2.3 against Azure Managed Postgres. What is the workaround?

[apeschel]

Ran into this issue, and did some digging.

This is due to AWS RDS PostgreSQL not allowing users access to the normal Postgres superuser role. Instead, AWS provides an rds_superuser role, which has many limitations that the superuser role does not. Some of these limitations don't really make any sense, and AWS support confirmed with me that none of the limitations are publicly documented.

However, AWS support was able to specifically confirm that the rds_superuser role does not have the ability to both create and set the owner for a schema in a single command, unless the rds_superuser is a member of the role that it is setting as the owner. That means this will not work:

CREATE SCHEMA IF NOT EXISTS stores AUTHORIZATION "test-db-group";

The work around is to break it into two pieces as follows:

CREATE SCHEMA IF NOT EXISTS stores;
ALTER SCHEMA stores OWNER TO "test-db-group";

I think it should be an easy patch, I'll see if I can create a PR shortly.