Data structure and it's effect

[r-scheele]

Previous data response, the changes and how it affects the way rego rules are written

[r-scheele]

Why is it important to understand the database's data structure?

To write rego rules using the data in the database, an administrator must be familiar with the structure of their database tables. The database's information could include the names of users, groups, or particular attributes that will be used in the rego rules, etc. database comparison could be with a properties on the input, or a static value supplied by the administrator e.g name of a company

The structure of the response from the data route could be in this format;

{
    "users": [],
    "groups":[],
    "attributes":[]
}

In order to enable efficient comparison in the rego rules, properties present on the input must also be known beforehand. These properties are assigned from the identity provider as a means to enable attribute based access control

The properties used in the API are the most common ones, gotten from keycloak identity provider, but others could be supplied as appropriate;

{
   "email_verified": false,
   "preferred_username": "John doe",
    "request_method":  "GET"
    "request_path": [...]
}

The previous architecture of the API doesn't allow changes in the SQL query that retrieve the database tale structure, because it was provided statically as part of the API code.

Since the sql query supplied to the policy management API must match the one supplied to the opal-client, the query below is supplied as part of the code in our APP.

{
"entries": [
   {"query": "select u.name, g.groupname from gs_usergroup_members r join gs_usergroup g on r.group_id = g.id join gs_user u on r.user_id = u.id;", "dst_path": "users"
}

This produce a giant list of values as a response, that leads to little flexibility while writing the rego rules. Hence the need to adjust the architecture.

Response from the sql query;

{
  "groups": [
    [
      "mss.llll",
      "everyone"
    ],
    [
      "PRD.LRT",
      "everyone"
    ],
    [
      "FRN.SVT",
      "everyone"
    ],
...
}

Rego rules written to match the data response;

input.preferred_username == data.users[i].name

input.groupname == data.groups[i].name

The query produced, needed some transformation because of the difference in data structure of the response between the opal-client and pycorpg2(rego API) i.e rego API returned a list of lists, while opal-client returned a list of dictionaries.

To ensure multiple queries can be supplied to get data by the administrator, we adjusted the API architecture

With this approach, only the data needed by the user to write rego rules is queried. The name associated with each queries can also be attached.

Multiple queries can also be supplied to the opal-client, and a name to give each of the list returned for the individual queries.

{
"entries": [
   {"query": "SELECT DISTINCT groupname AS value FROM geostore.gs_usergroup;", "dst_path": "groups"},
   {"query": "SELECT DISTINCT name AS value FROM geostore.gs_user;", "dst_path": "groups"},
}

The same query is supplied by the administrator from the front-end of the Policy API to match the one returned from the opal-client.

Request object;

{
       "queries": [
                    {
                        "data_list_name": "groups",
                        "sql_query": "SELECT DISTINCT groupname AS value FROM geostore.gs_usergroup;",
                    },
                    {
                        "data_list_name": "users",
                        "sql_query": "SELECT DISTINCT name AS value FROM geostore.gs_user;",
                    },
                ]
}

Example response for both the opal-client and the policy API;

 { "data" : {
    "groups": [
      {
        "value": "EDITOR_DPAU"
      },
      {
        "value": "EDITOR_CPQ"
      },
      {
        "value": "VIEWER"
      },
      {
        "value": "everyone"
      },
      {
        "value": "EDITOR_ATAC"
      },
      {
        "value": "TestGeocity"
      }
    ],
    "users": [
      {
        "value": "CCC.RRT"
      },
      {
        "value": "ndr.qtrn"
      },
      {
        "value": "MTT.SRA"
      },
      {
        "value": "QTR.NDR"
      },
      {
        "value": "guest"
      }
    ]
} }

To give illustration of how the new setup affects the rego rules;

# input from the identity provider

{
    "groupname": "VIEWER",
    "preffered_username": "guest"
}

# data response from the opal-client
{
    "groups": [
      {
        "value": "EDITOR_DPAU"
      },
      {
        "value": "EDITOR_CPQ"
      },
      {
        "value": "VIEWER"
      },
      {
        "value": "everyone"
      },
      {
        "value": "EDITOR_ATAC"
      },
      {
        "value": "TestGeocity"
      }
    ],
    "users": [
      {
        "value": "CCC.RRT"
      },
      {
        "value": "ndr.qtrn"
      },
      {
        "value": "MTT.SRA"
      },
      {
        "value": "QTR.NDR"
      },
      {
        "value": "guest"
      }
    ]
}

// Rego rules to allow only requests with users with their preferred_username and groupname present in the users and grups array, respectively.

allow {
    input.preffered_username == data.users[i].value
        input.groupname == data.groups[x].value
}