How to make table access controls work?

[ilsaloving]

I've opened an issue (#17689) regarding this because it seems like either the documentation for Table ACLs is wrong, or the functionality is broken.

In the mean time, does anyone have a working example of a json file that provides table-level ACLs against a postgres-based catalog?

I'm trying to restrict access to different schemas and tables based on user/group/role.

[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. 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 tables.
• 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": [
      ]
    }
  ]
}

[BrandonBlackwell]

I have been searching for a week on this issue. Thank you! You are God sent!