Database User Permission Hardening Tutorial: Complete MySQL, PostgreSQL, and MariaDB Access Control Configuration for Linux VPS in 2026

Understanding Database User Permission Models

Most database breaches start with overprivileged user accounts. Database user permission hardening cuts your attack surface and limits damage when credentials get compromised.

Each database system handles permissions differently. MySQL uses role-based privileges with granular controls. PostgreSQL adds row-level security and advanced role inheritance. MariaDB extends MySQL's foundation with enhanced authentication plugins.

Your VPS database setup directly affects application security. One misconfigured user can expose entire databases.

MySQL User Permission Configuration

Connect to MySQL as root to start hardening:

mysql -u root -p

Create application-specific users with minimal privileges. Never use root for application connections:

CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'SecurePass123!';
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass456!';

Grant only the database access you need:

GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp_user'@'localhost';
GRANT SELECT, LOCK TABLES ON webapp_db.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Remove dangerous default accounts:

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';
DROP USER IF EXISTS 'test'@'localhost';

Configure password validation for stronger requirements:

INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;

For production environments on HostMyCode VPS systems, implement host-based restrictions. Limit database connections to specific IP addresses instead of wildcard hosts.

PostgreSQL Role-Based Security Setup

PostgreSQL uses roles instead of traditional users. Connect as postgres superuser:

sudo -u postgres psql

Create roles with specific privileges:

CREATE ROLE webapp_role LOGIN PASSWORD 'WebAppSecure789!';
CREATE ROLE readonly_role LOGIN PASSWORD 'ReadOnlyPass321!';

Configure database-level permissions:

GRANT CONNECT ON DATABASE webapp_db TO webapp_role;
GRANT USAGE ON SCHEMA public TO webapp_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp_role;

Set up read-only access for reporting users:

GRANT CONNECT ON DATABASE webapp_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

Enable row-level security for sensitive tables:

ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_data_policy ON user_data FOR ALL TO webapp_role USING (user_id = current_setting('app.current_user_id')::INTEGER);

Configure pg_hba.conf for connection security. Edit the file at /etc/postgresql/14/main/pg_hba.conf:

# Require SSL connections
hostssl webapp_db webapp_role 192.168.1.0/24 md5
hostssl webapp_db readonly_role 192.168.1.0/24 md5

MariaDB Enhanced Authentication

MariaDB supports multiple authentication plugins for better security. Check available plugins:

SHOW PLUGINS WHERE Type = 'AUTHENTICATION';

Create users with specific authentication methods:

CREATE USER 'secure_app'@'localhost' IDENTIFIED VIA ed25519 USING PASSWORD('AppPassword123!');
CREATE USER 'api_user'@'%' IDENTIFIED VIA mysql_native_password USING PASSWORD('ApiSecure456!');

Configure role-based access with MariaDB extensions:

CREATE ROLE app_writer;
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO app_writer;
GRANT SELECT, INSERT, UPDATE ON app_db.customers TO app_writer;
GRANT app_writer TO 'secure_app'@'localhost';

Enable default roles for users:

SET DEFAULT ROLE app_writer FOR 'secure_app'@'localhost';

For applications hosted on managed VPS hosting, implement connection limits to prevent resource exhaustion:

ALTER USER 'api_user'@'%' WITH MAX_CONNECTIONS_PER_HOUR 1000;
ALTER USER 'api_user'@'%' WITH MAX_USER_CONNECTIONS 50;

Cross-Platform Security Hardening

Apply consistent security practices across all database systems. Disable unnecessary network protocols and services.

For MySQL, edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
skip-networking=0
bind-address=127.0.0.1
port=3306
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
local-infile=0

For PostgreSQL, configure /etc/postgresql/14/main/postgresql.conf:

listen_addresses = 'localhost'
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
log_connections = on
log_disconnections = on

Set up audit logging for all systems. This tracks unauthorized access attempts and privilege escalation.

Database-Specific Privilege Management

Each database type requires specific privilege management strategies. Understanding these differences prevents security gaps.

MySQL administrative privileges to restrict carefully:

• SUPER - Allows connection limit bypass and replication control
• PROCESS - Enables viewing all running queries
• FILE - Permits reading/writing server files
• SHUTDOWN - Allows database shutdown
• RELOAD - Enables configuration reloading

PostgreSQL superuser capabilities to limit:

• CREATEDB - Database creation rights
• CREATEROLE - Role management permissions
• SUPERUSER - Complete system access
• REPLICATION - Replication stream access
• BYPASSRLS - Row-level security bypass

Review existing privileges regularly. Run these queries to audit current permissions:

# MySQL privilege check
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Super_priv FROM mysql.user;

# PostgreSQL role check
\du

Regular privilege audits help identify permission creep and unauthorized escalations. Document all permission changes for compliance requirements.

Understanding database connection troubleshooting helps identify permission-related connectivity issues early.

Automated Permission Monitoring

Set up automated monitoring to detect privilege changes and suspicious access patterns. Create monitoring scripts for each database type.

MySQL privilege monitoring script:

#!/bin/bash
# Save as /opt/scripts/mysql_audit.sh

MYSQL_USER="audit_user"
MYSQL_PASS="audit_password"
LOG_FILE="/var/log/mysql_audit.log"

echo "$(date): Starting MySQL privilege audit" >> $LOG_FILE
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT User, Host, Super_priv, Grant_priv FROM mysql.user WHERE Super_priv='Y' OR Grant_priv='Y';" >> $LOG_FILE
echo "---" >> $LOG_FILE

PostgreSQL role monitoring:

#!/bin/bash
# Save as /opt/scripts/postgres_audit.sh

LOG_FILE="/var/log/postgres_audit.log"
echo "$(date): PostgreSQL role audit" >> $LOG_FILE
sudo -u postgres psql -c "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolsuper='t' OR rolcreaterole='t';" >> $LOG_FILE
echo "---" >> $LOG_FILE

Schedule these scripts with cron for regular execution:

# Run audits twice daily
0 6,18 * * * /opt/scripts/mysql_audit.sh
0 6,18 * * * /opt/scripts/postgres_audit.sh

Set up alerts for unusual privilege changes. Configure email notifications when new superusers appear or administrative privileges change.

Connection Security and SSL Configuration

Force SSL connections for all database access. This prevents credential interception and data exposure during transmission.

Generate SSL certificates for MySQL:

sudo mysql_ssl_rsa_setup --uid=mysql
sudo systemctl restart mysql

Require SSL for specific users:

ALTER USER 'webapp_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

For PostgreSQL, enable SSL in configuration and generate certificates:

sudo openssl req -new -x509 -days 365 -nodes -text -out /var/lib/postgresql/server.crt -keyout /var/lib/postgresql/server.key
sudo chown postgres:postgres /var/lib/postgresql/server.*
sudo chmod 600 /var/lib/postgresql/server.key

Configure pg_hba.conf to require SSL:

hostssl all webapp_role 0.0.0.0/0 md5

Implementing proper database backup validation ensures your security measures don't interfere with backup operations.

Application-Level Integration

Configure application connection strings to use least-privilege accounts. Never embed administrative credentials in application code.

PHP application example using different credentials:

// Read operations
$read_pdo = new PDO('mysql:host=localhost;dbname=webapp', 'readonly_user', 'readonly_pass');

// Write operations
$write_pdo = new PDO('mysql:host=localhost;dbname=webapp', 'webapp_user', 'webapp_pass');

Node.js connection pooling with role separation:

const mysql = require('mysql2/promise');

const readPool = mysql.createPool({
  host: 'localhost',
  user: 'readonly_user',
  password: 'readonly_pass',
  database: 'webapp',
  ssl: { rejectUnauthorized: true }
});

const writePool = mysql.createPool({
  host: 'localhost',
  user: 'webapp_user',
  password: 'webapp_pass',
  database: 'webapp',
  ssl: { rejectUnauthorized: true }
});

Store database credentials securely using environment variables or dedicated secret management systems. Never commit credentials to version control.

Troubleshooting Permission Issues

Common permission problems and their solutions help maintain database functionality while preserving security.

MySQL permission denied errors usually indicate insufficient privileges:

# Check current user privileges
SHOW GRANTS FOR CURRENT_USER();

# Check specific user privileges
SHOW GRANTS FOR 'username'@'hostname';

PostgreSQL permission errors often relate to schema access:

# Check role memberships
\du

# Check table permissions
\dp tablename

Test connections with different users to verify privilege configurations:

# Test MySQL connection
mysql -u webapp_user -p -h localhost webapp_db -e "SELECT 1;"

# Test PostgreSQL connection
psql -U webapp_role -h localhost -d webapp_db -c "SELECT 1;"

Monitor database error logs for permission-related messages. These logs help identify misconfigured access controls.

Understanding database slow query monitoring helps distinguish between performance issues and permission-related query failures.

FAQ

What are the most dangerous database privileges to avoid granting?

SUPER privilege in MySQL, SUPERUSER role in PostgreSQL, and FILE privilege in any system pose the highest security risks. These allow complete system access, file manipulation, and configuration changes that can compromise entire servers.

How often should database user permissions be audited?

Conduct formal permission audits monthly for production systems. Set up automated daily monitoring for privilege changes and quarterly comprehensive reviews for compliance requirements.

Can database user permission hardening affect application performance?

Properly configured permissions have minimal performance impact. Row-level security in PostgreSQL may add slight query overhead, but connection pooling and index optimization typically offset any performance concerns.

What's the difference between MySQL and PostgreSQL permission models?

MySQL uses a privilege-based system with host-specific grants, while PostgreSQL implements role-based access control with inheritance. PostgreSQL offers more granular control through row-level security and schema-based permissions.

How do I recover from accidentally removing database admin privileges?

Stop the database service, restart in skip-grant-tables mode (MySQL) or single-user mode (PostgreSQL), restore admin privileges, then restart normally. Always maintain multiple admin accounts to prevent complete lockouts.