Enabling pgAudit, pgcrypto and scram-SHA-256 in Distributed SQL
Tags: opensource, sql, postgres, security
Published: 2020-11-25 22:03:55

*This is a cross-post of my colleague Karthik Ranganathan's blog originally posted on the [Yugabyte blog.](https://blog.yugabyte.com/whats-new-in-yugabytedb-2-5-enterprise-grade-security-features/)*

The YugabyteDB 2.5 release adds many critical enterprise-grade security features. This blog post outlines these newly added features.

## Authentication
### Adding scram-sha-256 authentication
The first notable addition is the addition of a much improved, password-based authentication mechanism called Salted Challenge Response Authentication Mechanism (or simply SCRAM) as described in [RFC5802](https://tools.ietf.org/html/rfc5802). This scram-sha-256 authentication mechanism, identical to the most secure PostgreSQL authentication schema, is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure. To enable this feature, simply pass the following flag when starting the yb-tserver.

```
yb-tserver 
  <other flags> 
  --ysql_pg_conf="password_encryption=scram-sha-256"
```

Note that this is supported only by the YSQL API. Read more about [scram-sha-256 authentication mechanism in YugabyteDB documentation.](https://docs.yugabyte.com/latest/secure/authentication/password-authentication/)

### LDAP authentication
The Lightweight Directory Access Protocol, more commonly abbreviated to LDAP, is an open industry standard for authentication of users. This allows users to use a single password to connect to multiple services, including connecting to databases and running queries against them. With this release, the YSQL API can be configured to authenticate users using LDAP as the password verification method.

Enabling LDAP authentication in YugabyteDB internally sets up two host-based authentication rules. Configure YugabyteDB to work with an external LDAP directory service for authentication works as follows:

#### Setting up roles and permissions for LDAP users:
The first rule allows connecting to the database as the admin user (which is `yugabyte` by default) from the localhost (`127.0.0.1`) using password authentication. This allows administrators to immediately log in with the admin user credentials and set up the required roles and permissions for the LDAP users.

This can be accomplished by starting the yb-tserver processes with the following flag:

```
--ysql_hba_conf_csv='host all yugabyte 127.0.0.1/0 password,"host   all all 0.0.0.0/0 ldap ldapserver=<ldap-server-url> ldapprefix=""uid="" ldapsuffix="", dc=example, dc=com"" ldapport=389"'
```
The above would generate the following `ysql_hba.conf` internal configuration:

```
# This is an autogenerated file, do not edit manually!
host all yugabyte 127.0.0.1/0 trust
host   all         all      0.0.0.0/0  ldap ldapserver=<ldap-server-url> ldapprefix="uid=" ldapsuffix=", dc=example, dc=com" ldapport=389
```
#### Configure LDAP authentication for all other user/host pairs:
The second rule configures authentication for all other additional user/host pairs by using simple bind with a uid-based username (`ldapprefix`) and a suffix defining the domain component (dc). These should be provided by the LDAP provider.

Connect to the database using `ysqlsh` and create one or more of the roles required, as shown in the example below.

```
CREATE ROLE yb_user WITH LOGIN;
GRANT ALL ON DATABASE yugabyte TO yb_user;
```

#### Connect using LDAP authentication:
To test connecting to the database using LDAP, simply connect with an LDAP user, as shown below and enter the password when prompted.

```
./bin/ysqlsh -U yb_user
```

You can confirm the current user by running:

```
SELECT current_user;
 current_user
--------------
 yb_user
(1 row)
```

The LDAP authentication scheme can operate in a simple bind mode or the search-and-bind mode, as well as using a secure connection using TLS encryption between PostgreSQL and the LDAP server using the `ldaptls` option. Read more about [using YugabyteDB with LDAP.](https://docs.yugabyte.com/latest/secure/authentication/ldap-authentication/)

## Audit Logging
Audit logging allows administrators and users to track activity related to data access, such as who accessed which portions of data, helping to understand the extent of a breach and sometimes even identify the attackers. Retaining audit logs is also a compliance requirement not only in regulated industries, but also increasingly in scenarios where data privacy laws like GDPR take effect. Audit logging in YugabyteDB will write the output on each node to the standard logging facility, similar to the design of PostgreSQL. These partial log files can subsequently be merged for a global audit trail. This feature is supported for both the YSQL and YCQL APIs. You can read more about [audit logging in YugabyteDB in the documentation.](https://docs.yugabyte.com/latest/secure/audit-logging/)

The YSQL API internally uses the [pgAudit extension](https://www.pgaudit.org/) to provide detailed session-level and object-level audit logging. It is possible to both filter what gets logged, as well as configure the output format. The extension is preinstalled in YugabyteDB, so using this feature is easy.

### To configure audit logging:
Pass the following flag to the `yb-tserver` as shown below.

```
yb-tserver 
  <other flags> 
  --ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"
```

Alternatively, this feature can also be configured with the `SET` command to change runtime parameters, as shown below.

```
SET pgaudit.log='DDL'; 
SET pgaudit.log_client=ON;
SET pgaudit.log_level=notice;
```
### To enable audit logging:

First load the extension by running the following SQL command.

```
CREATE EXTENSION IF NOT EXISTS pgaudit;
```

### Testing the audit logging feature:

Let us create a table by running the following.

```
create table test (a int);
```

The following entry should get written to the standard log.

```
NOTICE:  AUDIT: SESSION,4,1,DDL,CREATE TABLE,TABLE,public.tmp,"CREATE TABLE test (a int);",<not logged>
```

### Customizing audit logging:
There are a number of configuration options available to customize this feature, for example specifying the classes of statements that should be logged, the output format, etc.

## Column Level Permissions

Column level security can be used to specify the exact set of columns a user can access or modify (`SELECT, INSERT, UPDATE` privileges) using the `GRANT` command. This would effectively prevent the user from seeing or updating all the other columns. You can learn more about [column level security in the YugabyteDB documentation.](https://docs.yugabyte.com/latest/secure/authorization/column-level-security/)

An example is shown below.

### Create an employee table and insert few sample rows:

```
yugabyte=# create table employees ( empno int, ename text, address text, salary int, account_number text );
CREATE TABLE
 
yugabyte=# insert into employees values (1, 'joe', '56 grove st',  20000, 'AC-22001' );
INSERT 0 1
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, 'AC-48901' );
INSERT 0 1
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, 'AC-77051' );
INSERT 0 1
 
yugabyte=# select * from employees;
 empno | ename |    address    | salary | account_number
-------+-------+---------------+--------+----------------
     1 | Joe   | 56 grove st   |  20000 | AC-22001
     2 | Mike  | 129 81 st     |  80000 | AC-48901
     3 | Julia | 1 finite loop |  40000 | AC-77051
(3 rows)
```

### Create a user `ybadmin` with column-level privileges on the table above:

Assume we want to prevent this `ybadmin` user from viewing sensitivity information of employees, such as `salary` and `account_number`. This can be done as follows by using the following `GRANT` statement.

```
yugabyte=> \c yugabyte yugabyte;
You are now connected to database "yugabyte" as user "yugabyte".
 
yugabyte=# create user ybadmin;
CREATE ROLE
 
yugabyte=# grant select (empno, ename, address) on employees to ybadmin;
GRANT
```

### Verify column-level permissions:

The `ybadmin` user will now be able to access only the columns to which permissions were granted. This can be verified as shown below.

```
yugabyte=# \c yugabyte ybadmin;
You are now connected to database "yugabyte" as user "ybadmin".
 
yugabyte=> select empno, ename, address from employees;
 empno | ename |    address
-------+-------+---------------
     1 | joe   | 56 grove st
     3 | julia | 1 finite loop
     2 | mike  | 129 81 st
(3 rows)
```

The `ybadmin` will still be denied if user tries to access other columns:

```
yugabyte=> select empno, ename, address, salary from employees;
ERROR:  permission denied for table employees
```

## Encryption of data

In addition to the natively supporting [encryption of data at rest](https://docs.yugabyte.com/latest/secure/encryption-at-rest/), the 2.5 release adds a number of other data encryption related features. YugabyteDB now interoperates with Vormetric Transparent Encryption (VTE) to secure sensitive data allowing users to protect themselves from a wide range of risks from malicious hackers to database administrators with privileged data access. Encrypted backups are now supported by YugabyteDB, enabling backing up sensitive data securely.

### Column-level encryption

Column-level encryption is also now supported by YugabyteDB (YSQL only). It uses the [pgcrypto](https://www.postgresql.org/docs/10/pgcrypto.html) module to allow only the columns containing sensitive data to be encrypted before storing them on disk. The client supplies the decryption key and the data is decrypted on the server and then sent to the client. You can read more about [column-level encryption](https://docs.yugabyte.com/latest/secure/column-level-encryption/) in the YugabyteDB docs.

Let’s run through an example of symmetric column-level encryption.

#### Enable `pgcrypto` extension:

Open the YSQL shell (ysqlsh), specifying the `yugabyte` user and prompting for the password.

$ ./ysqlsh -U yugabyte -W
When prompted for the password, enter the yugabyte password. You should be able to login and see a response like below.

```
ysqlsh (11.2-YB-2.5.0.0-b0)
Type "help" for help.
yugabyte=#
```

Enable `pgcrypto` extension on the YugabyteDB cluster `yugabyte=> \c yugabyte yugabyte;`

```
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION
```

#### Insert using PGP_SYM_ENCRYPT

Create `employees` table and insert data into the table using `PGP_SYM_ENCRYPT` function for columns that need to be encrypted.

```
yugabyte=# create table employees ( empno int, ename text, address text, salary int, account_number text );
CREATE TABLE
```

In this example, account numbers of `employees` table will be encrypted using `PGP_SYM_ENCRYPT` function.

```
yugabyte=# insert into employees values (1, 'joe', '56 grove st',  20000, PGP_SYM_ENCRYPT('AC-22001', 'AES_KEY'));
INSERT 0 1
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, PGP_SYM_ENCRYPT('AC-48901', 'AES_KEY'));
INSERT 0 1
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, PGP_SYM_ENCRYPT('AC-77051', 'AES_KEY'));
INSERT 0 1
```

#### Verify column encryption:

Review the encrypted `account_number` data, as shown below

```
yugabyte=# select ename, account_number from employees limit 1;
 ename |               account_number
-------+-------------------------------------------------
 joe   | \xc30d04070302ee4c6d5f6656ace96ed23901f56c717d4e
 162b6639429f516b5103acebc4bc91ec15df06c30e29e6841f4a5386
 e7698bfebb49a8660f9ae4b3f34fede3f28c9c7bb245bd
(1 rows)
```

#### Query using `PGP_SYM_DECRYPT`

Decrypt the account numbers using `PGP_SYM_DECRYPT` function as shown here. In order to retrieve the encrypted column data, use `PGP_SYM_DECRYPT` function to decrypt the data. The Decryption function needs to be used in both `SELECT` and `WHERE` clause depending on the query.

To allow the decryption, the field name is also casted to the binary data type with the syntax: `account_number:bytea.`

```
yugabyte=# select PGP_SYM_DECRYPT(account_number::bytea, 'AES_KEY') as AccountNumber
           from employees;
 accountnumber
---------------
 AC-22001
 AC-48901
 AC-77051
(3 rows)
```

## What’s Next
We’re very happy to be able to release all of these latest and greatest security features into YugabyteDB 2.5. We invite you to learn more and try it out:

* Register for the upcoming webinar, ["Enterprise Security Features in YugabyteDB,"](https://yugabyte.zoom.us/webinar/register/WN_OFxukOzGTyqa9r8Z7iVWIQ) taking place on December 8 at 11 am Pacific
* [Read the YugabyteDB 2.5 release announcement](https://blog.yugabyte.com/announcing-yugabytedb-2-5/)
* [Install YugabyteDB 2.5](https://download.yugabyte.com/) in mere minutes
* [Join us in Slack](http://yugabyte.com/slack)