Row Security Policies serve as a powerful PostgreSQL feature that controls row-level access based on specific policies. While RisingWave does not natively support Row Security Policies, you can achieve equivalent row-level security through a combination of logical views with access control. This approach ensures that users can only access data they are authorized to see.

In versions prior to v2.4, a bug in logical views’ access control failed to prevent unauthorized access. Ensure your RisingWave runs v2.4 or later.

​
Scenario

Imagine you have a table employees that stores employee information, including their department and salary. You want to enforce the following access rules:

  1. HR Managers can view all employees in the HR department.
  2. Engineering Managers can view all employees in the Engineering department.
  3. The CEO can view all employees.

To achieve this, we’ll use logical views to filter data based on user roles and user permissions to restrict access to the underlying table.

​
Procedure

1

Create the `employees` table

Create the employees table to store employee data:

    CREATE TABLE employees (
        name TEXT,
        department TEXT,
        salary NUMERIC,
        username TEXT
    );
2

Insert sample data

Insert some sample data into the employees table:

INSERT INTO employees (name, department, salary, username) VALUES
('Alice', 'HR', 50000, 'alice'),
('Bob', 'Engineering', 60000, 'bob'),
('Charlie', 'HR', 55000, 'charlie'),
('David', 'Engineering', 65000, 'david');
3

Create users

Create three users: hr_manager, engineering_manager, and ceo.

CREATE USER hr_manager WITH PASSWORD 'password';
CREATE USER engineering_manager WITH PASSWORD 'password';
CREATE USER ceo WITH PASSWORD 'password';
4

Create logical views for each user

Create logical views for each user to restrict their access to specific data.

HR Manager can only view employees in the HR department:

View for HR manager
CREATE VIEW hr_employee_view AS
SELECT name, department, salary
FROM employees
WHERE department = 'HR';

GRANT SELECT ON hr_employee_view TO hr_manager;

Engineering Manager can only view employees in the Engineering department:

View for engineering manager
CREATE VIEW engineering_employee_view AS
SELECT name, department, salary
FROM employees
WHERE department = 'Engineering';

GRANT SELECT ON engineering_employee_view TO engineering_manager;

The CEO can view all employees:

View for CEO
CREATE VIEW all_employee_view AS
SELECT name, department, salary
FROM employees;

GRANT SELECT ON all_employee_view TO ceo;
5

Verify privileges

Now we can connect to the database as the user hr_manager to ensure that they can only query the hr_employee_view and cannot access the employees table or other views.

$ psql -h localhost -p 4566 -d dev -U hr_manager
Password for user hr_manager:
psql (16.3, server 13.14.0)
Type "help" for help.

dev=> select * from hr_employee_view;
  name   | department | salary
---------+------------+--------
Charlie | HR         |  55000
Alice   | HR         |  50000
(2 rows)


dev=> select * from employees;
ERROR:  Failed to run the query

Caused by:
  Permission denied: Do not have the privilege

dev=> select * from engineering_employee_view;
ERROR:  Failed to run the query

Caused by:
  Permission denied: Do not have the privilege