Search code examples
prestotrino

AccessDeniedException when setting table rules in file based system access control in Presto

I set up a small Presto cluster on Kubernetes lately - PrestoSQL version 345. Everything works quite fine, but I am struggling a bit with setting up File Based System Access Control.

I tried to set up a small test case where I have one admin user who can select from all tables across all catalogs and schemas and one user bob who can only access one table from a specific schema of a MySQL catalog.

If I set only catalog and schema rules, everything works as expected. Both users can only see the catalogs that I allow. As soon as I set any table rule non of my users can select anything and they can't even see the catalogs anymore, also the admin user which should have sufficient permissions in any case according to the permission table in the docs (https://prestosql.io/docs/current/security/file-system-access-control.html). For both users I am getting AccessDeniedExceptions (see also coordinator logs at the end). Can somebody point me to what I am doing wrong?

access-rules.json

{
  "catalogs": [
    {
      "user": "bob",
      "catalog": "mysql",
      "allow": "read-only"
    },
    {
      "user": "admin",
      "catalog": ".*",
      "allow": "all"
    }
  ],
  "schemas": [
    {
      "user": "bob",
      "catalog": "mysql",
      "schema": "test",
      "owner": false
    },
    {
      "user": "admin",
      "catalog": ".*",
      "schema": ".*",
      "owner": true
    }
  ],
  "tables": [      
    {
      "user": "bob",
      "group": ".*",
      "catalog": "mysql",
      "schema": "test",
      "table": "test_table",
      "privileges": ["SELECT"]
    },
    {
      "user": "admin",
      "group": ".*",
      "catalog": ".*",
      "schema": ".*",
      "table": ".*",
      "privileges": ["SELECT"]
    }
  ]
}

Coordinator logs

presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:19.435Z    DEBUG   dispatcher-query-0  io.prestosql.security.AccessControl Invocation of checkCanSetUser(principal=Optional[admin], userName='admin') succeeded in 56.48ms
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:19.525Z    DEBUG   dispatcher-query-0  io.prestosql.security.AccessControl Invocation of checkCanExecuteQuery(identity=Identity{user='admin', groups=[], principal=admin, roles={}, extraCredentials=[]}) succeeded in 1.10ms
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:19.621Z    DEBUG   dispatcher-query-1  io.prestosql.execution.QueryStateMachine    Query 20201101_204019_00000_9jt42 is QUEUED
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:19.634Z    DEBUG   dispatcher-query-3  io.prestosql.execution.QueryStateMachine    Query 20201101_204019_00000_9jt42 is WAITING_FOR_RESOURCES
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:19.738Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getSystemTable(session=FullConnectorSession{queryId=20201101_204019_00000_9jt42, user=admin, source=presto-jdbc, timeZoneKey=Europe/Berlin, locale=en_DE, start=2020-11-01T20:40:19.521525Z, properties={}}, tableName=test.test_table) succeeded in 33.84us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.245Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getTableHandle(identity=JdbcIdentity{user=admin, principalName=Optional[admin], extraCredentials=[]}, schemaTableName=test.test_table) succeeded in 504.44ms
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.247Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getSystemTable(session=FullConnectorSession{queryId=20201101_204019_00000_9jt42, user=admin, source=presto-jdbc, timeZoneKey=Europe/Berlin, locale=en_DE, start=2020-11-01T20:40:19.521525Z, properties={}}, tableName=test.test_table) succeeded in 15.37us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.247Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getSystemTable(session=FullConnectorSession{queryId=20201101_204019_00000_9jt42, user=admin, source=presto-jdbc, timeZoneKey=Europe/Berlin, locale=en_DE, start=2020-11-01T20:40:19.521525Z, properties={}}, tableName=test.test_table) succeeded in 9.30us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.248Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getSystemTable(session=FullConnectorSession{queryId=20201101_204019_00000_9jt42, user=admin, source=presto-jdbc, timeZoneKey=Europe/Berlin, locale=en_DE, start=2020-11-01T20:40:19.521525Z, properties={}}, tableName=test.test_table) succeeded in 10.77us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.322Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.BaseJdbcClient Mapping data type of 'test.test_table' column 'tinyint_1': JdbcTypeHandle{jdbcType=-6, jdbcTypeName=TINYINT, columnSize=3, decimalDigits=Optional[0]} mapped to Optional[ColumnMapping{type=tinyint}]
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.323Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.BaseJdbcClient Mapping data type of 'test.test_table' column 'tinyint_2': JdbcTypeHandle{jdbcType=-6, jdbcTypeName=TINYINT, columnSize=3, decimalDigits=Optional[0]} mapped to Optional[ColumnMapping{type=tinyint}]
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.324Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.BaseJdbcClient Mapping data type of 'test.test_table' column 'int_1': JdbcTypeHandle{jdbcType=4, jdbcTypeName=INT, columnSize=10, decimalDigits=Optional[0]} mapped to Optional[ColumnMapping{type=integer}]
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.324Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.BaseJdbcClient Mapping data type of 'test.test_table' column 'int_10': JdbcTypeHandle{jdbcType=4, jdbcTypeName=INT, columnSize=10, decimalDigits=Optional[0]} mapped to Optional[ColumnMapping{type=integer}]
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.326Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getColumns(session=FullConnectorSession{queryId=20201101_204019_00000_9jt42, user=admin, source=presto-jdbc, timeZoneKey=Europe/Berlin, locale=en_DE, start=2020-11-01T20:40:19.521525Z, properties={}}, tableHandle=test.test_table test.test_table) succeeded in 75.52ms
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.329Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.jdbc.mysql.jdbcclient   Invocation of getTableProperties(identity=JdbcIdentity{user=admin, principalName=Optional[admin], extraCredentials=[]}, tableHandle=test.test_table test.test_table) succeeded in 27.95us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.333Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of getColumnMasks(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table, columnName='tinyint_1', type=tinyint) succeeded in 2.93ms
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.334Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of getColumnMasks(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table, columnName='tinyint_2', type=tinyint) succeeded in 88.59us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.334Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of getColumnMasks(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table, columnName='int_1', type=integer) succeeded in 59.19us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.335Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of getColumnMasks(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table, columnName='int_10', type=integer) succeeded in 58.13us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.336Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of getRowFilters(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table) succeeded in 819.99us
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.417Z    INFO    Query-20201101_204019_00000_9jt42-208   io.prestosql.plugin.base.security.FileBasedSystemAccessControl  Refreshing system access control from /var/presto/etc/accesss-rules.json
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.433Z    DEBUG   Query-20201101_204019_00000_9jt42-208   io.prestosql.security.AccessControl Invocation of checkCanSelectFromColumns(context=io.prestosql.security.SecurityContext@66d304f, tableName=mysql.test.test_table, columnNames=[tinyint_1, tinyint_2, int_1, int_10]) took 15.47ms and failed with io.prestosql.spi.security.AccessDeniedException: Access Denied: Cannot select from table mysql.test.test_table
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.437Z    DEBUG   dispatcher-query-2  io.prestosql.execution.QueryStateMachine    Query 20201101_204019_00000_9jt42 is FAILED
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.437Z    DEBUG   dispatcher-query-1  io.prestosql.execution.QueryStateMachine    Query 20201101_204019_00000_9jt42 failed
presto-coordinator-5c69cd7479-fmbc7 coordinator io.prestosql.spi.security.AccessDeniedException: Access Denied: Cannot select from table mysql.test.test_table
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.spi.security.AccessDeniedException.denySelectTable(AccessDeniedException.java:278)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.spi.security.AccessDeniedException.denySelectTable(AccessDeniedException.java:273)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.plugin.base.security.FileBasedSystemAccessControl.checkCanSelectFromColumns(FileBasedSystemAccessControl.java:632)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.plugin.base.security.ForwardingSystemAccessControl.checkCanSelectFromColumns(ForwardingSystemAccessControl.java:244)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.security.AccessControlManager.lambda$checkCanSelectFromColumns$62(AccessControlManager.java:727)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.security.AccessControlManager.systemAuthorizationCheck(AccessControlManager.java:950)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.security.AccessControlManager.checkCanSelectFromColumns(AccessControlManager.java:727)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.lang.reflect.Method.invoke(Method.java:566)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.plugin.base.util.LoggingInvocationHandler.handleInvocation(LoggingInvocationHandler.java:60)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at com.google.common.reflect.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:86)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at com.sun.proxy.$Proxy112.checkCanSelectFromColumns(Unknown Source)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.security.ForwardingAccessControl.checkCanSelectFromColumns(ForwardingAccessControl.java:297)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.sql.analyzer.Analyzer.lambda$analyze$0(Analyzer.java:88)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.sql.analyzer.Analyzer.lambda$analyze$1(Analyzer.java:87)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:86)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:75)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:257)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:183)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:759)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:123)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at io.prestosql.$gen.Presto_345____20201101_191624_2.call(Unknown Source)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
presto-coordinator-5c69cd7479-fmbc7 coordinator     at java.base/java.lang.Thread.run(Thread.java:834)
presto-coordinator-5c69cd7479-fmbc7 coordinator 
presto-coordinator-5c69cd7479-fmbc7 coordinator 
presto-coordinator-5c69cd7479-fmbc7 coordinator 2020-11-01T20:40:20.520Z    INFO    dispatcher-query-1  io.prestosql.event.QueryMonitor TIMELINE: Query 20201101_204019_00000_9jt42 :: Transaction:[039c11ba-b2a6-4f6c-ad3f-8f864392d7f9] :: elapsed 815ms :: planning 815ms :: waiting 0ms :: scheduling 0ms :: running 0ms :: finishing 0ms :: begin 2020-11-01T20:40:19.618Z :: end 2020-11-01T20:40:20.433Z
Solution

  • I think I found the problem. I explicitly added the default value (according to documentation for Presto 345) for group key in the table rules ("group": ".*"). I didn't specify any groups in my set up. Removing the group key fixed the the access issues.