Skip to main content

sql: Interacting with a SQL database | Enterprise OPA

The sql functions allow you to interact with many SQL databases, including any MySQL-compatible or PostgreSQL-compatible database.

These include:

  • CockroachDB
  • MariaDB
  • Microsoft SQL Server
  • MySQL
  • PlanetScale
  • PostgreSQL
  • SQLite
  • SingleStore (MemSQL)
  • Snowflake
  • TiDB
info

Check out our tutorial on implementing ABAC using SQL.

Shared Parameters

ParameterTypeRequiredDefaultDescription
driverStringYesmysql, postgres, sqlite, snowflake, sqlserver. The driver name of the queried database.
data_source_nameStringYesSee Data Source Name
max_open_connectionsIntegerNo0Maximum open connections to the database.
max_idle_connectionsIntegerNo2Maximum idle connections to the database.
connection_max_idle_timeIntegerNo0 (Indefinite)Maximum idle time (in seconds) of each connection.
connection_max_life_timeIntegerNo0 (Indefinite)Maximum life time (in seconds) of each connection.
max_prepared_statementsIntegerNo128Maximum number of prepared statements allowed in a query.
cacheBoolNofalseCache the results of queries.
cache_durationIntegerNo60Duration (in seconds) to keep cached query results.
raise_errorBoolNotrueSee Errors

Data Source Name

The data_source_name parameter is a string containing database connection information. Data source names (also called connection strings) often share a similar format:

scheme://username:password@host:port/dbname?param1=value1&param2=value2&...
caution

The data_source_name must not contain scheme, as that is determined by the provided driver configuration.

Check the documentation of the database type for the authoritative source on the format of the connection string, e.g.:

Errors

By default—and if raise_error is true—then an error returned will halt policy evaluation.

If raise_error is false, then the response object contains the error in an error key instead of its usual response.

{
"error": ...
}

sql.send

Example usage

subordinate := sql.send({
"driver": "sqlite",
"data_source_name": "/data/company.db",
"query": "SELECT * FROM subordinates WHERE manager = $1 AND subordinate = $2",
"args": [input.user, username],
})

Parameters

ParameterTypeRequiredDefaultDescription
queryStringYesQuery to execute against the database.
argsStringNoArguments supplied to the query.
row_objectBoolNofalseWhen set to true, the results of the query will be transformed into an object with column names as keys.

Example Response

Given the following table schema and values

CREATE TABLE T1 (id TEXT, description TEXT);
INSERT INTO T1(id, description) VALUES('A', 'B');

If row_object is false

{
"rows": [["A", "B"]]
}

If row_object is true

{
"rows": [{"id": "A", "description": "B"}]
}

Utility methods

Enterprise OPA comes with helper methods for using this builtin together with vault.send:

  1. mysql.send and mysql.send_opts
  2. postgres.send and postgres.send_opts

All of these methods are available in Enterprise OPA at data.system.eopa.utils.mysql.v1.vault and data.system.eopa.utils.postgres.v1.vault respectively.

package example

import data.system.eopa.utils.mysql.v1.vault as mysql
import data.system.eopa.utils.postgres.v1.vault as postgres

example_1 := mysql.send("SELECT * FROM users WHERE id = ?", [input.id])

# => {"rows": [ ... ]}

example_2 := postgres.send("SELECT * FROM users WHERE id = $1", [input.id])

# => {"rows": [ ... ]}

The utility methods will lookup connection data from a map it expects to find in Vault, under the path secret/mysql and secret/postgres:

{
"host": "...",
"port": "...",
"user": "...",
"password": "...",
}

For postgres.send, the TLS verification is configured by the key sslmode (defaults to require), for mysql.send it's tls (true).

If host or port are not defined, they default to localhost and port 3306 (MySQL), and 5432 (PostgreSQL).

To override the secret path within Vault, use:

package example
import data.system.eopa.utils.mysql.v1.vault as mysql

mysql_send(query, args) := result {
result := mysql.send(query, args)
with mysql.override.secret_path as "secret/prod/eopa-mysql"

example_3 := mysql_send("SELECT * FROM users WHERE id = ?", [input.x])
) # => {"rows": [ ... ]}

If you need to override the Vault address or token, you can use this:

package example
import data.system.eopa.utils.vault.v1.env as vault
import data.system.eopa.utils.mysql.v1.vault as mysql

mysql_send(query, args) := result {
result := mysql.send(query, args)
with mysql.override.secret_path as "secret/prod/eopa-mysql"
with vault.override.address as "localhost"
with vault.override.token as "dev-token-2"

example_4 := mysql_send("SELECT * FROM users WHERE id = ?", [input.x])
) # => {"rows": [ ... ]}

The same mechanism applies to postgres.send.

To control the caching and error raising behavior, cache, cache_duration, raise_error, and all other config keys can be passed to mysql.send_opts and postgres.send_opts as a third object argument:

package example
import data.system.eopa.utils.mysql.v1.vault as mysql

example_3 := mysql.send_opts("SELECT * FROM users WHERE id = ?", [input.x], {"cache": true, "cache_duration": "30s", "raise_error": false})
) # => {"rows": [ ... ]}