Access control is one of the trickiest pieces of puzzles in database design. In its crudest form, we can put the database behind a wholly closed network (e.g., within a Virtual Private Cloud, or VPC) and allow only a middleware to access it. Then, the authentication and authorization for data lies in the middleware.
Taking a step further, Postgres allows us to put access control closest to the data through one of its most powerful features: Row-Level Security (RLS). In its simplest form, we can define the access control for each row of each table, allowing us to provide granular access to the data.
RLS in PostgreSQL allows database administrators to create policies to restrict, on a per-user basis, whether to allow modifying, returning, or creating rows of any table.
We can think of RLS as a gatekeeper for each row of data. Before an operation is performed (select, insert, update, or delete), the row-level security policies are checked. If the policy check passes, the operation is allowed; if not, the operation is prevented.
In this lesson, we will dig deep into RLS and how Supabase has embraced RLS to provide powerful yet intuitive ways to design access control.
