Changes between Initial Version and Version 1 of P9Security

Timestamp:

01/22/26 01:57:50 (3 weeks ago)

Author:

193284

Comment:

--

P9Security

@@ +1 @@
+= Phase 9 — Security (SQL Injection & Least Privilege) =
+
+== Overview ==
+This section explains how security is enforced in the Wedding Planner database system, with a focus on SQL injection prevention and the principle of least privilege.
+Security is implemented through a combination of database-level controls, safe query practices, and role-based access management in PostgreSQL.
+
+== What we cover in this phase ==
+ * SQL injection prevention through parameterized queries
+ * Database-level protection using constraints
+ * Role-based access control (RBAC)
+ * Least privilege principle for database users
+
+== SQL Injection Prevention ==
+
+=== Description ===
+SQL injection occurs when user input is directly concatenated into SQL queries, allowing attackers to manipulate query logic and access or modify unauthorized data.
+
+Examples include:
+ * bypassing authentication
+ * reading private wedding or user data
+ * deleting bookings or entire tables
+
+=== Example: Unsafe vs Safe Query ===
+
+'''Unsafe (vulnerable to SQL injection)'''
+{{{
+SELECT * FROM "user"
+WHERE email = 'user_input';
+}}}
+
+If user_input = 'x' OR '1'='1', all users are returned.
+
+'''Safe (parameterized query)'''
+{{{
+SELECT * FROM "user"
+WHERE email = $1;
+}}}
+
+=== Reason for use ===
+Parameterized queries ensure that malicious input is always treated as data, not executable SQL code.
+
+== Input Validation and Domain Constraints ==
+While application-level validation is applied, the database also enforces security-related constraints:
+ * NOT NULL on mandatory fields (email, names)
+ * UNIQUE constraints to prevent duplicate accounts
+ * CHECK constraints to prevent invalid values
+
+These rules ensure that even if malicious input reaches the database, it cannot violate domain rules.
+
+== Principle of Least Privilege ==
+
+=== Concept ===
+The principle of least privilege states that each user or service should have only the minimum permissions required to perform its tasks.
+
+In the Wedding Planner system, different database roles are created for different responsibilities.
+
+=== Role-Based Access Control (RBAC) ===
+Roles used in our application:
+ * wp_app_user – used by the application backend
+ * wp_admin – used only for schema migrations and maintenance
+ * wp_readonly – used for reporting or analytics
+
+=== Example ===
+{{{
+CREATE ROLE wp_app_user LOGIN PASSWORD 'strong_password';
+
+GRANT CONNECT ON DATABASE wedding_planner TO wp_app_user;
+GRANT USAGE ON SCHEMA public TO wp_app_user;
+
+GRANT SELECT, INSERT, UPDATE, DELETE
+ON "user", wedding, venue, venue_booking
+TO wp_app_user;
+}}}
+
+=== Explanation ===
+This role represents the database user used by the application backend.
+It is allowed to connect to the database and use the public schema, but its privileges are strictly limited to data manipulation on specific application tables.
+
+=== Reason for use ===
+This approach enforces the principle of least privilege and reduces the impact of:
+ * application bugs
+ * SQL injection vulnerabilities
+ * compromised application credentials
+
+Even if the application role is abused, attackers cannot DROP tables, ALTER constraints/triggers, or access data outside the permitted scope.
+
+== Protecting Sensitive Data ==
+
+=== Example 1: Unauthorized Data Access Prevention ===
+In the Wedding Planner system, sensitive data protection is enforced by ensuring that queries are always scoped to the authenticated user and never rely on client-supplied identifiers alone.
+
+Example endpoint:
+{{{
+GET /weddings/{id}/guests
+}}}
+
+Potential attack scenario:
+An attacker could attempt to access another user’s wedding by guessing or manipulating the wedding ID.
+
+To prevent this, the database query always filters by both wedding ID and the authenticated user ID.
+
+'''SQL code'''
+{{{
+SELECT g.*
+FROM guest g
+JOIN wedding w ON w.wedding_id = g.wedding_id
+WHERE g.wedding_id = $1
+  AND w.user_id = $2;
+}}}
+
+'''Explanation'''
+$1 represents the wedding ID from the request, while $2 represents the authenticated user’s ID.
+
+'''Reason for use'''
+If the wedding does not belong to the user, the query returns no results, keeping data secure.
+
+=== Example 2: Availability Query Without Sensitive Data Exposure ===
+Example endpoint:
+{{{
+GET /availability/venue?date=2026-06-10
+}}}
+
+Potential attack scenario:
+An attacker could try to infer booking details for venues by repeatedly