Put database in readonly mode

[jlguerrero]

I want to improve the wordpress security.

I already have wp plugin that writes to a file and a cron that reads that file, validates everything and issues some commands.

One of the commands I want to run is a bash script to put the database in readonly mode (only select)

I have been playin around and tried this approach:
mysql --socket=/var/run/mysqld/mysqld.sock -e "REVOKE ALL PRIVILEGES ON . FROM ‘gruporingtel.com_wp’@‘localhost’;
mysql --socket=/var/run/mysqld/mysqld.sock -e “GRANT SELECT ON `gruporingtel.com_wp`.* TO ‘gruporingtel.com_wp’@‘localhost’;”

after I flush and restart mariadb

Then I go to the wp-backend, and edit posts as I usually do.

What can be happening? is this approach flawed? What else can I try?

[jlguerrero]

I am trying a different approach now…

Is it suicidal to “chmod -w” the database in “/var/lib/mysql/” ?

[sahsanu]

@jlguerrero

Is it suicidal to “chmod -w” the database in “/var/lib/mysql/” ?

I wouldn’t do that.

Could you please show the output of these commands?

mysql --socket=/var/run/mysqld/mysqld.sock -e "SHOW GRANTS FOR 'gruporingtel.com_wp'@'localhost';"
mysql --socket=/var/run/mysqld/mysqld.sock -e "SHOW GRANTS FOR 'gruporingtel.com_wp'@'%';"

[jlguerrero]

@sahsanu thank you very much for your help.

I just realised that the domain was moved to a different server so I was restricting the database in other server.

Therefore the Public wordpress installation was working completely unaffected by this changes.

I just tried it again in the correct server and everything works as expected.

This is a copy of the script from my private repo:

#!/bin/bash

# Carga las funciones desde el archivo externo
source /opt/nm-bin/functions.sh

USAGE="Pone la base de datos en modo solo lectura o lectura y escritura.
Sintaxis: $0 [usuario] [dominio] [modo]
Ejemplo:  $0 everydomain everydomain.es ro
Ejemplo2: $0 everydomain everydomain.es rw
"

# Verifica argumentos
parse_args 0 3 "$@"
comoroot

# Verifica si mysql está disponible
if ! command -v mysql &> /dev/null; then
    abort "El comando 'mysql' no está disponible. Asegúrate de que está instalado."
fi

# Obtengo $username y $domain
get_username_domain "$1" "$2"
mode="$3"

# Normaliza $mode
case "${mode,,}" in
    ro|readonly|read-only|solo-lectura)
        mode="ro"
        ;;
    rw|readwrite|read-write|lectura-escritura)
        mode="rw"
        ;;
    *)
        abort "Modo desconocido o vacío: $mode. Usa 'ro' para read-only o 'rw' para read-write. $USAGE"
        ;;
esac

# Variables
HESTIA_USERS_DIR="/usr/local/hestia/data/users"
PUBLIC_HTML="/home/$username/web/$domain/public_html"
wp_config="$PUBLIC_HTML/wp-config.php"

# Verifica existencia de wp-config.php
if [ ! -f "$wp_config" ]; then
    abort "No existe: $wp_config"
fi

# Extrae datos de conexión desde wp-config.php
extract_wp_config() {
    local config_file=$1

    # Usar awk para extraer las líneas relevantes y los valores
    DB_NAME=$(cat $config_file | grep "DB_NAME" | cut -d',' -f2 | cut -d"'" -f2)
    DB_USER=$(cat $config_file | grep "DB_USER" | cut -d',' -f2 | cut -d"'" -f2)
    DB_HOST=$(cat $config_file | grep "DB_HOST" | cut -d',' -f2 | cut -d"'" -f2)

    # Validar que las variables hayan sido correctamente extraídas
    if [ -z "$DB_NAME" ] || [ -z "$DB_USER" ]; then
        abort "No se pudieron extraer datos válidos desde $config_file."
    fi

    # Asignar valor predeterminado a DB_HOST si no se encuentra
    if [ -z "$DB_HOST" ]; then
        DB_HOST="localhost"
    fi

    # Mensaje de depuración opcional
    debug "Extraído: DB_NAME='$DB_NAME', DB_USER='$DB_USER', DB_HOST='$DB_HOST'"
}



# Configura modo read-only
set_database_readonly() {
    local db_name=$1
    local db_user=$2
    local db_host=$3

    msg "Poniendo '$db_name' en modo solo lectura para '$db_user'..."
    mysql --socket=/var/run/mysqld/mysqld.sock <<EOF
REVOKE ALL PRIVILEGES ON \`$db_name\`.* FROM '$db_user'@'$db_host';
GRANT SELECT ON \`$db_name\`.* TO '$db_user'@'$db_host';
FLUSH PRIVILEGES;
EOF

    if [ $? -eq 0 ]; then
        msg "Base de datos '$db_name' ahora es de solo lectura."
    else
        warn "Error configurando '$db_name' como solo lectura."
    fi
}

# Configura modo read-write
set_database_readwrite() {
    local db_name=$1
    local db_user=$2
    local db_host=$3

    msg "Restaurando permisos de lectura/escritura para '$db_name'..."
    mysql --socket=/var/run/mysqld/mysqld.sock <<EOF
GRANT ALL PRIVILEGES ON \`$db_name\`.* TO '$db_user'@'$db_host';
FLUSH PRIVILEGES;
EOF

    if [ $? -eq 0 ]; then
        msg "Base de datos '$db_name' ahora es de lectura y escritura."
    else
        warn "Error al restaurar permisos para '$db_name'."
    fi
}


# Ejecuta el cambio de modo
extract_wp_config "$wp_config"

if [ "$mode" == "ro" ]; then
    set_database_readonly "$DB_NAME" "$DB_USER" "${DB_HOST:-localhost}"
elif [ "$mode" == "rw" ]; then
    set_database_readwrite "$DB_NAME" "$DB_USER" "${DB_HOST:-localhost}"
fi

if [ "$DEBUG" == "1" ] ; then
    mysql --socket=/var/run/mysqld/mysqld.sock -e "SHOW GRANTS FOR '$DB_USER'@'localhost';"
fi

[sahsanu]

I’m glad you fixed it, also, thanks for sharing the script.

Tip: You shouldn’t create database names with dots in them, as it may cause problems.

[jlguerrero]

Thank you for the tip. The thing is have it all automated and I didn’t know that on the first place… It has been working flawlessly for 3 years now.

I will think about the dot though. I could substitute it with an underscore “_”