@oasislabs/dpclient

dpclient

The PrivateSQL admin CLI is a command line interface for use by database and privacy administrators to configure the PrivateSQL system for data sharing. It works by interacting with the PrivateSQL service run by Oasis at https://dp.oasislabs.com.

Usage

Usage: dpclient <command> [options]

Commands:
  dpclient add-user                    Add user with their email address and
                                       desired role
  dpclient delete-user                 Delete user with user UUID
  dpclient get-schema                  Get the current PrivateSQL schema.
  dpclient set-schema                  Set the current PrivateSQL schema.
  dpclient get-schema-for-table        Get the current PrivateSQL schema for a
                                       given table.
  dpclient set-schema-for-table        Set the current PrivateSQL schema for a
                                       given table.
  dpclient delete-schema-for-table     Delete the current PrivateSQL schema for
                                       a given table.
  dpclient autogenerate                Autogenerate the PrivateSQL schema for a
                                       given table.
  dpclient get-budget                  Get the current privacy budget.
  dpclient set-budget                  Set the current privacy budget. Note this
                                       is db wide.
  dpclient query                       Run query
  dpclient preview                     Generate a preview table with
                                       differentially private synthetic data.
  dpclient queryPreview                Run query on a preview table.
  dpclient status                      Status of the server
  dpclient generate-completion-script  generate completion script

Options:
  --help     Show help                                                 [boolean]
  --version  Show version number                                       [boolean]

Installing

To install dpclient:

• Make sure that you've installed Node.js v18 or higher.

  $ node -v
  v19.5.0
  

• Log in to npm locally. You'll need to be logged into NPM in order to access dpclient, which will remain private and accessible only to the members of the PrivateSQL admin community.

  npm login
  

• Install the CLI

  npm i -g @oasislabs/dpclient
  

Getting Started

Configuring your credentials

Prior to using dpclient, you will need to set the following environment variables:

• DP_ADMIN_UUID: the client UUID we sent you.

• DP_ADMIN_SECRET: the secret that we sent you.

• PRIVATESQL_ENDPOINT: either https://dp.oasislabs.com or https://dp.oasiscloud-staging.net.

The rest of this README assumes that the values for these environment variables have been set to the correct values for your identity.

Running Commands

Now you're ready to start running commands and interacting with the PrivateSQL platform. Remember, you will need to have your credentials exported as environment variables in order for these commands to work. The CLI will read in your credentials and use them to authenticate with the PrivateSQL server.

User management

PrivateSQL supports the following roles, DB_ADMIN, PRIV_ADMIN, and RESEARCH. The dpclient CLI is only available to the DB_ADMIN and PRIV_ADMIN roles. An admin can add other admins or research roles and manage these users using the following commands:

dpclient add-user --email [e-mail] --role RESEARCH
# sample output:
{
  "id": "c307e697-8773-4304-ac40-5927f02f022a",
  "secret": "dxzkfm5l6btpu8uxl2nzqxum7wu9kw"
}

dpclient delete-user -i c307e697-8773-4304-ac40-5927f02f022a
# output:
"ok"

Schema management

PrivateSQL requires a schema to be specified for each of the tables that are shared. For more on the Schema specification please refer to our Schema parameters documentation. The following commands can be used to manage PrivateSQL schemas:

dpclient get-schema --db osfdb
# sample output:
{
  "database": "osfdb",
  "dialect": "postgresql",
  "namespace": "",
  "tables": [
    {
      "table": "diseaseinfo_startdate_domain",
...
        {
          "name": "age",
          "clipUpper": 76,
          "binsStart": 42,
          "binsDistribution": "constant",
          "binsEnd": 76,
          "typeName": "INTEGER",
          "clipLower": 42,
          "binsStep": 0.34
        }
      ]
    }
  ]
}

dpclient set-schema --schema schema.json
# output:
"ok"

You can also get and set the schema for a specific table that is shared using the commands get-schema-for-table, set-schema-for-table, and delete-schema-for-table.

PrivateSQL supports an autogeneration capability to automatically generate a PrivateSQL schema. You can do this using the following command:

dpclient autogenerate --database osfdb --table diseaseinfo
# output:
<The PrivateSQL schema for the diseaseinfo table>

Budget management

You can get and set the current privacy budget parameters. Note that at the moment these parameters are database wide and apply to all researchers that query the database.

dpclient get-budget
# output:
{
  "epsilon": 0.1,
  "total": 100
}

dpclient set-budget -b '{"epsilon": 0.2, "total": 50}'
# output:
"ok"

Run queries

dpclient query --db osfdb --query "select avg(age), min(age), max(age) from CovidOncology.data group by race"
# output:
[
  [ '_agg0', '_agg1', '_agg2' ],
  [ '59', '55', '66' ],
  [ '60', '55', '68' ],
  [ '51', '43', '58' ],
  [ '53', '43', '65' ],
  [ '61', '44', '64' ],
  [ '59', '53', '67' ]
]

Preview capabilities

PrivateSQL supports a preview capability that can be used to generate synthetic samples from the actual data using differential privacy. For more on our preview capabilities please refer to the documentation at Preview generation.

The following command can be used to generate previews:

dpclient preview -d osfdb -t diseaseinfo -p diseaseinfo_preview -n 1000
# output:
"ok"

A researcher can query the preview tables with any SQL query that is supported by the underlying database. There are no restrictions on the types of queries that can be run on preview tables as they are synthetic samples generated using differentially private mechanisms. Furthermore, there is no cost in privacy budget to run queries on the preview tables.

Utility functions

The dpclient CLI also supports a few utility functions as described below.

Server status

You can get the server status at any time using the following command:

csclient status
# sample output:
{
  "status": "PrivateSQL server initialized",
  <put more stuff here>
}

Local builds with github access

To build the client from sources please run the following commands, once you have node installed per instructions at the head of this document.

cd qr/tools
yarn
yarn build