Trino access control behaviour does not match documentation

[ilsaloving]

I am trying to implement a Trino POC, and have been unsuccessful in getting file-based access controls to work. After spending most of the afternoon trying to get it working, I believe the information describing table ACLs is incorrect.

Tables ACL is broken

I have created two trino users, trinoadmin, and trinouser, and a connection to a postgres database, and I am trying to query the resulting trino db using DBeaver. I have the following sample rule file:

{ "catalogs": [
    { "user":"trinoadmin", "catalog":".*", "allow":"read-only" }
  ],
  "tables": [
    {"user":"trinouser", "catalog":"mydw", "schema":"myschema", "privileges": ["SELECT"] }
  ] }

According to the documentation:

• If no rule specifically matches a given combination, then the default is no access, but If a rule exists then any unspecified constraints for that rule default to ".*"
• According to https://trino.io/docs/current/security/file-system-access-control.html#visibility if visibility for a higher level object (catalog, schema) is not explicitly granted, then it will not be visible. But if permission is granted to a nested object (eg: a table), then the higher level objects will appear
• The documentation explicitly says "These rules do not apply to system-defined tables in the information_schema schema."

Therefore, the above json should mean that trinoadmin has full visibility into all catalogs, schemas, and table data. trinouser should be able to view all tables within the schema myschema of catalog it_dw. That is not what happens.

If I leave the tables stanza out completely, then trinoadmin can see all objects as expected, and trinouser can see nothing but the system catalog. But if the tables stanza is included, then ALL users are unable to access anything, instead getting the error "Cannot select from table system.jdbc.tables".

If I add the following to the tables stanza (which is not only supposed to be unnecessary, but the example under https://trino.io/docs/current/security/file-system-access-control.html#catalog-rules specifically denies access to the system catalog ):

    {"catalog": "system", "privileges": [ "SELECT" ] },

then I no longer get the above error and trinoadmin works again, but trinouser is still unable to access the tables in my desired catalog. So either the documentation is wrong, or the functionality is just broken.

[ilsaloving]

After a great deal of trial and error, I have confirmed that https://trino.io/docs/current/security/file-system-access-control.html#visibility is unequivocally wrong. Trino does not respect any kind of tree heirarchy.

I was able to mostly get what I want using the following. Key takeaways:

• The schemas stanza is useless unless you're doing something with ownership
• You must explicitly grant a user access to a catalog through the catalogs stanza for the user to see anything at all in that catalog. If you don't, the tables stanza won't help you.
• when you implement the tables stanza, you must comprehensively specify all rules necessary for all desired object combinations.
• You must make sure everyone has access to the system catalog or the querying agent won't be able to enumerate the database structure. For example, DBeaver cannot query a trino database if the system catalog is unavailable.
• The above is not true for the Trino CLI, which is disingenuous because the average user isn't going to be using the CLI.
• The end result is you can see the catalog, and all schemas within that catalog whether you can access them or not. But at least inaccessible tables are hidden, which is better than nothing.

{
  "catalogs": [
    {
      "group": "admins",
      "catalog": ".*",
      "allow": "all"
    },
    {
      "group": "users",
      "catalog": "system",
      "allow": "read-only"
    },
    {
      "group": "users",
      "catalog": "mydw",
      "allow": "read-only"
    }
  ],
  "schemas": [
    {
      "group": "admins",
      "schema": ".*",
      "owner": true
    }
  ],
  "tables": [
    {
      "group": "admins",
      "catalog": ".*",
      "privileges": [
        "SELECT",
        "INSERT",
        "DELETE",
        "UPDATE",
        "OWNERSHIP",
        "GRANT_SELECT"
      ]
    },
    {
      "catalog": "system",
      "privileges": [
        "SELECT"
      ]
    },
    {
      "group": "users",
      "catalog": "mydw",
      "schema": "myschema",
      "table": "mytable",
      "privileges": [
        "SELECT"
      ]
    },
   # Catchall to deny access to everything not specifically described
    {
      "group": "users",
      "catalog": ".*",
      "schema": ".*",
      "table": ".*",
      "privileges": [
      ]
    }
  ]
}

[jhlodin]

@mosabua @colebow Can you have a look?

[zach-overflow]

+1 I very much have seen similar issues as @ilsaloving has described, and I think this could be a very big issue with either the documentation, or a serious bug.

[mosabua]

I am not sure myself, but I am inclined to think that this is more likely a documentation issue.

Did you want to send one or more PRs to update the documentation and we get confirmation for all this via the PR review? This might be easiest..

Alternatively I can try and see who might be able to either verify your findings and either help with doc update or code fix.

[hashhar]

cc: @kokosing

[ruifpedro]

+1 I've also run into some of the issues @ilsaloving described above! Are there any developments on this?

[maxpoulain]

+1 I've the same issues as describe above !

[mosabua]

@dain do you maybe know off the top of your head

[SBylemans]

+1 having the same issue

[SGH-N]

+1 having the same issue

[mosabua]

I have this on my backlog to work on but its a long backlog .. so if anyone else gets around to send more PRs to update the docs please add me as reviewer so I can help and merge.

[MehulBatra]

+1 I have been trying to solve this for a week, but unable to generate the desired results.

• I want a admin user who has access to everything.
• An ad-hoc user who has access like admin but cannot access a pii schema and tables underneath.