2

I just created a new user dataengineer and user group data-engineer

When I try to connect with the user dataengineer using dbeaver I get the following errors.

SQL Error [4]: Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables Query failed (#20210927_124120_00084_kcmzr): Access Denied: Cannot select from table system.jdbc.tables io.trino.spi.security.AccessDeniedException: Access Denied: Cannot select from table system.jdbc.tables Access Denied: Cannot select from table system.jdbc.tables

When I try to connect using the same user from the trino cli I get the following errors

trino:jdbc> select 1; _col0 ------- 1 (1 row) Query 20210927_143506_00011_m9sds, FINISHED, 1 node https://trino.afsouth1ctcld.net/ui/query.html?20210927_143506_00011_m9sds Splits: 1 total, 1 done (100,00%) CPU Time: 0,0s total, 0 rows/s, 0B/s, 0% active Per Node: 0,0 parallelism, 0 rows/s, 0B/s Parallelism: 0,0 Peak Memory: 0B 0,34 [0 rows, 0B] [0 rows/s, 0B/s] trino:jdbc> select * from system.jdbc.tables limit 10; Query 20210927_144444_00018_m9sds failed: Access Denied: Cannot select from table system.jdbc.tables io.trino.spi.security.AccessDeniedException: Access Denied: Cannot select from table system.jdbc.tables

So the user can connect to trino but not query the system tables. If I connect with a different user called data science it works without any issues.

This is what my group.txt and rules.json file looks like

group.txt: |- data-science:datascience data-engineer:dataengineer rules.json: |- { "catalogs": [ { "user": "dpadmin", "allow": "all" }, { "group": "data-engineering|data-platform-admin", "catalog": ".*", "allow": "all" }, { "group": "data-science", "catalog": "(iceberg)", "allow": "all" }, { "group": "data-science", "catalog": ".*", "allow": "read-only" }, { "catalog": "system", "allow": "none" } ], "schemas": [ { "user": "dpadmin", "schema": ".*", "owner": true }, { "group": "data-engineering", "schema": ".*", "owner": true }, { "group": "data-science", "catalog": ".*", "schema": ".*", "owner": false }, { "user": "guest", "owner": false } ], "tables": [ { "group": "data-science", "privileges": ["SELECT", "INSERT", "DELETE", "OWNERSHIP"], "catalog": "(iceberg)", "schema": "(ds_scratch)" }, { "group": "data-science", "privileges": ["SELECT"], "catalog": ".*", "schema": ".*" } ] }
Improve this question

2 Answers 2

Reset to default
2

This is related to a problem with how the trino authorization system works.

Take from the git issue

You will need to create policies at the Catalog level, Schema level and Table level in order to ensure access.

Access determination does not drill down to lower levels. The default policy is deny and as such, the first check will indeed end up with access denied.

So because I didn't have table-level permissions defined for the data-engineer group it was defaulting to deny

I fixed this by adding the following to the tables rules.

 "tables": [ { "group": "data-engineering|data-platform-admin", "privileges": ["SELECT", "INSERT", "DELETE", "OWNERSHIP", "GRANT_SELECT"], "catalog": ".*", "schema": ".*" },

I put this ahead of the data-science rules as the rules are evaluated in order and I want users in the data-engineering group to get more access then those in the data-science group. But the same users might be defined in both groups via complex LDAP user queries and group matching. I tested and this works as I expect it to work.

Improve this answer
1
  • Essentially you have to define permissions for each user at all three levels, catalogues, schemas and tables. It defaults to deny. so any place you don't specify permissions for a user will come up as a deny. Commented Feb 1, 2024 at 9:24
0

Thanks for your answer. I am facing a similar issue but I am doing this at catalog level and wanted to know how can I grant an access to the user for a single table and avoid this error ? Is is possible to create system catalog access file ?

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.