Secure Database Connections

When your applications connect to a database, sensitive data often travels across the network. Without proper security, this data could be intercepted or tampered with.

• Confidentiality: Prevents unauthorized viewing of data.
• Integrity: Ensures data isn't altered during transit.
• Authentication: Verifies the identity of both client and server.

This lesson focuses on two key methods: SSL/TLS encryption and IP-based access restrictions.

SSL/TLS (Secure Sockets Layer/Transport Layer Security) is the standard technology for establishing an encrypted link between a web server and a client (or in our case, a database server and a client).

• It encrypts all data exchanged, protecting it from eavesdropping.
• It uses digital certificates to verify the identity of the server (and optionally, the client).
• It's crucial for protecting data like passwords, financial details, and personal information during transfer.

To use SSL/TLS, your database server needs a digital certificate and a private key. These prove the server's identity.

• Self-signed certificates: Easiest to create for testing or internal networks, but not trusted by default.
• CA-signed certificates: Issued by a Certificate Authority (CA) and globally trusted, ideal for production.

For this lesson, we'll assume you have a server certificate (server.crt) and a private key (server.key) ready.

To enable SSL in PostgreSQL, you need to edit the postgresql.conf file. This tells the server to listen for secure connections and where to find your certificates.

Key parameters:

• ssl = on: Turns SSL on.
• ssl_cert_file: Path to your server certificate.
• ssl_key_file: Path to your server's private key.

After changes, restart PostgreSQL.

For MySQL, you'll configure SSL/TLS in the my.cnf file (or my.ini on Windows), typically within the [mysqld] section.

You specify the paths to the server certificate, private key, and optionally a Certificate Authority (CA) certificate if you're using one to verify client certificates.

After modifying, restart the MySQL service for changes to take effect.

Once the server is configured, clients need to be told to use SSL/TLS when connecting. This often involves specifying SSL options in the connection string or command.

Here's an example using the psql client for PostgreSQL, explicitly requiring SSL mode:

• sslmode=require: Forces an SSL connection.
• sslrootcert: Path to the CA certificate for server verification.

Beyond encryption, restricting access based on the client's IP address is a fundamental security practice. This ensures only trusted machines can even attempt to connect to your database.

You can configure this:

• At the database server level (e.g., PostgreSQL's pg_hba.conf or MySQL's GRANT statements).
• Using a firewall (like UFW or firewalld) to block traffic to the database port from unauthorized IPs.

Combining both methods offers a layered defense.

PostgreSQL uses the pg_hba.conf file for host-based authentication. Each line specifies connection types, databases, users, and allowed IP addresses.

Common fields:

• TYPE: host (TCP/IP), local (Unix socket).
• DATABASE: all, specific DB name.
• USER: all, specific user.
• ADDRESS: IP range (e.g., 192.168.1.0/24) or single IP.
• METHOD: scram-sha-256, md5, peer, etc.

Remember to restart PostgreSQL after editing.

In MySQL, you restrict user access by specifying the host from which they can connect when creating or altering a user with the GRANT statement.

The 'user'@'host' syntax is key:

• 'localhost': Only from the server itself.
• '192.168.1.100': Only from a specific IP address.
• '%': From any host (use with extreme caution!).
• '192.168.1.%': From any IP in the 192.168.1.x range.

Always use the most restrictive host possible.

Securing database connections is an ongoing effort. Here are some best practices:

• Least Privilege: Grant only the necessary permissions to users and applications.
• Strong Passwords: Enforce complex passwords and rotate them regularly.
• Firewall Rules: Use your server's firewall (UFW/firewalld) to restrict access to database ports (e.g., 5432 for PostgreSQL, 3306 for MySQL) to only trusted IPs.
• Regular Audits: Periodically review database logs and access configurations.
• Keep Software Updated: Apply security patches to your database server and operating system promptly.

Which of the following are valid methods to enhance the security of database connections?

In this lesson, we explored vital techniques for securing your database connections:

• SSL/TLS: Encrypts data in transit, ensuring confidentiality and integrity. We saw how to enable it in PostgreSQL and MySQL, and how clients connect securely.
• IP Restrictions: Limits who can connect to your database based on their network address, using pg_hba.conf for PostgreSQL and GRANT statements for MySQL.
• Best Practices: We also covered general security tips like using strong passwords and regular audits.

By implementing these, you significantly reduce the risk of unauthorized access and data breaches.