Practice & Assessment
Test your understanding of Advanced PostgreSQL: Functions, JSON, and Security
Multiple Choice Questions
6What is the key difference between a PostgreSQL FUNCTION and a PROCEDURE?
A BEFORE trigger function returns NULL. What effect does this have on the DML operation?
Which JSONB operator returns the value as TEXT (not JSONB)?
You want all future tables created in the public schema to automatically grant SELECT to the readonly role. What is the correct command?
In an RLS policy, what is the difference between USING and WITH CHECK?
What does FORCE ROW LEVEL SECURITY do when added to a table?
Coding Challenges
1Multi-Tenant RLS Implementation
Create a table notes (id IDENTITY PK, tenant_id INT NOT NULL, author_id INT NOT NULL, title TEXT NOT NULL, body TEXT, created_at TIMESTAMPTZ DEFAULT now()). Enable RLS. Write two policies: (1) Users can SELECT only notes where tenant_id matches app.tenant_id session variable. (2) Users can INSERT only notes where both tenant_id and author_id match the corresponding session variables. Create two roles: tenant_user (LOGIN) and tenant_admin (LOGIN, BYPASSRLS). Write test queries using SET LOCAL app.tenant_id = 1 and SET LOCAL app.author_id = 5 to verify isolation. Show that switching tenant_id changes the visible rows. Time estimate: 30 minutes.
Mini Project
Secure Audited Multi-Tenant Application Database
Build a complete schema for a secure SaaS task management system. Requirements: (1) Tables: tenants, users (with tenant_id FK), projects (with tenant_id FK), tasks (with project_id FK, assigned_to FK to users, priority CHECK IN('low','medium','high'), status CHECK IN('open','in_progress','closed')), and audit_log (table_name, operation, old_data JSONB, new_data JSONB, changed_by, changed_at). (2) Write a trigger function that logs every INSERT, UPDATE, DELETE on tasks to audit_log using to_jsonb(NEW) and to_jsonb(OLD). Attach it as an AFTER trigger. (3) Enable RLS on tasks and projects. Write a USING policy so users only see rows matching their tenant_id via a session variable. (4) Create three roles: app_rw (SELECT/INSERT/UPDATE/DELETE on tasks and projects), app_ro (SELECT only), and report_user (SELECT on audit_log only). Set DEFAULT PRIVILEGES so future tables in the schema inherit these grants. (5) Write a PL/pgSQL function create_task(p_project_id INT, p_title TEXT, p_priority TEXT) RETURNS INT that validates the project exists, inserts the task, and returns the new task ID. Raise an exception if project not found. Document each security decision in comments.