DEV Community

Cover image for Enabling pgAudit, pgcrypto and scram-SHA-256 in Distributed SQL
YugabyteDB Distributed PostgreSQL Database profile image Jimmy Guerrero
Jimmy Guerrero for YugabyteDB Distributed PostgreSQL Database

Posted on • Edited on

Enabling pgAudit, pgcrypto and scram-SHA-256 in Distributed SQL

#opensource #sql #postgres #security

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.

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. 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"
Enter fullscreen mode Exit fullscreen mode

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 (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"'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

You can confirm the current user by running:

SELECT current_user;
 current_user
--------------
 yb_user
(1 row)
Enter fullscreen mode Exit fullscreen mode

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.

yb-tserver 
  <other flags> 
  --ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

To enable audit logging:

First load the extension by running the following SQL command.

CREATE EXTENSION IF NOT EXISTS pgaudit;
Enter fullscreen mode Exit fullscreen mode

Testing the audit logging feature:

Let us create a table by running the following.

create table test (a int);
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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 );
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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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-2.5.0.0-b0)
Type "help" for help.
yugabyte=#
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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:

Top comments (0)

Read next

jadelassery profile image

From Zero to Observability: Your first steps sending OpenTelemetry data to an Observability backend

Jade -

yiran_jing_d932aa8bc72a83 profile image

🔧 Embed SQL Editor ✍️ in Your Internal Web Portal 💻

Yiran Jing -

piotrkulpinski profile image

Top 5 Open-Source Projects for Developers

Piotr Kulpinski -

marcuskohlberg profile image

Encore Launch Week Day 1: Custom Middleware

Marcus Kohlberg -