elasticsearch/docs/reference/sql/security.asciidoc

[role="xpack"]
[[sql-security]]
== Security

{es-sql} integrates with security, if this is enabled on your cluster. 
In such a scenario, {es-sql} supports both security at the transport layer (by encrypting the communication between the consumer and the server) and authentication (for the access layer).

[discrete]
[[ssl-tls-config]]
==== SSL/TLS configuration

In case of an encrypted transport, the SSL/TLS support needs to be enabled in {es-sql} to properly establish communication with {es}. This is done by setting the `ssl` property to `true` or by using the `https` prefix in the URL. +
Depending on your SSL configuration (whether the certificates are signed by a CA or not, whether they are global at JVM level or just local to one application), might require setting up the `keystore` and/or `truststore`, that is where the _credentials_ are stored (`keystore` - which typically stores private keys and certificates) and how to _verify_ them (`truststore` - which typically stores certificates from third party also known as CA - certificate authorities). +
Typically (and again, do note that your environment might differ significantly), if the SSL setup for {es-sql} is not already done at the JVM level, one needs to setup the keystore if the {es-sql} security requires client authentication (PKI - Public Key Infrastructure), and setup `truststore` if SSL is enabled.

[discrete]
==== Authentication

The authentication support in {es-sql} is of two types:

Username/Password:: Set these through `user` and `password` properties.
PKI/X.509:: Use X.509 certificates to authenticate {es-sql} to {es}. For this, one would need to setup the `keystore` containing the private key and certificate to the appropriate user (configured in {es}) and the `truststore` with the CA certificate used to sign the SSL/TLS certificates in the {es} cluster. That is, one should setup the key to authenticate {es-sql} and also to verify that is the right one. To do so, one should set the `ssl.keystore.location` and `ssl.truststore.location` properties to indicate the `keystore` and `truststore` to use. It is recommended to have these secured through a password in which case `ssl.keystore.pass` and `ssl.truststore.pass` properties are required.

[discrete]
[[sql-security-permissions]]
==== Permissions (server-side)
On the server, one needs to add a few permissions to
users so they can run SQL. To run SQL, a user needs `read` and
`indices:admin/get` permissions at minimum while some parts of 
the API require `cluster:monitor/main`.

You can add permissions by <<defining-roles,creating a role>>, and assigning
that role to the user. Roles can be created using {kib}, an
<<sql-role-api-example,API call>> or the <<sql-role-file-example,`roles.yml`
configuration file>>. Using {kib} or the role management APIs is the preferred
method for defining roles. File-based role management is useful if you want to
define a role that doesn't need to change. You cannot use the role management
APIs to view or edit a role defined in `roles.yml`. 

[discrete]
[[sql-role-api-example]]
===== Add permissions with the role management APIs

This example configures a role that can run SQL in JDBC querying the `test`
index:

include::{xes-repo-dir}/rest-api/security/create-roles.asciidoc[tag=sql-queries-permission]

[discrete]
[[sql-role-file-example]]
===== Add permissions to `roles.yml`

This example configures a role that can run SQL in JDBC querying the `test` and `bort`
indices. Add the following to `roles.yml`:

[source, yaml]
--------------------------------------------------
include-tagged::{sql-tests}server/security/roles.yml[cli_drivers]
--------------------------------------------------