Skip to main content

Row Level Security (RLS)

mohammed abubeker
  • Updated

Introduction

Postgres provides level of security at row level means controlling the data access at row level. So, who can see which rows that is what you can control using RLS (Row-level security) in Postgres.

By default, all rows are visible to the users who have select permission on the table but using RLS we can say that only selected rows are visible because now the select query has to go thru a proper security policy to get the data. This is the stronger security mechanism and it controlled by the DBA on the database side.

Why RLS?

For ensure that workers access only those data rows that are pertinent to their department.

Policies

  • Use ALTER TABLE … ENABLE ROW LEVEL SECURITY to enable row-level security on the table.
  • Use the CREATE POLICY command to define new row-level security policies for each table.
  • Each policy grants permission to specific database operations, such as DELETE, UPDATE, or SELECT.
  • USING statements are used to check existing table rows for the policy expression.
  • WITH CHECK statements are used to check new rows.
  • Policy names can be repeated across tables - you can create a policy with the same name and reuse it across multiple tables.

 

Implementation Example

Step 1: Connect Cluster and Create user.

mceclip0.png

Step 2: Create a table and insert data.

mceclip1.png

Step 3: Grant User for access table.

mceclip2.png

Step 4: Let's enable RLS and define policy for restrict data access.

mceclip3.png

Step 5: Now we implemented row level security for a specific user. Now let’s connect via user.

mceclip4.png

Conclusion: So here you have seen that RLS implemented on user level and he is only able to view data which we defined in policy.

For more :

https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities#auth

PostgreSQL: Documentation: 14: 5.8. Row Security Policies