What ClickHouse UDFs should be able to do

ClickHouse user-defined functions are a wonderful feature with one design gap that gets more serious the more you use them: there is no access control on calling a function. Whatever UDF anyone creates is available to every user of the server the instant it exists.

This essay was written when UDFs were new, as a feature request. I re-checked it against ClickHouse 26.4 before republishing; the situation has not changed, which is why it is still worth arguing.

The gap

You can restrict who may create a function (the global CREATE FUNCTION and DROP FUNCTION privileges exist). On 26.4, system.privileges has nothing like EXECUTE for calling one. Compare that with almost every other DBMS, where functions and procedures are objects with their own grants.

For a SQL UDF this barely matters: it is a named expression, a shortcut for text you could have typed. For an executable UDF it matters a great deal. An executable UDF is a program the server runs, with access to whatever that program can reach: a file, an API, a model, another system. If access control is part of your deployment, say several databases for different teams, then “available to everybody” is the wrong default for something that powerful.

What a fix has to satisfy

Three requirements pull against each other, and the interesting part is meeting all three at once:

1. Grants. An administrator must be able to give execute rights to certain users or roles, and withhold them from the rest.
2. No database prefix. Today you call linear_equation(x). Forcing everyone to write default.linear_equation(x) would be a break of backward compatibility and, frankly, is worse to read. A function that is part of the language of a database should read like it: SELECT linear_equation(x) FROM testdb.t.
3. No per-UDF bookkeeping. Nobody wants to run GRANT for every new function and every user. The privilege should be granted once on a scope, such as GRANT EXECUTE ON default.* TO user, and then cover functions created in that scope afterwards, the same way grants on a database cover tables created later.

A plain “UDFs live in a database” model gives you 1 but breaks 2. Global UDFs with grants per function give you 2 but break 3.

The proposal: UDF scope

Give each UDF a scope: the set of databases in which it is callable without a prefix.

-- hypothetical syntax, not implemented
CREATE FUNCTION linear_equation SCOPED TO testdb AS (x) -> 2 * x + 1;

ALTER FUNCTION linear_equation ADD SCOPE 'default';

Rules that follow from the three requirements:

• No SCOPED TO clause: the function is global, exactly as today. Nothing existing changes.
• SCOPED TO db: callable without a prefix only while the current database is in the scope; elsewhere it is an unknown function.
• SCOPED with no database: created, but exposed nowhere yet. This lets you deploy a new function and then switch it on deliberately.
• Grants attach to the scope, so GRANT EXECUTE ON db.* covers current and future functions scoped there.

system.functions would gain the columns to show it: an is_global flag and a scoped_databases array.

The same idea solves a second problem. A scoped function is a handy way to make a thing available in every database at once without copying it, which is what people end up doing with views today.

Why it matters beyond permissions

A UDF is, for many teams, the only extension point ClickHouse offers. If executable UDFs are going to be the way you wire a model, a lookup service or a validation library into SQL, the cost of that power is a trust boundary, and the trust boundary needs a name in the access system. Until then, the practical advice is to treat every executable UDF as global code: review it as such, keep it small, and do not put one on a server that hosts databases you would not let every user touch.

Compare with table functions as a first-class abstraction, which makes the same point about scope for functions that return tables.

The issue itself, with its status and history, is summarised in UDF scope: the request.