Version: v4.13

Service Account Resolution for Custom Applications

Service account resolution is needed 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 service account resolution, there is no way to identify who accessed the repo through application. For custom applications, service account resolution can be achieved using CyralContext.

CyralContext is a query annotation format that helps the Cyral sidecar determine identity and application information for a given query.

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)
Deploy a sidecar and bind the repository to it (not required but you will need this for testing)

Procedure

In the Cyral control plane UI, go to Data Repos ➡️ choose your repository ➡️ Apps and BI Tools.
Click Register Database Account
In the Register Database Account wizard, provide the Database account name and click Next.
This account name must match an existing account on the database to which you're connecting. The custom application must use this service account to connect the database.
Click Custom Application and click Next.
If you want to track users' group affiliations and write access policies based on groups, turn ON Retrieve SSO group membership for users accessing data through this application.
Below the checkbox, you can see the identity provider(s) (IdP) that will provide group information.
If you haven't set up SSO or SCIM for the repo, click the Configure IdP Integration or Configure SCIM button to set it up now.
Click Next.
The Cyral UI displays an example of CyralContext comment format. Additional examples are provided here.
In the Cyral UI, click Next.
Provide a name for your application and click Register. This name will be logged to identify user sessions initiated through this database account.

Cyral setup for configuring your custom application is now complete. You now need to modify the application to add end user information to each query in the form of CyralContext comments.

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"
  },

Service Account Resolution for Custom Applications

Prerequisites​

Procedure​

CyralContext Comment Format​

Examples​

SQL Queries​

Populating Only the User Field​

Populating Additional Fields​

Troubleshooting​

Identity Information is Cached Per Connection​

The Connection Driver Name is Always Logged as the Application Name​