[feature]: Network Separation Issue for PostgreSQL Backend

[ziggie1984]

Problem Description

LND supports multiple database backends (bbolt, SQLite, PostgreSQL) and multiple Bitcoin networks (mainnet, testnet, regtest, signet, testnet4). However, there is currently an inconsistency in how network separation is handled across these backends that could lead to data corruption.

Current Behavior by Backend

bbolt/SQLite (File-based backends) ✅

These backends achieve network separation through file-system isolation. Each network gets its own directory and database files:

~/.lnd/data/chain/bitcoin/mainnet/channel.db
~/.lnd/data/chain/bitcoin/testnet/channel.db
~/.lnd/data/chain/bitcoin/regtest/channel.db

Result: It is impossible to accidentally mix data between networks because they are physically separate files.

PostgreSQL (Connection-based backend) ❌

The PostgreSQL backend uses a single DSN (connection string) configured via:

[db]
db.backend=postgres
db.postgres.dsn=postgresql://user:pass@host:5432/lnd

Problem: If a user switches networks (e.g., from mainnet to testnet) without changing the DSN, all networks will write to the same database tables, causing data corruption:

-- Same tables used for ALL networks!
channeldb_kv       -- Would contain mixed mainnet/testnet/regtest data
walletdb_kv        -- Would contain mixed wallet data
invoices           -- Would contain mixed wallet data

Risk Scenario

1. User runs LND with --bitcoin.mainnet and db.postgres.dsn=postgresql://user:pass@host/lnd
2. User stops LND
3. User runs LND with --bitcoin.testnet but forgets to change the DSN
4. Testnet data is now mixed with mainnet data in the same tables
5. Data corruption - mainnet channels, invoices, and wallet state are now mixed with testnet data

This is especially dangerous because:

• There is no warning or error
• The corruption may not be immediately obvious
• Recovery requires manual database inspection and cleanup
• Users familiar with bbolt/SQLite expect automatic network separation

Proposed Solutions

Option 1: Network-Aware Table Prefixes

Add the network identifier to all table names.

Implementation:

• Modify table name generation to include network: {network}_{prefix}_kv
• Example tables:

  mainnet_channeldb_kv
  testnet_channeldb_kv
  regtest_channeldb_kv

Pros:

• Simple implementation
• Single database can hold all networks
• Backward compatible with migration
• Consistent with existing table prefix approach

Cons:

• Adds "noise" to table names
• Users still share a single database for all networks
• Doesn't provide database-level isolation
• More complex migration path for existing deployments

Option 2: Runtime Network Validation with Metadata Table (Recommended) ✅

Store the network identifier in the database on first initialization and validate on every startup.

Implementation:

1. Create metadata table (using native SQL migrations):

   -- New migration: 000009_metadata.up.sql
   CREATE TABLE IF NOT EXISTS metadata (
       key TEXT PRIMARY KEY,
       value TEXT NOT NULL,
       updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );

2. On first database initialization:

   INSERT INTO metadata (key, value)
   VALUES ('network', 'mainnet');

3. On every subsequent startup:

   storedNetwork := fetchMetadata("network")
   if storedNetwork != cfg.ActiveNetParams.Name {
       return fmt.Errorf(
           "database network mismatch: database is configured for '%s' "+
           "but lnd is starting with '%s'. Either use a different database "+
           "or change your network configuration",
           storedNetwork, cfg.ActiveNetParams.Name,
       )
   }

4. User consequence when switching networks:

   • Must point to a different database DSN, OR
   • Must wipe existing database

Pros:

• ✅ Prevents data corruption - Hard error on mismatch
• ✅ Clean table names - No network prefix needed
• ✅ User flexibility - Users choose their own database naming
• ✅ Simple implementation - Just one metadata check
• ✅ Clear error messages - Users know exactly what's wrong
• ✅ Idiomatic Go/SQL - Metadata tables are standard practice
• ✅ Works with existing migration infrastructure - Uses golang-migrate
• ✅ Minimal code changes - Mostly in database initialization

Cons:

• Users must create a new database when switching networks (but this is the correct behavior)
• Requires one additional migration

Thank you for brining this to attention @mohamedawnallah & @yyforyongyu

[yyforyongyu]

Also favor option 2.

[Abdulkbk]

@saubyk, you can assign this to me :)