Database Security

val url = "jdbc:postgresql://db.example.com:5432/pesanan" +
          "?user=appuser" +
          "&password=" + System.getenv("DB_PASSWORD") +
          "&sslmode=verify-full" +
          "&sslrootcert=/etc/ssl/certs/db-ca.pem"

-- Role read-only untuk reporting
CREATE ROLE app_ro WITH LOGIN PASSWORD 'RANDOM_PASSWORD';
GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

-- Role read-write untuk aplikasi
CREATE ROLE app_rw WITH LOGIN PASSWORD 'RANDOM_PASSWORD';
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
REVOKE CREATE ON SCHEMA public FROM app_rw;

// database_security.kt
// Mendemonstrasikan praktik keamanan database: koneksi terenkripsi (TLS),
// prinsip least privilege (user dengan hak minimum), dan audit log. Memakai
// SQLite via JDBC untuk simulasi nyata, dengan bagian TLS/least-privilege
// dimodelkan sebagai konfigurasi yang dijelaskan (karena SQLite embedded
// tidak punya server TLS atau role-based access seperti PostgreSQL).

import java.sql.Connection
import java.sql.DriverManager

// Konfigurasi koneksi aman (di produksi: dari environment variable / vault)
data class SecureDbConfig(
    val jdbcUrl: String,
    val username: String,
    val passwordEnvVar: String,        // nama env var, bukan nilai password
    val sslmode: String = "require",   // require, verify-ca, verify-full
    val sslRootCert: String = "/etc/ssl/certs/db-ca.pem",
    val applicationName: String = "kotlin-tutorial"
)

// Audit log: catat siapa melakukan apa kapan
data class AuditEntry(
    val timestamp: String,
    val actor: String,
    val action: String,
    val tableName: String,
    val rowId: String,
    val details: String
)

class AuditLogger(private val conn: Connection) {
    fun createTable() {
        conn.createStatement().use { it.execute(
            """CREATE TABLE audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                actor TEXT NOT NULL,
                action TEXT NOT NULL,
                table_name TEXT NOT NULL,
                row_id TEXT NOT NULL,
                details TEXT
            )"""
        ) }
    }

    fun log(entry: AuditEntry) {
        conn.prepareStatement(
            """INSERT INTO audit_log(timestamp, actor, action, table_name, row_id, details)
               VALUES(?, ?, ?, ?, ?, ?)"""
        ).use { ps ->
            ps.setString(1, entry.timestamp)
            ps.setString(2, entry.actor)
            ps.setString(3, entry.action)
            ps.setString(4, entry.tableName)
            ps.setString(5, entry.rowId)
            ps.setString(6, entry.details)
            ps.executeUpdate()
        }
    }

    fun recent(limit: Int = 10): List<AuditEntry> {
        val result = mutableListOf<AuditEntry>()
        conn.prepareStatement("SELECT * FROM audit_log ORDER BY id DESC LIMIT ?").use { ps ->
            ps.setInt(1, limit)
            ps.executeQuery().use { rs ->
                while (rs.next()) {
                    result.add(AuditEntry(
                        timestamp = rs.getString("timestamp"),
                        actor = rs.getString("actor"),
                        action = rs.getString("action"),
                        tableName = rs.getString("table_name"),
                        rowId = rs.getString("row_id"),
                        details = rs.getString("details") ?: ""
                    ))
                }
            }
        }
        return result
    }
}

// Wrapper: setiap operasi write otomatis mengisi audit log
class SecureRepository(private val conn: Connection, private val audit: AuditLogger) {
    fun createTable() {
        conn.createStatement().use { it.execute(
            """CREATE TABLE pengguna (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT NOT NULL UNIQUE,
                nama TEXT NOT NULL,
                peran TEXT NOT NULL DEFAULT 'viewer'
            )"""
        ) }
    }

    fun insertPengguna(actor: String, email: String, nama: String, peran: String): Int {
        var newId = 0
        conn.prepareStatement(
            "INSERT INTO pengguna(email, nama, peran) VALUES(?, ?, ?)"
        ).use { ps ->
            ps.setString(1, email)
            ps.setString(2, nama)
            ps.setString(3, peran)
            ps.executeUpdate()
        }
        conn.createStatement().use { stmt ->
            stmt.executeQuery("SELECT last_insert_rowid()").use { rs ->
                if (rs.next()) newId = rs.getInt(1)
            }
        }
        audit.log(AuditEntry(
            timestamp = "2026-01-15T08:00:00Z",
            actor = actor,
            action = "INSERT",
            tableName = "pengguna",
            rowId = newId.toString(),
            details = "email=$email, peran=$peran"
        ))
        return newId
    }

    fun updatePeran(actor: String, id: Int, peranBaru: String): Boolean {
        var ok = false
        conn.prepareStatement("UPDATE pengguna SET peran = ? WHERE id = ?").use { ps ->
            ps.setString(1, peranBaru)
            ps.setInt(2, id)
            ok = ps.executeUpdate() > 0
        }
        if (ok) {
            audit.log(AuditEntry(
                timestamp = "2026-01-15T08:05:00Z",
                actor = actor,
                action = "UPDATE",
                tableName = "pengguna",
                rowId = id.toString(),
                details = "peran=$peranBaru"
            ))
        }
        return ok
    }

    fun deletePengguna(actor: String, id: Int): Boolean {
        var ok = false
        conn.prepareStatement("DELETE FROM pengguna WHERE id = ?").use { ps ->
            ps.setInt(1, id)
            ok = ps.executeUpdate() > 0
        }
        if (ok) {
            audit.log(AuditEntry(
                timestamp = "2026-01-15T08:10:00Z",
                actor = actor,
                action = "DELETE",
                tableName = "pengguna",
                rowId = id.toString(),
                details = ""
            ))
        }
        return ok
    }

    fun listPengguna(): List<Triple<Int, String, String>> {
        val result = mutableListOf<Triple<Int, String, String>>()
        conn.createStatement().use { stmt ->
            stmt.executeQuery("SELECT id, email, peran FROM pengguna ORDER BY id").use { rs ->
                while (rs.next()) result.add(Triple(rs.getInt("id"), rs.getString("email"), rs.getString("peran")))
            }
        }
        return result
    }
}

fun main() {
    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite:file::memory:?cache=shared"
    val keeper: Connection = DriverManager.getConnection(url)

    DriverManager.getConnection(url).use { conn ->
        println("=== Konfigurasi koneksi aman ===")
        val config = SecureDbConfig(
            jdbcUrl = "jdbc:postgresql://db.example.com:5432/pesanan",
            username = "appuser",
            passwordEnvVar = "DB_PASSWORD",
            sslmode = "verify-full",
            sslRootCert = "/etc/ssl/certs/db-ca.pem"
        )
        println("URL: ${config.jdbcUrl}")
        println("Username: ${config.username}")
        println("Password dari env: \${${config.passwordEnvVar}} (JANGAN hardcode)")
        println("SSL mode: ${config.sslmode}")
        println("CA cert: ${config.sslRootCert}")

        println("\n=== TLS layers ===")
        println("1. sslmode=require        : koneksi TLS, tak verifikasi identitas server")
        println("2. sslmode=verify-ca       : TLS + cek CA yang menandatangani sertifikat server")
        println("3. sslmode=verify-full     : TLS + cek CA + cek hostname (REKOMENDASI)")

        println("\n=== Least privilege: pisahkan role DB ===")
        println("| Role    | Hak                                         | Penggunaan          |")
        println("|---------|---------------------------------------------|---------------------|")
        println("| app_ro  | SELECT saja                                 | Reporting, analitik|")
        println("| app_rw  | SELECT, INSERT, UPDATE pada skema aplikasi  | Service utama       |")
        println("| app_ddl | app_rw + CREATE/ALTER/DROP (migration only) | Saat migration jalan|")
        println("| dbadmin | ALL                                         | DBA saja            |")

        println("\n=== Demo audit log ===")
        val audit = AuditLogger(conn)
        audit.createTable()
        val repo = SecureRepository(conn, audit)
        repo.createTable()

        val id1 = repo.insertPengguna("[email protected]", "[email protected]", "Budi", "editor")
        val id2 = repo.insertPengguna("[email protected]", "[email protected]", "Siti", "viewer")
        println("Inserted: id=$id1 (Budi), id=$id2 (Siti)")

        repo.updatePeran("[email protected]", id2, "editor")
        println("Updated peran id=$id2 ke editor")

        repo.deletePengguna("[email protected]", id1)
        println("Deleted id=$id1")

        println("\nPengguna tersisa:")
        repo.listPengguna().forEach { (id, email, peran) ->
            println("  $id | $email | $peran")
        }

        println("\nAudit log (terbaru ke atas):")
        audit.recent().forEach { e ->
            println("  ${e.timestamp} | ${e.actor} | ${e.action} ${e.tableName}[${e.rowId}] ${e.details}")
        }

        println("\n=== Praktik keamanan database ===")
        println("- Simpan kredensial di env var / secret manager, BUKAN di kode")
        println("- Gunakan TLS (verify-full) untuk koneksi ke DB server jarak jauh")
        println("- Aplikasi pakai user dengan hak minimum; DDL via role terpisah")
        println("- Catat audit log untuk operasi sensitif (insert/update/delete)")
        println("- Parameterized query (PreparedStatement) cegah SQL injection")
        println("- Enkripsi data sensitif di kolom (column-level encryption)")
        println("- Backup terenkripsi + rotasi kunci secara berkala")
    }

    keeper.close()
}

=== Konfigurasi koneksi aman ===
URL: jdbc:postgresql://db.example.com:5432/pesanan
Username: appuser
Password dari env: ${DB_PASSWORD} (JANGAN hardcode)
SSL mode: verify-full
CA cert: /etc/ssl/certs/db-ca.pem

=== TLS layers ===
1. sslmode=require        : koneksi TLS, tak verifikasi identitas server
2. sslmode=verify-ca       : TLS + cek CA yang menandatangani sertifikat server
3. sslmode=verify-full     : TLS + cek CA + cek hostname (REKOMENDASI)

=== Least privilege: pisahkan role DB ===
| Role    | Hak                                         | Penggunaan          |
|---------|---------------------------------------------|---------------------|
| app_ro  | SELECT saja                                 | Reporting, analitik|
| app_rw  | SELECT, INSERT, UPDATE pada skema aplikasi  | Service utama       |
| app_ddl | app_rw + CREATE/ALTER/DROP (migration only) | Saat migration jalan|
| dbadmin | ALL                                         | DBA saja            |

=== Demo audit log ===
Inserted: id=1 (Budi), id=2 (Siti)
Updated peran id=2 ke editor
Deleted id=1

Pengguna tersisa:
  2 | [email protected] | editor

Audit log (terbaru ke atas):
  2026-01-15T08:10:00Z | [email protected] | DELETE pengguna[1] 
  2026-01-15T08:05:00Z | [email protected] | UPDATE pengguna[2] peran=editor
  2026-01-15T08:00:00Z | [email protected] | INSERT pengguna[2] [email protected], peran=viewer
  2026-01-15T08:00:00Z | [email protected] | INSERT pengguna[1] [email protected], peran=editor

=== Praktik keamanan database ===
- Simpan kredensial di env var / secret manager, BUKAN di kode
- Gunakan TLS (verify-full) untuk koneksi ke DB server jarak jauh
- Aplikasi pakai user dengan hak minimum; DDL via role terpisah
- Catat audit log untuk operasi sensitif (insert/update/delete)
- Parameterized query (PreparedStatement) cegah SQL injection
- Enkripsi data sensitif di kolom (column-level encryption)
- Backup terenkripsi + rotasi kunci secara berkala

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO kartu_kredit(nomor_encrypted, nama)
VALUES (pgp_sym_encrypt('4111111111111111', 'KUNCI_DARI_KMS'), 'Budi');