Skip to content

Add feature to impersonate/proxy user sessions#70775

Merged
vitlibar merged 12 commits intoClickHouse:masterfrom
shiyer7474:impersonate_user
Nov 3, 2025
Merged

Add feature to impersonate/proxy user sessions#70775
vitlibar merged 12 commits intoClickHouse:masterfrom
shiyer7474:impersonate_user

Conversation

@shiyer7474
Copy link
Contributor

@shiyer7474 shiyer7474 commented Oct 17, 2024

Changelog category (leave one):

  • New Feature

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

Added new SQL statement EXECUTE AS to support user impersonation. Resolves #39048

Documentation entry for user-facing changes

  • Documentation is written (mandatory for new features)

WIP

Details

Add the ability to impersonate or proxy an end-user session over an application admin user connection.

Resolves #39048

@shiyer7474 shiyer7474 marked this pull request as draft October 17, 2024 06:24
@shiyer7474
Copy link
Contributor Author

  • PR does not handle the max_sessions_for_user setting. Need to figure out the background on why SessionTracker::Session instance is a a member of the Session class and can it be moved to the Session Context class.
  • Alternatives to adding a new SQL (EXECUTE AS) are -
  1. Implement the feature by enhancing the client/server protocol to add a proxied_user field. Corresponding changes to drivers maybe required.
    OR
  2. Just introduce a new special notation for the username to indicate that impersonation/proxy'ing is requested : appuser[serviceuser]. Implementation could be simple compared to above 2 approaches - Validate appuser & serviceuser, perform authentication as serviceuser, check serviceuser has impersonation privileges on appuser, setup new session context to appuser.

@UnamedRus
Copy link
Contributor

UnamedRus commented Oct 23, 2024

Just introduce a new special notation for the username to indicate that impersonation/proxy'ing is requested : appuser[serviceuser].

Somewhat theoretical, but it could affect/change result of queries.
Imagine, if there is ROW POLICY set as WHERE user = currentUser(); or WHERE groups IN dictGet('groups_for_user', 'groups', currentUser());

currentUser(), user() functions will return 'joe'

Ok, i see that it's covered already.

@filimonov
Copy link
Contributor

filimonov commented Oct 24, 2024

One issue with executing multiple statements in sequence (e.g., EXECUTE AS ...; SELECT ...) is the requirement to preserve the session between them. This can present challenges in various scenarios where session management is not practical or adds unnecessary complexity.

Consider the following cases:

  1. Stateless HTTP Clients: Many HTTP clients do not use sessions currently. Introducing session-based logic (session IDs, timeouts, etc.) could become a barrier for such clients, which may lack the infrastructure to manage sessions efficiently. This could be a significant obstacle for adoption.

  2. Load Balancers: In configurations with load balancers, requests might be routed to different servers within a cluster. Since session state lives on the server level (not the cluster level), this could disrupt the user context continuity across requests.

  3. Connection Pools: When client-side connection pools are used, subsequent requests may utilize different physical connections. Expecting session-based user context preservation across those connections would be cumbersome and error-prone.

In light of these issues, we need a way to switch users "in one shot," either during connection establishment or within a single SQL statement, without depending on session persistence. Protocol extensions might increase complexity for driver authors, which could become a barrier to adoption.

I would suggest considering three potential solutions:

  1. Special Username Format: A simple solution could involve using a special format for the username, such as serviceuser(as:appuser). This would allow the application user to be specified within the same string, making it trivial for client drivers to pass. The server would then handle the user switch seamlessly, without the need for session management.

  2. User Context as a Setting: Another approach could be to pass the target user as a setting. This can be done by passing settings in the query headers via the native protocol (example) or as query string parameters in the HTTP protocol. This method requires minimal to no changes for driver maintainers—they would simply need to pass the setting in the connection string. However, this must be done securely to ensure that end users cannot easily manipulate these settings.

  3. One-Shot SQL Statement: A third option would be to extend SQL with a one-shot EXECUTE AS user <query> statement. For example, EXECUTE AS misha SELECT ... would allow user context switching within a single query, eliminating the need for session preservation and simplifying the logic for client-side implementations.

@vitlibar vitlibar self-assigned this Oct 24, 2024
@shiyer7474
Copy link
Contributor Author

Thanks @filimonov for the inputs regarding "one shot" switch user. My preference is also option 1 - special format user name like 'serviceuser(as:appuser)' or 'serviceuser[appuser]'. The current PR will need to remove all the code added to support the new SQL "EXECUTE AS" and just add authentication time code like this : Validate appuser & serviceuser, perform authentication as serviceuser, check serviceuser has impersonation privileges on appuser, setup new session context asappuser.

@shiyer7474
Copy link
Contributor Author

@vitlibar Thanks for picking up this PR for review! Please review (and associated issue #39048) and let us know your opinion.

@shiyer7474
Copy link
Contributor Author

@vitlibar Let us know your feedback after initial review!

@shiyer7474
Copy link
Contributor Author

@vitlibar Let us know if you got a chance to review the approach and it aligns well, I can then mark the PR ready for review. Thanks!

class ASTRolesOrUsersSet;

/** EXECUTE AS <user>
*/
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It would be nice to have a command to execute only one statement as a specified user, then get back to normal mode. Perhaps something like

EXECUTE AS <user> SELECT ...

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Random passer-by here. This EXECUTE AS <user> SELECT ... syntax is exactly the thing I'm looking for, to implement proxying of multiple users with a single connection, or from the HTTP interface. I'm overall very excited for this PR!

class ASTRolesOrUsersSet;

/** EXECUTE AS <user>
*/
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It may be also useful to enable switching this feature off:

EXECUTE AS <user> ON
...
EXECUTE AS <user> OFF

Simple Execute AS <user> could be made an alias of EXECUTE AS <user> ON.

auto new_user_uuid = *(users.ids.begin());
setUser(new_user_uuid);
setCurrentUserName(getUser()->getName());
setInitialUserName(getUser()->getName());
Copy link
Member

@vitlibar vitlibar Jan 9, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Why do we need to set the initial user name here? A comment is needed.

need_recalculate_access = true;
}

void Context::setAuthUserName(const String & auth_user_name)
Copy link
Member

@vitlibar vitlibar Jan 9, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Do we call this function somewhere? Let's remove it if not.

M(SHOW_QUOTAS, "SHOW CREATE QUOTA", GLOBAL, SHOW_ACCESS) \
M(SHOW_SETTINGS_PROFILES, "SHOW PROFILES, SHOW CREATE SETTINGS PROFILE, SHOW CREATE PROFILE", GLOBAL, SHOW_ACCESS) \
M(SHOW_ACCESS, "", GROUP, ACCESS_MANAGEMENT) \
M(IMPERSONATE, "", USER_NAME, ACCESS_MANAGEMENT) \
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Alias "EXECUTE AS" seems logical here:

M(IMPERSONATE, "EXECUTE AS", USER_NAME, ACCESS_MANAGEMENT) \

Or perhaps vice versa - perhaps it's better to name the privilege EXECUTE AS and make IMPERSONATE an alias, WDYT?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Separating the privilege from the SQL allows us to use the privilege in some other feature in the future. e.g external authentication of the end-user and then IMPERSONATE using some connect time token etc. I have added "EXECUTE AS" an an alias. Thanks!

setCurrentRolesImpl(user->granted_roles.findGranted(user->default_roles), /* throw_if_not_granted= */ false, /* skip_if_not_granted= */ false, user);
}

void Context::switchImpersonateUser(const RolesOrUsersSet & users )
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It seems this function is used only once and it's very simple. Let's move it to InterpreterExecuteAs.cpp perhaps?

struct RolesOrUsersSet;
struct User;

class InterpreterExecuteAsQuery : public IInterpreter, WithMutableContext
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

InterpreterExecuteAs looks a bit better (because it's rather execute as <user>, not execute as <query>, and InterpreterExecuteAsUserQuery is just a bit too long).

M(Bool, disable_insertion_and_mutation, false, "Disable all insert/alter/delete queries. This setting will be enabled if someone needs read-only nodes to prevent insertion and mutation affect reading performance.", 0) \
M(UInt64, keeper_multiread_batch_size, 10'000, "Maximum size of batch for MultiRead request to [Zoo]Keeper that support batching. If set to 0, batching is disabled. Available only in ClickHouse Cloud.", 0) \
M(Bool, use_legacy_mongodb_integration, true, "Use the legacy MongoDB integration implementation. Note: it's highly recommended to set this option to false, since legacy implementation will be removed in the future. Please submit any issues you encounter with the new implementation.", 0) \
M(Bool, allow_impersonate_user, true, "Enable/disable the IMPERSONATE feature (EXECUTE AS <user>).", 0) \
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We usually put our experimental feature flags in Core/Settings.cpp, it seems it's better to put this flag there too.

<path>./</path>
</local_directory>
</user_directories>
</clickhouse>
Copy link
Member

@vitlibar vitlibar Jan 9, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This config file won't be needed if you move allow_impersonate_user to Core/Settings.cpp. And the test can be made much shorter and simpler in this case too.

M(Bool, disable_insertion_and_mutation, false, "Disable all insert/alter/delete queries. This setting will be enabled if someone needs read-only nodes to prevent insertion and mutation affect reading performance.", 0) \
M(UInt64, keeper_multiread_batch_size, 10'000, "Maximum size of batch for MultiRead request to [Zoo]Keeper that support batching. If set to 0, batching is disabled. Available only in ClickHouse Cloud.", 0) \
M(Bool, use_legacy_mongodb_integration, true, "Use the legacy MongoDB integration implementation. Note: it's highly recommended to set this option to false, since legacy implementation will be removed in the future. Please submit any issues you encounter with the new implementation.", 0) \
M(Bool, allow_impersonate_user, true, "Enable/disable the IMPERSONATE feature (EXECUTE AS <user>).", 0) \
Copy link
Member

@vitlibar vitlibar Jan 9, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Let's rename it to allow_execute_as (because execute_as is just easier to type :) )

@vitlibar
Copy link
Member

vitlibar commented Jan 9, 2025

@shiyer7474 Sorry for the waiting.
This is a cool PR, I like it!

@vitlibar vitlibar added the can be tested Allows running workflows for external contributors label Jan 9, 2025
@shiyer7474
Copy link
Contributor Author

@vitlibar Thanks for the detailed review! Please give me 2 days to incorporate and test the changes.

@robot-ch-test-poll4 robot-ch-test-poll4 added the pr-feature Pull request with new product feature label Jan 11, 2025
@robot-ch-test-poll4
Copy link
Contributor

robot-ch-test-poll4 commented Jan 11, 2025

This is an automated comment for commit 947d077 with description of existing statuses. It's updated for the latest CI running

❌ Click here to open a full report in a separate page

Check nameDescriptionStatus
Docs checkBuilds and tests the documentation❌ failure
Successful checks
Check nameDescriptionStatus
BuildsThere's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS✅ success
Fast testNormally this is the first check that is ran for a PR. It builds ClickHouse and runs most of stateless functional tests, omitting some. If it fails, further checks are not started until it is fixed. Look at the report to see which tests fail, then reproduce the failure locally as described here✅ success
Flaky testsChecks if new added or modified tests are flaky by running them repeatedly, in parallel, with more randomization. Functional tests are run 100 times with address sanitizer, and additional randomization of thread scheduling. Integration tests are run up to 10 times. If at least once a new test has failed, or was too long, this check will be red. We don't allow flaky tests, read the doc✅ success
Install packagesChecks that the built packages are installable in a clear environment✅ success
Integration testsThe integration tests report. In parenthesis the package type is given, and in square brackets are the optional part/total tests✅ success
Stateless testsRuns stateless functional tests for ClickHouse binaries built in various configurations -- release, debug, with sanitizers, etc✅ success
Style checkRuns a set of checks to keep the code style clean. If some of tests failed, see the related log from the report✅ success
Unit testsRuns the unit tests for different release types✅ success

@shiyer7474
Copy link
Contributor Author

I will do the documentation changes once we finalize on the syntax and behaviour specifics!

@shiyer7474
Copy link
Contributor Author

Checking the test failures.

@shiyer7474
Copy link
Contributor Author

I will add the docs, code review can continue.

@shiyer7474 shiyer7474 marked this pull request as ready for review January 28, 2025 05:43
@shiyer7474 shiyer7474 changed the title [WIP] Add feature to impersonate/proxy user sessions Add feature to impersonate/proxy user sessions Jan 28, 2025
@Enmk
Copy link
Contributor

Enmk commented Feb 10, 2025

Hi @vitlibar could you please take a look?


query->targetuser = targetuser;

/// support 1) EXECUTE AS <user1> 2) EXECUTE AS <user1> SELECT ...
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It makes sense to support any query after EXECUTE AS <user>, not just SELECT.

For example,

EXECUTE AS <user1> CREATE TABLE ...

Copy link
Member

@vitlibar vitlibar Nov 3, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I added support for that.

"--listen_host=127.1"
# we will discover the real port later.
"--tcp_port=0"
"--shutdown_wait_unfinished=0"
Copy link
Member

@vitlibar vitlibar Feb 16, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

It's unnecessary to start another server in this test.
access_management is already set to 1 in our CI for all our tests.
And without starting another server this test should be much simpler.

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I rewrote the test and simplified it.


BlockIO return_value = {};

auto restore_previous_user = [&] () { getContext()->getSessionContext()->switchImpersonateUser(RolesOrUsersSet(current_user_uuid.value())); };
Copy link
Member

@vitlibar vitlibar Feb 16, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Here restore_previous_user() won't work good enough - because switchImpersonateUser() also resets settings and roles. What is better is to clone the query context and you don't need to worry about restoring it afterwards:

if (query.subquery)
{
    auto impersonation_context = Context::createCopy(*getContext());
    impersonation_context->switchImpersonateU
    impersonation_context->makeQueryContext();
    auto subquery_io = executeQuery(queryToString(query.subquery), impersonation_context, QueryFlags{ .internal = true }).second;
    return subquery_io;
}
else
{
    getContext()->getSessionContext()->switchImpersonateUser(...);
    return {};
}

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I rewrote this code in a more proper way.

@clickhouse-gh
Copy link
Contributor

clickhouse-gh bot commented Mar 25, 2025

Dear @vitlibar, this PR hasn't been updated for a while. You will be unassigned. Will you continue working on it? If so, please feel free to reassign yourself.

@vitlibar vitlibar enabled auto-merge November 3, 2025 10:16
@vitlibar
Copy link
Member

vitlibar commented Nov 3, 2025

@vitlibar vitlibar added this pull request to the merge queue Nov 3, 2025
Merged via the queue into ClickHouse:master with commit ceecd4d Nov 3, 2025
240 of 246 checks passed
@robot-ch-test-poll3 robot-ch-test-poll3 added the pr-synced-to-cloud The PR is synced to the cloud repo label Nov 3, 2025
mkmkme pushed a commit to Altinity/ClickHouse that referenced this pull request Nov 5, 2025
Add feature to impersonate/proxy user sessions
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Nov 6, 2025
25.3.8 Backport of ClickHouse#70775 - Add feature to impersonate/proxy user sessions
@fm4v
Copy link
Member

fm4v commented Nov 10, 2025

@vitlibar @shiyer7474
Negative test when allow_impersonate_user=0 is missing which is a huge security gap. These cases should be added:

  • Can't run EXECUTE AS target_user or EXECUTE AS target_user subquery; even as an admin user
  • Can't run EXECUTE AS with IMPERSONATE grant or as admin
  • EXECUTE AS in subqueries, vews, mat views

@alexbakharew
Copy link
Contributor

Hi @vitlibar @shiyer7474

it seems that query GRANT IMPERSONATE ON user1 TO user2 produces no exception in case when IMPERSONATE feature is disabled.

Is it expected?

I assume it should give the same exception as it does for EXECUTE AS queries:

EXECUTE AS default
SELECT *
FROM system.tables

Query id: 21f2656b-36c8-479e-9201-341665a9c31d


Elapsed: 0.003 sec. 

Received exception from server (version 25.11.1):
Code: 344. DB::Exception: Received from localhost:9000. DB::Exception: IMPERSONATE feature is disabled, set allow_impersonate_user to 1 to enable. (SUPPORT_IS_DISABLED)

@fm4v fyi

@alexbakharew alexbakharew mentioned this pull request Nov 19, 2025
1 task
@vitlibar
Copy link
Member

vitlibar commented Nov 19, 2025

it seems that query GRANT IMPERSONATE ON user1 TO user2 produces no exception in case when IMPERSONATE feature is disabled.

I think that's OK because user2 won't be able to use such a grant anyway.

zvonand pushed a commit to Altinity/ClickHouse that referenced this pull request Dec 8, 2025
Add feature to impersonate/proxy user sessions
zvonand pushed a commit to Altinity/ClickHouse that referenced this pull request Dec 15, 2025
Add feature to impersonate/proxy user sessions
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Dec 16, 2025
25.8.12 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions #
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Dec 23, 2025
25.8.12 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions #
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Dec 24, 2025
25.8.13 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Jan 15, 2026
25.8.13 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Jan 27, 2026
25.8.13 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions
zvonand added a commit to Altinity/ClickHouse that referenced this pull request Jan 28, 2026
25.8.15 Backport of ClickHouse#70775: Add feature to impersonate/proxy user sessions
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

can be tested Allows running workflows for external contributors pr-feature Pull request with new product feature pr-synced-to-cloud The PR is synced to the cloud repo

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Make queries using a specific role or user's applicable row policies and grants