Protect Exhibitor Reads and Writes

Introduction

Fern Studio and Cedar Works are preparing for a small craft fair. Each exhibitor should maintain its own loading registrations in one shared PostgreSQL table. The fair team must still see everyone and assign booths. A filter in a client command is useful for browsing, but an exhibitor can omit it; the database needs to enforce the boundary.

You will save and apply row policies, then use separate ordinary password logins to complete real registrations. PostgreSQL and the scoped accounts are prepared. You need basic terminal navigation and PostgreSQL SELECT/WHERE, INSERT, UPDATE and table concepts. Roles, row-level security, policy expressions, transactions and the required shell syntax are explained before use. No Python programming is required. Use Terminal throughout. The 95-minute duration is an author estimate including reading and ordinary mistakes, not a measured beginner completion time.

Work in /home/labex/project/exhibitor-desk. Author read-access.sql and write-access.sql, change row policies on fair.registrations, and create or correct only the two new registrations specified later. Preserve all original records, table identity and definitions, prepared grants/accounts, credentials, tools and services. Policy files must be self-contained SQL: no shell commands, connection changes or external includes. Equivalent policy names and correct policy arrangements are accepted. You are the course operator and can deliberately choose each prepared test account; this exercise protects database identities from one another, not credentials from the VM's sudo operator. It does not implement website authentication or promise protection from every information side channel.

Limit Visibility Without Losing the Fair Team

In this step, you will give each exhibitor a complete view of its own registrations and keep the organizer's cross-exhibitor work available. Exhibitor writes will remain unavailable until the next step adds their write policies.

Start in Terminal. cd changes directory; this absolute path works regardless of your current directory:

cd /home/labex/project/exhibitor-desk

./ runs a program from the current directory. The prepared db launcher authenticates a real PostgreSQL connection and runs psql, the PostgreSQL client. It does not filter rows or manufacture results. List its account aliases:

./db accounts

The aliases select these separately prepared logins:

An alias is a convenience for you as the operator, not a database permission granted to Fern. Choosing cedar deliberately uses Cedar's separate credentials. Inside a Fern connection, changing to Cedar must remain forbidden.

-c supplies one command string. Double quotes keep its spaces together for the shell. session_user identifies the authenticated login; current_user identifies the role whose permissions the statement currently uses. They should match in these ordinary connections:

./db fern -c "SELECT session_user, current_user;"

Expect fern_studio in both columns. Read Fern's known normal record first. The number of crates is a whole count; zero means a known zero, not a missing registration:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 belongs to Fern, has contact Fern desk, 2 crates, booth A1 and a fee of 1200 cents. fair is the schema grouping the table. These original business facts must stay unchanged.

Now omit the optional client filter:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Initially Fern sees R101/R102 and Cedar's R201/R202. WHERE owner_role = 'fern_studio' would hide them in that one query, but would not prevent this unfiltered query. The same problem exists for Cedar.

PostgreSQL has two relevant permission layers. A GRANT allows an operation on a table or named columns; prepared grants already allow these users to reach their required operations. Row-level security, or RLS, additionally decides which rows they may read or change. Enabling RLS without an applicable policy denies access to rows by default; it does not automatically invent the ownership rule.

The editor owns this course table and can change policies. Owners normally bypass RLS, so an editor's successful query cannot prove that an exhibitor is restricted. Use the editor to inspect definitions and ordinary accounts to test the result. \d is a psql description command, not SQL:

./db editor -c "\d fair.registrations"

The six columns are registration_id, owner_role, contact, crates, booth_label and fee_cents. A new registration defaults to booth Pending and fee 0. Prepared column grants allow exhibitors to insert ID/owner/contact/crates and update owner/contact/crates. They cannot update IDs, booths or fees, delete rows, or truncate the table. The policy must still refuse a changed owner, even though the owner column is reachable by an UPDATE grant.

Inspect existing policies through PostgreSQL's pg_policies catalog view:

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access allows everyone. Policies are normally permissive: applicable policies combine with OR. Adding a narrow policy beside this broad one would still allow the broad result. Retire the legacy policy as part of the change.

A policy's USING expression decides whether an existing row is visible. owner_role = current_user compares the row's owner to the actual database identity. FOR SELECT limits this policy to reading. The organizer needs a separate policy, scoped TO fair_team, allowing its own legitimate operations on all rows; true means every row passes for that named role.

Save the first migration. cat > read-access.sql writes the following text into the file, replacing an older copy. <<'SQL' begins a here-document: the quoted marker keeps the enclosed SQL literal until SQL appears alone on a line. BEGIN and COMMIT make this policy change one transaction, so the statements either commit together or roll back on an error. DROP POLICY IF EXISTS also lets you correct and rerun this migration:

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

If the shell keeps waiting because you omitted the ending marker, type SQL alone. Read the saved file before executing it:

cat read-access.sql

-f tells psql to read commands from a file. Apply it as the scoped table owner:

./db editor -f read-access.sql

Expect BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY and COMMIT messages. A notice saying that one of your new policy names did not yet exist is harmless. The launcher enables ON_ERROR_STOP: a syntax error in a file stops execution, with psql status 3. The closing connection rolls back an unfinished transaction. Correct the file and rerun it; do not reset the VM. For a filename/path error, use the absolute cd, run ls, read the actual filename, and retry. No original row needs to be deleted or recreated.

Use each ordinary identity to inspect the complete allowed set:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern sees R101/R102, with counts 2/0. Cedar sees R201/R202, with counts 3/1. The organizer still sees all four originals:

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

This is an intentional intermediate state: exhibitors have a SELECT policy but no INSERT/UPDATE policy yet. Keep their prepared grants unchanged. Run the outcome check; sudo permits the prepared checker to inspect protected state. It reads the live business and tests policy SQL on disposable data, without filling in your missing work:

sudo python3 /opt/labex-exhibitors/verify.py visibility

Expect confirmation that both accounts see their own records and the organizer remains usable. Next, add the separate rules for proposed writes.

Check Both Existing Rows and Proposed Values

In this step, you will let exhibitors add and update their own registrations without creating or transferring records for another owner. Start in Terminal, in /home/labex/project/exhibitor-desk.

A SELECT policy alone does not authorize an INSERT. A write policy needs to consider two moments. For UPDATE, USING selects the existing rows the role may change; WITH CHECK tests the proposed new row. For INSERT there is no old row, so its policy uses only WITH CHECK. In both cases, the proposed owner must equal current_user.

Save the write migration using the same quoted here-document format. It adds policies to the table already protected by read-access.sql:

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

Read it, then apply it as the editor:

cat write-access.sql

./db editor -f write-access.sql

Expect the two CREATE POLICY statements to commit. Correcting the file and rerunning it replaces only these named write policies; it does not reset registrations. If you start over from your saved policy files, apply read-access.sql first and then write-access.sql.

Equivalent policy arrangements can work. An ALL or UPDATE policy with only USING implicitly uses that expression for its missing WITH CHECK; omitting that clause is not automatically a defect. Here the separate command policies make each job explicit. What matters is the behavior of every permitted and forbidden operation, not the policy names or exact spelling.

First exercise a legitimate write without leaving test data. BEGIN starts a transaction. RETURNING displays the row actually stored by INSERT/UPDATE. ROLLBACK discards the transaction's changes, leaving the four originals intact:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

Expect TRY-FERN, owner fern_studio and 2 crates, followed by ROLLBACK. The normal insert accepts a real zero and the update works; a rule that denies every write would fail this requirement.

Now test a hidden existing row. This statement must succeed as a command while finding no allowed row to update:

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

Expect no returned row and UPDATE 0. R201 belongs to Cedar. A zero-row result is row filtering, not a connection failure or a database error.

The next two commands are expected SQL failures. Both wrap the probe in a transaction: if a wrong policy accidentally accepted it, the final ROLLBACK would still discard it. If an error stops the command before ROLLBACK, closing the connection discards its unfinished transaction too.

Try creating a Cedar-owned record while authenticated as Fern. Deliberately omit RETURNING: a SELECT-policy check on returned rows must not stand in for the proposed-row write rule:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

Expect new row violates row-level security policy. $? is the status of the immediately preceding shell command; print it before running anything else:

echo $?

Expect status 1 for this failed -c SQL command. Status 2 with a connection/authentication error is a different failure and does not prove the row policy worked. The launcher chooses the prepared credentials on each invocation; use ./db accounts and retry the documented alias rather than editing credential files.

Next try changing the owner of Fern's visible rows to Cedar. The constant UPDATE has no WHERE or RETURNING that reads a table column. This reaches the proposed-row check directly; the surrounding transaction keeps even an accidentally accepted probe reversible:

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

Again expect the row-security error and status 1. Do not infer that only updates with RETURNING need protection: permitted SQL can omit it.

Column permissions provide a different boundary. An exhibitor may change its own contact/count, but the fair team assigns booths and fees. This attempted booth change must produce a table-permission error, even for Fern's own rows:

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

Expect permission denied for table registrations, rather than a proposed-row policy error. Likewise, a Fern connection cannot become Cedar merely by naming that role:

./db fern -c "SET ROLE cedar_works;"

Expect permission denied to set role. Prepared exhibitors are ordinary logins without cross-account membership, table ownership or BYPASSRLS. Changing a freely chosen application setting would not authenticate another login; the policy compares the real database role.

Confirm that the reversible probes left the original business facts intact:

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

The original four rows, contacts, counts, booths and fees must match their initial values. Run the write-boundary check:

sudo python3 /opt/labex-exhibitors/verify.py writes

It checks both ordinary identities, legitimate writes, changed inventories, cross-owner writes and organizer access. A complete pass means you can now use the protected table for normal work.

Complete Registrations Through the Protected Accounts

In this step, you will use the working permissions to prepare two real loading registrations. Start in Terminal, in /home/labex/project/exhibitor-desk. These two new records are the only business rows you may add or correct; retain every original row.

Fern starts a new registration with a known zero crate count. The supplied defaults leave its booth Pending and fee 0 until the organizer assigns them:

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

Expect R301, fern_studio, Fern loading, 0, Pending, 0. This is a real committed INSERT, unlike the earlier transaction probes. Repeating it would cause a duplicate-key error; it is not an idempotent creation command.

Set its final crate count to four with a targeted ordinary update:

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar creates its own new registration with one crate:

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

Expect R302, cedar_works and 1. Each exhibitor now has three visible records, including its two originals.

The organizer's own ordinary identity assigns the agreed booth and fee to each new record. Fees are whole cents; these supplied amounts are scenario facts, not a tax or accounting calculation:

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

Expect R301/C3/1800 and R302/D2/600. This work uses fair_team, not the table owner; an owner bypass would not establish that the organizer's policy remains usable.

Read the final complete records through each exhibitor:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern sees R101/R102/R301, with the new R301 count 4, booth C3 and fee 1800. Cedar sees R201/R202/R302, with the new R302 count 1, booth D2 and fee 600. Original records must be unchanged.

If you accidentally repeat an INSERT, inspect that named new record with the corresponding account. If it already exists, use a targeted UPDATE of its contact/count rather than deleting it or resetting the database. For example, Fern's new record can be corrected with UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';. Only correct the named new records; rerun the organizer's targeted booth/fee assignment if you made an error there. The saved migrations can also be corrected and reapplied without recreating data.

Run the final outcome check:

sudo python3 /opt/labex-exhibitors/verify.py business

Expect confirmation that both new registrations are ready with the correct ownership, counts, booths and fees. You have reusable policy SQL and a shared register that ordinary exhibitors and the organizer can actually use. The independent challenge applies the same boundaries to a different makers' register.

Summary

You separated table/column permissions from row ownership, retired a broad permissive policy, and checked both existing rows and proposed writes through real ordinary logins. Two new registrations and their organizer-assigned booths now work without exposing or changing another exhibitor’s records.

The boundary is the authenticated database identity. Table owners and administrators remain trusted, and this exercise does not provide website login or a guarantee against every database side channel.