dbpulse 0.9.1

command line tool to monitor that database is available for read & write
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

# Integration Testing Scripts

This directory contains the certificate generation script for TLS integration testing.

## Overview

TLS testing is fully integrated into the justfile. No manual script execution needed!

## Quick Start

```bash
# Run all tests (unit, integration, TLS)
just test

# Run only TLS tests
just test-tls

# Run only integration tests (non-TLS)
just test-integration
```

The justfile automatically:
- ✅ Generates TLS certificates
- ✅ Starts containers with proper configuration
- ✅ Waits for databases to be ready
- ✅ Runs tests
- ✅ Cleans up everything

## Scripts

### `gen-certs.sh`

Generates self-signed TLS certificates for both PostgreSQL and MariaDB.

**Output structure:**
```
.certs/
├── postgres/
│   ├── ca.crt          # Certificate Authority
│   ├── ca.key          # CA private key
│   ├── server.crt      # Server certificate
│   ├── server.key      # Server private key
│   ├── client.crt      # Client certificate (optional)
│   └── client.key      # Client private key (optional)
└── mariadb/
    ├── ca.crt
    ├── ca.key
    ├── server.crt
    ├── server.key
    ├── client.crt
    └── client.key
```

**Features:**
- 10-year validity period
- Subject Alternative Names (SAN) for localhost, DNS names, and IP
- Proper permissions for database servers
- OpenSSL verification

## TLS Container Configuration

TLS-enabled databases are managed directly by the justfile using Podman.

**What gets created:**

#### PostgreSQL
- Image: Custom-built `postgres-tls:test` (proper key ownership)
- Port: `5432`
- TLS: Enabled (TLSv1.2, TLSv1.3)
- Container name: `dbpulse-postgres-tls`

#### MariaDB
- Image: `mariadb:11`
- Port: `3306`
- TLS: Enabled (TLSv1.2, TLSv1.3)
- Configuration:
  - SSL available but not required
  - CA certificate validation
  - Self-signed server certificate

#### MariaDB
- Image: `mariadb:11`
- Port: `3306`
- TLS: Enabled (TLSv1.2, TLSv1.3)
- Container name: `dbpulse-mariadb-tls`

**Manual access:**
```bash
# View PostgreSQL SSL status
podman exec dbpulse-postgres-tls psql -U postgres -d testdb -c "SHOW ssl;"

# View MariaDB SSL status
podman exec dbpulse-mariadb-tls mariadb -u root -psecret -e "SHOW VARIABLES LIKE 'have_ssl';"
```

## Connection Strings

### PostgreSQL

```bash
# TLS required
postgresql://postgres:secret@tcp(localhost:5432)/testdb?sslmode=require

# TLS with CA verification
postgresql://postgres:secret@tcp(localhost:5432)/testdb?sslmode=verify-ca&sslrootcert=.certs/postgres/ca.crt

# TLS with full verification
postgresql://postgres:secret@tcp(localhost:5432)/testdb?sslmode=verify-full&sslrootcert=.certs/postgres/ca.crt
```

### MariaDB/MySQL

```bash
# TLS required
mysql://dbpulse:secret@tcp(localhost:3306)/testdb?ssl-mode=REQUIRED

# TLS with CA verification
mysql://dbpulse:secret@tcp(localhost:3306)/testdb?ssl-mode=VERIFY_CA&ssl-ca=.certs/mariadb/ca.crt

# TLS with full verification
mysql://dbpulse:secret@tcp(localhost:3306)/testdb?ssl-mode=VERIFY_IDENTITY&ssl-ca=.certs/mariadb/ca.crt
```

## Testing TLS Connections

### PostgreSQL

```bash
# Using psql
PGSSLMODE=require psql "postgresql://postgres:secret@localhost:5432/testdb" \
  -c "SELECT ssl_is_used(), version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

# Verify SSL is enabled
podman exec dbpulse-postgres-tls psql -U postgres -d testdb -c "SHOW ssl;"
```

### MariaDB

```bash
# Using mariadb client
mariadb -h 127.0.0.1 -u dbpulse -psecret -D testdb --ssl-mode=REQUIRED \
  -e "SHOW STATUS LIKE 'Ssl_cipher';"

# Verify SSL is enabled
podman exec dbpulse-mariadb-tls mariadb -u root -psecret -e "SHOW VARIABLES LIKE 'have_ssl';"
```

## GitHub Actions

The repository includes a dedicated workflow for TLS integration testing:

**`.github/workflows/integration-test-tls.yml`**
- Generates certificates on-the-fly
- Starts Docker containers with TLS enabled
- Runs integration tests with TLS connections
- Verifies TLS is actually being used
- Shows logs on failure

## Troubleshooting

### Certificate Issues

```bash
# Verify certificate validity
openssl verify -CAfile .certs/postgres/ca.crt .certs/postgres/server.crt
openssl verify -CAfile .certs/mariadb/ca.crt .certs/mariadb/server.crt

# Inspect certificate details
openssl x509 -in .certs/postgres/server.crt -text -noout

# Check certificate expiration
openssl x509 -in .certs/postgres/server.crt -noout -dates
```

#### PostgreSQL Key Permission Issues

PostgreSQL requires the private key to be owned by the `postgres` user and have `600` permissions. When using Docker bind mounts, this can be problematic. Solutions:

1. **Use Docker COPY (Recommended for CI/CD)**:
   ```bash
   # Build custom image with proper permissions
   cat > Dockerfile.postgres-tls <<EOF
   FROM postgres:18-alpine
   COPY .certs/postgres/server.crt /var/lib/postgresql/server.crt
   COPY .certs/postgres/server.key /var/lib/postgresql/server.key
   COPY .certs/postgres/ca.crt /var/lib/postgresql/ca.crt
   RUN chown postgres:postgres /var/lib/postgresql/server.* /var/lib/postgresql/ca.crt && \
       chmod 600 /var/lib/postgresql/server.key && \
       chmod 644 /var/lib/postgresql/server.crt /var/lib/postgresql/ca.crt
   EOF
   docker build -t postgres-tls:local -f Dockerfile.postgres-tls .
   docker run -d --name postgres-tls -p 5432:5432 postgres-tls:local -c ssl=on ...
   ```

2. **Fix MariaDB Key Permissions**:
   ```bash
   # MariaDB requires readable key when using bind mounts
   chmod 644 .certs/mariadb/server.key
   ```
```

### Container Issues

```bash
# Check container logs
podman logs dbpulse-postgres-tls
podman logs dbpulse-mariadb-tls

# Check container status
podman ps -a | grep dbpulse

# Clean up manually
podman rm -f dbpulse-postgres-tls dbpulse-mariadb-tls
rm -rf .certs
```

### Connection Issues

```bash
# Test PostgreSQL connectivity
podman exec dbpulse-postgres-tls pg_isready -U postgres

# Test MariaDB connectivity
podman exec dbpulse-mariadb-tls mariadb -u dbpulse -psecret -D testdb -e "SELECT 1"

# Check if ports are listening
ss -tlnp | grep -E '5432|3306'
```

## Best Practices

1. **Certificate Regeneration**: Run `just test-tls` - it regenerates certificates automatically
2. **Clean State**: The justfile handles cleanup automatically between test runs
3. **Port Conflicts**: Ensure ports 5432 and 3306 are available before running tests

## Environment Variables

- `TEST_POSTGRES_DSN`: Override PostgreSQL connection string for tests
- `TEST_MARIADB_DSN`: Override MariaDB connection string for tests

## References

- [PostgreSQL SSL Support](https://www.postgresql.org/docs/current/ssl-tcp.html)
- [MariaDB TLS Configuration](https://mariadb.com/kb/en/securing-connections-for-client-and-server/)
- [Podman](https://podman.io/)