Database Security Best Practices Every Developer Should Know

Why Database Security Matters

Your database is the most valuable asset in any application. It stores user credentials, financial records, personal information, and business-critical data. A single breach can expose millions of records, destroy user trust, and result in severe regulatory penalties under laws like GDPR or HIPAA.

Database security is not an optional add-on — it is a fundamental responsibility that must be addressed from day one. This guide covers the most impactful practices you can apply regardless of which database system you use.

Principle of Least Privilege

Every user, service, or application connecting to your database should have the minimum permissions required to do its job — nothing more.

-- Create a read-only user for reporting services
CREATE USER reporting_user WITH PASSWORD 'strong_password_here';
GRANT CONNECT ON DATABASE myapp TO reporting_user;
GRANT USAGE ON SCHEMA public TO reporting_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

-- Create an application user with only necessary write permissions
CREATE USER app_user WITH PASSWORD 'another_strong_password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON users, orders, products TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Explicitly revoke dangerous permissions
REVOKE DELETE ON users FROM app_user;

With this approach, if your application is compromised, the attacker can only perform what the app_user role allows. They cannot drop tables, delete all users, or access unrelated schemas.

Also apply this principle at the column level for sensitive data:

-- Create a view that hides sensitive columns
CREATE VIEW users_public AS
  SELECT id, username, created_at FROM users;

GRANT SELECT ON users_public TO app_user;
-- app_user never sees password_hash or payment_info columns

Parameterized Queries and SQL Injection Prevention

SQL injection remains one of the most prevalent and dangerous attack vectors. It occurs when user-supplied input is concatenated directly into a SQL string.

Vulnerable pattern (never do this):

-- This is dangerous! If username = "admin'--", authentication is bypassed
SELECT * FROM users WHERE username = '${username}' AND password = '${password}';

Safe pattern using parameterized queries:

-- Parameters are treated as data, never as executable SQL
SELECT * FROM users WHERE username = $1 AND password_hash = $2;
-- Pass values separately: ['admin', 'hashed_password']

Most ORMs and database drivers use parameterized queries by default. However, be careful with dynamic ORDER BY clauses or table names, which cannot be parameterized. Use allowlists for those cases.

Encryption at Rest and in Transit

At rest: Use your database system's encryption features or rely on full-disk encryption. PostgreSQL supports Transparent Data Encryption (TDE) via pgcrypto or third-party extensions. AWS RDS and Google Cloud SQL offer encryption at rest with a single checkbox.

In transit: Always require SSL/TLS connections between your application and the database.

-- PostgreSQL: Check if SSL is active for current connection
SELECT ssl, client_addr FROM pg_stat_ssl WHERE pid = pg_backend_pid();

-- Force SSL in pg_hba.conf
-- hostssl  myapp  app_user  0.0.0.0/0  scram-sha-256

Sensitive column encryption: For highly sensitive data like social security numbers or payment tokens, encrypt at the application layer before storing:

-- Using pgcrypto for column-level encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO user_secrets (user_id, ssn_encrypted)
VALUES (
  $1,
  pgp_sym_encrypt($2, current_setting('app.encryption_key'))
);

SELECT pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key')) AS ssn
FROM user_secrets WHERE user_id = $1;

Auditing and Monitoring

Enable logging for authentication events and sensitive operations. Many breaches go undetected for months — monitoring helps you catch anomalies early.

-- PostgreSQL: Enable query logging for slow or all queries
-- In postgresql.conf:
-- log_min_duration_statement = 1000  -- Log queries over 1 second
-- log_connections = on
-- log_disconnections = on
-- log_failed_authentications = on

-- Create an audit log table
CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100),
  operation  VARCHAR(10),  -- INSERT, UPDATE, DELETE
  user_id    UUID,
  old_data   JSONB,
  new_data   JSONB,
  changed_at TIMESTAMP DEFAULT now()
);

-- Trigger to log changes automatically
CREATE OR REPLACE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
AFTER UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_changes();

Design Your Security with Erdmates

Visualizing your database schema with Erdmates makes security reviews much easier. You can see at a glance which tables contain sensitive data, map relationships between entities, and share diagrams with your security team to identify vulnerabilities early in the design phase — before any code is written.

Conclusion

Database security requires a layered approach: restrict permissions aggressively, use parameterized queries without exception, encrypt data at rest and in transit, and monitor access continuously. Address security during schema design, not as an afterthought. The cost of prevention is always lower than the cost of a breach.