Row-Level Security

If you’ve been managing separate Excel files to keep each user’s data private, row-level security (RLS) makes it much easier. Instead of maintaining individual files, everyone can use a single shared database table. The system automatically filters the data so that users only see and update their own records, while managers can view and analyze everything in one place without having to manually combine multiple files.

For example, in a sales organization, each opportunity record can be visible only to the salesperson responsible for it and their manager—even though all records are stored in the same database table.

Note

Row-level security is currently only available for Postgres databases. Other database types are not supported at this time.

Setup required: Enabling RLS requires additional setup beyond toggling this feature on. You’ll need to create policies that define which rows each user can access as explained below.

Column for ownership: To use RLS, your table must include a column that stores the email address of the user who owns each row. Visual DB passes the logged-in user’s email address to PostgreSQL, which then filters rows based on your policies. This column should contain email addresses that match your users’ login credentials.

Flexible access control: RLS policies are highly flexible and can be tailored to your organization’s needs. For example, you can configure policies where everyone can view all records but only owners can update them, or restrict both viewing and updating to owners only. You can also set up hierarchical access where individual users see only their own rows, while managers automatically see all rows belonging to anyone in their reporting chain. Multiple policies can work together to create sophisticated access patterns that match your business requirements.

Tutorial

Setting up row level security requires you to run SQL code. You can use SQL Scratchpad (found in the Database tab) to type and run SQL code.

Let’s create a table for this tutorial. Use this SQL:

CREATE TABLE opportunities (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  account_name TEXT,
  stage TEXT,
  close_date DATE,
  comments TEXT,
  owner_email TEXT NOT NULL
);

The most important column in this table is owner_email. To use RLS the table must have a column for keeping track of the owner of each record.

Enable Row-Level Security

Row-level security is a per-table setting in Postgres. Let’s turn it on on our table. Run this SQL:

ALTER TABLE opportunities ENABLE ROW LEVEL SECURITY;

Create RLS Policies

Once you enable row-level security on a table, superusers and the account that created the table can continue to see and modify all rows. Everyone else is locked out. You create policies that determine what everyone can access.

You can create multiple policies on the same table, and they work together using OR logic - if any policy grants access to a row, the user can see/modify it. For example, you might have one policy that grants users access to their own records, and another that grants managers access to their team’s records.

Let’s create a basic policy:

CREATE POLICY salesperson_opportunities ON opportunities
  FOR ALL
  USING (owner_email = current_setting('app.current_user_email', true))
  WITH CHECK (owner_email = current_setting('app.current_user_email', true));

This is a basic “users can only access their own data” policy:

  • Alice (alice@company.com) can only see opportunities where owner_email = ‘alice@company.com’

  • Alice can create new opportunities, but only with owner_email = ‘alice@company.com’

  • Alice cannot change an opportunity’s owner_email to someone else

  • Alice cannot see or modify Bob’s opportunities

The most important part here is app.current_user_email. This is how Visual DB passes the logged-in user’s email to Postgres.

Create a Non-Owner User

Superusers and table owners can see and modify all rows (RLS doesn’t apply to them). For that reason, we need to create a new user who will be subject to RLS.

-- Create the user with a password
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password_here';

-- Grant connect to the database
GRANT CONNECT ON DATABASE your_database_name TO app_user;

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant SELECT, INSERT, UPDATE, DELETE on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Grant permissions on sequences (for auto-increment columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Automatically grant permissions on future tables (recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

Using This Table in Visual DB

  1. In Visual DB, create a database connection using the new app_user we just created. Always use this connection when creating queries to ensure RLS restrictions are enforced.

  2. Create a query and add the opportunities table as the main table.

  3. Create a form or sheet using this query.

  4. In the design panel of the form or sheet, check the “Row-level security” checkbox.

Warning

Make sure your Visual DB users have the Builder or User role. Users with Database Admin or Owner roles can execute arbitrary SQL and bypass RLS restrictions.

Giving Managers Access to Their Team’s Data

You can optionally create an additional policy that allows managers to view and update opportunities owned by anyone who reports to them (directly or indirectly), and reassign those opportunities within their reporting chain.

To do this you need to first create an employee table:

CREATE TABLE employees (
    email_address TEXT PRIMARY KEY,
    manager_email TEXT NULL,
    first_name TEXT,
    last_name TEXT,
    
    -- Foreign key to reference manager (also in this table)
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_email) 
        REFERENCES employees(email_address)
        ON DELETE SET NULL
);

-- Index for better performance on recursive queries
CREATE INDEX idx_employees_manager_email ON employees(manager_email);

Populate the employees table with your organizational structure, making sure each employee’s manager_email points to their manager’s email_address.

Then add this rule:

CREATE POLICY employee_hierarchy ON opportunities FOR ALL 
USING (
    owner_email = current_setting('app.current_user_email', true)
    OR owner_email IN (
        WITH RECURSIVE managed AS (
            -- Direct reports
            SELECT email_address
            FROM employees
            WHERE manager_email = current_setting('app.current_user_email', true)
            
            UNION ALL
            
            -- Reports of reports
            SELECT e.email_address
            FROM employees e
            INNER JOIN managed m ON e.manager_email = m.email_address
        )
        SELECT email_address FROM managed
    )
)
WITH CHECK (
    owner_email = current_setting('app.current_user_email', true)
    OR owner_email IN (
        WITH RECURSIVE managed AS (
            SELECT email_address
            FROM employees
            WHERE manager_email = current_setting('app.current_user_email', true)
            
            UNION ALL
            
            SELECT e.email_address
            FROM employees e
            INNER JOIN managed m ON e.manager_email = m.email_address
        )
        SELECT email_address FROM managed
    )
);