Opensource internals

How ClickHouse Turns Row-Level Security into a Filter Expression

20 min read Jay Hubbert

ClickHouse mixes permissive and restrictive row policies into one boolean expression, then rewrites the query plan so that expression is evaluated as a filter on the table read.

TL;DR: A CREATE ROW POLICY statement stores a SQL predicate and a set of roles. At read time, the policies that match the current user are mixed into one expression — permissive policies OR-ed together, restrictive policies AND-ed — and that expression is compiled into an ActionsDAG and attached to the table read. If the storage supports PREWHERE, the filter is pushed into the reader; otherwise it becomes an explicit filter step in the plan.

Row-level security is a promise that a user who can SELECT a table will only see the rows they are entitled to. Implementing it is easy if you own the storage engine. Implementing it in a query planner is not, because the planner does not know which tables a query touches until it has already resolved the whole join tree.

ClickHouse’s implementation is worth studying because it does the security check in the one place where it cannot be bypassed: the table read. The row policy is not a filter the user could remove with a WHERE clause, because it is not part of the user’s query at all — it is attached to the read step of the plan.

The entity: a predicate and a set of roles

A row policy is an access entity, like a user or a role:

cpp 
// src/Access/RowPolicy.h:16
struct RowPolicy : public IAccessEntity
{
    void setShortName(const String & short_name);
    void setDatabase(const String & database);
    void setTableName(const String & table_name);
    void setFullName(const String & short_name, const String & database, const String & table_name);
    void setFullName(const RowPolicyName & full_name_);

    const String & getDatabase() const { return full_name.database; }
    const String & getTableName() const { return full_name.table_name; }
    const String & getShortName() const { return full_name.short_name; }
    const RowPolicyName & getFullName() const { return full_name; }

    /// A SQL conditional expression used to figure out which rows should be visible
    /// for user or available for modification.
    std::array<String, static_cast<size_t>(RowPolicyFilterType::MAX)> filters;

There is exactly one filter type implemented — SELECT_FILTER. The others are declared but explicitly disabled:

cpp 
// src/Access/Common/RowPolicyDefs.h:28
enum class RowPolicyFilterType : uint8_t
{
    /// Filter is a SQL conditional expression used to figure out which rows should be visible
    /// for user or available for modification. If the expression returns NULL or false for some rows
    /// those rows are silently suppressed.
    SELECT_FILTER,

#if 0 /// Row-level security for INSERT, UPDATE, DELETE is not implemented yet.
    /// Check is a SQL condition expression used to check whether a row can be written into
    /// the table. If the expression returns NULL or false an exception is thrown.
    /// If a conditional expression here is empty it means no filtering is applied.
    INSERT_CHECK,
    UPDATE_FILTER,
    UPDATE_CHECK,
    DELETE_FILTER,
#endif

    MAX
};

The #if 0 is honest engineering: the shape is there for a future implementation, and the compiler will not let anyone use it by accident.

A policy is either permissive or restrictive, and the semantics are the SQL-standard ones:

cpp 
// src/Access/RowPolicy.h:33
    /// Sets that the policy is permissive.
    /// A row is only accessible if at least one of the permissive policies passes,
    /// in addition to all the restrictive policies.
    void setPermissive(bool permissive_ = true) { setRestrictive(!permissive_); }
    bool isPermissive() const { return !isRestrictive(); }

A policy can be attached to a database rather than a table, in which case it applies to every table in it:

cpp 
// src/Access/RowPolicy.h:39
    /// Applied for entire database
    bool isForDatabase() const { return full_name.table_name == RowPolicyName::ANY_TABLE_MARK; }

And a policy is only applied to a set of users or roles:

cpp 
// src/Access/RowPolicy.h:62
    /// Which roles or users should use this row policy.
    RolesOrUsersSet to_roles;

Finally, the filter expression is validated at creation time, not at read time:

cpp 
// src/Access/RowPolicy.cpp:19
void checkRowPolicyFilterExpression(const ASTPtr & expression)
{
    /// `arrayJoin` changes the number of rows, while a row policy filter must yield one verdict per row.
    if (expressionContainsArrayJoin(expression))
        throw Exception(ErrorCodes::ILLEGAL_PREWHERE, "arrayJoin is not allowed in a row policy filter expression");

    /// A row policy filter is a predicate over the rows of one table, so a column matcher has no meaning in it.
    /// Reject it here, when the policy is created or altered, rather than only on the next read of the table.
    if (expression)
    {
        if (const auto * matcher = findColumnMatcherInExpression(*expression))
            throw Exception(ErrorCodes::BAD_ARGUMENTS,
                "Column matcher {} is not allowed in a row policy filter expression; list the columns explicitly. In filter {}",
                matcher->formatForErrorMessage(),
                expression->formatForErrorMessage());
    }
}

Both rejections are about shape, not about values: a row policy must be a one-verdict-per-row predicate over named columns.

The mix: OR the permissive, AND the restrictive

The mixing happens in RowPolicyCache::mixFiltersFor, and the algebra is small:

cpp 
// src/Access/RowPolicyCache.cpp:31
    class FiltersMixer
    {
    public:
        void add(const ASTPtr & filter, bool is_restrictive)
        {
            if (is_restrictive)
                restrictions.push_back(filter);
            else
                permissions.push_back(filter);
        }

        ASTPtr getResult(bool users_without_row_policies_can_read_rows) &&
        {
            if (!permissions.empty() || !users_without_row_policies_can_read_rows)
            {
                /// Process permissive filters.
                restrictions.push_back(makeASTForLogicalOr(std::move(permissions)));
            }

            /// Process restrictive filters.
            ASTPtr result;
            if (!restrictions.empty())
                result = makeASTForLogicalAnd(std::move(restrictions));

            if (result)
            {
                bool value = false;
                if (tryGetLiteralBool(result.get(), value) && value)
                    result = nullptr; /// The condition is always true, no need to check it.
            }

            return result;
        }

Read it carefully: the permissive filters are OR-ed into a single expression, and that expression is then pushed into the restrictions list and AND-ed with the restrictive ones. So the final predicate is

text 
(permissive_1 OR permissive_2 OR ...) AND restrictive_1 AND restrictive_2 AND ...

which is exactly the SQL-standard semantics.

The users_without_row_policies_can_read_rows flag decides the degenerate case. If a table has policies but none of them match the current user, the question is: does the user see everything, or nothing?

  • If users_without_row_policies_can_read_rows = true (the default), the user is treated as having no applicable policy, so the permissive expression is not added — and the user sees everything.
  • If it is false, the permissive expression is added, but the permission list is empty, so makeASTForLogicalOr({}) produces… nothing sensible, and the user sees nothing.

The config knob is one line:

cpp 
// src/Access/AccessControl.cpp:349
    setEnabledUsersWithoutRowPoliciesCanReadRows(config_.getBool("access_control_improvements.users_without_row_policies_can_read_rows", true));

and the “throw instead of silently showing nothing” variant is a separate flag:

cpp 
// src/Access/ContextAccess.cpp:596
        if (access_control->shouldThrowOnUnmatchedRowPolicies())
        {
            throw Exception(ErrorCodes::ACCESS_DENIED,
                            "{}: Table {}.{} has row policies, but none of them are for the current user",
                            getUserName(), backQuoteIfNeed(database), backQuoteIfNeed(table_name));
        }

That is a fail-closed default with a fail-open alternative — the kind of decision that has to be made explicitly, and the code makes it visible.

The cache: per (user, role set), rebuilt on change

Mixing filters is O(policies × enabled sets), so it is not done per query. The cache is keyed by the enabled set — the user id plus the flat set of enabled roles:

cpp 
// src/Access/EnabledRowPolicies.h:39
    struct Params
    {
        UUID user_id;
        boost::container::flat_set<UUID> enabled_roles;

        auto toTuple() const { return std::tie(user_id, enabled_roles); }
        friend bool operator ==(const Params & lhs, const Params & rhs) { return lhs.toTuple() == rhs.toTuple(); }
        friend bool operator !=(const Params & lhs, const Params & rhs) { return !(lhs == rhs); }
        friend bool operator <(const Params & lhs, const Params & rhs) { return lhs.toTuple() < rhs.toTuple(); }
        friend bool operator >(const Params & lhs, const Params & rhs) { return rhs < lhs; }
        friend bool operator <=(const Params & lhs, const Params & rhs) { return !(rhs < lhs); }
        friend bool operator >=(const Params & lhs, const Params & rhs) { return !(lhs < rhs); }
    };

and the cache stores weak pointers, so an enabled set nobody uses disappears on its own:

cpp 
// src/Access/RowPolicyCache.h:53
    std::map<EnabledRowPolicies::Params, std::weak_ptr<EnabledRowPolicies>> enabled_row_policies TSA_GUARDED_BY(mutex);

The lookup path is:

cpp 
// src/Access/RowPolicyCache.cpp:120
std::shared_ptr<const EnabledRowPolicies> RowPolicyCache::getEnabledRowPolicies(const UUID & user_id, const boost::container::flat_set<UUID> & enabled_roles)
{
    std::lock_guard lock{mutex};
    ensureAllRowPoliciesRead();

    EnabledRowPolicies::Params params;
    params.user_id = user_id;
    params.enabled_roles = enabled_roles;
    auto it = enabled_row_policies.find(params);
    if (it != enabled_row_policies.end())
    {
        auto from_cache = it->second.lock();
        if (from_cache)
            return from_cache;
        enabled_row_policies.erase(it);
    }

    auto res = std::shared_ptr<EnabledRowPolicies>(new EnabledRowPolicies(params));
    enabled_row_policies.emplace(std::move(params), res);
    mixFiltersFor(*res, all_policies, access_control.isEnabledUsersWithoutRowPoliciesCanReadRows());
    return res;
}

The result of the mix is a map from (database, table, filter_type) to a prepared RowPolicyFilter, published atomically:

cpp 
// src/Access/EnabledRowPolicies.h:19
struct RowPolicyFilter
{
    ASTPtr expression;
    std::shared_ptr<const std::pair<String, String>> database_and_table_name;
    std::vector<RowPolicyPtr> policies;

    bool isAlwaysTrue() const;
    bool isAlwaysFalse() const;
};

and the read side is lock-free — it loads the whole map with an atomic shared_ptr load and then does a hash lookup:

cpp 
// src/Access/EnabledRowPolicies.cpp:40
RowPolicyFilterPtr EnabledRowPolicies::getFilter(const String & database, const String & table_name, RowPolicyFilterType filter_type) const
{
    /// We don't lock `mutex` here.
    auto loaded = mixed_filters.load();
    auto it = loaded->find({database, table_name, filter_type});
    if (it == loaded->end())
    {   /// Look for a policy for database if a table policy not found
        it = loaded->find({database, RowPolicyName::ANY_TABLE_MARK, filter_type});
        if (it == loaded->end())
        {
            return {};
        }
    }

    return it->second;
}

The two-step lookup is the database-level fallback: a policy on mydb applies to mydb.mytable unless mydb.mytable has its own policies — and in that case the table mixer is seeded from the database mixer, so the database policy is still AND-ed in.

The seeding happens here:

cpp 
// src/Access/RowPolicyCache.cpp:334
                    auto table_it = table_mixers.find(key);
                    if (table_it == table_mixers.end())
                    {   /// no exact match - create new mixer
                        MixedFiltersKey database_key = key;
                        database_key.table_name = RowPolicyName::ANY_TABLE_MARK;

                        auto database_it = database_mixers.find(database_key);

                        if (database_it == database_mixers.end())
                        {
                            table_it = table_mixers.try_emplace(key).first;
                        }
                        else
                        {
                            /// table policies are based on database ones
                            table_it = table_mixers.insert({key, database_it->second}).first;
                        }
                    }

Note the subtlety in the comment above the final publish:

cpp 
// src/Access/RowPolicyCache.cpp:384
            /// The key's string_view-s must borrow from the value's own pair (which the filter
            /// owns), not from `key` built at first insertion: several policies can share one
            /// (database, table) key while the value's pair is reassigned to the last of them,
            /// so a key borrowing another policy's pair would dangle once that policy is dropped.
            const auto & names = *mixed_filter->database_and_table_name;
            MixedFiltersKey owned_key{names.first, names.second, key.filter_type};
            mixed_filters->emplace(owned_key, std::move(mixed_filter));

The MixedFiltersKey holds string_views. Those views must point into the filter’s own std::pair<String, String>, not into the temporary key that was built at insertion time — otherwise, when several policies share a (database, table) and the value’s pair is reassigned, the key would dangle. This is exactly the kind of bug that a std::string-keyed map would have made impossible, and it is why the code builds an owned_key at publish time.

Invalidation: rebuild off the lock, coalesce per batch

When a policy changes, every enabled set that could be affected has to be re-mixed. The rebuild is O(enabled sets × policies), so it is done off the mutex:

cpp 
// src/Access/RowPolicyCache.cpp:210
void RowPolicyCache::mixFiltersIfNeeded()
{
    /// Rebuilding is O(enabled sets * policies) and slow with lots of policies, so do it off `mutex`
    /// to not block getEnabledRowPolicies (i.e. logins). Safe because it always runs single-threaded
    /// from AccessChangesNotifier::sendNotifications, on an immutable snapshot, publishing via atomic store.
    std::unordered_map<UUID, PolicyInfo> policies_snapshot;
    std::vector<std::shared_ptr<EnabledRowPolicies>> targets;
    bool users_without_row_policies_can_read_rows = false;
    {
        std::lock_guard lock{mutex};
        if (!need_mix_filters)
            return;
        policies_snapshot = all_policies;

The snapshot-and-publish pattern is the reason the readers can be lock-free: the rebuild works on an immutable copy of the policy set and publishes the result with an atomic store. A reader either sees the old map or the new one, never a half-built one.

The need_mix_filters flag is coalesced: a batch of ten ALTER ROW POLICY statements causes one rebuild, not ten:

cpp 
// src/Access/RowPolicyCache.cpp:235
        /// Cleared only after the snapshot is built (so a throwing snapshot is retried), but under
        /// `mutex` so a change during the unlocked rebuild re-sets it and is picked up next batch.
        need_mix_filters = false;

and if the rebuild throws, the flag is set back so it is retried:

cpp 
// src/Access/RowPolicyCache.cpp:247
    catch (...)
    {
        /// Rebuild failed: request a retry on the next batch (some sets may be left stale).
        std::lock_guard lock{mutex};
        need_mix_filters = true;
        throw;
    }

Finding the filter for a storage

A query does not read a table — it reads a storage, which may be a Merge over several tables, a Distributed, a Buffer, an Alias. The filter for the storage is the combination of the filters of everything underneath it:

cpp 
// src/Storages/getEffectiveRowPolicyFilter.cpp:16
void collectRowPolicyFilters(const IStorage & storage, const ContextPtr & context, RowPolicyFilterPtr & result, NameSet & visited)
{
    /// A synthetic data source is not a table of the catalog, and it may borrow the name of one,
    /// so the row policies of that name have nothing to do with the rows it produces.
    if (storage.isSyntheticDataSource())
        return;

    auto storage_id = storage.getStorageID();
    if (!storage_id.hasDatabase() || !visited.emplace(storage_id.getFullTableName()).second)
        return;

    result = combineRowPolicyFilters(
        std::move(result),
        context->getRowPolicyFilter(storage_id.getDatabaseName(), storage_id.getTableName(), RowPolicyFilterType::SELECT_FILTER));

    for (const auto & underlying : storage.getUnderlyingStorages())
        if (underlying)
            collectRowPolicyFilters(*underlying, context, result, visited);
}

Three things to notice:

  1. A synthetic data source (a Values table function, a subquery materialised as a storage) is skipped: it is not a catalog table, and it may borrow the name of one, so the policies of that name have nothing to do with the rows it produces.
  2. The visited set terminates the walk — a storage chain that loops would otherwise recurse forever.
  3. The filters are combined with AND, via combineRowPolicyFilters:
cpp 
// src/Access/EnabledRowPolicies.cpp:62
RowPolicyFilterPtr combineRowPolicyFilters(RowPolicyFilterPtr filter, RowPolicyFilterPtr combine_with_filter)
{
    if (filter && combine_with_filter)
    {
        auto new_filter = std::make_shared<RowPolicyFilter>(*filter);

        if (filter->isAlwaysTrue())
        {
            new_filter->expression = combine_with_filter->expression;
        }
        else if (combine_with_filter->isAlwaysTrue())
        {
            new_filter->expression = filter->expression;
        }
        else
        {
            new_filter->expression = makeASTForLogicalAnd({filter->expression, combine_with_filter->expression});
        }

        std::copy(combine_with_filter->policies.begin(), combine_with_filter->policies.end(), std::back_inserter(new_filter->policies));
        filter = new_filter;
    }
    else if (!filter)
    {
        filter = combine_with_filter;
    }

    return filter;
}

The isAlwaysTrue shortcuts avoid building true AND x expressions, which matters because a Merge over ten tables would otherwise produce a chain of degenerate conjunctions.

Finally, a filter that is always true is dropped entirely:

cpp 
// src/Storages/getEffectiveRowPolicyFilter.cpp:46
RowPolicyFilterPtr getEffectiveRowPolicyFilter(const IStorage & storage, const ContextPtr & context)
{
    auto filter = getRowPolicyFilterForStorage(storage, context);
    if (!filter || filter->isAlwaysTrue())
        return nullptr;
    return filter;
}

Injection: the filter becomes a plan node

Now the interesting part. The planner attaches the filter to the table expression:

cpp 
// src/Planner/PlannerJoinTree.cpp:985
std::optional<FilterDAGInfo> buildRowPolicyFilterIfNeeded(const StoragePtr & storage,
    SelectQueryInfo & table_expression_query_info,
    PlannerContextPtr & planner_context,
    std::set<std::string> & used_row_policies,
    NameSet required_names_without_filter = {})
{
    const auto & query_context = planner_context->getQueryContext();

    auto row_policy_filter = getEffectiveRowPolicyFilter(*storage, query_context);
    if (!row_policy_filter)
        return {};

    for (const auto & row_policy : row_policy_filter->policies)
    {
        auto name = row_policy->getFullName().toString();
        if (query_context->hasQueryContext())
            query_context->getQueryContext()->addUsedRowPolicy(name);
        used_row_policies.emplace(std::move(name));
    }

    return buildFilterInfo(
        row_policy_filter->expression,
        table_expression_query_info.table_expression,
        planner_context,
        std::move(required_names_without_filter));
}

The used policies are recorded in the context, so system.query_log shows which policies were in effect. The expression is turned into a FilterDAGInfo by buildFilterInfo, which compiles the AST into an ActionsDAG against the table’s schema:

cpp 
// src/Planner/Utils.cpp:762
FilterDAGInfo buildFilterInfo(QueryTreeNodePtr filter_query_tree,
        const TableExpressionNodePtr & table_expression,
        PlannerContextPtr & planner_context,
        NameSet table_expression_required_names_without_filter)
{
    const auto & query_context = planner_context->getQueryContext();

    /// Optimize logical expressions in the filter, e.g. convert OR-chains of
    /// equalities into IN (important for row policies that produce many
    /// permissive conditions like `x = 1 OR x = 2 OR ... OR x = N`).
    LogicalExpressionOptimizerPass logical_expression_optimizer_pass;
    logical_expression_optimizer_pass.run(filter_query_tree, query_context);

The comment is a nice piece of engineering judgement: a permissive row policy that lists a hundred values produces an OR-chain of a hundred equalities, which the optimizer turns into a single IN. That is a 100× reduction in the work done per row.

Then the AST becomes an ActionsDAG:

cpp 
// src/Planner/Utils.cpp:782
    collectSourceColumns(filter_query_tree, planner_context, false /*keep_alias_columns*/);
    collectSets(filter_query_tree, *planner_context);

    ActionsDAG filter_actions_dag;

    ColumnNodePtrWithHashSet empty_correlated_columns_set;
    PlannerActionsVisitor actions_visitor(planner_context, empty_correlated_columns_set, false /*use_column_identifier_as_action_node_name*/);
    auto [expression_nodes, correlated_subtrees] = actions_visitor.visit(filter_actions_dag, filter_query_tree);
    correlated_subtrees.assertEmpty("in row-policy and additional table filters");
    if (expression_nodes.size() != 1)
        throw Exception(ErrorCodes::BAD_ARGUMENTS,
            "Filter actions must return single output node. Actual {}",
            expression_nodes.size());

Note correlated_subtrees.assertEmpty(...): a row policy filter may not contain a correlated subquery. That is a security-relevant restriction — a correlated subquery in a policy could observe the outer query’s values, which would let a policy leak information about the query it is filtering.

Push-down: the dangerous part

Once the filter is an ActionsDAG, the planner has a choice: push it into the reader (as a PREWHERE-like filter, evaluated while the granules are being read), or keep it as an explicit filter step in the plan.

cpp 
// src/Planner/PlannerJoinTree.cpp:2220
                    /// The filter is built against this table's schema, but read() hands it to wrapper
                    /// storages' children (Merge, Buffer), which re-derive it against their own types.
                    /// Push it down only if every column it consumes is in the PREWHERE contract.
                    /// A remote storage cannot carry it at all: read() only ships query text to the
                    /// remote servers and never lowers the filter into it, so pushing would silently
                    /// drop an access-control filter. Refuse, and let the stage check fail closed.
                    bool can_push_down_filter = storage->supportsPrewhere() && !storage->isRemote();
                    if (can_push_down_filter)
                    {
                        if (const auto supported_prewhere_columns = storage->supportedPrewhereColumns())
                        {
                            const auto & table_columns = storage_snapshot->metadata->getColumns();
                            const bool include_subcolumns = storage->supportedPrewhereColumnsIncludeSubcolumns();
                            for (const auto & column_name : row_policy_filter_info->actions.getRequiredColumnsNames())
                            {
                                if (!prewhereSupportedColumnsContain(*supported_prewhere_columns, include_subcolumns, table_columns, column_name))
                                {
                                    can_push_down_filter = false;
                                    break;
                                }
                            }
                        }
                    }

The !storage->isRemote() is the important one. A remote storage (Distributed, a MergeTree over S3) implements read() by shipping query text to the remote server. The row policy filter is not part of that text, so pushing it down would mean the filter is evaluated… nowhere. The rows would come back unfiltered, and the plan would look like it had a filter.

So the planner refuses to push down for a remote storage. The filter then becomes an explicit plan step, applied on the initiator after the remote rows come back.

There is a third case — a logical plan (a serialized query plan, or a parallel-replicas plan), which reads through a placeholder ReadFromTableStep that cannot carry the filter:

cpp 
// src/Planner/PlannerJoinTree.cpp:2244
                    /// A logical plan (a serialized query plan or a parallel replicas plan) reads through a
                    /// placeholder `ReadFromTableStep`, which cannot carry `row_level_filter`: the node that
                    /// executes the plan rebuilds the read without it and would return the rows the policy
                    /// excludes. For a storage whose PREWHERE support depends on the columns (e.g. `Memory`)
                    /// that node cannot tell whether the policy was pushed down, so keep the policy as an
                    /// explicit filter step of the plan, ahead of the PREWHERE filter step, so that the
                    /// user's conditions never see the excluded rows.
                    /// A table function read directly on that node loses a pushed-down policy whatever its contract.
                    if (select_query_options.build_logical_plan
                        && (storage->supportedPrewhereColumns().has_value() || (table_function_node && can_push_down_filter)))
                        where_filters.emplace(where_filters.begin(), std::move(*row_policy_filter_info), makeDescription("Row-level security filter"));
                    /// TODO: Never put row-level security filter in WHERE clause for storages that do not support PREWHERE to avoid merging of filters.
                    else if (can_push_down_filter)
                        row_level_filter = std::make_shared<FilterDAGInfo>(std::move(*row_policy_filter_info));
                    else
                    {
                        where_filters.emplace_back(std::move(*row_policy_filter_info), makeDescription("Row-level security filter"));
                        row_policy_filter_not_pushed = true;
                    }

Three branches:

  • Pushed down (row_level_filter): the filter is evaluated inside the reader, against the raw columns, before the user’s own WHERE clauses. This is the fast path and it is also the semantically important one: the excluded rows never enter the pipeline at all.
  • Explicit filter step (where_filters): the filter becomes a plan node before the user’s filters, so the user’s conditions never see the excluded rows either. This is the fail-closed path.
  • build_logical_plan: the filter is put at the front of the where filter list — where_filters.emplace(where_filters.begin(), ...) — which is the same fail-closed property, expressed as ordering.

The row_policy_filter_not_pushed flag then propagates to the stage check, which refuses to advance the query past FetchColumns if the filter was not applied:

cpp 
// src/Planner/PlannerJoinTree.cpp:2744
                    if (row_policy_filter_not_pushed && till_stage > QueryProcessingStage::FetchColumns)

So if a plan tries to skip the filtering step, the stage check fails. This is the belt-and-braces that makes the “refuse to push down” decision safe.

What the user sees

sql 
CREATE TABLE t (id UInt64, tenant UInt64) ENGINE = MergeTree ORDER BY id;
CREATE ROW POLICY p ON t FOR SELECT USING tenant = 42;

SELECT count() FROM t;
-- only the rows with tenant = 42 are counted
sql 
CREATE ROW POLICY tenant_isolation ON db.* FOR SELECT USING tenant = currentUser()::UInt64;
-- applies to every table in db, unless the table has its own policies
sql 
CREATE ROW POLICY strict ON t FOR SELECT USING tenant = 1 RESTRICTIVE;
-- combined with a permissive policy: (permissive OR ...) AND tenant = 1

The important property is that the user cannot see or defeat the policy. The filter is not in the query, so there is no WHERE clause to remove, no PREWHERE to override, and no EXPLAIN output that shows a user-writable expression. The EXPLAIN output does show a Row-level security filter description for the non-pushed case, which is the honest thing to do.

Trade-offs

What it buys.

  • One enforcement point. The filter is attached to the read, so every path into the data — SELECT, a view, a subquery, a JOIN — goes through it.
  • No user-visible syntax. The user does not write the predicate, so they cannot accidentally drop it, and the query text they send does not leak the policy.
  • Fast when pushed down. The filter is evaluated against the raw columns while the granules are read, so the excluded rows never cost a column materialisation.

What it costs.

  • The planner has to know about it. Row policies are not a storage-layer feature; they are a planner feature that reaches into the storage layer. Every storage that wants push-down has to declare supportedPrewhereColumns.
  • Fail-closed paths are slower. A remote storage or a logical plan gets an explicit filter step, which means the excluded rows are transferred from the remote server and then filtered. The policy is still enforced, but the bandwidth saving is lost.
  • The mix is not free. Changing a policy rebuilds the filters for every live enabled set. On a server with thousands of users and hundreds of policies, that is a visible pause — which is why the rebuild happens off the lock, and why the need_mix_filters flag coalesces a batch of changes into one rebuild.
  • Only SELECT is implemented. INSERT, UPDATE and DELETE filters are declared in the enum and disabled with #if 0. A table with a restrictive row policy can still be written to by a user with INSERT privilege. The #if 0 block makes the gap explicit rather than hiding it.
  • The expression is user-defined SQL. A row policy is arbitrary SQL, and it is evaluated for every row. A policy like tenant IN (SELECT ... FROM another_table) is a correlated lookup per row. The validation rejects arrayJoin and column matchers, but it does not reject a slow policy.

The bottom line

Row-level security in ClickHouse is a query-plan transformation, not a storage filter. The policies are mixed into one boolean expression ahead of time (per user, per role set, cached, invalidated on change), and the expression is compiled into an ActionsDAG and attached to the table read at plan time.

The security-relevant design decisions are all about failing closed:

  • a remote storage refuses push-down, because the filter could not travel with the query;
  • a logical plan keeps the filter as an explicit step, because the placeholder read step cannot carry it;
  • the stage check refuses to advance past FetchColumns if the filter was not applied;
  • a policy with no matching user fails closed (or throws) unless the server is explicitly configured to fail open.

The performance of the common case is good — the filter is evaluated inside the reader against raw columns — and the performance of the uncommon cases is merely correct, which is the right trade for an access-control filter.

The thing people get wrong

A row policy on a table is not inherited by a view over that table, and it is not the same thing as a WHERE clause. Two consequences:

  1. A view defined as SELECT * FROM t does not carry t’s row policy with it. The policy applies when the view’s body is planned, against the view’s own tables — and the analyzer inlines views aggressively, so the policy may end up applied to the underlying table anyway. But if the view is not inlined, the policy of the view’s tables applies, not the policy of the tables the view reads.
  2. users_without_row_policies_can_read_rows defaults to true, which means “a user with no matching policy sees the whole table”. If you create a policy for one team and expect it to hide the table from everyone else, you need access_control_improvements.users_without_row_policies_can_read_rows = 0 (or the throw_on_unmatched_row_policies variant).

ClickhouseInternalsSecurityRow policiesQuery planner