No alert in spring log for "Not enough privileges." error

[jisa6001]

Here are 2 scenarios:

1. Correct user/pasword but wrong database in URL

Steps to reproduce:

1. Configurations in application.yml. Correct user/pasword but wrong database in URL
2. Hardcode the correct database name in mapper.xml
3. We won't get any warnning info or alert when starting the spring application
4. Also no alert when we call the corresponding api interface thru postman or others

2. Correct user/pasword and correct database in URL, but wrong database name in mapper.xml

Steps to reproduce:

1. Configurations in application.yml. Correct user/pasword and correct database in URL
2. Hardcode the wrong database name in mapper.xml
3. Call the interface by postman or else, we got 500 error.
4. No alert in application log.

[zhicwu]

I assume you're using JDBC driver 0.3.0 but what's the version of ClickHouse?

[jisa6001]

I assume you're using JDBC driver 0.3.0 but what's the version of ClickHouse?

clickhouse-20.3.10.75

[zhicwu]

Tried scenario 1 using ClickHouse 20.3, and I got below exception when creating a connection:

Message:
ClickHouse exception, code: 81, host: localhost, port: 50093; Code: 81, e.displayText() = DB::Exception: Database nonExistDb doesn't exist (version 20.3.21.2 (official build))
Stack trace:
ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 81, host: localhost, port: 50093; Code: 81, e.displayText() = DB::Exception: Database nonExistDb doesn't exist (version 20.3.21.2 (official build))

I'm not sure what exactly you need and why you didn't get any error log - it may relate to your log configuration but from JDBC driver perspective I didn't find anything abnormal.

[jisa6001]

Tried scenario 1 using ClickHouse 20.3, and I got below exception when creating a connection:

Message:
ClickHouse exception, code: 81, host: localhost, port: 50093; Code: 81, e.displayText() = DB::Exception: Database nonExistDb doesn't exist (version 20.3.21.2 (official build))
Stack trace:
ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 81, host: localhost, port: 50093; Code: 81, e.displayText() = DB::Exception: Database nonExistDb doesn't exist (version 20.3.21.2 (official build))

I'm not sure what exactly you need and why you didn't get any error log - it may relate to your log configuration but from JDBC driver perspective I didn't find anything abnormal.

Please let me clarify a bit more, it's not a non-existing db, but a existing database, which doesn't have permission access to with the current username/password.

btw, by using the non-existing db config. I did get the same error log as u attached , so it may not be a log configuration issue.

[zhicwu]

Well... I took it back :p I got below error when using an account has no privilege to access database secrets:

Message:
ClickHouse exception, code: 497, host: localhost, port: 50148; Code: 497, e.displayText() = DB::Exception: guest: Not enough privileges. To execute this query it's necessary to have grant SELECT(user, password) ON secrets.credentials (version 21.2.4.6 (official build))
Stack trace:
ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 497, host: localhost, port: 50148; Code: 497, e.displayText() = DB::Exception: guest: Not enough privileges. To execute this query it's necessary to have grant SELECT(user, password) ON secrets.credentials (version 21.2.4.6 (official build))

My test case:

-- run below as super user
create database if not exists secrets;
create table if not exists secrets.credentials(user String, password String) engine=Memory;
create user guest identified with sha256_password by '123';
grant select on system.* to guest;

-- and then run below as guest(set database to secrets in connection url)
select * from credentials

[jisa6001]

Here is my case: ddl

create database if not exists non_privilege_db;
create table if not exists non_privilege_db.credentials(user String, password String) engine=Memory;

user.xml

<test>
    <password>123</password>
    <networks incl="networks" replace="replace">
        <ip>::/0</ip>
    </networks>
    <!-- Settings profile for user. -->
    <profile>default</profile>
    <!-- Quota for user. -->
    <quota>default</quota>
    <allow_databases>
        <database>system</database>
    </allow_databases>
</test>

application.yml

spring:
  datasource:
    url: jdbc:clickhouse://localhost:8123/non_privilege_db
    username: test
    password: 123

And then when I started the SpringBootApplication, I didn't get any error info.

but if thru CLI, I could get the same error as you.

[zhicwu]

Interesting... I still cannot reproduce the issue you met.

Below test case works for me(dba is the super user I mentioned above).

@Test
public void testAcl() throws Exception {
    ClickHouseProperties props = new ClickHouseProperties();
    props.setUser("dba");
    props.setPassword("dba");
    ClickHouseDataSource ds = ClickHouseContainerForTest.newDataSource(props);
    try (ClickHouseConnection c = ds.getConnection(); Statement s = c.createStatement()) {
        s.execute("create database if not exists secrets");
        s.execute("create table if not exists secrets.credentials(user String, password String) engine=Memory");
        s.execute("create user guest identified with sha256_password by '123'");
        s.execute("grant select on system.* to guest");
    }

    ClickHouseProperties newProps = new ClickHouseProperties();
    newProps.setUser("test");
    newProps.setPassword("123");
    ClickHouseDataSource newDs = ClickHouseContainerForTest.newDataSource("secrets", newProps);
    try (ClickHouseConnection c = newDs.getConnection(); Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("select * from credentials")) {
        fail("Should fail as test user does not have access to secrets database");
    } catch (ClickHouseException e) {
        assertTrue(e.getErrorCode() == 497);
    }

    newProps = new ClickHouseProperties();
    newProps.setUser("guest");
    newProps.setPassword("123");
    newDs = ClickHouseContainerForTest.newDataSource("secrets", newProps);
    try (ClickHouseConnection c = newDs.getConnection(); Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("select * from credentials")) {
        fail("Should fail as guest user does not have access to secrets database");
    } catch (ClickHouseException e) {
        assertTrue(e.getErrorCode() == 497);
    }
}

My suggestion is to enable Apache http client debug log to see what's going on there. Pay attention to the authorization header to double check if the user is test.

log4j.logger.org.apache.http=DEBUG

[jisa6001]

I debuged the souce code and compared two case: non-existing database & no privilege database; And I assumed the connection url will be verified by "ClickHouseConnectionImpl"- initConnection, and the below sql will be executed. ResultSet rs = s.executeQuery("select timezone(), version()") ClickHouseStatementImpl - executeQuery InputStream is = getInputStream(sql, additionalDBParams, externalData, additionalRequestParams); ClickHouseStatementImpl - getInputStream

            HttpResponse response = client.execute(post, httpContext);
            entity = response.getEntity();
            checkForErrorAndThrow(entity, response);

checkForErrorAndThrow

    private void checkForErrorAndThrow(HttpEntity entity, HttpResponse response) throws IOException, ClickHouseException {
        if (response.getStatusLine().getStatusCode() != HttpURLConnection.HTTP_OK) {
            InputStream messageStream = entity.getContent();
            byte[] bytes = Utils.toByteArray(messageStream);
            if (properties.isCompress()) {
                try {
                    messageStream = new ClickHouseLZ4Stream(new ByteArrayInputStream(bytes));
                    bytes = Utils.toByteArray(messageStream);
                } catch (IOException e) {
                    log.warn("error while read compressed stream {}", e.getMessage());
                }
            }
            EntityUtils.consumeQuietly(entity);
            String chMessage = new String(bytes, StandardCharsets.UTF_8);
            throw ClickHouseExceptionSpecifier.specify(chMessage, properties.getHost(), properties.getPort());
        }
    }

non-existing database： response.getStatusLine().getStatusCode() = 404, and throw a ClickHouseException, and the druid connection pool will catch this exception. (btw, I used druid connection pool)

no privilege database： response.getStatusLine().getStatusCode() = 200, and it won't throw any Exception

I'm not sure if this two errors should be captured in the same place, but it could explain the case i met.