Partial Database Scanning for Relational Database Targets

This example describes the workflow and sequence of requests to make to enable the partial database scanning feature for a PostgreSQL database Target using the Enterprise Recon API.

• Step 1 - Add Credentials
• Step 2 - Get the Agent ID
• Step 3 - Get the Target ID
• Step 4 - Add PostgreSQL Database Target Location
• Step 5 - Get Data Type Profile ID
• Step 6 - Scan the PostgreSQL Database Target Location

Defaults and Assumptions

This example uses the following default values and makes the following assumptions:

1. "My-PostgreSQL-Server" is the host name of the PostgreSQL database server.

2. "My-PostgreSQL-Server" has been added as a Target to the Master Server.

3. A suitable Linux proxy agent has been installed on the same host ("My-PostgreSQL-Server") as the PostgreSQL database server and is connected to the Master Server.
4. The maximum number of rows to scan for all tables in the PostgreSQL database is 100 rows (sorted by the primary key in descending order).

Step 1 - Add Credentials

Start by adding a new credential set for the PostgreSQL database Target location, where:

• label is a descriptive label for the PostgreSQL database credential set,
• username is the user name for the PostgreSQL database server,
• password is password for the corresponding username, and
• type is Server.

Sample Request

curl --request POST 'https://er-master:8339/v1/credentials' \
--user apiuser:password123 \
--header "Content-Type: application/json" \
--data-raw '{
  "label": "My-PostgreSQL-Server Credentials",
  "username": "pgsql-user-A",
  "password": "pgsql-user-A-password",
  "type": "Server"
}'

Expected Response

HTTP/1.1 201 Created
Content-Type: application/json
Content-Length: xxx
{
  "id": "15792178316638465022"
}  

The credential id (15792178316638465022) created from this request will be required when adding the PostgreSQL database as a Target Location in Step 4.

Step 2 - Get the Agent ID

Next, get the agent ID of a suitable Linux proxy agent that will be used to scan the PostgreSQL database Target.

Sample Request

curl --request GET 'https://er-master:8339/v1/agents?agent_name=My-PostgreSQL-Server' \
--user apiuser:password123 \
--header "Accept: application/json"

Expected Response

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: xxx
[
    {
        "id": "3519591954114186070",
        "name": "My-PostgreSQL-Server",
        "type": "node",
        "version": "2.5",
        "platform": "Linux 5.11.0-41-generic",
        "platform_compatibility": "Linux Debian Jessie/Ubuntu Trusty 64 bit",
        "verified": true,
        "connected": true,
        "proxy": true,
        ...
    }
]

The agent id (3519591954114186070) returned in this request will be required when adding the PostgreSQL database as a Target Location in Step 4.

Step 3 - Get the Target ID

Next, get the Target ID for the PostgreSQL database host machine, "My-PostgreSQL-Server" which has already been added as a Target to the Master Server.

Sample Request

curl --request GET 'https://er-master:8339/v1/targets?target_name=My-PostgreSQL-Server' \
--user apiuser:password123 \
--header "Accept: application/json"

Expected Response

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: xxx
[
    {
        "id": "4759598330602895744",
        "name": "My-PostgreSQL-Server",
        "comments": "",
        "search_time": 0,
        "search_status": "none",
        "errors": {
            "notice": 0,
            "error": 0,
            "critical": 0
        },
        "matches": {
            "test": "0",
            "match": "0",
            "prohibited": "0"
        },
        "platform": "Linux Debian Jessie/Ubuntu Trusty 64 bit"
    }
]

The Target id (4759598330602895744) returned in this request will be required when adding the PostgreSQL database Target Location in Step 4.

Step 4 - Add PostgreSQL Database Target Location

After completing Step 1 to Step 3, you will have all the information required to add the PostgreSQL database as a Target Location with the row_limit option specified to enable partial database scanning, where:

• path is specified with the row_limit option,
• protocol is pgsql,
• credential_id is the credential id created in Step 1, and
• proxy_id is the agent id returned in Step 2.

Sample Request

curl --request POST 'https://er-master:8339/v1/targets/4759598330602895744/locations' \
--user apiuser:password123 \
--header "Content-Type: application/json" \
--data-raw '{
  "path": "(row_limit=100)",
  "protocol": "pgsql",
  "credential_id": "15792178316638465022",
  "proxy_id": "3519591954114186070"
}'

Expected Response

HTTP/1.1 201 Created
Content-Type: application/json
Content-Length: xxx
{
  "id": "9832457584012239212"
}  

The Target Location id (15792178316638465022) created from this request will be required to set up scan schedules for the PostgreSQL database in Step 6.

Step 5 - Get Data Type Profile ID

Next, get the data type profile ID of the data type profiles to enable when scanning the PostgreSQL database Target Location.

Sample Request

curl --request GET 'https://er-master:8339/v1/datatypes/profiles' \
--user apiuser:password123 \
--header "Accept: application/json"

Expected Response

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: xxx
[
    {
        "id": "1",
        "label": "PCI Compliance",
        "version": 1,
        "owner": "0",
        "modified": 1632129035,
        "default": true,
        "disabled": false,
        "global": true,
        "sealed": true
    },
    {
        "id": "2",
        "label": "All Cardholder Data",
        "version": 1,
        "owner": "0",
        "modified": 1632129035,
        "default": true,
        "disabled": false,
        "global": true,
        "sealed": true
    },
    ...
    {
        "id": "14",
        "label": "USA Protected Health Information (PHI)",
        "version": 1,
        "owner": "0",
        "modified": 1632129035,
        "default": true,
        "disabled": false,
        "global": true,
        "sealed": true
    }
]

The data type profile id(s) returned in this request will be required when setting up scan schedules for the PostgreSQL database Target in Step 6.

Step 6 - Scan the PostgreSQL Database Target Location

Schedule a scan for the newly added PostgreSQL database Target Location, where:

• label is a descriptive label for the scan,
• targets.id is the Target id returned in Step 3,
• targets.locations.id is the Target Location id created in Step 4 with the partial database scanning option set to 100 rows, and
• profiles is the data type profile id(s) returned in Step 5.

Sample Request

curl --request POST 'https://er-master:8339/v1/schedules' \
--user apiuser:password123 \
--header "Content-Type: application/json" \
--data-raw '{
  "label": "PostgreSQL database PDB API scan",
  "targets": {
    "id": "4759598330602895744",
    "locations": [
      {
        "id": "9832457584012239212"
      }
    ]
  },
  "profiles": [
    "1",
    "2",
    "3"
  ],
  "cpu": "low",
  "throughput": 0,
  "memory": 0,
  "capture": true,
  "trace": false,
  "match_detail": "balanced"
}'

Expected Response

HTTP/1.1 201 Created
Content-Type: application/json
Content-Length: xxx
{
  "id": "102"
}  

You can check the status and progress of the scan using the scan schedule id (102) created in this request.