kloeckner-i / db-operator Goto Github PK

We use a postgres exporter and because of the open connection to the database the db-operator can't delete the database because "it is used".

It would be cool if it could be deleted "WITH (FORCE)" here: https://github.com/kloeckner-i/db-operator/blob/master/pkg/utils/database/postgres.go#L210 (maybe as an option in the CRD)

@allanger
There are currently no info - except in the issues - that development has moved to https://github.com/db-operator/db-operator

I suggest that this repo is archived and the readme.md file updated to link to the new repo.

In cases where we don't know the exact database version used on a host, it would be practical to have access to this value from the secret template for the database resource.

Operator shows error POSTGRES_DB key does not exist in secret data, secret created with just one field - POSTGRES_HOST. After downgrading (from helm chart 1.4.0 to 1.3.0) it works.

I ran into a situation where failing to create a database leaks connections, eventually consuming all the connections available to the MySQL server.

I am using the same setup as in #52 - MySQL 5.6, generic DB instance. The MySQL database itself is create - I checked using the mysql shell but something else fails:

time="2021-05-10T23:35:38Z" level=info msg="Instance: name=example-generic Running"
time="2021-05-10T23:35:44Z" level=info msg="DB: namespace=lmn-system, name=ex start Creating"
time="2021-05-10T23:35:44Z" level=error msg="DB: namespace=lmn-system, name=ex failed creating database"
time="2021-05-10T23:35:44Z" level=error msg="DB: namespace=lmn-system, name=ex failed creating database - Error 1044: Access denied for user 'USER'@'HOSTSPEC' to database 'lmn_system_ex'"
time="2021-05-10T23:35:44Z" level=error msg="DB: namespace=lmn-system, name=ex failed Creating - Error 1044: Access denied for user 'USER'@'HOSTSPEC' to database 'lmn_system_ex'"

(I've replaced the actual user and host specification with placeholders).

After some point this ends up failing completely

time="2021-05-10T23:37:38Z" level=info msg="Instance: name=example-generic Running"
time="2021-05-10T23:37:45Z" level=info msg="DB: namespace=lmn-system, name=ex start Creating"
time="2021-05-10T23:37:45Z" level=error msg="DB: namespace=lmn-system, name=ex failed creating database"
time="2021-05-10T23:37:45Z" level=error msg="DB: namespace=lmn-system, name=ex failed creating database - Error 1040: Too many connections"
time="2021-05-10T23:37:45Z" level=error msg="DB: namespace=lmn-system, name=ex failed Creating - Error 1040: Too many connections"

The reason for the failure is something I will look into (perhaps again MySQL being too old) but I think the connection leak should definitely be fixed.

It would be amazing if we could change the generated secret for a database and map its values similar to how the connectionStringTemplate works, but for all the values. This has the advantage that we could have the keys for the database variable. It makes it easier for writing deployments.

Maybe something like this

apiVersion: "kci.rocks/v1alpha1"
kind: "Database"
metadata:
  name: "example-db"
spec:
  secretName: example-db-credentials # DB Operator will create secret with this name. it contains db name, user, password
  instance: example-gsql # This has to be match with DbInstance name
  deletionProtected: false # Protection to not delete database when custom resource is deleted
  backup:
    enable: false # turn it to true when you want to use back up feature. currently only support postgres
    cron: "0 0 * * *"
  secretEnvTemplate:  |
    DATABASE_HOST={{ .DatabaseHost }}
    DATABASE_USERNAME={{ .UserName }}
    DATABASE_PASSWORD={{ .Password }}
    DATABASE_URL=pgsql:{{ .Protocol }}://{{ .UserName }}:{{ .Password }}@{{ .DatabaseHost }}:{{ .DatabasePort }}/{{ .DatabaseName }}?version={{ .DatabaseVersion }} 
    DATABASE_NAME={{ .DatabaseName }}
    DATABASE_PORT={{ .DatabasePort }}
    DATABASE_VERSION={{ .DatabaseVersion }} 

Reading through the docs it seems that only GCP database or a generic DB (i.e. server already setup) is currently support. Is that correct?

The current Helm chart isn't compatible with Helm 3 due to the CRDs being managed as resources. A quick and easy fix for this would be to add a value to conditionally create the CRDs. A better solution would be to move the CRDs into a crds folder, you could still add them to the resources but they could also be managed correctly by Helm 3 (Helm 2 is long since deprecated). The best solution would be to only support Helm 3 by updating the chart API version and removing the CRDs from the resources.

In the meantime, I want to be able to use this operator as it looks great and is exactly what I've been putting off building myself, I'm going to create an idiomatic helm v3 chart in my own Helm repo. I'd be more than happy to submit this back as a PR once it's done if you want it?

Hi, if I force my helm release to override the image.tag with 1.0.0 (without "v" prefix) then kubernetes tries to deploy image ghcr.io/kloeckner-i/db-operator:v1.0.0 (with "v" prefix)

helm command: helm upgrade -i db-operator db-operator/db-operator --set-string "image.tag=1.0.0"

The 1.0.0 image in your registry under https://github.com/kloeckner-i/db-operator/pkgs/container/db-operator does not have the "v" prefix, so image pull fails.

Hey. I’ve also been developing a db operator. For me it’s mostly started as a hobby project. But I’m now working towards making it usable in our company. We have similar goals, similar design. A list of things I’ve implemented:

• kuttl tests
• Unit test coverage of 45%
• Mysql support
• Postgres support
• Initial support for cockroach
• Backups to s3
• Restore from s3
• Db copy jobs and cronjobs
• Privileges for Postgres and MySQL are WIP
• adding support for ecs and azure blobs would be easy with the architecture I have

https://github.com/obeleh/db-operator Docs are behind. But would you be interested in a call?

When a PostgreSQL database is created a "public" schema will be created automatically. And all other users on the same database instance have full access to this public schema.

When a new database is created, PostgreSQL by default creates a schema named public and grants access on this schema to a backend role named public. All new users and roles are by default granted this public role, and therefore can create objects in the public schema.
https://aws.amazon.com/de/blogs/database/managing-postgresql-users-and-roles/

I think that's true for PostgreSQL in general and does not only apply to AWS RDS.

PostgreSQL recommends not using the public schema anymore and dropping it completely:

https://wiki.postgresql.org/wiki/Database_Schema_Recommendations_for_an_Application

I really like this db-operator and would like to see if there's an elegant solution to:

• remove the "public" schema after the database has been created (configurable in the "Database" CRD, e.g. dropPublicSchema: true)
• create a new schema (configurable in the "Database" CRD, e.g. createSchema: <schemaName>)
• (and maybe also revoke access to other databases' public schemas for user that are being generated)

DatabaseHost and DatabasePort in a connection string map to proxy data, as seen in

This makes the whole connection string incompatible with generic databases, as those do not have any proxying logic.

Mysql-Operators (like https://hub.helm.sh/charts/stable/mysql or https://github.com/oracle/mysql-operator/tree/master/mysql-operator) generate a secret containing the generated password. How can I get this secret into db-operator? The keys do not macht. Could you please give some hints or adapt the code?

Same question in so: https://stackoverflow.com/questions/61211378/use-kubernetes-secret-in-another-one

How to configure the database configuration file

mysql.cnf and postgresql.conf pg_hba.conf

It would be nice to be able to set the ownership of the created database to the created user.

I'm migrating to the integrated monitoring because of #138

I see the following issues:

Duplicate definition of admin secrets

Not a big deal, just a bit duplication of code when using existingAdminSecret. Monitoring templates will just look at adminUserSecret. If the existing one is defined, it could use that one?

Structure of secret hardcoded

For some reason, while existingAdminSecret works, the same secret doesn't for the pg_exporter.

I'm using the zalando postgres operator and it creates secrets with username and password as keys. The monitoring pod expects user and password:

If I'm not mistaken, the operator itself has a fallback to use the "postgres" user in that case, which apparently works in my setup:

https://github.com/kloeckner-i/db-operator/blame/aefb00fb1ac9e37db02706f7e2209c8c26e7a23e/pkg/utils/database/postgres.go#L356-L365

Changing the secret in the operator is not possible yet: zalando/postgres-operator#1746

I think, that it I provide an unexpected values to templater, I should see expected ones in logs and in database description. Now it's only showing that a field can't be evaluated.

Currently, we have the webhook as a part of an operator, and it would be nice to have two containers, one with the operator only, and the other with webhook

Hi,

Thank you for all of your work. We use your operator and its very nice. I looked at the docs and I cannot find anywhere were postgres version compatibility is listed. I did try the operator in a new test cluster today in which i had the postgres DB set to 15. I noticed that the databases were created as normal and the dbinstance is healthy. Also login to the databases with the user/password created by the operator is fine. However, when I try to set up one of the databases (in this case its grafana) I get errors when i try to create a table saying

 PERMISSION DENIED FOR SCHEMA PUBLIC

I did a bit of research and it looks as if the default privs for the public schema are much tighter in postgres 15. See this article:

https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

I tried the grant statement in that article and it fixed my issue. I wonder if this grant needs to be added to the operator if the version is 15?

I do have to mention I am still using helm release 1.3.4 which i believe corresponds to 1.6.5 app version so I am not up to current, this may have been fixed since then but I am unable to find much in the way of docs about that.

Thanks in advance for your assistance!

Good afternoon. I would like to submit a small feature request. We would like the ability to pass pod labels through to the deployment template as part of a Helm release. Currently, though, there doesn't seem to be a way to do that. For example, in a sample values.yaml:

podLabels:
  foo: bar
  baz: quux

I plan on submitting a pull request shortly, but let me know if there's anything specific I need to do or if you need any more information.

Thank you.

First of all, I love the operator. It solves a lot of problems we experienced, running databases on kubernetes.

There is only one fragment missing for us. Often times applications require database credentials in the form of a database url like mysql://user:secret@localhost/mydb or jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC.

It would be awesome if the credentials secret contained such an url, so we can directly mount the secret as env var. As the URL format is application dependant, a go template like mysql://{{ .user }}:{{ .password }}@{{ .host }}:{{ .port }}/{{ .database }} could be provided via the Database custom resource.

What do think of this?

Currently I can not see which mysql database is from which project.

When I undestand stringShortener() in the code correctly, the name of the database is 16 characters of md5 hash.

I suggest to allow more verbose names. To remain BC compatible, shortening should be configurable in spec of Database. (Maybe not boolean, for potential feature variants.)

My suggestion for when configured verbose name:
$dbName | trunc 44 | lower | replaceAll("[^a-z0-9]", "_") ~ '_' ~ $dbName | md5 | trunc 16)
(dummy template code since I can not write GO)

Permitted characters in unquoted identifiers (from [1]):

ASCII: [0-9,a-z,A-Z$_]
(Extended: U+0080 .. U+FFFF )

lowercase (from [2]):

To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names.

max length 63 (from [3])

1: https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
2: https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
3: https://dev.mysql.com/doc/refman/5.7/en/identifier-length.html

When the db-operator is creating the connection string and inserting it into the secret the value is wrong.

It seems to omit the RDS endpoint name with :0 in the string path.

It should read <rds-endpoint-name>.<region>.rds.amazon.com/5432

Sometimes deleting the secret and letting the db-operator recreate it fixes but this is not always the case.

Seeing this in chart versions 1.1.6 and 0.3.0
Without upgrading to the latest is there anything clearly wrong?

CustomResourceDefinition in v1beta1 is no longer served in kubernetes v1.22 so this can't be installed in such clusters.

Thanks for this awesome operator!
Some applications do not initialize their database on their own.
It would be very convenient if the CRD included an optional parameter to specify custom SQL queries to be run on database creation.
In my opinion the "contract" should be that those queries can be run several times (e.g. on updates to the custom resource).
The queries need to include something like CREATE TABLE IF NOT EXISTS to handle this.
I guess this should not be too hard to implement and is a nice addition for convenient usage.
Thanks for the consideration.

It would be great if this could support cockroach DB, it is amazing database for scalability and resiliency. Currently when used it gives some errors but it should be a relatively easy addition as it uses the Postgres wire protocol and very similar syntax

This operator seems to fit my usecase of managing the databases in my k8s cluster. However as this cluster is running on ARM nodes, I cannot use it currently. Is it possible to publish docker images for ARM as well?
Thanks!

I see following in logs:

time="2021-12-10T22:47:08Z" level=info msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox spec changed"
time="2021-12-10T22:47:09Z" level=info msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox initialized"
time="2021-12-10T22:47:09Z" level=info msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox start Creating"
time="2021-12-10T22:47:09Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database"
time="2021-12-10T22:47:09Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
time="2021-12-10T22:47:09Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed Creating - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
2021-12-10T22:47:09.151Z DEBUG controller-runtime.manager.events Warning {"object": {"kind":"Database","namespace":"stepshealth-sandbox","name":"db-stepshealth-sandbox","uid":"f69c84fb-0ab8-4203-bce6-3b315bbb9d33","apiVersion":"kci.rocks/v1alpha1","resourceVersion":"259339459"}, "reason": "FailedCreating", "message": "Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"}
time="2021-12-10T22:47:29Z" level=info msg="Instance: name=db3-generic Running"
time="2021-12-10T22:47:29Z" level=info msg="Instance: name=db3-generic Running"
time="2021-12-10T22:47:39Z" level=info msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox start Creating"
time="2021-12-10T22:47:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database"
time="2021-12-10T22:47:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
time="2021-12-10T22:47:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed Creating - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
2021-12-10T22:47:39.748Z DEBUG controller-runtime.manager.events Warning {"object": {"kind":"Database","namespace":"stepshealth-sandbox","name":"db-stepshealth-sandbox","uid":"f69c84fb-0ab8-4203-bce6-3b315bbb9d33","apiVersion":"kci.rocks/v1alpha1","resourceVersion":"259339459"}, "reason": "FailedCreating", "message": "Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"}
time="2021-12-10T22:48:29Z" level=info msg="Instance: name=db3-generic Running"
time="2021-12-10T22:48:29Z" level=info msg="Instance: name=db3-generic Running"
time="2021-12-10T22:48:39Z" level=info msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox start Creating"
time="2021-12-10T22:48:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database"
time="2021-12-10T22:48:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed creating database - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
time="2021-12-10T22:48:39Z" level=error msg="DB: namespace=stepshealth-sandbox, name=db-stepshealth-sandbox failed Creating - Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"
2021-12-10T22:48:39.841Z DEBUG controller-runtime.manager.events Warning {"object": {"kind":"Database","namespace":"stepshealth-sandbox","name":"db-stepshealth-sandbox","uid":"f69c84fb-0ab8-4203-bce6-3b315bbb9d33","apiVersion":"kci.rocks/v1alpha1","resourceVersion":"259339459"}, "reason": "FailedCreating", "message": "Error 1044: Access denied for user 'admin'@'%' to database 'stepshealth_sandbox_db_stepshealth_sandbox'"}

The database "stepshealth_sandbox_db_stepshealth_sandbox" was created on target my sql server. Secret with credentials is created too, but there was no ConfigMap with connection parameters created. Databse resource has state "Creating",

Would it be possible to add an ability (a custom field in the CRD?) to create PostgreSQL database using what's known as a template database? For reference, template databases are a feature in PostgreSQL and can be used like this:

createdb pull-request-1234-db --template=staging-db

For context, our use case echoes what stated in the readme, e.g.:

[...] support the on demand creation of test environments in CI/CD pipelines.

In our case, as part of CI/CD pipeline, when developers submit pull requests, we are creating what we call "review apps", e.g. a fully functional application built from a code in respective pull request. We would like to create such app & pre-create a database for it. The database for PR is a copy of our staging database.

Currently we have some open requests for features that we probably won't implement, because we either don't have a use-case or we don't want to make the code too complicated and overload the operator with features.

Maybe creating a plugin system would make it easier?

For example we could use phases as triggers for executing plug-ins, then initialization queries could be implemented as one, also s3 backups could be implemented this way, I think.

I'm not sure what exactly it should look like, but I think if we think it's a good idea, we can come up with something

It would let users have features without understanding the code base, and we could keep it simple

In the log of the operator, there is the following output:
level=error msg="Instance: name=my-db-instance failed to get instance admin user secret /"

In the code I see there should be a namespace before "/" and the name after. Why is it empy? Do I set it wrong?

my file my-db-instance.yaml:

apiVersion: kci.rocks/v1alpha1
kind: DbInstance
metadata:
  name: my-db-instance
spec:
  adminUserSecret: db-root-secret
  engine: mysql
  generic:
    host: mysql.my-db-ns.svc.cluster.local
    port: 3306

With kubectl get secret db-root-secret I get the secret. I also tried setting in my-db-instance.yaml adminUserSecret: default/db-root-secret and adminUserSecret: { namespace: default, name: db-root-secret}, but I always get the same error message.

I think there is something wrong with the helm chart v0.6.3, it tries to pull the cloudish-sql image...
The helm chart code on github doesn't seem to match the generated resources

The latest version in https://kloeckner-i.github.io/db-operator/helm/ is apparently v0.5.0

But this repository has a releases up to v0.9.0.

Would be cool if they are all easily available.

It would be nice if db-operator could support custom database and user name in the database resource.

Right now database and username is build like this: <NAMESPACE>_<DB_INSTANCE_NAME>. This also means there can only be one database per db instance per namespace.

We've added this in our fork and are open to contribute this upstream.

reciprocity@941783e

apiVersion: "kci.rocks/v1alpha1"
kind: "Database"
metadata:
  name: "example-db"
spec:
  secretName: example-db-credentials
  instance: example-gsql
  deletionProtected: false
  database: customdbname
  user: customuser

What would be the best way to add tests for this? Duplicate all existing tests to also cover this?

It'd be great if this operator could support S3 compliant backup locations; specifically S3 for AWS users and Minio for a self contained cluster or testing purposes.

I am just trying out this operator and so far it looks great!

I have hit a little problem with creating a database using the generic backend, probably because of an old MySQL version (5.6.43)

time="2021-05-10T22:15:26Z" level=error msg="DB: namespace=lmn-system, name=example-db failed Creating - Error 1470: String 'lmn_system_example_db' is too long for user name (should be no longer than 16)"

I understand that this is quite an old version, and rather than asking everyone to live with much shorter names, perhaps one of these two solutions can be applied:

• be made configurable so I can set them in the helm chart
• auto-detected at runtime based on the MySQL version

Thanks for considering.

When updating the password for an existing user. A syntax error occurs.

time="2021-11-13T09:52:53Z" level=error msg="DB: namespace=wms-staging, name=app failed creating database"
time="2021-11-13T09:52:53Z" level=error msg="DB: namespace=wms-staging, name=app failed creating database - Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('xxxxxxxxx')' at line 1"
time="2021-11-13T09:52:53Z" level=error msg="DB: namespace=wms-staging, name=app failed Creating - Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('x_46F6w466Iq9m-vRpC9')' at line 1"
2021-11-13T09:52:53.242Z DEBUG controller-runtime.manager.events Warning {"object": {"kind":"Database","namespace":"wms-staging","name":"app","uid":"cb6881ad-dd37-495a-84de-6ee9f43eca57","apiVersion":"kci.rocks/v1alpha1","resourceVersion":"37542112"}, "reason": "FailedCreating", "message": "Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('xxxxxxxxx')' at line 1"}

The query which is executed is:
SET PASSWORD FOR wms_staging_app = PASSWORD('xxxxxxxxx')
But It seems = PASSWORD( does not work anymore in mysql 8

https://dev.mysql.com/doc/refman/5.7/en/set-password.html
vs
https://dev.mysql.com/doc/refman/8.0/en/set-password.html

Hello! After endless googling for K8s MySQL operators, I think this is the one for me!
I tested it out, and was able to successfully create a database (schema), and then delete it. However, after deleting, I am still left with the database ConfigMap and Secret that the operator created for the database that it created (and then deleted) — is this by design? I had expected it to clean up completely, since it removed the database and user from the database server.

Hey, it would be nice if the resources crated by the operator had an ownerReference (e.g. the secrets referencing the Database resource in .metadata.ownerReference

Hi Team, I have updated db-operator to the latest version and caught the next:
The latest version of db-operator re-creates the whole Database Secret on "PG_PASSWORD Update Event".
By this action, db-operator erases 3d party metadata that might be inside the existing secret, like customer-specific annotations or secret's key like conjur-map key used in the conjur k8s-secrets-provider solution.
It would be nice to update only the required "Database Secret Keys" instead of re-creating the whole secret resource.
Thanks.

I am getting the following error

time="2020-09-21T22:28:53Z" level=info msg="Instance: name=shared-postgres Running"
time="2020-09-21T22:28:53Z" level=info msg="Instance: name=cockroachdb Creating"
time="2020-09-21T22:28:53Z" level=error msg="Instance: name=cockroachdb failed creating instance - creating generic db instance is not yet implimented"
time="2020-09-21T22:28:53Z" level=error msg="Instance: name=cockroachdb instance creation failed - creating generic db instance is not yet implimented"

Is it possible to enable verbose logging to get a better error?