Service Account Resolution for Custom Applications

SAR is usually used when applications connect to a repo using a service account and different users log into the custom application with their identity. In this case, without SAR, there is no way to identify who accessed the repo through application and SAR helps disambiguating.

In the steps below, you’ll set up one or more service accounts that your application users can use to connect to a repository, and you’ll configure Cyral and your application to identify the application username and group when the service account is used.

Prerequisites

• Set up SSO Authentication for the repository.
• Set up the SCIM integration between Cyral and your identity provider (Azure AD or Okta)
• Set up a data repository for a repository type that supports service account resolution (MySQL, PostgreSQL, SQL Server, Snowflake, Redshift, Denodo)
• Install a sidecar and bind the repository to it (not required but you will need this for testing)

Procedure

Create the Custom Connection Driver Integration

This POST call to the /v1/integrations/confExtensions/instances is used to create a connection driver instance that will be associated with the service account to be used for query annotations.

The POST must contain the following JSON syntax

{
    "name": "PGCyralContextTesting",
    "category": "builtin",
    "parameters": "{\"applicationName\":\"PGCyralContextTesting\"}",
    "purpose": "connectionDriver",
    "templateType": "cyralContext"
}

The following items are fixed and should remain as shown in the example:

• category
• purpose
• templateType

The applicationName can be whatever free form text you’d like to use as a name based identifier. The only change that should be made to the parameters string is the PGCyralContextTesting. This should be changed to whatever name you would like to be shown in the logs for the client.applicationName field.

NOTE : This applicationName field will override any client name provided by client applications in the logs.

After you make the post call, capture the response which will be something like the below

{
    "id": "2FxNwNM1qkwSW8LR0cOH8w8JJmZ"
}

Make note of the id as this will be needed to assign to the serviceAccount created in the next section.

Create the Service Account

The next step is to create a Service Account under the repo that will be using the connection driver. This is done by making a POST request to /v1/repos/{{repoID}}/serviceAccounts with the following payload.

{
  "connectionDriverInstanceIDs": [
    "2FxNwNM1qkwSW8LR0cOH8w8JJmZ"
  ],
  "description": "Here is to hoping this works",
  "fetchGroups": false,
  "name": "django_user"
}

The id returned from the previous API call should be used in the connectionDriverInstanceIDslist. You can add whatever text you’d like in the description field. For the purposes of this guide, fetchGroups is set to false because we do not have SCIM configured in this example. The name should match the username of the Service Account being used by the application.

NOTE : The username used by the application when connecting must be the name defined in the above request in order for Service Account Resolution to work. When the Cyral sidecar sees the username defined in the name field connecting to the database issung queries containing the CyralContext comment, the sidecar knows to parse through the comment for user and group information. The sidecar can then extract this user and group information to help disambiguate the identity of the end user.

The fetchGroups setting will require SCIM to be configured with the identity provider. When fetchGroups is set to false, a user's group information is determined by the CyralContext.group field on the annotation. When fetchGroups is set to true, the sidecar will attempt to also look up all of the user's group membership information in the identity provider that is configured for the repository.

CyralContext Comment Format

The CyralContext comment makes use of a JSON object that makes use of the following JSON structure:

{ 
   "user" : "Some User",
   "userGroup" : "Some Group",
   "serviceName" : "Some Service or Applicaion Name",
   "attributes" : {
      "item1" : "value1",
      "item2" : "value2"
   }
}

The various entries in this object can be explained as below

• user (string): used to identify an individual user
• userGroup (string): used to identify a group that the individual user is a member of
• serviceName (string): used to identify a service/application
• attributes (map[string]string): used to provide arbitrary information

If you supply additional fields in the CyralContext annotation, they will be logged in the CyralContext log path as noted in the examples below.

NOTE : The CyralContext comment is case sensitive.

Examples

We can support both multi line comments

/*
CyralContext {"user":"Ted Theodore Logan"}
*/

and single line comments

-- CyralContext {"user":"Ted Theodore Logan"}

/* CyralContext {"user":"Ted Theodore Logan"} */

We can also support having multiple comments on the query if the event the customer already annotates the query with their own details

/* CyralContext {"user":"Ted Theodore Logan"} */
/* customer comment */

/* customer comment */
/* CyralContext {"user":"Ted Theodore Logan"} */

NOTE : The comments must be separate in the above cases but ordering does not matter.

SQL Queries

Populating Only the User Field

Executing the following query

/* CyralContext {"user":"Ted Theodore Logan"} */
SELECT 42;

results in the following log

{
...
  "identity": {
    "endUser": "Ted Theodore Logan",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
  "cyralContext": {
    "user": "Ted Theodore Logan",
    "userGroup": "",
    "serviceName": "",
    "attributes": null
  }
}

the user provided via the CyralContext.user is mapped to the identity.endUser in the logs. In addition to this, the details are added to the cyralContext log path.

Populating Additional Fields

Executing the following query

/* CyralContext {"user":"Ted Theodore Logan","userGroup":"Wild Stallions","serviceName":"ExcellentAdventuresApp","attributes":{"affiliate":"Bill S Preston Esquire"}} */
SELECT 42;

results in the following log

{
...
  "identity": {
    "endUser": "Ted Theodore Logan",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
  "cyralContext": {
    "user": "Ted Theodore Logan",
    "userGroup": "Wild Stallions",
    "serviceName": "ExcellentAdventuresApp",
    "attributes": {
      "affiliate": "Bill S Preston Esquire"
    }
  }
}

Troubleshooting

Identity Information is Cached Per Connection

Assume you connect to the server and issue the following queries

/* CyralContext {"user":"Ted Theodore Logan"} */
SELECT 42;

SELECT current_date;

/* CyralContext {"user":"Bill S Preston Esquire"} */
SELECT 42;

SELECT current_time;

The results logs will be as follows where the identity.endUser remains what is provided by CyralContext unless a new query changes that information on the same connection.

Query #1 - Log

{
...
  "identity": {
    "endUser": "Ted Theodore Logan",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
  "cyralContext": {
    "user": "Ted Theodore Logan",
    "userGroup": "",
    "serviceName": "",
    "attributes": null
  }
}

Query #2 - Log

{
...
  "identity": {
    "endUser": "Ted Theodore Logan",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
}

Query #3 - Log

{
...
  "identity": {
    "endUser": "Bill S Preston Esquire",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
  "cyralContext": {
    "user": "Bill S Preston Esquire",
    "userGroup": "",
    "serviceName": "",
    "attributes": null
  }
}

Query #4 - Log

{
...
  "identity": {
    "endUser": "Bill S Preston Esquire",
    "repoUser": "django_user",
    "dbRole": "django_user"
...
  "client": {
...
    "applicationName": "PGCyralContextTesting"
...
}

The Connection Driver Name is Always Logged as the Application Name

Assume we force the application name such as the below

# PGAPPNAME='Testing Application Name' psql -h psql-server-name.local -U django_user -d django_testing
psql (14.2 (Debian 14.2-1.pgdg110+1), server 13.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

django_testing=> select 42;
 ?column? 
----------
       42
(1 row)

django_testing=> show application_name;
     application_name     
--------------------------
 Testing Application Name
(1 row)

In checking the logs, we can see that the application name still matches that of the connection driver

  "client": {
...
    "applicationName": "PGCyralContextTesting"
  },