postgres gc10 Declarative Setup Summary

PostgreSQL gc-10 Declarative Setup - Implementation Summary

Overview

The PostgreSQL configuration has been updated to be fully declarative, eliminating the need for manual intervention to run the service with the endoreg-service-user. All database users, passwords, and authentication are now managed automatically by NixOS.

Changes Made

1. Enhanced PostgreSQL Default Role (modules/nixos/roles/postgres-default/default.nix)

Added:

• Automatic password generation for endoregDbLocal user
• Systemd service postgres-endoreg-setup that:
  • Generates password if it doesn't exist
  • Sets password in PostgreSQL
  • Manages file permissions securely
• Proper secret file management with group permissions

Key Features:

• Password stored in /etc/secrets/vault/SCRT_local_password_maintenance_password
• File permissions: 640 with group sensitiveServices
• Automatic PostgreSQL user password synchronization

2. Updated EndoReg DB API Service (modules/nixos/services/endoreg-db-api-local/default.nix)

Changes:

• Removed manual password generation logic
• Added dependency on postgres-endoreg-setup.service
• Simplified password handling (reads from vault)
• Error handling if password not available

3. Enhanced User Configuration (modules/nixos/user/endoreg-service-user/default.nix)

Added:

• endoreg-service-user is now member of sensitiveServices group
• Can read password files securely

4. Updated Documentation (postgres-documentation.md)

Added:

• Comprehensive declarative setup explanation
• Verification commands
• Deployment instructions
• Troubleshooting guide for new setup

5. Updated Scripts

Modified:

• Added notices that scripts should no longer be needed
• Kept for legacy troubleshooting only

Deployment Steps

1. Deploy Configuration

sudo nixos-rebuild switch

2. Verify Services

systemctl status postgresql
systemctl status postgres-endoreg-setup
systemctl status endo-api-boot

3. Test Database Connection

PGPASSWORD="$(sudo cat /etc/secrets/vault/SCRT_local_password_maintenance_password)" \
psql -h localhost -U endoregDbLocal -d endoregDbLocal -c "SELECT current_user;"

Benefits of New Setup

1. Fully Declarative: No manual intervention required
2. Secure: Proper file permissions and group-based access
3. Reliable: Service dependencies ensure proper startup order
4. Maintainable: Centralized password management
5. Debuggable: Clear service states and logs

Key Files Modified

• /home/admin/dev/luxnix/modules/nixos/roles/postgres-default/default.nix
• /home/admin/dev/luxnix/modules/nixos/services/endoreg-db-api-local/default.nix
• /home/admin/dev/luxnix/modules/nixos/user/endoreg-service-user/default.nix
• /home/admin/dev/luxnix/postgres-documentation.md
• /home/admin/dev/luxnix/scripts/fix-postgres-gc10.sh

What Happens Automatically Now

1. System Boot:

   • PostgreSQL starts
   • postgres-endoreg-setup service runs
   • Password is generated/verified
   • endoregDbLocal user password is set
   • endo-api-boot service starts

2. No Manual Steps Needed:

   • No need to run scripts
   • No need to manually create users
   • No need to set passwords
   • No need to modify pg_hba.conf

Next Steps

1. Deploy the configuration with sudo nixos-rebuild switch
2. Verify everything works as expected
3. Test that the service can connect to the database
4. Remove or archive the manual fix scripts once confirmed working

The setup should now work completely without manual intervention!