Users, roles and security

The account model: who is asking

MySQL An account is the pair 'user'@'host', and the host is part of the identity, so 'app'@'localhost' and 'app'@'%' are two accounts with their own passwords and grants. The host can be a name, an IP, a pattern such as '10.0.0.%', or '%' for anywhere, and the most specific match wins. An app moved to another machine then gets "Access denied for user 'app'@'10.0.0.7'": that account does not exist. PostgreSQL has one concept, the role; a role with LOGIN is what you call a user, a role without it is a group, and where a role may connect from lives in pg_hba.conf, one line per rule, first match wins.

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-secret';   -- MySQL
SELECT user, host, plugin FROM mysql.user WHERE user = 'app';

CREATE ROLE app LOGIN PASSWORD 'a-long-random-secret';               -- PostgreSQL
-- pg_hba.conf:  host  shop  app  10.0.0.0/24  scram-sha-256

Authentication: proving it

Each MySQL account has an authentication plugin. caching_sha2_password is the default in 8.4, 9.7 and 26.x; mysql_native_password is disabled by default in 8.4 and was removed in 9.0, so 9.x cannot use such accounts at all. In PostgreSQL the method comes from the matching pg_hba.conf line, and scram-sha-256 has been the default since 14. Old clients fail because their connector only speaks the old scheme: the error names caching_sha2_password, or an account restored from an old dump fails with ERROR 1524 Plugin 'mysql_native_password' is not loaded. The fix is a newer driver, never a weaker server.

Privileges and roles: what you may do

A privilege is always this action, on this scope, for this account. GRANT gives one, REVOKE takes it back, and SHOW GRANTS in MySQL or \du and \dp in psql show what someone has. A grant at a wide level covers every narrower object inside it.

A role is a named bundle: grant privileges to the role once, grant it to as many accounts as you like, and tightening the bundle fixes all of them. In MySQL 8 and later a granted role is inert until SET DEFAULT ROLE or activate_all_roles_on_login turns it on. In PostgreSQL roles are the same objects as users, membership is a plain GRANT, and built-in roles such as pg_read_all_data and pg_monitor replace long grant lists. GRANT ... ON ALL TABLES covers only today's tables, so ALTER DEFAULT PRIVILEGES covers tomorrow's.

CREATE ROLE shop_read;                                   -- MySQL
GRANT SELECT ON shop.* TO shop_read;
GRANT shop_read TO 'reporting'@'10.0.0.%';
SET DEFAULT ROLE shop_read TO 'reporting'@'10.0.0.%';    -- without this: no privileges

GRANT pg_read_all_data TO reporting;                     -- PostgreSQL
ALTER DEFAULT PRIVILEGES FOR ROLE deploy IN SCHEMA public
  GRANT SELECT ON TABLES TO reporting;                   -- tomorrow's tables

Least privilege: the recipes

Least privilege means each account can do its job and nothing more. It limits the damage from a leaked password, a buggy deploy and SQL injection at once.

The network: where connections may come from

• Listening interfaces. MySQL's bind_address defaults to all of them; PostgreSQL's listen_addresses defaults to localhost. Set each to the smallest value that works.
• TLS. Both clients fall back to plain text silently, so demand it: require_secure_transport=ON or REQUIRE SSL per account in MySQL, hostssl lines and sslmode=require in PostgreSQL.
• Windows Firewall. Restrict the inbound rule for port 3306 or 5432 to the application servers' subnet.
• Never the internet. A VPN, an SSH tunnel or a bastion host is the way in from outside.

Credentials and SQL injection

Scheduled jobs must log in without a human, so store the credentials outside the script: mysql_config_editor writes a login path to %APPDATA%\MySQL\.mylogin.cnf and every MySQL client then takes --login-path=backup instead of a password. PostgreSQL reads %APPDATA%\postgresql\pgpass.conf, one line per server in the form hostname:port:database:username:password.

# MySQL: store once (prompts for the password), then no password in any script
mysql_config_editor set --login-path=backup --host=localhost --user=backup --password
mysqldump --login-path=backup --single-transaction shop --result-file=C:\backups\shop.sql

# PostgreSQL: one line in %APPDATA%\postgresql\pgpass.conf
# localhost:5432:*:backup:the-password

SQL injection happens when an application glues user input into SQL text, so ' OR 1=1 -- becomes part of the statement. Parameterized queries stop it completely, and your least-privilege grants keep an injected query from dropping tables or reading other databases.

The rest, in one line each

• Password lifecycle. MySQL has validate_password, PASSWORD EXPIRE INTERVAL, PASSWORD HISTORY, FAILED_LOGIN_ATTEMPTS and ACCOUNT LOCK for leavers; PostgreSQL has VALID UNTIL, NOLOGIN and CONNECTION LIMIT.
• Auditing. MySQL Enterprise Audit is paid, so on Community you lean on the binary log, short bursts of the general log and audit triggers; PostgreSQL has log_statement, log_connections and the free pgAudit extension.

Go deeper

• Privileges provided by MySQL

  Every static and dynamic privilege.

• Using roles in MySQL

  Default roles and activation.

• mysql_native_password: deprecated, disabled, removed

  The timeline behind most "old app cannot connect" tickets.

• PostgreSQL database roles

  Roles, attributes, membership, predefined roles.

• The pg_hba.conf file

  Line format and methods.