Configure Audit Logging in YSQL
YugabyteDB YSQL uses PostgreSQL Audit Extension (
pgAudit) to provide detailed session and/or object audit logging via YugabyteDB TServer logging.
The goal of the YSQL audit logging is to provide YugabyteDB users with capability to produce audit logs often required to comply with government, financial, or ISO certifications. An audit is an official inspection of an individual’s or organization’s accounts, typically by an independent body.
Enabling Audit Logging
Step 1. Enable audit logging on YB-TServer
This can be done in one of the following ways.
Option A: Using
--ysql_pg_conf TServer flag
- Database administrators can leverage
ysql_pg_confto set appropriate values for pgAudit configuration.
- These configuration values are set when the YugabyteDB cluster is created and hence are picked up for all users and for every session.
Option B: Using YugabyteDB
- An alternative suggestion is to use the YB
SETcommand, which essentially changes the run-time configuration parameters.
SETonly affects the value used by the current session. A detailed description of the set command is illustrated in this webpage.
Step 2. Load the
Enable Audit logging in YugabyteDB clusters by creating the
pgaudit extension. Executing the below statement in a YSQL shell will enable Audit logging.
yugabyte=# CREATE EXTENSION IF NOT EXISTS pgaudit; CREATE EXTENSION
Customizing Audit Logging
YSQL Audit logging can be further customized by configuring the pgAudit flags as described below.
||Specifies which classes of statements will be logged by session audit logging.
The default is
The default is
The default is
||Specifies the master role to use for object audit logging. Multiple audit roles can be defined by granting them to the master role. This allows multiple groups to be in charge of different aspects of audit logging.
There is no default.
Use these steps to configure audit logging in a YugabyteDB cluster with bare minimum configurations.
1. Enable audit logging
Start the YugabyteDB Cluster with the following Audit logging Configuration.
``` --ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice,pgaudit.log_client=ON" ``` Alternatively, go to a YSQL shell and execute the following commands. ``` SET pgaudit.log='DDL'; SET pgaudit.log_client=ON; SET pgaudit.log_level=notice; ```
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-22.214.171.124-b0) Type "help" for help. yugabyte=# ```
pgaudit extension on the YugabyteDB cluster.
Connect to the database using the following: `yugabyte=> \c yugabyte yugabyte;` Create the `pgAudit` extension. ``` You are now connected to database "yugabyte" as user "yugabyte". yugabyte=# CREATE EXTENSION IF NOT EXISTS pgaudit; CREATE EXTENSION ```
3. Create a table, verify log
pgaudit.log='DDL' is configured,
CREATE TABLE YSQL statements will be logged and the corresponding log will be shown in the ysql client.
yugabyte=# create table employees ( empno int, ename text, address text, salary int, account_number text ); NOTICE: AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.employees, "create table employees ( empno int, ename text, address text, salary int, account_number text );",<not logged> CREATE TABLE
Notice that audit logs get generated for DDL statements.