search

cryptomator / hub

Cryptomator Hub helps you manage vaults in large teams
GNU Affero General Public License v3.0
41 stars 9 forks source link

Revert native sql unlock before we ship the refactored access management #222

Closed SailReal closed 8 months ago

SailReal commented 1 year ago

Please agree to the following

Summary

Revert https://github.com/cryptomator/hub/commit/1baf7340870093676e7476331b6e94e417917518 before we ship the refactored access management to remove the native call which can introduce major problems in the future.

Motivation

Make sure that the introduced native unlock call https://github.com/cryptomator/hub/commit/1baf7340870093676e7476331b6e94e417917518 never makes it into production. I strongly suspect that Hibernate ORM will be at version 6.2.8 or 6.3.0 until we ship the refactored access management.

This issue should just remind us to undo that commit before shipping.

Considered Alternatives

We should not downgrade Quarkus to v3.1.0 because of https://github.com/cryptomator/hub/issues/220

Downgrading only the hibernate dependency wasn't successfully.

Anything else?

Regarding the issue itself, see https://github.com/quarkusio/quarkus/issues/35386

SailReal commented 1 year ago

Quarkus 3.4.1 fixes it: https://github.com/quarkusio/quarkus/blob/6c6c3b972b34268a34d5ca8f55858d494742ac0e/bom/application/pom.xml#L102

SailReal commented 11 months ago

Unfortunately, Updating Quarkus to 3.4.3 does not fix this, although Hibernate is now at 6.2.9 and 6.2.8 and 6.3.0 should fix it.

Still getting

2023-10-19 14:18:51,276 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (executor-thread-1) ERROR: missing FROM-clause entry for table "u1_1" Position: 49
2023-10-19 14:18:51,277 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-1) HTTP Request to /api/vaults/db9142f3-1af6-421e-a3fc-76b9d58aa6ef/access-token failed, error id: 4a5c7668-5365-4282-a85e-153cf2a58f74-4: org.hibernate.exception.SQLGrammarException: JDBC exception executing 
  SQL [select a1_0."user_id",a1_0."vault_id",u1_0."id",u1_1."name",u1_0."email",u1_0."picture_url",u1_0."privatekey",u1_0."publickey",u1_0."setupcode",a1_0."vault_masterkey" from "user_details" u1_0 join "effective_vault_access" e1_0 on u1_0."id"=e1_0."authority_id" join "access_token" a1_0 on u1_0."id"=a1_0."user_id" and a1_0."vault_id"=? and a1_0."user_id"=u1_0."id" where e1_0."vault_id"=? and u1_0."id"=? fetch first ? rows only] [ERROR: missing FROM-clause entry for table "u1_1" Position: 49] [n/a]
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:89)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:257)
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:163)
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:254)
        at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:134)
        at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19)
        at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:66)
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:178)
        at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:361)
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:168)
        at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:93)
        at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
        at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:110)
        at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:303)
        at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:244)
        at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:518)
        at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:367)
        at org.hibernate.query.Query.getResultList(Query.java:119)
        at io.quarkus.hibernate.orm.panache.common.runtime.CommonPanacheQueryImpl.firstResult(CommonPanacheQueryImpl.java:296)
        at io.quarkus.hibernate.orm.panache.runtime.PanacheQueryImpl.firstResult(PanacheQueryImpl.java:159)
        at org.cryptomator.hub.entities.AccessToken.unlock(AccessToken.java:52)
        at org.cryptomator.hub.api.VaultResource.unlock(VaultResource.java:328)
        at org.cryptomator.hub.api.VaultResource_Subclass.unlock$$superforward(Unknown Source)
        at org.cryptomator.hub.api.VaultResource_Subclass$$function$$9.apply(Unknown Source)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:73)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext$NextAroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:97)
        at io.quarkus.security.runtime.interceptor.SecurityHandler.handle(SecurityHandler.java:27)
        at io.quarkus.security.runtime.interceptor.RolesAllowedInterceptor.intercept(RolesAllowedInterceptor.java:29)
        at io.quarkus.security.runtime.interceptor.RolesAllowedInterceptor_Bean.intercept(Unknown Source)
        at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:70)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext$NextAroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:97)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:136)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:107)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.doIntercept(TransactionalInterceptorRequired.java:38)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.intercept(TransactionalInterceptorBase.java:61)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.intercept(TransactionalInterceptorRequired.java:32)
        at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired_Bean.intercept(Unknown Source)
        at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:70)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:62)
        at io.quarkus.resteasy.reactive.server.runtime.StandardSecurityCheckInterceptor.intercept(StandardSecurityCheckInterceptor.java:44)
        at io.quarkus.resteasy.reactive.server.runtime.StandardSecurityCheckInterceptor_RolesAllowedInterceptor_Bean.intercept(Unknown Source)
        at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
        at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:30)
        at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27)
        at org.cryptomator.hub.api.VaultResource_Subclass.unlock(Unknown Source)
        at org.cryptomator.hub.api.VaultResource$quarkusrestinvoker$unlock_c4bf3a23a826086d74e4e7c73c8aa7e2cfd5d47b.invoke(Unknown Source)
        at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
        at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
        at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
        at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:582)
        at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
        at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
        at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "u1_1"
  Position: 49
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
        at org.postgresql.jdbc.PgPreatement.executeQuery(PgPreparedStatement.java:134)
        at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)
        at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:239)
        ... 57 more
SailReal commented 11 months ago

For the record: Quarkus with v3.5.0 and Hibernate in version 6.2.13 does not fix the problem as well.

overheadhunter commented 8 months ago

During my work on #252 we noticed that we added further NamedNativeQueries with #207, that should not be required. While AccessToken may still require a join on User, LegacyAccessToken does not. Hence, we should be able to translate those to NamedQueries:

This is our @NamedNativeQuery in LegacyAccessToken:

SELECT t.device_id, t.vault_id, t.jwe
FROM access_token_legacy t
INNER JOIN device_legacy d ON d.id = t.device_id
INNER JOIN effective_vault_access a ON a.vault_id = t.vault_id AND a.authority_id = d.owner_id
WHERE t.vault_id = :vaultId AND d.id = :deviceId AND d.owner_id = :userId

Hibernate translates this to:

    SELECT
        t.device_id,
        t.vault_id,
        t.jwe 
    FROM
        access_token_legacy t 
    INNER JOIN
        device_legacy d 
            ON d.id = t.device_id 
    INNER JOIN
        effective_vault_access a 
            ON a.vault_id = t.vault_id 
            AND a.authority_id = d.owner_id 
    WHERE
        t.vault_id = ? 
        AND d.id = ? 
        AND d.owner_id = ?

I converted this to the following normal @NamedQuery:

SELECT t
FROM LegacyAccessToken t
INNER JOIN LegacyDevice d ON d.id = t.id.deviceId
INNER JOIN EffectiveVaultAccess p ON t.id.vaultId = p.id.vaultId AND d.ownerId = p.id.authorityId
WHERE t.id.vaultId = :vaultId AND t.id.deviceId = :deviceId AND d.ownerId = :userId

Which is translated to this:

    select
        l1_0."device_id",
        l1_0."vault_id",
        l1_0."jwe" 
    from
        "access_token_legacy" l1_0 
    join
        "device_legacy" l2_0 
            on l2_0."id"=l1_0."device_id" 
    join
        "effective_vault_access" e1_0 
            on l1_0."vault_id"=e1_0."vault_id" 
            and l2_0."owner_id"=e1_0."authority_id" 
    where
        l1_0."vault_id"=? 
        and l1_0."device_id"=? 
        and l2_0."owner_id"=?