PingAuthorize

Database services

Configure database settings to directly connect PingAuthorize to a relational database management system (RDBMS).

The policy decision point (PDP) can perform data requests to relational databases. By using complex SQL queries to efficiently retrieve and transform structured data, the database service type enables you to implement dynamic authorization logic based on identity information stored in an RDBMS.

PingAuthorize has been tested with the following database versions:

  • Oracle

    • Driver: Oracle JDBC Thin Driver Version 21.7.0.0

    • Database: Oracle Database 12c Release 2 (12.2)

  • PostgreSQL

    • Driver: PostgreSQL JDBC Driver Version 42.7.3

    • Database: PostgreSQL 15.0

To use these databases as policy information providers in embedded or external PDP mode, you must add the JDBC driver .jar library to the lib folder in your PingAuthorize distribution. The .jar libraries for both of the above database drivers are added to PingAuthorize-PAP/lib by default.

To use a JDBC-compliant database version not listed above, you must do the following:

  • Add the JDBC .jar library to the lib folder in your PingAuthorize and PingAuthorize-PAP distributions.

  • Add the JDBC driver to the database drivers allow list.

Core settings

Screen capture of the Service Settings section in the Policy Editor for a newly defined Database service

  • Connection String: The Java Database Connectivity (JDBC) connection string that defines the connection between PingAuthorize and your relational database. For the proper formatting of the connection string, consult the documentation for your specific database driver.

    The following are examples of valid connection strings:

    • jdbc:postgresql://dbhost/dbname

    • jdbc:oracle:thin:@localhost:1521:SID

    You can interpolate attributes anywhere in the connection string.

  • SQL: The SQL query sent from the PDP to the database.

    By default, you can only interpolate attributes in the position of an attribute value. In this example, the PDP uses the UserId attribute defined in the Trust Framework as a query parameter and populates it at runtime:

    SELECT user, email FROM people WHERE userid = {{UserId}}

    You can interpolate attributes anywhere in the SQL query with the unsafe modifier. In this example, the PDP selects an entire row’s values from an interpolated table, where the userid value equals 1234:

    SELECT * FROM {{userTable | unsafe}} WHERE userid = 1234

    Depending on where the attribute value comes from, using the unsafe modifier could leave the SQL query vulnerable to injection attacks. To help prevent such attacks, make sure the interpolated attribute is a deterministic constant resolved from service values or decision request attributes.

Service responses

Database services convert the results from a database query into an array of JSON objects. For example, if the "first_name" and "last_name" columns are queried from the following table:

first_name last_name age

John

Smith

44

Sally

White

47

The service response will be the following array:

[
  {
    "first_name": "John",
    "last_name": "Smith"
  },
  {
    "first_name": "Sally",
    "last_name": "White:
  }
]

The service will attempt to convert SQL values of type json or jsonb to JSON objects. For example, if the following set of data is queried from a database:

personjson jsonlength

\{"first_name": "John", "last_name": "Smith", "age": 44}

53

\{"first_name": "Sally", "last_name": "White", "age": 45}

55

The service response will be the following JSON array:

[
  {
    "personjson": {
      "first_name": "John",
      "last_name": "Smith",
      "age": 44
    },
    "jsonlength": 53
  },
  {
    "personjson": {
      "first_name": "Sally",
      "last_name": "White",
      "age": 45
    },
    "jsonlength": 55
  }
]

Because JSON parsing can be an expensive operation, be careful when designing queries that include large JSON objects.

If an entry in the database query result is null, the service will return this value as a null JSON object. For example, if the following set of data is queried from a database:

actor_id first_name last_name last_update

201

Penelope

<null>

2024-02-21 14:23:14.0

The service response will be the following JSON array:

[
  {
    "actor_id": 201,
    "first_name": "Penelope",
    "last_name": null,
    "last_update": "2024-02-21 14:23:14.0"
  }
]

If an entry in the database query result is of a binary or unrecognized type, the service will convert the value to a Base64-encoded string so that it can be safely returned in a JSON object.

Because PingAuthorize converts the result of an SQL query to a JSON document, you must set the service value type to JSON and use a JSONPath processor to extract data from the service response.

Screen capture of the Database Value Settings with JSON selected as the value type

What to do next

You can configure the pool of database connections maintained by PingAuthorize to optimize data retrieval. Learn more in Configuring database service connections and Configuring Policy Editor database service connections.