Logo ← PostgreSQL Blog

Postgresql Row Level Security

In multi-tenant or user-centric applications, ensuring that users can only access their own data is a foundational requirement. PostgreSQL…

Postgresql Row Level Security

In multi-tenant or user-centric applications, ensuring that users can only access their own data is a foundational requirement. PostgreSQL addresses this need with a powerful feature called Row-Level Security (RLS), which allows policies to be enforced at the row level enabling precise and scalable access control. This article demonstrates how to implement user-based access control in PostgreSQL using RLS with a practical basketball team scenario. We’ll create a database and schema, define tables of players and coaches, and restrict access so that each coach sees only their own players.

Step 1: Create Database and Schema

We begin by creating a dedicated database and schema for our EuroLeague use case.

-- Create the database
CREATE DATABASE euroleague;

-- Connect to the database
\c euroleague

-- Create a schema
CREATE SCHEMA fla;

-- Set search path to the schema
SET search_path TO fla;

Step 2: Create the Coaches Table

Each coach will represent a unique PostgreSQL user.

CREATE TABLE fla.coaches (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE NOT NULL
);


-- Insert 5 sample coaches
INSERT INTO fla.coaches (username) VALUES
('jasikevicius'),
('ataman'),
('mateo'),
('grimau'),
('bartzokas');

Step 3: Create the Players Table

Each player belongs to one coach, creating a clear ownership structure.

CREATE TABLE fla.team (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    surname TEXT NOT NULL,
    coach_id INT REFERENCES fla.coaches(id)
);

Step 4: Insert Sample Player Data

Each group of players below corresponds to a different coach.

-- Players for Coach Jasikevicius
INSERT INTO fla.team (name, surname, coach_id) VALUES
('Nigel', 'Hayes-Davis', 1),
('Nick', 'Calathes', 1),
('Scottie', 'Wilbekin', 1),
('Dyshawn', 'Pierre', 1),
('Tarik', 'Biberovic', 1),
('Johnathan', 'Motley', 1),
('Marko', 'Guduric', 1),
('Tyler', 'Dorsey', 1),
('Georgios', 'Papagiannis', 1),
('Yam', 'Madar', 1);


-- Coach Ataman
INSERT INTO fla.team (name, surname, coach_id) VALUES
('Shane', 'Larkin', 2),
('Will', 'Clyburn', 2),
('Rodrigue', 'Beaubois', 2),
('Darius', 'Thompson', 2),
('Ante', 'Zizic', 2),
('Bryant', 'Dunston', 2),
('Ercan', 'Osmani', 2),
('Elijah', 'Bryant', 2),
('Erten', 'Gazi', 2),
('Tibor', 'Pleiss', 2);


-- Coach 3 Mateo
INSERT INTO fla.team (name, surname, coach_id) VALUES
('Walter', 'Tavares', 3),
('Sergio', 'Llull', 3),
('Mario', 'Hezonja', 3),
('Gabriel', 'Deck', 3),
('Dzanan', 'Musa', 3),
('Facundo', 'Campazzo', 3),
('Vincent', 'Poirier', 3),
('Guerschon', 'Yabusele', 3),
('Alberto', 'Abalde', 3),
('Carlos', 'Alocen', 3);


-- Coach 4 Grimau
INSERT INTO fla.team (name, surname, coach_id) VALUES
('Nicolas', 'Laprovittola', 4),
('Tomas', 'Satoransky', 4),
('Jan', 'Vesely', 4),
('Nikola', 'Kalinic', 4),
('Cory', 'Higgins', 4),
('Oscar', 'da Silva', 4),
('Jabari', 'Parker', 4),
('James', 'Nnaji', 4),
('Joel', 'Parra', 4),
('Rokas', 'Jokubaitis', 4);


-- Coach 5 Bartzokas
INSERT INTO fla.team (name, surname, coach_id) VALUES
('Kostas', 'Sloukas', 5),
('Thomas', 'Walkup', 5),
('Moustapha', 'Fall', 5),
('Shaquielle', 'McKissic', 5),
('Isaiah', 'Canaan', 5),
('Alec', 'Peters', 5),
('Joel', 'Bolomboy', 5),
('Giannoulis', 'Larantzakis', 5),
('Ignas', 'Brazdeikis', 5),
('Luke', 'Sikma', 5);

Step 5: Enable Row-Level Security (RLS)

This enables and enforces RLS so that only defined policies apply.

ALTER TABLE fla.team ENABLE ROW LEVEL SECURITY;
ALTER TABLE fla.team FORCE ROW LEVEL SECURITY;

Step 6: Create RLS Policy

The following policy filters rows based on the current user’s identity.

CREATE POLICY coach_view_policy ON fla.team
FOR SELECT
USING (
    coach_id = (
        SELECT id FROM fla.coaches WHERE username = current_user
    )
);

This policy ensures that each logged-in coach can only access rows where coach_id matches their own user identity — effectively sandboxing data access on a per-user basis.

To make this work, users need SELECT privileges on the coaches table:

GRANT SELECT ON fla.coaches TO jasikevicius, ataman, mateo, grimau, bartzokas;

Step 7: Create PostgreSQL Roles

Each coach gets a login role and permission to read from the team table.

-- Create roles for each coach
CREATE ROLE jasikevicius LOGIN PASSWORD 'secret';
CREATE ROLE ataman LOGIN PASSWORD 'secret';
CREATE ROLE mateo LOGIN PASSWORD 'secret';
CREATE ROLE grimau LOGIN PASSWORD 'secret';
CREATE ROLE bartzokas LOGIN PASSWORD 'secret';

-- Grant access
GRANT SELECT ON fla.team TO jasikevicius, ataman, mateo, grimau, bartzokas;
GRANT SELECT ON fla.coaches TO jasikevicius, ataman, mateo, grimau, bartzokas;

-- Grant Usage Schema
grant usage on schema fla to jasikevicius, ataman, mateo, grimau, bartzokas;

Test: What Each Coach Sees

When jasikevicius and Ataman log in and run:

psql -p 5435 -U jasikevicius -d euroleague


euroleague=> SELECT * FROM fla.team;
 id |   name    |   surname   | coach_id
----+-----------+-------------+----------
  1 | Nigel     | Hayes-Davis |        1
  2 | Nick      | Calathes    |        1
  3 | Scottie   | Wilbekin    |        1
  4 | Dyshawn   | Pierre      |        1
  5 | Tarik     | Biberovic   |        1
  6 | Johnathan | Motley      |        1
  7 | Marko     | Guduric     |        1
  8 | Tyler     | Dorsey      |        1
  9 | Georgios  | Papagiannis |        1
 10 | Yam       | Madar       |        1



psql -p 5435 -U ataman -d euroleague

euroleague=> SELECT * FROM fla.team;
 id |   name   | surname  | coach_id
----+----------+----------+----------
 11 | Shane    | Larkin   |        2
 12 | Will     | Clyburn  |        2
 13 | Rodrigue | Beaubois |        2
 14 | Darius   | Thompson |        2
 15 | Ante     | Zizic    |        2
 16 | Bryant   | Dunston  |        2
 17 | Ercan    | Osmani   |        2
 18 | Elijah   | Bryant   |        2
 19 | Erten    | Gazi     |        2
 20 | Tibor    | Pleiss   |        2

They will only see their 10 players.

Benefits of This Approach

  • Fast filtering using indexed integer IDs (coach_id)
  • Full referential integrity
  • Easy user management (especially in large systems)
  • Clean separation between users and their data

Conclusion

Row-Level Security in PostgreSQL allows precise control over who can see which rows. When implemented with user IDs and foreign keys, it provides a scalable, performant, and secure approach suitable for enterprise systems.

Need to restrict data access at a per-user level? PostgreSQL RLS with foreign key user mapping is your best friend.

Best Practices for Implementing RLS

  • Always use FORCE ROW LEVEL SECURITY to prevent privilege bypass.
  • Avoid hardcoding user mappings consider integrating with external authentication.
  • Use roles and schema separation for large-scale deployments.
  • Monitor query plans RLS can influence performance if policies are complex.