· clickhouse til

ClickHouse - DB::Exception:: there is no writeable access storage in user directories (ACCESS_STORAGE_FOR_INSERTION_NOT_FOUND)

I’ve been working with ClickHouse’s access control/account management as part of a video that I created showing how to login to a ClickHouse server with an SSH key, but getting it all setup locally was a bit fiddly. In this blog post, we’ll go through the mistakes I made and how to fix them.

I initially tried starting the ClickHouse server:

./clickhouse server

Connecting to it with a client:

./clickhouse client

And then tried to create a user:

IDENTIFIED WITH sha256_password
BY 'my_password';

This resulted in the following error:

Received exception from server (version 23.11.1):
Code: 514. DB::Exception: Received from localhost:9000. DB::Exception: Could not insert user `mark` because there is no writeable access storage in user directories. (ACCESS_STORAGE_FOR_INSERTION_NOT_FOUND)

It turns out I hadn’t followed these instructions from the documentation:

Setup a directory for configurations storage.

ClickHouse stores access entity configurations in the folder set in the access_control_path server configuration parameter.

Enable SQL-driven access control and account management for at least one user account.

By default, SQL-driven access control and account management is disabled for all users. You need to configure at least one user in the users.xml configuration file and set the values of the access_management, named_collection_control, show_named_collections, and show_named_collections_secrets settings to 1.

I wasn’t sure where I should be setting those properties, but Rich Raposa came to the rescue. It turns out that we need to put those values in config files under the config.d directory:




If we restart the server and then go back to the client, we can try to create the user again:

IDENTIFIED WITH sha256_password
BY 'my_password';
Query id: bdccd4a3-91f9-47c1-846c-29229fe95abe


And we can see the user if we call SHOW USERS:

Query id: 668a4379-fd56-4dec-ab96-2e914977a60a

│ default │
│ mark    │

If we then look under the my_access_folder directory, we’ll see the following files:

ATTACH USER mark IDENTIFIED WITH sha256_hash BY '493AFB0836EEF5296D5E50AAD9CF40DE8F4932FA6B6D2B5209B1082934DF2EFF' SALT '2728C60AD0CC7023F3414ABC870973B8F7AB30ADB3CB6AC8B370A6F460C0CD88';

If you run that yourself, you’ll have a different UUID used for the user, but the structure should be similar.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket