Preparing the Graph Server (PGX) to Connect to Autonomous AI Database

You can configure your graph server(PGX) to connect to an Autonomous AI Database instance.

Irrespective of whether your graph server (PGX) instance is running on premises or on Oracle Cloud Infrastructure (OCI), you can perform the following steps to determine the service name to connect to your Autonomous AI Database instance and update the JDBC URL in /etc/oracle/graph/pgx.conf file.

1. Download and save the wallet for your Autonomous AI Database instance from the Oracle Cloud Infrastructure (OCI) Console. See Download Client Credentials (Wallets) for more information.
2. Unzip the wallet to a new subdirectory in /etc/oracle/graph/wallets/<dbname>, and change the group permission as shown:

   sudo unzip Wallet_<dbname>.zip -d /etc/oracle/graph/wallets/<dbname>
   sudo chgrp -R oraclegraph /etc/oracle/graph/wallets/<dbname>

3. Determine the connect identifier from the tnsnames.ora file in /etc/oracle/graph/wallets/<dbname> directory. For example, the entry must be similar to:

   graphdb_low = 
       description= (retry_count=20)(retry_delay=3)
           (address=
               (protocol=tcps)(port=1522)
               (host=adwc.example.oraclecloud.com)
           )
           (connect_data=(service_name=graphdb_low.adwc.oraclecloud.com))
           (security=(ssl_server_cert_dn="CN=adwc.example.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US"))
   )

   In the preceding example, graphdb_low is the connect identifier.

4. Update the JDBC URL in /etc/oracle/graph/pgx.conf file with the connect identifier determined in the preceding step along with the directory path to the unzipped wallet file. For example:

   ...
   "pgx_realm": {
     "implementation": "oracle.pg.identity.DatabaseRealm",
     "options": {
       "jdbc_url": "jdbc:oracle:thin:@graphdb_low?TNS_ADMIN=/etc/oracle/graph/wallets/<dbname>",
       "token_expiration_seconds": 3600,
   ...
   

5. Finally, restart the graph server as shown:

   sudo systemctl restart pgx

Write the PGX graph configuration file to load a graph directly from relational tables

The following example loads a subset of the HR sample data from relational tables directly into PGX as a graph. The configuration file specifies a mapping from relational to graph format by using the concept of vertex and edge providers.

{
    "name":"hr",
    "jdbc_url":"jdbc:oracle:thin:@myhost:1521/orcl",
    "username":"hr",
    "keystore_alias":"database1",
    "vertex_id_strategy": "no_ids",
    "vertex_providers":[
        {
            "name":"Employees",
            "format":"rdbms",
            "database_table_name":"EMPLOYEES",
            "key_column":"EMPLOYEE_ID",
            "key_type": "string",
            "props":[
                {
                    "name":"FIRST_NAME",
                    "type":"string"
                },
                {
                    "name":"LAST_NAME",
                    "type":"string"
                },
                {
                    "name":"EMAIL",
                    "type":"string"
                },
                {
                    "name":"SALARY",
                    "type":"long"
                }
            ]
        },
        {
            "name":"Jobs",
            "format":"rdbms",
            "database_table_name":"JOBS",
            "key_column":"JOB_ID",
            "key_type": "string",
            "props":[
                {
                    "name":"JOB_TITLE",
                    "type":"string"
                }
            ]
        },
        {
            "name":"Departments",
            "format":"rdbms",
            "database_table_name":"DEPARTMENTS",
            "key_column":"DEPARTMENT_ID",
            "key_type": "string",
            "props":[
                {
                    "name":"DEPARTMENT_NAME",
                    "type":"string"
                }
            ]
        }
    ],
    "edge_providers":[
        {
            "name":"WorksFor",
            "format":"rdbms",
            "database_table_name":"EMPLOYEES",
            "key_column":"EMPLOYEE_ID",
            "source_column":"EMPLOYEE_ID",
            "destination_column":"EMPLOYEE_ID",
            "source_vertex_provider":"Employees",
            "destination_vertex_provider":"Employees"
        },
        {
            "name":"WorksAs",
            "format":"rdbms",
            "database_table_name":"EMPLOYEES",
            "key_column":"EMPLOYEE_ID",
            "source_column":"EMPLOYEE_ID",
            "destination_column":"JOB_ID",
            "source_vertex_provider":"Employees",
            "destination_vertex_provider":"Jobs"
        },
        {
            "name":"WorkedAt",
            "format":"rdbms",
            "database_table_name":"JOB_HISTORY",
            "key_column":"EMPLOYEE_ID",
            "source_column":"EMPLOYEE_ID",
            "destination_column":"DEPARTMENT_ID",
            "source_vertex_provider":"Employees",
            "destination_vertex_provider":"Departments",
            "props":[
                {
                    "name":"START_DATE",
                    "type":"local_date"
                },
                {
                    "name":"END_DATE",
                    "type":"local_date"
                }
            ]
        }
    ]
}

Read the data

Now you can instruct PGX to connect to the database and read the data by passing in both the keystore and the configuration file to PGX, using one of the following approaches:

• Interactively in the graph shell

  If you are using the graph shell, start it with the --secret_store option. It will prompt you for the keystore password and then attach the keystore to your current session. For example:

  cd /opt/oracle/graph
  ./bin/opg4j --secret_store /etc/my-secrets/keystore.p12
   
   enter password for keystore /etc/my-secrets/keystore.p12:
  

  Inside the shell, you can then use normal PGX APIs to read the graph into memory by passing the JSON file you just wrote into the readGraphWithProperties API:

  opg4j> var graph = session.readGraphWithProperties("config.json")
  graph ==> PgxGraph[name=hr,N=215,E=415,created=1576882388130]
  

• As a PGX preloaded graph
  As a server administrator, you can instruct PGX to load graphs into memory upon server startup. To do so, modify the PGX configuration file at /etc/oracle/graph/pgx.conf and add the path the graph configuration file to the preload_graphs section. For example:

  {
    ...
    "preload_graphs": [{
      "name": "hr", 
      "path": "/path/to/config.json"
    }],
    "authorization": [{
      "pgx_role": "GRAPH_DEVELOPER",
      "pgx_permissions": [{
        "preloaded_graph": "hr",
        "grant": "read"
      }]
    },	
  	....
    ]
  }

  As root user, edit the service file at /etc/systemd/system/pgx.service and change the ExecStart command to specify the location of the keystore containing the password:

  
  ExecStart=/bin/bash start-server --secret-store /etc/keystore.p12

  Note:

  Please note that /etc/keystore.p12 must not be password protected for this to work. Instead protect the file via file system permission that is only readable by oraclegraph user.
  After the file is edited, reload the changes using:

  sudo systemctl daemon-reload

  Finally start the server:

  sudo systemctl start pgx

• In a Java application

  To register a keystore in a Java application, use the registerKeystore() API on the PgxSession object. For example:

  import oracle.pgx.api.*;
   
  class Main {
     
    public static void main(String[] args) throws Exception {
      String baseUrl = args[0];
      String keystorePath = "/etc/my-secrets/keystore.p12";
      char[] keystorePassword = args[1].toCharArray();
      String graphConfigPath = args[2];
      ServerInstance instance = Pgx.getInstance(baseUrl);
      try (PgxSession session = instance.createSession("my-session")) {
        session.registerKeystore(keystorePath, keystorePassword);
        PgxGraph graph = session.readGraphWithProperties(graphConfigPath);
        System.out.println("N = " + graph.getNumVertices() + " E = " + graph.getNumEdges());
      }
    }
  }
  

  You can compile and run the preceding sample program using the Oracle Graph Client package. For example:

  cd $GRAPH_CLIENT
  // create Main.java with above contents
  javac -cp 'lib/*' Main.java
  java -cp '.:conf:lib/*' Main http://myhost:7007 MyKeystorePassword path/to/config.json
  

Secure coding tips for graph client applications

When writing graph client applications, make sure to never store any passwords or other secrets in clear text in any files or in any of your code.

Do not accept passwords or other secrets through command line arguments either. Instead, use Console.html#readPassword() from the JDK.

Revoking Graph Permissions

If you have the MANAGE permission on a graph, you can revoke graph access from users or roles using the PgxGraph#revokePermission API. For example:

PgxGraph g = ...
g.revokePermission(new PgxRole("GRAPH_DEVELOPER")) // revokes previously granted role access
g.revokePermission(new PgxUser("SCOTT")) // revokes previously granted user access