Manage database service accounts

Database service accounts can be as simple as a user with a separate password or certificate for authentication. More advanced users may wish to set up accounts where permission scope can be changed dynamically with SET ROLE to enable quick switching between profiles without logging out or reloading content.

Overview

SET ROLE can be used to dynamically scope permissions for a service account during a session. This works by limiting a user's effective permissions to only those granted by the activated role(s). This approach has several advantages:

• Service accounts can be assigned multiple roles, but only activate the one needed for a specific query.
• Attackers can only use the active role's permissions if the service account is compromised.
• A single account can perform diverse tasks by switching roles rather than requiring separate credentials for each task.
• Permissions can be updated for an entire class of service accounts by modifying one role instead of updating individual users.
• Logs can track which specific role was active during a query, providing clearer context for security audits.

In practice, you:

1. Design roles that encode the alowed boundaries (read_only, maintenance, etc.)
2. Grant them to the service account
3. At connection time, choose the active role(s) via SET ROLE (or role parameter), thereby constraining what that session can do

Set up a service role

Grant roles to the service account

First, create roles with the privileges/ settings you want, then grant them to the service account.

CREATE ROLE read_only_role;
GRANT SELECT ON db1.* TO read_only_role;

CREATE ROLE maint_role;
GRANT SELECT, INSERT, ALTER on db1.* TO maint_role;

GRANT read_only_role, maint_role TO service_user;

Use SET ROLE to define session boundaries

At the start of a session, the service account chooses which roles are active:

-- Only read-only behavior for this session
SET ROLE read_only_role;

or:

-- Use all granted roles (full power)
SET ROLE ALL;

SET ROLE activates roles for the current user; the effective privileges are the union of all active roles plus ay directly granted to the user.

You can also deactivate all roles:

SET ROLE NONE;

or activate multiple roles:

SET ROLE read_only_role, maint_role;

The currently active roles can be inspected via system.current_roles.

Set default roles for the service account

To ensure the service account always starts in a restricted mode, configure default roles:

SET DEFAULT ROLE read_only_role TO service_user;

or

SET DEFAULT ROLE ALL EXCEPT maint_role TO service_user;

Using SET ROLE over HTTP / programmatically

If the service account connects via HTTP, you cannot send SET ROLE; SELECT ... as a multi-statement. Instead, pass the role as a query parameter:

curl "https://host:8123?user=service_user&password=...&role=read_only_role" \
 --data-binary "SELECT * FROM db1.table1"

?role=... is equivalent to executing SET ROLE read_only_role before the statement. Multiple role parameters behave like SET ROLE role 1, role 2.

Some drivers (e.g. ClickHouse Connect for Python) also expose a role setting that is sent with each request, which the server uses as the session role.