This is a cross-post of my colleague Karthik Ranganathan's blog originally posted on the Yugabyte blog.
The YugabyteDB 2.5 release adds many critical enterprise-grade security features. This blog post outlines these newly added features.
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.
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.
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:
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
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;
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.
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.
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;
First load the extension by running the following SQL command.
CREATE EXTENSION IF NOT EXISTS pgaudit;
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>
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 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.
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)
Assume we want to prevent this
ybadmin user from viewing sensitivity information of employees, such as
account_number. This can be done as follows by using the following
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
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)
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
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 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.
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-188.8.131.52-b0) Type "help" for help. yugabyte=#
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
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
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
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)
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
WHERE clause depending on the query.
To allow the decryption, the field name is also casted to the binary data type with the syntax:
yugabyte=# select PGP_SYM_DECRYPT(account_number::bytea, 'AES_KEY') as AccountNumber from employees; accountnumber --------------- AC-22001 AC-48901 AC-77051 (3 rows)
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: