RLS support

[vincent-lecrubier-skydio]

I wonder if you have any plans to support Row Level Security (RLS)?

Most uses of RLS at scale use a single user (or a few users corresponding to roles), and use SET LOCAL variables to describe the current user ID. See https://www.graphile.org/postgraphile/security/#how-it-works

So it would be a matter of:

• Having ways to make Electric-server add SET LOCAl variables when querying postgres
• Maybe supporting many shapes per table (one shape per user?)
• Dealing with real-time implications (Something like what Supabase does, running RLS on replication stream)

[KyleAMathews]

This definitely something we're interested in. Getting to a stable, scalable 1.0 is our immediate priority (we'll rely on APIs for auth until then) but we're keen to explore other options in the future including RLS.

[thruflo]

Just for some addition context, this is something we've looked into in some depth. Your summary is exactly right:

1. for queries and direct writes, we can use an auth context from the client connection and set local to provide this auth context to RLS rules
2. for ongoing replication the picture is a little more complex, because Electric uses a single replication stream and then fans out shapes to multiple clients

There are two primary approaches for solving (2):

a. find a way to use a replication stream per tenant / auth context; this can work for some data access patterns
b. find a way to apply RLS rules at the sync-service level

The second approach requires getting the rules and running them against the operations in the replication stream. This has a challenge, in that RLS rules are quite flexible/expressive and allow you to query the state of the database. For example, when authorising access to a row, you can query another table. This is challenging when processing the replication stream, because the replication stream is behind the current state of the database.

Postgres has MVCC internally but does not (natively) allow you to query the database at a particular snapshot. This means that by the time you have received a transaction in the replication stream, you can't query the database within the same snapshot. This limits the complexity of RLS rules that can be supported.

Possible solutions

1. gain the ability to query Postgres at a snapshot -- requires extensions / branching capability or changes to a data model; this can be unsupported, vendor specific and potentially slow to hydrate a branch-snapshot
2. support limited RLS rules that just use the row and auth context; this seems promising, as where necessary, triggers can be added to put necessary auth information in the row
3. use a replication stream per tenant / auth context

[lukvermeulen]

Especially as you're mentioning supabase, it would be cool if electric could just leverage RLS policies that are created in supabase already. This way an offline enabled app with electric + supabase could have quite a head start.

[thruflo]

One of the observations about the https://supabase.com/docs/guides/realtime/authorization design is that we could potentially support something similar, where RLS rules set on an electric.shapes table (or whatever) could be evaluated in the sync engine (or provided to an auth proxy or gatekeeper endpoint) when authorizing shape access. This could allow RLS to be used to power GET shape level auth. In our case, with the shape definition available rather than the channel topic.

[matis-dk]

Any progress on this matter? This limitation is preventing us from adopting electic's sync engine.

[thruflo]

We’ve actually gone a bit more in the opposite direction: https://electric-sql.com/docs/guides/auth#it-s-all-http

Rather than codifying auth logic in database rules, Electric allows you to filter and authorize data access in your web framework / middleware, much as you would any other web resource like a REST or GraphQL endpoint.

You can use RLS to filter the data that the Electric dbuser sees but you don’t need to express auth logic in database rules.

Obviously if you have an investment in existing RLS rules, this may be less useful / attractive. And in the longer run we may implement some user aware connection support, a bit like Nile’s tenant context or similar.

However, right now we’re leaning into the more web-native approach of “it’s all just HTTP and the shape definition is in the request params”.

[Donnerstagnacht]

I tried to follow your guide but I was not able to understand it.

Since you write, you would support users to move existing apps, I would like to ask if you (or someone else) could explain it based on the following:

1. How would I migrate a setup based on a react client and a managed supabase postgres database to use electric sql?

I believe it is a simple example but covers in my opinion the three core adoption questions:

1. How to handle (supabse) Auth?
2. How to handle (row level security) authorization?
3. How to handle the join (views)?

Example setup:

Supabase with rls enabled having a

1. users table
2. groups table
3. member table

React client having a

1. fetch function
2. real time subscription

The goal:

1. Display a real time list of group member names

Example code:
(I think you could also replace auth.uid() with current_user() and then the code would be more generic (independent of supabase) or the fetch function could call a backend rest api)

-- Create users table (linked to Supabase auth.uid())
CREATE TABLE users (
    id UUID PRIMARY KEY,  -- Matches Supabase auth.uid()
    name TEXT NOT NULL
);

-- Create groups table
CREATE TABLE groups (
    id UUID PRIMARY KEY,
    title TEXT NOT NULL
);

-- Create join table for group membership
CREATE TABLE group_members (
    group_id UUID REFERENCES groups(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (group_id, user_id)
);

-- Enable Row-Level Security on the join table
ALTER TABLE group_members ENABLE ROW LEVEL SECURITY;

-- Allow each user to select only members of the groups they belong to
CREATE POLICY "select_own_group_members" ON group_members
FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM group_members AS gm
    WHERE gm.group_id = group_members.group_id
      AND gm.user_id = auth.uid()  -- Supabase-authenticated user
  )
);

-- Real time for group_members
ALTER TABLE group_members REPLICA IDENTITY FULL;

import { useEffect, useState } from 'react';

type Member = {
  user_id: string;
  group_id: string;
  name: string;
};

// component displaying a list of realtime group members
export function GroupMembers({ groupId }: { groupId: string }) {
  const [members, setMembers] = useState<Member[]>([]);

  useEffect(() => {
    // Initial fetch with join
    const fetchMembers = async () => {
      const { data, error } = await supabase
        .from('group_members')
        .select('user_id, group_id, users(name)')
        .eq('group_id', groupId);

      if (!error && data) {
        setMembers(data.map(m => ({
          user_id: m.user_id,
          group_id: m.group_id,
          name: m.users.name,
        })));
      }
    };

    fetchMembers();

    // Subscribe to realtime updates
    const channel = supabase
      .channel('group-members')
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'group_members',
          filter: `group_id=eq.${groupId}`,
        },
        () => {
          fetchMembers(); // re-fetch on insert/delete/update
        }
      )
      .subscribe();

    return () => {
      supabase.removeChannel(channel);
    };
  }, [groupId]);

  return (
    <ul>
      {members.map(member => (
        <li key={member.user_id}>{member.name}</li>
      ))}
    </ul>
  );
}

[thruflo]

Complex joins are one of the limitations of Electric shapes atm. Shapes are filtered tables so you can't express exactly what you want with an Electric shape alone.

You can emulate what you have in the example code with:

1. a normal API call to fetch the group members
2. a shape for changes to the group_members table

A great way of achieving this is with TanStack DB. It explicitly allows you to use both query collections and sync collections as part of the same data model.

If you wanted a pure shape solution, you could use a shape waterfall in the client. So (1) subscribe to a shape of group_members where user_id = the current user. Then (2) subscribe to a shape of group_members where group_id IN the group_ids from the first shape. Then if you need the user name, (3) subscribe to a third shape of all users matching the user_ids of the second shape.

Shape waterfall isn't the most optimal way of doing joins but it's a workaround that can be viable in some cases, e.g.: if the group memberships don't change too often.

In all cases, Electric (and APIs) authorise HTTP requests. So you need to be able to take the user context and the request data and authorise the request. If you were to mix API calls and sync in the same app, you can use a different user for the API calls than for Electric. This allows you to set the user context for the API calls, using a user that is subject to RLS rules and then have a different DB user for Electric with the BYPASS_RLS role. This is one way for Electric and RLS to coexist.

Alternatively, you can use a proxy to directly filter the stream. So subscribe to the users table and then filter updates by doing a "is this user in the same group as the authenticated user" query for every row that comes through. If they aren't, drop the row, if they are, proxy it on.

These workarounds aren't super elegant right now. Generally Electric works best when you can sync filtered views of single database tables, without needing complex joins to filter the table. Many to many memberships are one of the worst data models for this. As opposed to e.g.: being able to just denormalise a workspace_id or tenant_id (or group_id) onto all the tables in your data model. That's the kind of thing that makes both filtering and authorizing shapes trivial.

[Donnerstagnacht]

Thank you for the quick and detailed reply!

So, if I understood you right, wouldn't it be an equal or better migration strategy to denormalise data in the postgres database itself using triggers over shape waterfalls in the client?

That way views would not be required, and the join would not be required. You would get an easy to implement localfirst db at the cost of one (or many in case of more complex join) trigger and some additional disk space (but then denormalization has also benefits).

Would you agree to that or disagree?

Speaking in code and in the example above:

-- Trigger function to sync name into group_members
CREATE OR REPLACE FUNCTION sync_group_member_name()
RETURNS TRIGGER AS $$
BEGIN
  SELECT name INTO NEW.name FROM users WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger before INSERT or UPDATE
CREATE TRIGGER trg_sync_group_member_name
BEFORE INSERT OR UPDATE ON group_members
FOR EACH ROW
EXECUTE FUNCTION sync_group_member_name();

[thruflo]

Yup, exactly -- if you can denormalise your data model this simplifies a lot 👍

[KazimirPodolski]

This limitation is preventing us from adopting electic's sync engine.

Same for me: lack of RLS + lack of joins + lack of views + only trivial filtering are all deal breakers. IMO currently Electric looks like a mere "single table downloader", which is still something, but:

• I can get Electric and then painfully fill all the holes in functionality which any web app needs on a daily basis, basically implementing a framework instead of my actual app
• or I can throw in the good old "data fetching" with Tanstack + optimistic state + serializable mutations, which would be very tedious, but effectively will do the same sync job

IMO PGLite + this at 1.0, ideally as a drop-in thing rather than super verbose tables declaration thing, would be the real BOMB and can really shake the foundations towards local-first/offline-first web apps finally being efforless.

[john-rtr]

Hi!
Electric checks nearly all our boxes, but the absence of server-side row-level filtering is a show-stopper.
We need relation-based filters applied before sync; adding a “visibility” column to every table just to work around this isn’t maintainable.

[forabi]

I understand the syntax for "where" is limited, but it's not a dealbreaker IMO. I am currently doing the filtering like this. Is there anything wrong with that approach?

app.get("/v1/shape", async (c) => {
  const {
    req,
    var: { userId },
  } = c;

  const originUrl = new URL(`${env(c).ELECTRIC_ORIGIN_URL}/v1/shape`);
  const requestUrl = new URL(req.url);
  const columns = requestUrl.searchParams.get("columns")?.split(",") ?? [];

  if (!columns.length) {
    return c.json({ error: "No columns specified" }, 400);
  }

  const columnsToKeep = [...new Set(["id", ...columns])];

  requestUrl.searchParams.forEach((value, key) => {
    originUrl.searchParams.set(key, value);
  });

  requestUrl.searchParams.delete("columns");
  requestUrl.searchParams.set("columns", columnsToKeep.join(","));

  const table = originUrl.searchParams.get("table") as keyof DB | null;
  if (!table) {
    return c.json({ error: "No table specified" }, 400);
  }

  const organizationIds = await db
    .selectFrom("organizations")
    .select("id")
    .where(buildOrganizationsWhere({ userId, action: "select" }))
    .execute();

  const userOrganizationIds = organizationIds.map((o) => o.id);

  console.log("User organization ids", userOrganizationIds.length);
  // Preliminary filtering should provide a huge performance boost
  // it also avoids the penalty of the customer base growing affecting the sync for every single user
  // because without this, all sync sessions for all users will have to iterate through every single table row
  // to check if they are allowed to see it.
  let preliminaryWhere = "";
  let preliminaryWhereParams: string[] = [];

  switch (table) {
    case "organizations": {
      if (!userOrganizationIds.length) {
        preliminaryWhere = "false";
        break;
      }
      preliminaryWhere = `id IN (${userOrganizationIds.map((id, index) => `$${index + 1}`).join(",")})`;
      preliminaryWhereParams = userOrganizationIds;
      break;
    }
    case "todos":
    case "todo_lists": {
      if (!userOrganizationIds.length) {
        preliminaryWhere = "false";
        break;
      }
      preliminaryWhere = `organization_id IN (${userOrganizationIds.map((id, index) => `$${index + 1}`).join(",")})`;
      preliminaryWhereParams = userOrganizationIds;
      break;
    }
    default:
      break;
  }

  if (preliminaryWhere) {
    originUrl.searchParams.set("where", preliminaryWhere);
    for (let i = 0; i < preliminaryWhereParams.length; i++) {
      originUrl.searchParams.set(`params[${i + 1}]`, preliminaryWhereParams[i]);
    }
  }

  const response = await fetch(originUrl.toString());

  if (!response.ok) {
    return response;
  }

  const responseBody = (await response.json()) as any[];

  const rowsToCheck = responseBody.filter((item) => {
    // Control messages and other non-row data don't have a `value` property.
    // We should always pass these through.
    if (!item.value || !item.value.id) {
      return false;
    }

    return true;
  }) as { value: { id: string } }[];

  const checkResults = await selectAccessibleRowsFromTable(
    table,
    new Set(rowsToCheck.map((item) => item.value.id)),
    userId,
    db
  );

  console.log(
    `Check results for table ${table} (num accessible rows): ${checkResults.size} / ${rowsToCheck.length}`
  );
  // Filter the original response array based on the permission check results.
  const filteredBody = responseBody.filter((item, index) => {
    if (!item.value || !item.value.id) {
      return true;
    }
    return checkResults.has(item.value.id);
  });

  // Return the filtered array as a new JSON response.
  // We also forward the original headers from Electric.
  const headers = new Headers(response.headers);
  // Recalculate content-length as we have modified the body.
  headers.delete("content-length");

  return c.json(filteredBody, { headers });
});

Of course, having more complex where support would be better for performance.

[john-rtr]

Thanks for your example @forabi
While your approach works as a workaround, post-filtering with selectAccessibleRowsFromTable has significant performance limitations at scale.

Here's our simplified schema that illustrates why we need server-side filtering (we have way more table to synchronize, and I simplified the relations for the example):

erDiagram
    direction LR
    Team {
        string id PK
        string organisationId FK
    }
    
    User {
        string id PK
        string email
        string name
        string organisationId FK
        string teamId FK
    }
    
    Inbox {
        string id PK
        string name
        string organisationId FK
        string teamId FK
        string userId FK
    }
    
    Thread {
        string id PK
        string subject
        string organisationId FK
        string inboxId FK
    }
    
    Message {
        string id PK
        string body
        string organisationId FK
        string threadId FK
    }
    
    Recipient {
        string messageId PK, FK
        string contactId PK, FK
        string type
    }
    
    Contact {
        string id PK
        string email
        string name
        string organisationId FK
    }

    
    %% Team access control
    Team ||--o{ User : "has members"
    Team ||--o{ Inbox : "has team inbox"
    
    %% User relationships
    User ||--o{ Inbox : "has personal inbox"
    
    %% Message flow
    Inbox ||--o{ Thread : "contains"
    Thread ||--o{ Message : "contains"
    Message ||--o{ Recipient : "sent to"
    Contact ||--o{ Recipient : "receives"

Loading

Here are simple use cases we need:

• Retrieve all threads a user has access to over a period: we can filter on all inbox he has access to + filters on dates
• Retrieve all related messages
• Retrieve all contacts a user has access to

[john-rtr]

I ended up adding denormalized columns to the tables that need filtering by user_id, team_id, and similar fields.
These columns are kept in sync automatically through triggers.
It’s not ideal, but it gets the job done.