Turn off database features that reach the server

How to do it

A role with these powers turns one injected query into control of the machine. In PostgreSQL, grant no role pg_read_server_files, pg_write_server_files or pg_execute_server_program. The PostgreSQL docs warn that these roles could be used to gain superuser access. Keep superuser for the one admin role, never the app. In MySQL and MariaDB, give no user the FILE privilege, and run mysql_secure_installation to remove default accounts and databases. In SQL Server, turn off xp_cmdshell and CLR execution. Run the database service as its own low-privilege system user. The app's own grants follow Give the app a least-privilege database user. Default passwords follow Remove defaults and samples.

Examples

SQL example

-- PostgreSQL: roles that are superuser or can reach the server's files and programs
SELECT r.rolname FROM pg_roles r
WHERE r.rolsuper
   OR pg_has_role(r.oid, 'pg_read_server_files', 'MEMBER')
   OR pg_has_role(r.oid, 'pg_write_server_files', 'MEMBER')
   OR pg_has_role(r.oid, 'pg_execute_server_program', 'MEMBER');
-- MySQL: users with the FILE privilege
SELECT user, host FROM mysql.user WHERE File_priv = 'Y';

Shell example

# SQL Server: expect 0 for both
sqlcmd -Q "SELECT name, value_in_use FROM sys.configurations WHERE name IN ('xp_cmdshell', 'clr enabled')"

How to check

The queries below return only the one admin role in PostgreSQL, and only root in MySQL. In SQL Server, xp_cmdshell and clr enabled both show 0 in sys.configurations.

Sources

• OWASP Database Security Cheat Sheet
• PostgreSQL predefined roles

Related rules

• Give the app a least-privilege database user in Data
• Remove defaults and samples in Security testing