Row-Level Security

Secure your data at the database level using PostgreSQL's RLS.

What is RLS?

Row-Level Security (RLS) filters query results based on the authenticated user, on every table where you enable it.

Important: RLS is off on a new table. Volcano grants new tables in public to anon and authenticated by default, so until you run ALTER TABLE ... ENABLE ROW LEVEL SECURITY, any user session, including an anonymous user's, can read, change, and delete every row. See Default grants.

Without RLS:

SELECT * FROM posts;
-- Returns ALL posts (security hole)

With RLS:

SELECT * FROM posts;
-- Returns only current user's posts (automatically filtered)

Setup

1. Auth Helpers (Auto-Installed)

When you create a database via Volcano, auth helpers are automatically installed:

auth.uid()    -- Returns current user's ID
auth.email()  -- Returns current user's email
auth.role()   -- Returns current user's role

2. Enable RLS on Table

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  title TEXT,
  content TEXT
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

3. Create Policies

-- Users can only see their own posts
CREATE POLICY "users_view_own_posts" ON posts
  FOR SELECT
  USING (user_id = auth.uid());

-- Users can only insert their own posts
CREATE POLICY "users_create_own_posts" ON posts
  FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Users can only update their own posts
CREATE POLICY "users_update_own_posts" ON posts
  FOR UPDATE
  USING (user_id = auth.uid());

-- Users can only delete their own posts
CREATE POLICY "users_delete_own_posts" ON posts
  FOR DELETE
  USING (user_id = auth.uid());

Or all operations at once:

CREATE POLICY "users_own_posts" ON posts
  FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Set DATABASE_URL yourself as a project variable, from the connection string in Connection strings. That connection string uses application_name=volcano_full_access, which bypasses RLS. To have your policies apply, connect as the signed-in user by replacing application_name with volcano_user_access:{user_id}:

const { Client } = require('pg');

exports.handler = async (event) => {
  const auth = event.__volcano_auth;
  if (!auth) {
    return { statusCode: 401, body: 'Unauthorized' };
  }

  // Replace application_name; don't append a second one.
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${auth.user_id}`);

  const client = new Client({ connectionString: url.toString() });
  await client.connect();
  try {
    // RLS filters to the current user's rows
    const result = await client.query('SELECT * FROM posts');
    return { statusCode: 200, body: JSON.stringify(result.rows) };
  } finally {
    await client.end();
  }
};

How it works:

Your function reads the authenticated user from event.__volcano_auth.
volcano_user_access:{user_id} tells Volcano's database proxy to run the session as that user.
The proxy sets the session variables that auth.uid() reads, so your policies apply.
A connection left on volcano_full_access skips RLS. Use it only for trusted server-side work.

For a pooled version, see Function integration.

Auto-Set user_id

Create a trigger to automatically set user_id on insert:

CREATE FUNCTION set_user_id() RETURNS TRIGGER AS $$
BEGIN
  NEW.user_id := auth.uid();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_user_id_trigger
  BEFORE INSERT ON posts
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

Now you can omit user_id in inserts:

// user_id automatically set to current user
await client.query('INSERT INTO posts (title, content) VALUES ($1, $2)', ['My Post', 'Content']);

Public Read, Authenticated Write

-- Anyone can read
CREATE POLICY "public_read" ON posts
  FOR SELECT
  USING (true);

-- Any user session, including an anonymous user, can insert its own post
CREATE POLICY "session_insert" ON posts
  FOR INSERT
  WITH CHECK (auth.uid() IS NOT NULL AND user_id = auth.uid());

// Users can only modify their own
CREATE POLICY "own_update" ON posts
  FOR UPDATE
  USING (user_id = auth.uid());

To allow only registered users to insert, use auth.role() = 'authenticated' instead. An anonymous user session has auth.role() = 'anonymous', but still has a user ID. Both session types use the PostgreSQL authenticated role, so TO authenticated alone does not exclude anonymous users.

Advanced: Public and Private Data

Combine public and private data in one table:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL,
  title TEXT,
  content TEXT,
  is_public BOOLEAN DEFAULT false
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can see own posts OR public posts
CREATE POLICY "posts_read" ON posts
  FOR SELECT
  USING (user_id = auth.uid() OR is_public = true);

-- Users can only modify own posts
CREATE POLICY "posts_write" ON posts
  FOR INSERT, UPDATE, DELETE
  USING (user_id = auth.uid());

GRANT ALL ON posts TO authenticated;
GRANT SELECT ON posts TO anon;  -- Requests without a user session can only read

Testing RLS

Test your RLS policies using different authenticated users:

// Test as User 1
const user1Token = await signIn('user1@example.com', 'password');
const response1 = await invokeFunction(functionId, {}, user1Token);
// Should see only User 1's data

// Test as User 2
const user2Token = await signIn('user2@example.com', 'password');
const response2 = await invokeFunction(functionId, {}, user2Token);
// Should see only User 2's data

Common Patterns

User-owned resources:

USING (user_id = auth.uid())

Role-based:

USING (
  auth.role() = 'admin' OR
  user_id = auth.uid()
)

Public + owned:

USING (
  is_public = true OR
  user_id = auth.uid()
)