Security, Backups & Real-World Operations

Production database — auth, injection, backup, monitoring

Read: ~35 min Intermediate 12 practice problems Production focus

1. The Three Things a Database Owner Cannot Outsource

Every production database — bKash, NID, your university registrar, Daraz — has the same three non-negotiables: only the right people can access it, nobody can corrupt it from a web form, and it can be restored after any disaster. Miss any one and you are one bad day away from a headline.

যেকোনো production database-এ তিনটি জিনিস কখনই অবহেলা করা যায় না — সঠিক access control, SQL injection-এর বিরুদ্ধে সুরক্ষা, এবং বিপর্যয়-পরবর্তী restore। একটিতেও ফাঁক থাকলে পুরো system বিপদে।

This module is the production handbook: who-can-do-what, the world's most famous database vulnerability and its fix, the backup strategy that has saved a thousand careers, and a quick monitoring checklist.

2. Authentication, Roles & GRANT / REVOKE

Authentication answers "who are you?". Authorization answers "what are you allowed to do?". Almost every serious DBMS implements authorization with roles and the GRANT / REVOKE statements.

ConceptPostgres / MySQL / SQL ServerSQLite
User accountsCREATE USER alice WITH PASSWORD '...';No user system; OS file permissions only.
RolesCREATE ROLE analyst;—
PrivilegesGRANT SELECT, UPDATE ON orders TO analyst;—
MembershipGRANT analyst TO alice;—
RevokeREVOKE UPDATE ON orders FROM analyst;—
SQLite caveat SQLite is an embedded library, not a server. There are no users or GRANT — access control is purely OS-level (file permissions, encryption). Code blocks below show Postgres-style syntax for illustration; they are not runnable in our in-browser SQLite.
grants.sql (Postgres — illustrative)
-- Postgres example (won't run in SQLite — read-only illustration).
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

CREATE ROLE writer;
GRANT read_only TO writer;
GRANT INSERT, UPDATE, DELETE ON orders TO writer;

CREATE USER alice WITH PASSWORD '***';
GRANT writer TO alice;
Principle of least privilege Every account gets only the privileges it actually needs. Web app reads from a customer table → an account that has SELECT on that table and nothing else. Your reporting tool should never connect with a superuser.
Least privilege = প্রয়োজনের চেয়ে এক চুল-ও বেশি permission নয়। যত বেশি permission, compromised account থেকে তত বড় ক্ষতি।

3. SQL Injection — and the One True Fix

Despite being known for over 25 years, SQL injection is still on the OWASP Top 10. The vulnerability: an application builds an SQL string by concatenating user input. The fix is identical everywhere: use parameterized queries.

The unsafe pattern (DO NOT DO THIS)

injection_demo.sql
-- Imagine the app builds the query as:
--   "SELECT * FROM users WHERE username = '" + input + "' AND password = '...';"
-- A friendly user types:    alice
-- A nasty user types:       ' OR '1'='1
-- The query that runs is:
SELECT * FROM users
WHERE username = '' OR '1'='1'
  AND password = 'whatever';
-- ⇒ returns every user row. Authentication bypassed.
Worse forms exist With '; DROP TABLE users; -- an attacker can destroy data. With UNION SELECT tricks they can read other tables. With UPDATE balance SET ... they can take money. Every one of these has been used in real breaches.

The safe pattern — parameterized queries

Every modern language SDK supports placeholders. The driver sends the parameter values separately from the SQL text, so input can never be parsed as SQL. Examples:

LanguageSafe pattern
Python (sqlite3)cur.execute("SELECT * FROM users WHERE username=? AND password=?", (u, p))
Node.js (better-sqlite3)db.prepare("...WHERE username=? AND password=?").get(u, p)
PHP (PDO)$stmt->execute([':u' => $u, ':p' => $p]);
Java (PreparedStatement)stmt.setString(1, u); stmt.setString(2, p);
Go (database/sql)db.Query("...WHERE username=$1 AND password=$2", u, p)
মূল কথা: User input কখনও SQL string-এর সাথে concatenate করা যাবে না। সর্বদা placeholder (?, $1, :name) ব্যবহার করতে হবে। Driver-ই input-কে নিরাপদে handle করবে।

4. Backups — Full, Incremental & Point-In-Time Recovery (PITR)

A database without a tested backup is just a fancy memory cache. Three backup types are common in production:

Full backup

  • Complete copy of the entire database.
  • Slow, large; usually nightly or weekly.
  • Restore = single step.

পুরো database-এর copy। নিয়মিত (যেমন প্রতি রাতে) নেওয়া হয়।

Incremental backup

  • Only what changed since the last backup.
  • Small, fast.
  • Restore = full + chain of incrementals in order.

গত backup-এর পর থেকে যা যা পরিবর্তন হয়েছে শুধু সেগুলো।

Point-In-Time Recovery (PITR)

  • Full backup + every transaction log (WAL) since.
  • Restore to any millisecond in the past.
  • The gold standard. Recover from "we just dropped the wrong table" 10 minutes ago.

যেকোনো সময়ের নির্দিষ্ট মুহূর্তে database ফিরিয়ে আনা।

SQLite backup — the simplest case

SQLite is a single file. The simplest reliable backup is the .backup command (in the CLI) or the C-level sqlite3_backup_* API. Copying the file directly while the DB is in use can corrupt the backup; always use the API.

Postgres / MySQL — mature ecosystem

  • Postgres: pg_basebackup + WAL archiving + pg_restore.
  • MySQL: mysqldump, xtrabackup, binary log archiving.
  • Cloud (RDS, Cloud SQL, Aurora) — automatic snapshots and PITR are usually on by default.
Untested backups don't exist A backup is only real once you have restored from it on a fresh server and verified the data. Schedule a quarterly restore drill — every team that doesn't has, eventually, a horror story.
Backup শুধু নিলেই হয় না — মাঝে মাঝে test restore করতে হয়। যে backup কখনও restore করে দেখা হয়নি, সেটি "ছিল" ধরে নেওয়া বিপজ্জনক।

5. Monitoring & Runbooks

Production databases need a constant pulse check. The minimum metrics to watch:

  • Connection count — sudden spike usually means a leak in the app.
  • Slow query log — top 10 by total time. Optimize one a week.
  • Replication lag — if you have replicas. Lag > X seconds → page someone.
  • Disk usage — alert at 80%, page at 90%. Out-of-disk = read-only DB.
  • Error rate — failed queries, deadlocks, lock waits.
  • Backup success — alert if a scheduled backup fails or succeeds 0 bytes.

Pair monitoring with runbooks: short, written-down playbooks for every common failure ("what to do if disk is full", "what to do if replication breaks", "what to do if every query times out"). On-call engineers are not expected to invent solutions at 3 AM.

ভালো monitoring + ভালো runbook = ভালো ঘুম। প্রতিটি ব্যর্থতার জন্য আগে থেকে লেখা পদ্ধতি থাকলে রাত ৩টায় চিন্তা না করে কাজ করা যায়।

6. Production Readiness Checklist

Before any database goes live, verify all of:
  • ✅ Application connects with a least-privilege account (not superuser).
  • ✅ Every query in code uses parameterized queries — no string concatenation, anywhere.
  • ✅ TLS is enforced for all connections.
  • ✅ Passwords are hashed (bcrypt/argon2) — never stored in plaintext.
  • ✅ Daily full backup + WAL archiving for PITR.
  • ✅ Last quarterly restore drill — completed and signed off.
  • ✅ Monitoring + alerts wired up; on-call rotation defined.
  • ✅ Runbook exists for: disk full, replication broken, all queries slow, accidental table drop.
  • ✅ Schema migrations are idempotent and reversible.
  • ✅ Sensitive columns (NID, phone) encrypted at rest if regulation requires.

7. Glossary (শব্দকোষ)

TermMeaningবাংলায়
AuthenticationProving who you are.আপনি কে — সেটি যাচাই।
AuthorizationWhat you are allowed to do.আপনি কী করতে পারবেন।
RoleNamed bundle of privileges that users can be granted.Privilege-এর nomenclature bundle।
SQL injectionHostile SQL fragments smuggled in via app input.App input-এর মাধ্যমে অপরিচিত SQL ঢোকানো।
Parameterized queryQuery with placeholders; values bound separately, never concatenated.String concatenation না করে placeholder ব্যবহার।
PITRPoint-in-time recovery using a base backup + WAL.WAL ব্যবহার করে অতীতের যেকোনো সময়ে restore।
RunbookWritten playbook for common production incidents.সাধারণ ব্যর্থতার জন্য লেখা পদক্ষেপ।

8. Practice Problems

  1. Run the unsafe authentication query from §3 and observe how ' OR '1'='1 bypasses the password check.
    Injection-এর demo নিজে চালিয়ে দেখুন।
    ✨ Show Answer
    ans1.sql
    SELECT * FROM users
WHERE username = '' OR '1'='1'
  AND password = 'wrong';

    Every row appears — full bypass. Why: OR '1'='1' is always true, and SQL precedence makes it short-circuit the AND.

  2. Rewrite the login query in Python (sqlite3) using parameterized queries.
    Python-এ parameterized query-র উদাহরণ।
    ✨ Show Answer 
    cur.execute(
    "SELECT id FROM users WHERE username = ? AND password = ?",
    (entered_username, entered_password)
)
row = cur.fetchone()

    The driver substitutes the values at the binary protocol level — they can never be parsed as SQL. Whether the user types alice or ' OR '1'='1, the parameter is treated as a literal string compared to username.

  3. Design a role hierarchy for an e-commerce app: admin, support, analyst, app_user. Who needs SELECT only? Who needs UPDATE on which tables?
    Role hierarchy ডিজাইন করুন।
    ✨ Show Answer

    Sketch:

    • analyst — SELECT on every reporting view; no access to PII tables.
    • support — SELECT on customer + orders, UPDATE on order status only.
    • app_user — SELECT/INSERT/UPDATE/DELETE on the tables the public website needs; nothing else.
    • admin — DDL access for migrations; used only by deploy pipeline, never by humans directly.
  4. Why does a daily full backup alone not protect you from "we ran UPDATE orders SET status='cancelled' with no WHERE clause" at 4 PM?
    Full backup-ই কেন যথেষ্ট নয়?
    ✨ Show Answer

    Answer: Last night's full backup is up to 24 hours old. Restoring it loses every legitimate change made today. PITR replays the WAL up to the moment just before the bad UPDATE — keeping all other work intact.

  5. Name three signals you would put on the on-call dashboard for a Postgres database that backs a payments service.
    তিনটি গুরুত্বপূর্ণ monitoring signal।
    ✨ Show Answer

    Sample answer: (1) Replication lag > 5 s; (2) p99 query latency on charge/refund tables; (3) failed login rate & transaction rollback rate. Bonus: free disk < 20%, last successful backup age > 26 h.

  6. In one sentence, explain why parameterized queries also tend to be faster than concatenated ones.
    Parameterized query কেন প্রায়ই দ্রুত?
    ✨ Show Answer

    Answer: The DB parses and plans the SQL once, then re-uses that compiled plan with new parameter values — string-concatenated queries look different every time and need fresh parsing/planning on each call.

Summary — Module 39

Production-grade database engineering rests on three pillars: least-privilege access via roles and GRANT/REVOKE, parameterized queries everywhere to defeat SQL injection, and tested backups with PITR so any disaster — human, hardware, or hostile — can be undone. Add monitoring and a runbook, and you have something you can actually keep alive at scale.

Production database = least privilege + parameterized query + tested backup + monitoring + runbook। একটি ছাড়া বাকিগুলোও দুর্বল হয়ে পড়ে।

Next Module → Capstone — pick one of four tracks and ship a real database-backed system.

Prev: NoSQL Next: Capstone