Access denied for user 'shibboleth'@'%' to database 'shibboleth'

[krunalp983]

hallo

I am following your tutorial to run a How to replicate MariaDB in K8s.

This is my configuration file

when I try to connect a database to my web app running in Kubernetes which shibboleth it gives me an error

Access denied for user 'shibboleth'@'%' to database 'shibboleth.'

this is basically a permission issue from a database side

it would be good if you help me

# ConfigMap holding information about configuration files for primary/secondary and dockerinit
apiVersion: v1
kind: ConfigMap
metadata:
  name: mariadb-configmap
data:

  primary.cnf: |
    [mariadb]
    log-bin                         # enable binary logging
    log-basename=my-mariadb         # used to be independent of hostname changes (otherwise is in datadir/mysql)

  replica.cnf: |
    [mariadb]
    log-basename=my-mariadb         # used to be independent of hostname changes (otherwise is in datadir/mysql)

  primary.sql: |
    CREATE USER 'repluser'@'%' IDENTIFIED BY 'replsecret';
    GRANT REPLICATION REPLICA ON *.* TO 'repluser'@'%';
    SET NAMES 'utf8';
    SET CHARACTER SET utf8;
    CHARSET utf8;
    CREATE DATABASE IF NOT EXISTS shibboleth CHARACTER SET=utf8;
    USE shibboleth;

    CREATE TABLE IF NOT EXISTS StorageRecords (
    context varchar(255) NOT NULL,
    id varchar(255) NOT NULL,
    expires bigint(20) DEFAULT NULL,
    value longtext NOT NULL,
    version bigint(20) NOT NULL,
    PRIMARY KEY (context, id)
    ) COLLATE utf8_bin;

    CREATE TABLE IF NOT EXISTS shibpid (
    localEntity VARCHAR(255) NOT NULL,
    peerEntity VARCHAR(255) NOT NULL,
    persistentId VARCHAR(50) NOT NULL,
    principalName VARCHAR(50) NOT NULL,
    localId VARCHAR(50) NOT NULL,
    peerProvidedId VARCHAR(50) NULL,
    creationDate TIMESTAMP NOT NULL,
    deactivationDate TIMESTAMP NULL,
    PRIMARY KEY (localEntity, peerEntity, persistentId)
    );
    CREATE USER 'shibboleth'@'localhost' IDENTIFIED BY 'psltest';
    GRANT ALL PRIVILEGES ON shibboleth.* TO 'shibboleth'@'localhost';
    FLUSH PRIVILEGES;

  secondary.sql: |
    # We have to know name of sts (`mariadb-sts`) and 
    # service `mariadb-service` in advance as an FQDN.
    # No need to use master_port
    CHANGE MASTER TO 
    MASTER_HOST='mariadb-sts-0.mariadb-service.default.svc.cluster.local',
    MASTER_USER='repluser',
    MASTER_PASSWORD='replsecret',
    MASTER_CONNECT_RETRY=10;

# Secret holds information about root password
---
apiVersion: v1
kind: Service
metadata:
  name: mariadb-service
  labels:
    app: mariadb
spec:
  ports:
  - port: 3306
    name: mariadb-port
  clusterIP: None
  selector:
    app: mariadb

# Statefulset
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mariadb-sts
spec:
  serviceName: "mariadb-service"
  replicas: 2
  selector:
    matchLabels:
      app: mariadb
  template:
    metadata:
      labels:
        app: mariadb
    spec:
      initContainers:
      - name: init-mariadb
        image: mariadb
        imagePullPolicy: Always
        command:
        - bash
        - "-c"
        - |
          set -ex
          echo 'Starting init-mariadb';
          # Check config map to directory that already exists 
          # (but must be used as a volume for main container)
          ls /mnt/config-map
          # Statefulset has sticky identity, number should be last
          [[ `hostname` =~ -([0-9]+)$ ]] || exit 1
          ordinal=${BASH_REMATCH[1]}
          # Copy appropriate conf.d files from config-map to 
          # mariadb-config volume (emptyDir) depending on pod number
          if [[ $ordinal -eq 0 ]]; then
            # This file holds SQL for connecting to primary
            cp /mnt/config-map/primary.cnf /etc/mysql/conf.d/server-id.cnf
            # Create the users needed for replication on primary on a volume
            # initdb (emptyDir)
            cp /mnt/config-map/primary.sql /docker-entrypoint-initdb.d
          else
            # This file holds SQL for connecting to secondary
            cp /mnt/config-map/replica.cnf /etc/mysql/conf.d/server-id.cnf
            # On replicas use secondary configuration on initdb volume
            cp /mnt/config-map/secondary.sql /docker-entrypoint-initdb.d
          fi
          # Add an offset to avoid reserved server-id=0 value.
          echo server-id=$((3000 + $ordinal)) >> etc/mysql/conf.d/server-id.cnf
          ls /etc/mysql/conf.d/
          cat /etc/mysql/conf.d/server-id.cnf
        volumeMounts:
          - name: mariadb-config-map
            mountPath: /mnt/config-map
          - name: mariadb-config
            mountPath: /etc/mysql/conf.d/
          - name: initdb
            mountPath: /docker-entrypoint-initdb.d
      restartPolicy: Always
      containers:
      - name: mariadb
        image: mariadb
        ports:
        - containerPort: 3306
          name: mariadb-port
        env:
        - name: MARIADB_USER
          value: shibboleth
        - name: MARIADB_DATABASE
          value: shibboleth
        # Using Secrets
        - name: MARIADB_ROOT_PASSWORD
          value: psltest
        - name: MYSQL_INITDB_SKIP_TZINFO
          value: "1"
        # Mount volume from persistent volume claim
        volumeMounts:
        - name: datadir
          mountPath: /var/lib/mysql/
        - name: mariadb-config
          mountPath: /etc/mysql/conf.d/
        - name: initdb
          mountPath: /docker-entrypoint-initdb.d
      volumes:
      - name: mariadb-config-map
        configMap:
          name: mariadb-configmap
          #defaultMode: 0544
      - name: mariadb-config
        emptyDir: {}
      - name: initdb
        emptyDir: {}

  volumeClaimTemplates:
  - metadata:
      name: datadir
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: ionos-enterprise-ssd
      resources:
        requests:
          storage: 10Gi

[grooverdan]

please use https://mariadb.com/kb/en/getting-help-with-mariadb/ as a way of getting more prompt help.

Without a MARIADB_PASSWORD for your MARIADB_USER the user won't be created.

Also if your datadir is already initialized, no database or users users are created and initdb won't be run.