7 Nov 2023

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:

CREATE USER mark
IDENTIFIED WITH sha256_password
BY 'my_password';

This resulted in the following error:

Output

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:

config.d/folders.xml

<clickhouse>
        <access_control_path>my_access_folder</access_control_path>
</clickhouse>

config.d/users.xml

<clickhouse>
  <users>
        <default>
            <password/>

            <networks>
                <ip>::/0</ip>
            </networks>

            <profile>default</profile>
            <quota>default</quota>
            <access_management>1</access_management>
			<named_collection_control>1</named_collection_control>
			<show_named_collections>1</show_named_collections>
			<show_named_collections_secrets>1</show_named_collections_secrets>
        </default>
    </users>
</clickhouse>

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

CREATE USER mark
IDENTIFIED WITH sha256_password
BY 'my_password';

Output

Query id: bdccd4a3-91f9-47c1-846c-29229fe95abe

Ok.

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

SHOW USERS;

Output

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

┌─name────┐
│ default │
│ mark    │
└─────────┘

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

my_access_folder/users.list

mark10471a13-2fb4-1bec-7a81-6afc7296df98

my_access_folder/10471a13-2fb4-1bec-7a81-6afc7296df98.sql

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.

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.