MariaDB issue: Access denied for user root@localhost

The installation script fails with following error:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)

Hosting environment: Ubuntu 18.04 LTS 64 bits - hosted on Digital Ocean - 2 GB RAM

According to an older topic, this is a known issue. I was wondering if this is going to be fixed.

Thanks and regards

It can happen if installation process has been stopped then restarted.
In this case, as you don’t have any data yet, you can try this and retry the installation process :

  • do a full Docker cleanup docker-compose down -v while in /mnt/repo-base dir (you can check with docker system df -v, only images should remain).
  • cleanup /mnt/repo-base/config-dynamic, /mnt/repo-base/config-static, /mnt/repo-base/volumes

Thanks for your reply! I tried as you said, unfortunately the error remains.

Hmmmm … Maybe more cleanup is needed.
But this will take more time, as you will download all again.

I’ll try (from /mnt/repo-base):

  • docker-compose down --rmi all --volumes
  • docker container ls (should be empty, if not use docker container kill)
  • docker container prune --force
  • docker system prune --all --volumes --force
  • rm -rf /var/lib/docker/overlay2/*
  • cd && mv /mnt /mnt.bak && mkdir /mnt

And restart the installation from very beginning …

If error persists, please try this : Self-hosting /e/ Cloud Services is here!
Be aware that :

  • this is quite old, so mariadb image version may not be accurate (get TAG from docker image ls "mariadb")
  • you’ll have to replace <MYSQL_ROOT_PASSWORD> with value from .env file

I’m trying your workaround from the other post. It goes as you say until the last step, where I’m stuck with a syntax error:

MariaDB [mysql]> grant all privileges on . to ‘postfix’@‘172.18.0.%’ identified by ‘’ with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘to ‘postfix’@‘172.18.0.%’ identified by ‘’ with grant option’ at line 1

What am I doing wrong?

Thanks

My bad, sorry :frowning:

Some characters are being withdrawn from my original message, and I can’t edit it anymore.

Correct syntax is :
grant all privileges on *.* to ‘postfix’@‘172.18.0.%’ identified by ‘<password>’ with grant option;
or (same command without formatting) :
grant all privileges on *.* to ‘postfix’@‘172.18.0.%’ identified by ‘<password>’ with grant option;

<password> is to be replaced with, I think, DBPASS value from .env file (it’s the var name used in docker-compose.yml file).

You can find full syntax and examples here : https://mariadb.com/kb/en/grant/

Note 1 : you can go directly to MariaDB shell with docker exec -it mariadb mysql -u root -p then enter value of MYSQL_ROOT_PASSWORD from .env file :wink: . Exit with \q.
Note 2 : while in MariaDB shell, you can list grants for a user (ie postfix) using show grants for postfix; . You can also dump users & rights using use mysql; then select * from user; (full help here : https://mariadb.com/kb/en/mysqluser-table/).

Note 3 : I assume all self-hosted are using the same IP network … But it doesn’t hurt to check with docker network inspect repobase_serverbase (from /mnt/repo-base, as usual :wink: ).
If listed IP network (172.18.0) is different, please modify SQL GRANT commands accordingly.

Thanks a lot for your help, unfortunately it does still not work. After executing the grant as you describe, when I restart the postinstall.sh I get:
root@mail:/mnt/repo-base# /mnt/repo-base/scripts/postinstall.sh
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for ‘nc_Fpkf’@’%’

That is what I did:
MariaDB [mysql]> grant all privileges on . to ‘postfix’@‘172.18.0.%’ identified by ‘<DBPASS from .env file>’ with grant option;
Query OK, 0 rows affected (0.090 sec)

I guess 0 rows affected is not good. Something should have changed.

This is difficult for me to troubleshoot.

There is no grants defined for postfix?
MariaDB [mysql]> show grants for postfix;
ERROR 1141 (42000): There is no such grant defined for user ‘postfix’ on host ‘%’

Hi @eramon, wish you an happy '21 with /e/ Cloud :slight_smile:

Your grant command ended with : “0 rows affected”, that’s not good.
Let’s take a look at the command.

  • I assume that “on . to” was “on *.* to”, stars being withdrawn by the forum ? If not please correct.
  • I wasn’t clear enough about the password, my bad :frowning: . Here is how to get the correct value :
    • cd /mnt/repo-base/
    • grep DBPASS .env
    • it should answer a line starting with DBPASS=
    • copy the string after the = and use it as the password for the SQL grant (after “identified by”, surrounded by ')

But ! This was to address the “grant to postfix” problem.
The message in your last post is about “create user” … I really hope this wasn’t the last output of posinstall.sh script ! It shouldn’t.
Could you please retry and post back the whole output (taking care of removing passwords, if any) ?

Happy 2021 to you too :slight_smile:

  • You assume right, the stars were withdrawn by the forum.
  • You were perfectly clear about the password, and that’s the one I took indeed.

Everything gets as you say until the second error. Then I go to the MariaDB shell again and I execute:

MariaDB [mysql]> grant all privileges on *.* to 'postfix'@'172.18.0.%' identified by '<password>' with grant option;
Query OK, 0 rows affected (0.001 sec)

Weird is that with the 0 rows.

After that, I exit the container and try to run the script again:

root@mail:/mnt/repo-base# /mnt/repo-base/scripts/postinstall.sh
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'nc_A4Fy'@'%'

I tried to start from the beginning twice, both times I get the same error.

Should I kill the container after executing the
grant command?

Thanks and regards

Here the steps BEFORE doing the grant:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

eramon@mail:/mnt/repo-base$ sudo docker-compose stop

eramon@mail:/mnt/repo-base$ sudo docker container run -i -t -v "/mnt/repo-base/volumes/mysql/db:/var/lib/mysql" -v "/mnt/repo-base/config-dynamic/nextcloud/database:/docker-entrypoint-initdb.d" mariadb:10.3.17 /bin/bash

root@d8481b4a5171:/# mysqld_safe --defaults-file=/etc/mysql/debian.cnf --skip-grant-tables --user=root &
[1] 11
root@d8481b4a5171:/# 210106 13:36:27 mysqld_safe Logging to '/var/lib/mysql/d8481b4a5171.err'.
210106 13:36:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

root@d8481b4a5171:/# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB-1:10.3.17+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=password('password') where user='root';
Query OK, 4 rows affected (0.004 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [mysql]> update user set authentication_string='' where user='root';
Query OK, 0 rows affected (0.000 sec)
Rows matched: 4  Changed: 0  Warnings: 0

MariaDB [mysql]> update user set plugin='' where user='root';
Query OK, 0 rows affected (0.000 sec)
Rows matched: 4  Changed: 0  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

I assume you used the value of MYSQL_ROOT_PASSWORD from .env file as password, right ?
Also, you better remove the “emergency” MariaDB container, docker will create a new one.

I may have skipped something in the instructions or failed to make them clear enough :frowning:
So I’ll try to reproduce the problem on a new Cloud instance and keep you posted.
Unfortunately I only have a very few free time, however I’ll try to find some next sunday.

Okay, today I have good news : I could reproduce your problem, and may have a solution :slight_smile:

“Come on, let’s get to work!” (What I did)

I fired up a brand new Ubuntu 18.06 Hetzner CX21 server, adjusted a spare domain records & PTR accordingly. Nothing fancy :coffee:
Then followed carefully instructions here.

Interesting facts :

  • the “develop” branch is the default for the ecloud-selfhosting GitLab project
  • for this branch, user are instructed to add a “master” parameter to “bootstrap-generic” script (this is not advised in “master” branch instructions !). This very detail is important, I’ll write later about.

“Anyone home?” (What happened)

Everything went fine, until the “Nexcloud drop_account” step :

Script output while in postinstall.sh
Sat Jan  9 09:05:42 CET 2021: Waiting for Nextcloud to finish installation.............................................................................................................................................................................................................................................................................................................Nextcloud was successfully installed
Nextcloud is in maintenance mode - no apps have been loaded

All tables already up to date!
Nextcloud is in maintenance mode - no apps have been loaded

System config value trusted_domains => 0 set to string smu44.fr
Installing nextcloud plugins
Nextcloud is in maintenance mode - no apps have been loaded

calendar installed
calendar enabled
Nextcloud is in maintenance mode - no apps have been loaded

tasks installed
tasks enabled
Nextcloud is in maintenance mode - no apps have been loaded

notes installed
notes enabled
Nextcloud is in maintenance mode - no apps have been loaded

user_backend_sql_raw installed
user_backend_sql_raw enabled
Nextcloud is in maintenance mode - no apps have been loaded

rainloop installed
rainloop enabled
Nextcloud is in maintenance mode - no apps have been loaded

Config value rainloop-autologin for app rainloop set to 1
Cloning into 'volumes/nextcloud/custom_apps/drop_account'...
remote: Enumerating objects: 84, done.
remote: Counting objects: 100% (84/84), done.
remote: Compressing objects: 100% (54/54), done.
remote: Total 447 (delta 25), reused 82 (delta 23), pack-reused 363
Receiving objects: 100% (447/447), 276.03 KiB | 3.17 MiB/s, done.
Resolving deltas: 100% (190/190), done.
Nextcloud is in maintenance mode - no apps have been loaded

App "Array" cannot be installed because it is not compatible with this version of the server.

:scream_cat: Wait ! It is not supposed to end like this ! Looking at the “postinstall.sh” script, they are many steps more, including some info that I didn’t see !

Something went bad, take a look at last line of output above.

“Oh my god, they killed Kenny!” (Why it happened)

Looking at “postinstall.sh”, we were somewhere between :
echo "Installing nextcloud plugins"
and
echo "Installing custom ecloud drop account plugin" (which we never seen)
According to the script output, we can easily find that the guilty is the foreign FramaGit resource at https://framagit.org/tcit/drop_user.git :japanese_goblin:

The “Array” Nexcloud app error is then generated by docker-compose exec -T --user www-data nextcloud php occ app:enable drop_account, stopping the script run abruptly.

There are a lot of steps that have not been carried out, including mandatory postfix and MariaDB configuration !

What we are missing
docker-compose exec -T --user www-data nextcloud php occ maintenance:mode --off

echo "Restarting Nextcloud container"
docker-compose restart nextcloud

echo "Configuring Rainloop"
mkdir -p "/mnt/repo-base/volumes/nextcloud/data/rainloop-storage/_data_/_default_/domains/"
echo "$ADD_DOMAINS" | tr "," "\n" | while read add_domain; do
    cp "templates/rainloop/domain-config.ini" "/mnt/repo-base/volumes/nextcloud/data/rainloop-storage/_data_/_default_/domains/$add_domain.ini"
done
chown www-data:www-data /mnt/repo-base/volumes/nextcloud/ -R

echo "Creating postfix database schema"
curl --silent -L https://mail.$DOMAIN/setup.php > /dev/null

echo "Adding Postfix admin superadmin account"
docker-compose exec -T postfixadmin /postfixadmin/scripts/postfixadmin-cli admin add $ALT_EMAIL --password $PFA_SUPERADMIN_PASSWORD --password2 $PFA_SUPERADMIN_PASSWORD --superadmin

# adding sudo to postfixadmin container
docker-compose exec -T postfixadmin apk add sudo
# giving pfexec user a specific sudo perm ONLY for launching the bind-mounted mailbox-postdeletion script
docker-compose exec -T postfixadmin bash -c 'echo "" >> /etc/sudoers && echo "#pfexec single command perm" >> /etc/sudoers && echo "pfexec ALL=(root) NOPASSWD: /usr/local/bin/postfixadmin-mailbox-postdeletion.sh" >> /etc/sudoers'

# Adding domains to postfix is done by docker exec instead of docker-compose exec on purpose. Reason: with compose the loop aborts after the first item for an unknown reason
echo "Adding domains to Postfix"
# The password_expiry parameter is only a workaround, and does not have any effect
# https://github.com/postfixadmin/postfixadmin/issues/280#issuecomment-511788887
echo "$ADD_DOMAINS" | tr "," "\n" | while read line; do docker exec -t postfixadmin /postfixadmin/scripts/postfixadmin-cli domain add $line --password_expiry 0; done

echo "Adding email accounts used by system senders (drive, ...)"
docker-compose exec -T postfixadmin /postfixadmin/scripts/postfixadmin-cli mailbox add drive@$DOMAIN --password $DRIVE_SMTP_PASSWORD --password2 $DRIVE_SMTP_PASSWORD --name "drive" --email-other $ALT_EMAIL
docker-compose exec -T postfixadmin /postfixadmin/scripts/postfixadmin-cli mailbox add $SMTP_FROM --password $SMTP_PW --password2 $SMTP_PW --name "welcome" --email-other $ALT_EMAIL

# display DKIM DNS setup info/instructions to the user
echo -e "\n\n\n"
echo -e "Please add the following records to your domain's DNS configuration:\n"
find /mnt/repo-base/volumes/mail/dkim/ -maxdepth 1 -mindepth 1 -type d | while read line; do DOMAIN=$(basename $line); echo "  - DKIM record (TXT) for $DOMAIN:" && cat $line/public.key; done

echo "================================================================================================================================="
echo "================================================================================================================================="
echo "Your logins:"
bash scripts/show-info.sh

echo "================================================================================================================================="
echo "Your signup link:"
bash scripts/generate-signup-link.sh --user-email $ALT_EMAIL

echo "Please reboot the server now"

We can easily confirm the problem while attempting to re-launch the “postinstall” script :

root@tst1:/mnt/repo-base# /mnt/repo-base/scripts/postinstall.sh
ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for ‘nc_OsAF’@‘%’

:crying_cat_face:

“Knowing where one’s towel is” (How to get by)

Digging into the ecloud-selfhosting GitLab project, I’ve found the
“auto-delete-account” branch, with a very interesting comment : “update of drop account url in post_install script as repo was moved by maintainer”.

That sounds very promising! :crossed_fingers:
Let’s give it a try …

As we will use another branch, I recommend to start over with the whole installation.
I just rebuilt my server, but one can also use cleaning instructions in some previous post.

Then grabbed the bootstrap script with :
wget https://gitlab.e.foundation/e/infra/bootstrap/raw/master/bootstrap-generic.sh
and launched it with :
bash bootstrap-generic.sh https://gitlab.e.foundation/e/infra/ecloud-selfhosting auto-delete-account
Note the “auto-delete-account” parameter !

Sadly, It didn’t do the trick, same error :exploding_head:
Seems that the git clone didn’t get the right file ! It’s still the “old” one, without the change from “auto-delete-account” branch …
AND, after manually modifying the script, it didn’t work either :frowning:

Okay, time to show our muscles !

After cleaning again, I launched again with “master” as parameter. At “Enter your mailserver (management) domain (e.g. domainA.com):” prompt, I launched another shell and modified manually the /mnt/repo-base/scripts/postinstall.sh, as is (note the “#” in front of 2 lines) :


docker-compose exec -T --user www-data nextcloud php /var/www/html/occ config:app:set rainloop rainloop-autologin --value 1
#git clone --single-branch https://framagit.org/tcit/drop_user.git volumes/nextcloud/custom_apps /drop_account
#docker-compose exec -T --user www-data nextcloud php occ app:enable drop_account

echo “Installing custom ecloud drop account plugin”
# Add WELCOME_SECRET from .env file as a system config value, to be used by our ecloud_drop_account plugin

Save, exit, resume run in other shell.

“Yeah baby, we got mojo back!”

After some :beer:, a reboot, I conducted some lite testings and can confirm everything’s ok ! :tada:

What about the “auto delete” feature ? Well, I think we can live without on a self-hosted small server.
Anyway, I’ll open an issue in GitLab … later :wink:

Just wow :slight_smile:
Thank you very much for your patience and your persistence.

Unfortunately, it still does not work for me. I modified the script manually script exactly as you said, resumed the installation, got again the first error (about the root password), I applied your first workaround, restarted the postinstall script, got the second known error (about the postfix missing grants), applied the second workaround (grant all privileges…) and restarted the postinstall script again, getting again the same third error I always get ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for ‘nc_EAYh’@’%’

The grant again returned “0 rows affected” which is probably bad.

I don’t want to waste your time anymore, I’m sorry it’s not working for me. Just two questions:

  • Which branch is the one I’m supposed to get the script from? The master branch, right?
  • The parameter “master” must be included when launching the script, right?
  • So at the end you are executing exactly what is in the instructions on the gitlab?

Again, lots of thanks. I will try again from the beginning… later.

Thanks for your feedback :slight_smile:

That’s where, I believe, you are missing something. You have to cleanup everything, expect Docker images.
I may have forgotten some explanations about this, sorry :frowning:
I choose the easy way by rebuilding my test server at each try, but you may use this :

  • cd /mnt/repo-base
  • docker-compose down --volumes --remove-orphans
  • cd /
  • rm -rf /mnt

Yes, yes, and … yes, you’re absolutely right :slight_smile:
There was a workaround attempt from devs with the “auto-delete-account” branch, but it didn’t work better for me. So better stick to the “master”.

After patching (you have to do it each time you launch the boostrap-generic.sh) then resuming the run, it must end with “Please reboot the server now”.
If not, please post back the few last lines of output, along with your postinstall.sh script (better use https://pastebin.com/ for this).
If you see the “please reboot” message, I strongly recommend that you do not restart the script postinstall.sh script again.

And please, forget about SQL instructions. I misleaded you with this, as I wasn’t aware of the “drop-account” problem. My bad :frowning:

I rebuilt the server and tried again your workaround of removing the two lines of postinstall.sh. This time it worked :slight_smile:

I had already try rebuilding the server without success, it was rebuilding the server AND removing the lines what did the trick. Without removing the lines, on a clean environment with a new installation, I had got the “access denied for root”.

Thing is, with your SQL instructions and the workaround you described, the behaviour was also exactly the one you had described (until the “grant” which returned no rows). So I thought the problem was the same you were having in the other post.

Thank you very much for all your help :slight_smile:
Cheers

You’re welcome, I’m very happy it finally worked ! :partying_face:

For information, I opened a Git issue : https://gitlab.e.foundation/e/backlog/-/issues/2399
It contains a proposal for a new solution, if accepted I could help you with applying it to your /e/ Cloud instance.

Meanwhile, please free to open some other threads about, for example, managing, monitoring, securing, …

You may want to start with something I think as an absolute must-have : [HOWTO] Properly backup self-hosted /e/ cloud databases

Welcome to your own /e/ Cloud :smile_cat: