Alright, let’s get our hands dirty with Row-Level Security (RLS). This is where PostgreSQL stops being a polite data librarian and starts acting like a paranoid bouncer with a very specific guest list. It’s a fantastic feature, but it demands precision. One wrong move and you’re either locking everyone out or leaving the VIP door wide open.

The core idea is simple: you write a policy—a WHERE clause on steroids—that PostgreSQL automatically applies to every query on a table, be it a SELECT, UPDATE, or DELETE. It filters rows on the database side based on the current user’s characteristics. This is infinitely more secure than trying to filter things in your application code because you can’t accidentally forget to add the WHERE user_id = ? clause. The database enforces it, always.

Before you even think about writing a policy, you have to enable the bouncer. By default, tables don’t have RLS. It’s a crucial safety feature. If you just created a policy and wondered why it’s doing nothing, you probably forgot this step.

-- This is the master switch. Flip it ON.
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

Important: The table owner (usually you, the superuser) and users with the BYPASSRLS attribute are exempt from all RLS policies. This is your emergency escape hatch. You can always see all the data to fix things. For everyone else, if RLS is enabled and no policy exists for a specific command, the default policy is to deny all access. They get zero rows. So, enabling RLS on a table without creating policies first is a great way to create a “table not found” panic in your app. Don’t do that.

The Anatomy of a CREATE POLICY Statement

Let’s break down the command. It looks more intimidating than it is.

CREATE POLICY policy_name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER } ]
    USING ( using_expression )
    [ WITH CHECK ( check_expression ) ]
  • policy_name: Just a descriptive label. I use names like "Users can only see their own data".
  • [ FOR ... ]: This specifies which operations the policy applies to. FOR ALL is a catch-all for SELECT, INSERT, UPDATE, DELETE. Often, you’ll need separate policies for SELECT vs. INSERT/UPDATE.
  • [ TO ... ]: This lets you assign the policy to specific roles. This is advanced stuff; you’ll usually just apply it to everyone (PUBLIC) or omit the clause entirely.
  • USING: This is the heart of it. It’s a boolean expression that determines which existing rows are visible for SELECT, UPDATE, and DELETE. Think of it as the bouncer checking the list for people already inside.
  • WITH CHECK: This expression validates new rows being INSERTed or UPDATEd. It’s the bouncer checking your ID before you’re allowed to enter. This is a common trip-up.

The Classic Example: User-Based Access

Let’s implement the most common pattern: users can only see and manipulate their own records.

-- Let's assume a table for user notes
CREATE TABLE user_notes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL, -- This links to your app's users table
    note TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable the bouncer
ALTER TABLE user_notes ENABLE ROW LEVEL SECURITY;

-- Policy for SELECT: users can only see notes where they are the owner.
CREATE POLICY "Users can view own notes" ON user_notes
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Policy for INSERT/UPDATE: users can only create/update notes with their own user_id.
CREATE POLICY "Users can modify own notes" ON user_notes
    FOR ALL -- This covers INSERT, UPDATE, DELETE. We could be more granular.
    USING (user_id = current_setting('app.current_user_id')::INTEGER)
    WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);

See that current_setting('app.current_user_id') bit? This is the magic. You can’t directly reference your application’s user ID inside a PostgreSQL policy. The solution is to set a session variable at the beginning of every database connection from your app. This is non-negotiable.

-- Your application code MUST run this right after connecting:
SET app.current_user_id = '123'; -- The actual ID of the logged-in user

PostgreSQL provides CURRENT_USER, but that’s the database role, which is almost never what you want for application users. Using a custom config variable like this is the standard, correct practice.

The WITH CHECK Gotcha and Why It Exists

This is the single most confusing part for newcomers. Why do we need both USING and WITH CHECK? Let’s imagine we only had the USING clause from our SELECT policy and tried to INSERT a note.

-- Bad policy that only has USING
CREATE POLICY "Bad policy" ON user_notes
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Now user 123 tries to insert a note for user 456 (hacking!)
INSERT INTO user_notes (user_id, note) VALUES (456, 'I am a hacker');

What happens? The USING clause governs what’s visible. During the INSERT, it’s not checking visibility of existing rows, so the USING clause is irrelevant. The insert would succeed! The WITH CHECK clause exists specifically to prevent this. It validates the proposed new row before it’s written. Without it, your INSERT and UPDATE operations are wide open to this kind of privilege escalation.

Best Practices and Pitfalls

  1. Test as a Non-Owner: Always test your policies by switching to a low-privilege role (SET ROLE low_privilege_user;) or by ensuring your application connection is properly set up. Testing as a superuser is useless; you’re the owner who bypasses RLS.
  2. Performance Matters: The USING expression is a WHERE clause on every query. Make sure it’s sargable. An index on the user_id column in our example is absolutely critical. Without it, you’re forcing a sequential scan on the entire table for every single query, which will murder your performance at scale.
  3. Keep It Simple: Policies are not the place for 50-line SQL functions with multiple joins. The more complex they are, the harder they are to reason about and the worse they perform.
  4. The Superuser Escape Hatch: Remember, if you completely bork your policies and lock everyone out (including yourself from the app), you can always connect as the superuser (who bypasses RLS) to DROP POLICY or DISABLE ROW LEVEL SECURITY and fix your mistake. It’s your get-out-of-jail-free card.

RLS is powerful because it moves a critical security layer as far down the stack as possible. It’s the last line of defense. Use it, respect it, and test the hell out of it.