Database security checklist: exposure, roles, backups

Databases are rarely broken into. They are usually walked into. The overwhelming majority of incidents come down to four things: the port answered the whole internet, the password was guessable or reused, one all-powerful account did every job in the system, and there was no restorable backup when it went wrong. Everything clever in security literature sits behind those four, and if you fix only them you are ahead of most production systems.

This is a checklist you can work through in an afternoon, in the order that removes the most risk first. It uses PostgreSQL and MongoDB for the examples because those are the two engines people most often run themselves, but the shape is identical for any database: decide who can reach it, decide who can log in, decide what each account may do, encrypt the path, limit the damage a single client can cause, and keep a copy you have proved you can restore.

One framing to keep as you go. Security is not a property you add at the end, it is a set of decisions about blast radius. You will not prevent every mistake. The question each step answers is: when something goes wrong, how far does it get.

Reachability: the first and largest win#

Every database that listens on a public address is found. Not might be - is. Mass scanners sweep the whole address space for 5432, 27017, 3306 and 6379 continuously, and a new host with an open port typically sees its first login attempt within minutes of coming up. The 2017 wave of MongoDB extortion cases was not a clever exploit; it was thousands of instances bound to 0.0.0.0 with authentication off.

Start by finding out what is actually listening:

$ ss -ltnpState  Recv-Q Send-Q Local Address:Port  ProcessLISTEN 0      244    127.0.0.1:5432      users:(("postgres",pid=812,fd=6))LISTEN 0      4096   0.0.0.0:27017       users:(("mongod",pid=944,fd=11))

127.0.0.1:5432 is reachable only from the machine itself. 0.0.0.0:27017 is reachable from anywhere the firewall allows. Then check from outside, because what the machine believes and what the network does are different questions:

$ nc -vz db.example.net 5432Connection to db.example.net 5432 port [tcp/postgresql] succeeded!

The settings that control this:

If the application and the database are on the same machine, bind to localhost and stop reading this section. If they are not, allow exactly the addresses that need it and nothing else. Firewall rules that matter covers writing those rules without locking yourself out, and PostgreSQL remote connections covers the three settings that have to agree before a remote client can connect at all.

Authentication that is not guessable#

Once the port is reachable by the people who need it, the next question is who may log in.

PostgreSQL decides with pg_hba.conf, read top to bottom, first matching line wins. That ordering catches people: a permissive line above a restrictive one means the restrictive one never runs.

# TYPE    DATABASE  USER     ADDRESS           METHODlocal     all       postgres                   peerhostssl   app       app_rw   10.0.0.0/24       scram-sha-256hostssl   app       app_ro   10.0.0.0/24       scram-sha-256host      all       all      0.0.0.0/0         reject

Use scram-sha-256, not md5, and never trust, which means no password at all. Set password_encryption = scram-sha-256 before creating users, or their passwords are stored with whatever the old setting was. hostssl rather than host refuses the connection if it is not encrypted, which is stronger than asking the client politely.

MongoDB ships with access control off, and it is the single most important line in mongod.conf:

security:  authorization: enablednet:  bindIp: 127.0.0.1,10.0.0.5  port: 27017

Without it, anyone who reaches the port is an administrator. With it, MongoDB uses SCRAM-SHA-256 and every operation needs a user.

For the passwords themselves: generated, long, unique per database, stored in a password manager, never reused between staging and production. Twenty random characters from a generator beats a memorable phrase with substitutions, because the attacker's dictionary already contains the substitutions. If a password has ever been pasted into a chat, a ticket, or a screenshot, it is burnt - rotate it.

One user per job#

This is the step that turns a break-in into an incident rather than a catastrophe, and it is the step most often skipped, because a single superuser in the connection string always works.

The application does not need to create tables at runtime. The analytics dashboard does not need to delete rows. The migration tool needs schema rights for ninety seconds a month, not permanently. Four roles cover most systems:

In PostgreSQL that looks like this:

CREATE ROLE app_rw LOGIN PASSWORD 'generated-here';CREATE ROLE app_ro LOGIN PASSWORD 'a-different-one';GRANT CONNECT ON DATABASE app TO app_rw, app_ro;GRANT USAGE ON SCHEMA public TO app_rw, app_ro;GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;ALTER DEFAULT PRIVILEGES IN SCHEMA public  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;ALTER DEFAULT PRIVILEGES IN SCHEMA public  GRANT SELECT ON TABLES TO app_ro;

The ALTER DEFAULT PRIVILEGES lines are the ones people forget. Without them, every table created by the next migration is invisible to the application user and you get a permission error in production at three in the morning. PostgreSQL roles and permissions goes through the whole model, including why PUBLIC has more rights than you expect on older versions.

In MongoDB the same idea uses built-in roles scoped to one database:

db.createUser({  user: "app_rw",  pwd: passwordPrompt(),  roles: [ { role: "readWrite", db: "app" } ]})

Never give an application root, dbOwner or anything ending in AnyDatabase. The connection string then needs authSource pointing at the database where the user was created, which is the most common reason a correct password is rejected - MongoDB connection strings has the full anatomy.

The same principle applies to the humans. Panel accounts, deploy keys and dashboards should each have the narrowest role that lets the work happen, with a named owner. Subusers and least privilege covers that side.

Encrypt the connection, and verify the certificate#

If the database and the application are not on the same machine, the credentials and every row cross a network. Encrypting that is easy. Encrypting it in a way that actually proves who you connected to takes one more step, and most connection strings stop before it.

PostgreSQL's sslmode has six values and only two of them are worth using:

postgresql://app_rw:pw@db.example.net:5432/app?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca.pem

MongoDB is the same argument with different spelling: tls=true in the URI, a tlsCAFile if the certificate is not from a public authority, and never tlsAllowInvalidCertificates=true, which turns the verification off while leaving the word TLS in the string to reassure you.

Keep the credentials out of the code#

A database password in a git repository is a password on the internet, whether the repository is public or not - it survives in history, in forks, in the laptop backups of everyone who ever cloned it, and in whatever indexes your git host. Credentials belong in environment variables or a secrets store, injected at runtime, with .env in .gitignore from the first commit rather than the day you notice.

Three practical rules:

• Never put the password on a command line. It lands in shell history and in the output of ps for every user on the machine. Use ~/.pgpass with mode 0600, PGPASSWORD in the environment, or a prompt.
• Different credentials for staging and production, always. A staging box is a lower-trust machine by definition, and a shared password makes it a route in.
• When someone leaves the team, rotate. Access revoked is not the same as access forgotten.

Environment variables and secrets covers the mechanics on a panel host, where the Startup tab holds the values and they are not in the image or the repository.

Queries that cannot be injected#

Injection is still, decades on, the way most databases get read by someone who was not meant to read them. The mechanism is always the same: user input is concatenated into a query, so the input can change the query's structure rather than just its values.

// Broken. An email of  ' OR 1=1 --  returns every user.const sql = "SELECT * FROM users WHERE email = '" + email + "'";// Correct. The driver sends the query and the value separately.await client.query("SELECT * FROM users WHERE email = $1", [email]);

# psycopg, the same idea. The second argument is never string formatting.cur.execute("SELECT * FROM users WHERE email = %s", (email,))

Parameterised queries are not an escaping trick, they are a protocol feature: the statement is parsed first, then the values are bound, so a value cannot become syntax. Use them everywhere, including for the query you are sure is safe.

Two things parameters cannot do, which is where injection sneaks back in:

• Identifiers. Table and column names cannot be parameters. If a sort column comes from a query string, compare it against an allow-list of known column names and reject anything else. Never interpolate it.
• Whole clauses. Building WHERE fragments by string concatenation is the same bug in a nicer jumper, ORM or not. Most ORMs are safe by default and unsafe the moment you use their raw-SQL escape hatch.

MongoDB has its own version. If a JSON request body is passed straight into a query document, a client can send {"password": {"$ne": null}} and match every record. Cast incoming values to the type you expect before they reach the query, and disable server-side JavaScript evaluation rather than relying on $where being used carefully.

The reason this section sits after least privilege is that the two combine. An injection against an account that can only SELECT from three tables is a bad day. The same injection against a superuser can write files and run commands.

Limits, so one client cannot take the database down#

Availability is part of security, and the easiest way to take a small database offline is to open connections to it until it has none left.

• `max_connections` in PostgreSQL is a hard cap, and each connection costs memory whether it is working or idle. On a 1 to 2 GB instance, 100 is already optimistic. Put a pooler in front and give each application a CONNECTION LIMIT of its own with ALTER ROLE app_rw CONNECTION LIMIT 20;. Connection pools and limits explains the arithmetic.
• `statement_timeout` stops one runaway query from holding resources forever. Set it per role, not globally, so that a reporting user gets a longer leash than the web application: ALTER ROLE app_rw SET statement_timeout = '15s';
• `idle_in_transaction_session_timeout` kills sessions that opened a transaction and wandered off. Those block vacuum and hold locks, and a web application with a bug will produce them by the hundred.
• Rate limits in the application, in front of anything that runs an expensive query for an anonymous visitor. Search endpoints are the usual victim. Rate limits and abuse covers the pattern.

Backups you have actually restored#

A backup nobody has restored is a hypothesis. It is the last line in this checklist and the one that decides whether an incident is an inconvenience or the end of the business.

1. Automated, on a schedule. A manual backup is a backup you will stop taking during the busy week when you need it most.
2. Off the machine it protects. A copy on the same disk survives a deleted table and nothing else.
3. More than one generation. Corruption and malicious changes are often noticed days later, and a single nightly copy will have overwritten the good one by then.
4. Restored on purpose, at a time you choose. Restore into a scratch database every month or two, count some rows, run the application against it. That is the only evidence the backup works.
5. Treated as sensitive. A dump file is the whole database in one downloadable object. Do not leave one in a web-accessible directory, which is a surprisingly common way sites leak everything.

Database backups and restores has the mechanics per engine, and testing a restore before you need it is the half people skip. If the worst has already happened, what to do when your server is hacked covers the order to do things in, and it is not the order instinct suggests.

Logs that show you what changed#

You cannot investigate what you did not record, and the default logging on most databases records almost nothing useful.

log_connections = onlog_disconnections = onlog_statement = 'ddl'log_min_duration_statement = 1000log_line_prefix = '%m [%p] %q%u@%d from %h '

log_statement = 'ddl' records every schema change without the noise of logging every query. log_min_duration_statement = 1000 records anything slower than a second, which doubles as your performance data. %h in the prefix puts the client address on every line, which is what you will want first when something looks wrong.

Beyond the engine, keep an eye on the things that change rarely: a new role, a changed pg_hba.conf, a user granted more than it had. Those are small events with large meanings. Logs worth keeping covers retention, and the panel's per-server activity log records who did what on the account side.

The checklist#

Work down it once for each database you run. Anything you cannot answer is the next thing to fix.

FAQ#

Is it enough to use a very long password if the port is public?

It stops guessing, and it does not stop anything else. A public port still exposes you to protocol-level bugs in whatever version you are running, to credential leaks from elsewhere, and to a denial of service by connection exhaustion. Restrict the source addresses as well, always. Authentication and reachability are separate controls and you want both.

Do I need TLS if the database is on a private network?

Use it anyway. Private networks are shared with other tenants more often than their name suggests, an internal machine that gets compromised can watch traffic, and the cost of turning TLS on is a line in a connection string. The one place to be careful is verification: require without verify-full encrypts without proving who answered.

Does an ORM protect me from SQL injection?

For the queries it builds itself, yes. The risk is the raw query method every ORM provides, and the places where an identifier or a clause is assembled as a string because the query builder could not express it. Those are the lines to review, and they are usually the ones with a comment apologising for them.

How often should database passwords be rotated?

On a schedule, rotation is mostly theatre. On an event it is essential: someone leaves, a credential appears in a log or a screenshot, a machine that held it is compromised, or a third-party tool you granted access to has an incident. Build the rotation so it takes five minutes and does not need a deploy, and you will do it when it matters.

What is the single most valuable thing on this list?

Restricting reachability, then a tested backup. The first prevents most of what happens; the second means the rest is survivable. Everything else narrows the damage in between.

My host manages the database. Which of this is still mine?

Reachability, engine patching and the underlying machine are the host's. Who you create, what rights you give them, whether the connection string verifies TLS, whether your queries are parameterised, where the credentials are stored and whether you have ever restored a backup are all yours, and they are where the incidents come from.