Designing a Data Source Discovery App - Part 21: Securing the kds_discovery_2025 Database

by DL Keeshin

May 24, 2025

kDS Discovery 2025 Database ERD

In my last post, I covered securing the application layer with token-based authentication for our kDS Discovery app. While these measures are crucial for protecting the application perimeter, they represent only part of the security equation. The app database is the crown jewel of the kDS Discovery application – it holds all of the sensitive data, user information, and business logic. If an attacker bypasses application security or exploits a vulnerability in the application code, a poorly secured database becomes completely exposed.

This post outlines the requirements for implementing robust PostgreSQL database security that goes far beyond basic password protection. The kDS Discovery App security plan covers everything from encryption and access control to monitoring and backup security, creating multiple layers of defense that protect the app's data even if other security measures fail.

The Foundation: Connection Security

SSL/TLS Encryption - The First Line of Defense

Every piece of data traveling between the kDS Discovery app and the kds_discovery_2025 database should be encrypted. PostgreSQL's SSL/TLS encryption ensures that even if network traffic is intercepted, the app's data remains unreadable.

Enable SSL in the app's PostgreSQL configuration:

-- In postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

-- Require SSL for all connections
ALTER SYSTEM SET ssl = on;

Update the app connection strings to require SSL:

DATABASE_URL = "postgresql://user:password@host:5432/kds_discovery_2025?sslmode=require"

The sslmode=require parameter ensures the app will refuse to connect if SSL isn't available, preventing accidental unencrypted connections.

Moving Beyond Passwords: Certificate Authentication

While encrypted connections protect data in transit, the app needs to authenticate who's connecting to the database. Certificate-based authentication is significantly more secure than password-based authentication because:

  • Certificates can't be brute-forced
  • They're harder to accidentally expose in logs or configuration files
  • They provide mutual authentication (both client and server verify each other)
-- In pg_hba.conf, require client certificates
hostssl all all 0.0.0.0/0 cert clientcert=1

If we must use password authentication, we use PostgreSQL's strongest option:

-- Enable SCRAM-SHA-256 authentication
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

-- Update existing user passwords to use new encryption
ALTER USER dbuser PASSWORD 'new_password';

Implementing Role-Based Access Control (RBAC)

Not every part of the application needs the same level of database access. A reporting dashboard only needs to read data, while the kDS Discovery app might need read-write access, and administrative tools need full control. Implementing proper RBAC follows the principle of least privilege – each component gets exactly the permissions it needs, nothing more.

Creating Meaningful Roles

Design database roles around actual business functions:

-- Create three distinct access levels for kds_discovery_2025
CREATE ROLE kdsd_read;        -- For reporting, analytics
CREATE ROLE kdsd_readwrite;   -- For main kDS Discovery app
CREATE ROLE kdsd_admin;       -- For migrations, admin tools

-- Read-only access for analytics and reporting
GRANT CONNECT ON DATABASE kds_discovery_2025 TO kdsd_read;
GRANT USAGE ON SCHEMA interview, study TO kdsd_read;
GRANT SELECT ON ALL TABLES IN SCHEMA interview, study TO kdsd_read;

-- Read-write access for main kDS Discovery app operations
GRANT CONNECT ON DATABASE kds_discovery_2025 TO kdsd_readwrite;
GRANT USAGE ON SCHEMA interview, study TO kdsd_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA interview, study TO kdsd_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA interview, study TO kdsd_readwrite;

-- Full administrative access
GRANT ALL PRIVILEGES ON DATABASE kds_discovery_2025 TO kdsd_admin;

Creating Dedicated Database Users

Each role should have corresponding database users with connection limits to prevent resource exhaustion:

-- Create users for each access level
CREATE USER app_reader WITH PASSWORD 'secure_password' IN ROLE kdsd_read;
CREATE USER app_writer WITH PASSWORD 'secure_password' IN ROLE kdsd_readwrite;
CREATE USER app_admin WITH PASSWORD 'secure_password' IN ROLE kdsd_admin;

-- Limit connections to prevent abuse
ALTER USER app_reader CONNECTION LIMIT 50;   -- Many concurrent reports
ALTER USER app_writer CONNECTION LIMIT 20;   -- Normal app usage
ALTER USER app_admin CONNECTION LIMIT 5;     -- Limited admin operations

Fine-Grained Security with Row-Level Security (RLS)

Role-based access control handles what tables users can access, but what about controlling which rows they can see? This is where PostgreSQL's Row-Level Security becomes invaluable, especially in scenarios where interview respondents should only see their own data.

Implementing Customer Data Isolation

-- Enable RLS on sensitive tables
ALTER TABLE interview.respondent ENABLE ROW LEVEL SECURITY;

-- Create policy for customer isolation
CREATE POLICY customer_isolation ON interview.respondent
FOR ALL
USING (customer_id = current_setting('app.current_customer_id')::uuid);

-- Policy for users to access only their own data
CREATE POLICY respondent_self_access ON interview.invite
FOR SELECT
TO kdsd_read
USING (respondent_id = current_setting('app.current_respondent_id')::uuid);

The app sets the current customer or user context, and PostgreSQL automatically filters all queries to respect these boundaries. Even if there's a bug in the application code that constructs the wrong SQL query, users still can't access data they shouldn't see.

Network Security: Keeping Database Traffic Private

Firewall Configuration

The database should never be directly accessible from the internet. We use cloud provider firewalls to restrict access to only the application servers:

# Digital Ocean Cloud Firewall example
doctl compute firewall create \
  --name "kdsd-db-firewall" \
  --inbound-rules "protocol:tcp,ports:5432,sources:addresses:10.0.1.0/24"

Private Network Architecture

Keep database traffic off the public internet entirely by using private IP addresses:

# Use private IP addresses for kds_discovery_2025 connections
DATABASE_URL = "postgresql://user:password@10.0.1.5:5432/kds_discovery_2025?sslmode=require"

This ensures that even if someone compromises the app's network configuration, database traffic never leaves the private network.

Secrets Management: Never Hardcode Credentials

Hardcoded database credentials are a security disaster waiting to happen. They end up in version control, log files, environment variables that get exposed, and configuration files that get misconfigured. Instead, implement proper secrets management with encryption and rotation.

Environment Variable Encryption

import os
from cryptography.fernet import Fernet

def encrypt_config():
    key = Fernet.generate_key()
    cipher = Fernet(key)
    
    # Encrypt database credentials
    encrypted_password = cipher.encrypt(b"actual_password")
    
    # Store encrypted values
    os.environ['DB_PASSWORD_ENCRYPTED'] = encrypted_password.decode()
    os.environ['ENCRYPTION_KEY'] = key.decode()

def get_db_password():
    key = os.environ['ENCRYPTION_KEY'].encode()
    cipher = Fernet(key)
    encrypted_password = os.environ['DB_PASSWORD_ENCRYPTED'].encode()
    return cipher.decrypt(encrypted_password).decode()

Monitoring and Auditing: Know What's Happening

Security isn't just about prevention – we need to detect and respond to potential threats. Comprehensive logging and auditing help us identify suspicious activity and meet compliance requirements.

Enable Comprehensive Logging

-- In postgresql.conf
log_statement = 'all'
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p-%l] %u@%d '
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

Backup Security: Protecting the kDS Discovery App Data at Rest

Backups contain the same sensitive data as the app's live database, so they need the same level of protection.

Encrypted Backup Strategy

#!/bin/bash
BACKUP_FILE="kds_discovery_2025_backup_$(date +%Y%m%d_%H%M%S).sql"

# Create backup of kds_discovery_2025
pg_dump -h localhost -U postgres kds_discovery_2025 > $BACKUP_FILE

# Encrypt backup with strong cipher
gpg --symmetric --cipher-algo AES256 $BACKUP_FILE

# Remove unencrypted file
rm $BACKUP_FILE

# Upload to secure cloud storage
aws s3 cp $BACKUP_FILE.gpg s3://kdsd-secure-backups/

The Bottom Line

Database security is the foundation everything else is built on. The kDS Discovery app tackles this with multiple layers of protection:

  • SSL/TLS encryption and certificate authentication secure connections
  • Role-based access control limits what each component can do
  • Row-level security isolates customer data
  • Private networks and firewalls keep traffic locked down
  • Encrypted credentials eliminate hardcoded passwords
  • Comprehensive logging catches suspicious activity
  • Encrypted backups protect data at rest

This defense-in-depth approach protects the app's data even when other security measures fail.

The kDS Discovery app treats security as an ongoing process – we regularly review our security posture, apply updates promptly, and monitor for threats. We started with SSL encryption and strong authentication, then built up to advanced features like row-level security and automated auditing. The time invested in proper database security has paid off in reduced risk, better compliance, and peace of mind.

Join Our Beta Program

As we continue developing the kDS Data Source Discovery App with these enterprise-grade security features, we're actively seeking organizations to participate in our beta program. If your organization could benefit from streamlined data source discovery with robust security controls, we'd love to collaborate with you.

Interested? Leave a comment below or reach out directly at info@keeshinds.com.

Thank you for stopping by.

Leave a Comment: