GitLab CI - MySQL - Access denied for user 'debian-sys-maint'@'172.17.0.3' (using password: YES)

JasonSnapIT · January 7, 2020, 9:16pm

Hello all, currently trying to troubleshoot my gitlab-ci setup for a Ruby on Rails project that uses a MySQL -v 5.7 database. Running into the below error:

Mysql2::Error::ConnectionError: Access denied for user 'debian-sys-maint'@'172.17.0.3' (using password: YES)

I store the username and password as environment variables in the GitLab CI / CD Variables section.

DB_USERNAME
DB_PASSWORD

I then connect to the DB and access those variables:

echo "SELECT 'OK';" | mysql --user="$DB_USERNAME" --password="$DB_PASSWORD" --host="$DB_HOST" "$MYSQL_DATABASE"

After that I run:

bundle exec rake db:create db:migrate db:seed

That’s when the error from above occurs and fails to create the DB. Of note, I’m able to connect to the standup db locally with the same user and password stored in the GitLab CI / CD environment variables.

.gitlab-ci.yml

image: 'registry.gitlab.com/lisasnapit/onboarding-app:latest'

stages:
  - build
  - mysql
  - rubocop

variables:
  LC_ALL: C.UTF-8
  LANG: en_US.UTF-8
  LANGUAGE: en_US.UTF-8

.default-cache: &default-cache
  cache:
    untracked: true
    key: suna-cache
    paths:
      - node_modules/
      - vendor/
      - public/

build:
  <<: *default-cache
  stage: build
  script:
    - ruby -v
    - node -v
    - yarn --version
    - which ruby
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
    - yarn install

mysql:
  <<: *default-cache
  stage: mysql
  variables:
    MYSQL_DATABASE: standup
    MYSQL_ROOT_PASSWORD: ''
    MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
    DB_USERNAME: $DB_USERNAME
    DB_PASSWORD: $DB_PASSWORD
    DB_HOST: 'mysql'
  services:
    - mysql:5.7
  before_script:
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
    - cp config/database.gitlab config/database.yml
  script:
    - echo "SELECT 'OK';" | mysql --user="$DB_USERNAME" --password="$DB_PASSWORD" --host="$DB_HOST" "$MYSQL_DATABASE"
    - sleep 20
    - bundle exec rake db:create db:migrate db:seed
    - bundle exec rails assets:precompile

rubocop:
  <<: *default-cache
  stage: rubocop
  before_script:
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
  script:
    - bundle exec rubocop

config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV['RAILS_MAX_THREADS'] || 5 %>
  timeout: 5000
  database: <%= ENV['DB'] %>
  username: <%= ENV['DB_USERNAME'] || 'root' %>
  password: <%= ENV['DB_PASSWORD'] %>
  host: <%= ENV['DB_HOST'] %>

development:
  <<: *default
  database: <%= ENV['DB'] || 'standup' %>

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: standup_test

production:
  <<: *default
  username: <%= ENV['DB_USERNAME'] %>

dnsmichi · January 7, 2020, 9:53pm

Hi,

maybe an origin problem with different IPs for the client. Can you show the database access for this specific user?

select host, user, password from mysql.user;

Cheers,
Michael

JasonSnapIT · January 7, 2020, 10:08pm

Output after logging in and changing to mysql DB and standup DB to check both.

mysql -u debian-sys-maint -p

password input

mysql db

use mysql;

The below command doesn’t work for me however since I use MySQL 5.7, and the password field is replaced with authentication_string.

mysql> select host, user, password from mysql.user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

standup db

use standup;

dnsmichi · January 8, 2020, 11:51am

Hi,

hmmmm, it could be that the debian-sys-maint is just for local access. AFAIK this user only exists to recover a locked out root user, and other local tasks.

If you need a user which is allowed to create a database next to the root user, create a new one which may access from an ip range. I don’t suggest to use a wildcard with %, this opens up a security problem.

Something like 'maint'@'172.17.0.0/255.255.0.0' as subnet should be sufficient.

Cheers,
Michael

JasonSnapIT · January 8, 2020, 3:35pm

Created a user maint with access to an ip range of 172.17.0.0/255.255.0.0 and utilized the same password stored in the environment variable on GitLab.

Seems to be the same output as before with the debian-sys-maint user.

When I test with just use the root user, it seems I’m able to get passed that, but upon the bundle exec rake db:create db:migrate commands, it comes back with the following error:

Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

At the start of this jobs output, it initially says the concurrent-0-mysql-0 service runner probably didn't start properly and the ...wait-for-service timed out.

It proceeds to initialize however shortly after and mysqld listens for connections via socket, /var/run/mysqld/mysqld.sock.

Since I’m using the docker executor on my gitlab-runner I had registered for this project since the shared runners were giving me trouble, it was recommended to use the host mysql for the mysql:5.7 service.
https://docs.gitlab.com/ee/ci/docker/using_docker_images.html#how-services-are-linked-to-the-job

Update:

Upon removing the saved variables within GitLab CI / CD settings and the Variables section, which is DB_USERNAME and DB_PASSWORD, I am able to run the rake db:* commands and the job succeeds. This is of course using just the root user with no password.

I’m not sure why that works, but a user created that has access to the 172.17.0.0/255.255.0.0 subnet does not.

dnsmichi · January 10, 2020, 2:30pm

Hi,

maybe the subnets don’t really match in this regard, and we have to go the % route (access from anywhere). With using root you also grant too many permissions, I’d recommend avoiding this - same as debian-sys-maint.

Cheers,
Michael

coclav · April 14, 2020, 4:27pm

this was not an easy one to debug for us, we just had a ....$F.... in the randomly-generated database password and gitlab was trying to replace that with a variable. essentially altering the password, and this caused the same issue

dnsmichi · April 21, 2020, 12:46pm

Hi,

actually, that’s the shell where the command is invoked. Since some of the examples use double quotes, all non-escaped character sequences will be interpreted as such. Even if $ characters in passwords might be recommended, they cause more troubles in different input methods. I’d suggest avoiding them, especially with different access levels and operating systems.

The following should work with escaping $ with \$:

DB_PASSWORD='abc$'
DB_PASSWORD="${DB_PASSWORD//$/\\$}"
echo $DB_PASSWORD

Cheers,
Michael