Row level security

Control data access at the row level for multi-tenant applications

Row level security (RLS) allows you to control which rows of data users can access in your database. This is particularly useful for multi-tenant applications where different users should only see their own data, or for team-based access where users should only access data relevant to their department or role.

Row level security is currently only supported for Postgres databases. If you need RLS support for other database dialects, please contact us to discuss your use case.

How it works

When a user in your Basedash workspace runs a query, Basedash automatically sets a session variable called basedash.groups that contains all the groups the user belongs to (comma-separated). You can then create policies on your Postgres database that reference this session variable to restrict which rows are returned.

We will always set up the basedash.groups session variable for Postgres databases. It will be an empty string if the user making the query doesn’t belong to any groups.

Here’s the basic flow:

1. You assign users to groups in Basedash (e.g., “Engineering”, “Sales”, “Support”)
2. You create row-level security policies on your database tables
3. When a user runs a query, Basedash sets the basedash.groups session variable
4. Your database policies automatically filter the results based on the user’s groups

The key advantage of this approach is that you maintain full control over your security policies at the database level, while Basedash simply provides the user context needed to enforce those policies.

Setting up RLS in Basedash

Whenever a query is executed against a Postgres database, Basedash automatically sets the basedash.groups session variable with the user’s group memberships.

To view the available groups that will be passed in the session variable:

1. Open the command menu (Cmd+K or Ctrl+K)
2. Navigate to your data source settings
3. Select “Row Level Security”
4. View the available group names that you can reference in your policies

Creating policies on your database

To control data access, you need to create RLS policies directly on your Postgres database. These policies define which rows users can access based on their group membership.

When you enable row level security on a table in Postgres, it will deny all access by default until you create policies that explicitly grant access. Make sure to create the appropriate policies immediately after enabling RLS to avoid disrupting access to your data.

Basic example

Here’s a simple example for an orders table where you want to restrict access based on a department column:

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy for SELECT operations
CREATE POLICY orders_group_policy ON orders
  FOR SELECT
  USING (
    current_setting('basedash.groups', true) IS NULL
    OR ',' || current_setting('basedash.groups', true) || ','
      LIKE '%,' || department || ',%'
  );

This policy allows:

• Normal access when basedash.groups is not set (so your existing applications continue to work)
• Filtered access when the session variable is set (Basedash queries)

Multiple group membership

The policy pattern uses comma-separated values, so it automatically handles users who belong to multiple groups. For example, if a user belongs to both “Engineering” and “Support” groups, the basedash.groups variable will be set to "Engineering,Support", and the policy will match rows where the department is either “Engineering” or “Support”.

Permissive policies for external systems

If you want to allow all operations from external systems (those not using Basedash), you can create permissive policies:

-- Allow external systems full access for INSERT, UPDATE, DELETE
CREATE POLICY orders_external_insert ON orders
  FOR INSERT
  WITH CHECK (current_setting('basedash.groups', true) IS NULL);

CREATE POLICY orders_external_update ON orders
  FOR UPDATE
  USING (current_setting('basedash.groups', true) IS NULL);

CREATE POLICY orders_external_delete ON orders
  FOR DELETE
  USING (current_setting('basedash.groups', true) IS NULL);

Basedash Warehouse

If you’re using Basedash Warehouse (our managed Postgres warehouse for Fivetran connections), you can create policies using the warehouse credentials.

To access your Basedash Warehouse credentials:

1. Open the command menu (Cmd+K or Ctrl+K)
2. Type “Basedash Warehouse”
3. Select “View credentials”
4. Use these credentials to connect to your warehouse and create policies

Note that you must have at least one Fivetran connection set up through Basedash Warehouse for this option to be available.

Once you have the credentials, you can connect to your warehouse using any Postgres client (like psql, pgAdmin, or TablePlus) and run the CREATE POLICY statements directly.

Forcing row level security

When using Basedash Warehouse, you’ll need to force row level security on your tables to ensure that RLS policies are properly applied:

ALTER TABLE public."User" FORCE ROW LEVEL SECURITY;

This is necessary because the Postgres user used to query and create tables in Basedash Warehouse is the same one. By default, Postgres allows table creators to bypass RLS policies and query all rows in their tables. The FORCE ROW LEVEL SECURITY command ensures that RLS policies are enforced even for the table owner, so your security policies will be properly applied to all queries running through Basedash.

When forcing row level security, make sure to create policies that allow INSERT, UPDATE, and DELETE operations for data synchronization. Without these policies, Fivetran and other external systems won’t be able to sync data into your warehouse. See the Permissive policies for external systems section for examples of how to create these policies.

RLS with chat

Row level security automatically applies to all queries in Basedash, including those generated through the AI chat interface. When users ask questions about their data in chat, the AI-generated SQL queries will respect your RLS policies, ensuring users only see data they’re authorized to access.

This means your team members can freely explore and ask questions about data through natural language, while your database policies ensure they stay within their authorized scope.

RLS with reports

When scheduled reports are executed, the basedash.groups session variable is set using the groups associated with the user who created the report. RLS policies are applied based on the report creator’s group memberships.

To maintain security, only the report creator can modify the report’s prompt/instructions. This ensures that users cannot modify reports created by someone with broader access to query data they wouldn’t normally have access to. Other users with access to the report can view it and receive scheduled deliveries, but cannot modify its configuration.

RLS with slack integration

When queries are executed through the Basedash Slackbot, we attempt to match the Slack user to their Basedash user account.

• If a match is found: The basedash.groups session variable is set with the user’s group memberships, and RLS policies are applied based on their access
• If no match is found: The basedash.groups session variable will be set to an empty string, and tables with RLS policies that require group membership will return no rows

This ensures that Slack users who are also Basedash users can access data according to their group permissions, while unmatched Slack users cannot access any group-restricted data.

Important considerations

Database support

Row level security is currently supported for Postgres databases only. Support for other database systems may be added in the future. If you need RLS support for other database dialects, please contact us and let us know about your use case.

Admin access outside Basedash

Admin users who have access to your database credentials (including Basedash Warehouse credentials) can connect to the database directly and query data without RLS restrictions. The policies only apply when the basedash.groups session variable is set, which only happens during Basedash queries.

If you need to ensure that all access to your database goes through RLS policies, you should use dedicated database users for external access and create policies that apply to those users as well.

Compatibility with existing applications

The policy pattern shown above uses a NULL check (current_setting('basedash.groups', true) IS NULL) to ensure that your existing applications and services that query the database directly will continue to work normally without any restrictions. Only queries that come through Basedash (which set the session variable) will have the RLS policies applied.

Performance

Row level security policies are evaluated for every query, so complex policies can impact query performance. Keep policies simple and ensure that any columns referenced in the policies are properly indexed.

Best practices

1. Start simple - Begin with policies on a single table and test thoroughly before expanding to your entire database

2. Use consistent column names - If you’re using RLS across multiple tables, use consistent column names (like department or team_id) to make policy management easier

3. Test with different user groups - Create test users in different groups and verify that they can only access the appropriate data

4. Document your policies - Keep track of which tables have RLS enabled and what the policies do, especially as your database schema grows

5. Monitor query performance - Watch for any performance degradation after enabling RLS, and optimize policies or add indexes as needed