What basic steps would you take to secure a new production database?

What the interviewer wants to hear

• Keep it on a private network and restrict access with firewall rules
• Use strong authentication such as scram-sha-256 and remove default or shared accounts
• Give each application its own least-privilege role
• Require TLS in transit and encrypt data and backups at rest
• Patch regularly and enable auditing of logins and privileged actions

Model answer

First I make sure the database is not reachable from the internet: it lives in a private subnet with security group or firewall rules that only allow the app servers and admin hosts. Authentication uses strong methods like scram-sha-256 or certificates, default accounts are removed, and secrets are stored in a vault, not in code. Each application gets its own role with only the privileges it needs, so the app cannot drop tables, and humans use personal accounts. I require TLS for connections and enable encryption at rest for data files and backups. Then I keep the server patched to the latest minor version, log connections and privileged actions with something like pgAudit, and review who has access regularly.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• Explain the main SQL join types and give an example of when you would use each.