Automated user/role removal feature for postgresql CR

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using?
  registry.opensource.zalan.do/acid/postgres-operator:v1.8.2
• Where do you run it - cloud or metal? Kubernetes or OpenShift? [AWS K8s | GCP ... | Bare Metal K8s]
  Bare Metal K8s
• Are you running Postgres Operator in production? [yes | no]
  no
• Type of issue? [Bug report, question, feature request, etc.]
  question, feature request

Hello,

Overview:

• In PostgresTeams feature there is an option to enable enable_team_member_deprecation which will mark deleted users/roles with appended prefix. Those users can be fetched and deleted via custom script. Users don't have generated passwords and should use OAuth2 token in order to access database.

• In postgresql CR users/roles are added and have generated password in secret {namespace}.{username}.{clustername}.credentials.postgresql.acid.zalan.do but removal feature for the users/roles seems to be missing.

Questions:

• Is PostgresTeams CR and postgresql CR somehow related and user/role removal feature can be used for postgresql CR in similar way?

• Is there currently any other way how to automatically remove user/role from database when user is removed from postgresql CR as well without the usage of any custom scripts?

In documentation there is mentioned:

• The Postgres Operator does not delete database roles when users are removed from manifests.
  (I'm really just trying my luck here.)

Requested Feature:

Give as an option/enabler to automatically delete users/roles from database when they are removed from postgresql CR without usage of any additional custom scripts:

• Check between removed users in postgresql CR and database will be scheduled based on resync_period variable.
• When users/roles are removed from postgresql CR the same roles/users will DROP in database.
• The secret located in {namespace}.{username}.{clustername}.credentials.postgresql.acid.zalan.do will be removed safely with the role/user itself.
• User removal should be supported for user sync operations (spec pkg)

Thank you.

Start with the user sync operations in the spec package and the resync_period behavior mentioned in the request; compare how the postgresql CR and PostgresTeams feature handle removed roles. Done means removed CR users and their credentials secrets are safely deleted from the database on the configured schedule without custom scripts.