Database Management · Advanced level

Advanced level · Module 3: Data Security and Privacy · Lesson 2 of 4

Row-Level Security

Restrict which rows each user can see or change with PostgreSQL row-level security policies.

In this lesson you will

  • Enable row-level security and write policies
  • Test policies for each role

Key concepts

row-level security (RLS)policy USING / WITH CHECKsession settingstesting policies

The lesson

Row-level security (RLS) lets the database filter rows per user. After ALTER TABLE patients ENABLE ROW LEVEL SECURITY, a policy such as USING (clinic_id = current_setting('app.clinic_id')::int) shows each clinic only its own patients.

USING controls which rows can be read; WITH CHECK controls which rows can be written, so staff cannot create records for another clinic.

Policies must be tested for every role, including attempts to read and write other clinics' data. Table owners and superusers bypass RLS by default, so applications must not connect as owners.

Step by step

  1. Enable RLS on the patients table.
  2. Write read and write policies by clinic.
  3. Test as staff of two clinics.
  4. Confirm the application role does not bypass RLS.

Practice activity

Implement per-clinic row-level security for patients and visits.

Expected result: Staff see and change only their clinic's rows; cross-clinic attempts fail in tests.

Check your understanding

  1. What does WITH CHECK do in a policy?

    Show answer

    Controls which rows may be inserted or updated.

  2. Who bypasses row-level security by default?

    Show answer

    Table owners and superusers.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.