The Distributed SQL Blog

Thoughts on distributed databases, open source and cloud native

What’s New in YugabyteDB 2.5 – Enterprise-Grade Security Features

Founder & CTO

YugabyteDB Enterprise-Grade Security Features

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


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. 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.

  <other flags> 

Note that this is supported only by the YSQL API. Read more about scram-sha-256 authentication mechanism in YugabyteDB documentation.

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 ( 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 password,"host   all all 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 trust
host   all         all  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.

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;
(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.

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.

The YSQL API internally uses the pgAudit extension 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.

  <other flags> 

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.


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.

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 );
yugabyte=# insert into employees values (1, 'joe', '56 grove st',  20000, 'AC-22001' );
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, 'AC-48901' );
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, 'AC-77051' );
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;
yugabyte=# grant select (empno, ename, address) on employees to ybadmin;

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, 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 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 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-
Type "help" for help.

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

You are now connected to database "yugabyte" as user "yugabyte".

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 );

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'));
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, PGP_SYM_ENCRYPT('AC-48901', 'AES_KEY'));
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, PGP_SYM_ENCRYPT('AC-77051', 'AES_KEY'));

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
(1 rows)


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;
(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:

Related Posts

Founder & CTO