Table of contentsClick link to navigate to the desired location
This content has been automatically translated from Ukrainian.
PostgreSQL has a powerful but often underrated feature - Row Level Security (RLS). In short, this data protection at the table row level, that is, the — system decides, which records can the user see or change, before the request even gets into your Rails code.
How it works
In a normal situation, access to data is controlled in the — application, for example, in Rails we write:
@posts = Post.where(user_id: current_user.id)
But RLS allows this check to be delegated the base itself. You enable the security policy for the table:
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_is_owner
ON posts
FOR SELECT USING (user_id = current_setting('app.current_user_id')::int);
After that, even if someone does SELECT * FROM posts, PostgreSQL will automatically impose a condition that the user sees only your own lines.
How to integrate RLS into Rails
In Rails, you can set current_user.id in the database context before executing a request:
ActiveRecord::Base.connection.execute("SET app.current_user_id = #{current_user.id}")
And then all requests (Post.all, Post.find, even joins) will return only the allowed data - no additional where in the code.
This is convenient for multiplayer systems, SaaS or APIs, where security should not depend only on the application level.
Why this at all
- Security at the database level <TAG1> even if someone mistakenly forgets where(user_id: ...), the data will not leak.
- Ease of requests <TAG1> can write Model.all without thinking about filters.
- Unified access control <TAG1> rules are stored with data, not scattered across controllers and services.
RLS does not replace authorization in the application. She - additional level of protection, which ensures that even at a low level no one will receive "extra" data. Row Level Security <TAG1> is like where(user_id: current_user.id) but sewn into the database itself.
This post doesn't have any additions from the author yet.
