AWS Redshift Database audit logging
Overview
Amazon Redshift logs information about connections and user activities in your database. These logs help you to monitor the database for security and troubleshooting purposes, which is a process often referred to as database auditing. The logs are stored in Amazon S3 buckets. These provide convenient access with data security features for users who are responsible for monitoring activities in the database.
Amazon Redshift logs
Amazon Redshift logs information in the following log files:
- Connection log — logs authentication attempts, and connections and disconnections.
- User log — logs information about changes to database user definitions.
- User activity log — logs each query before it is run on the database.
Connection log
Logs authentication attempts, and connections and disconnections. The following table describes the information in the connection log.
| Column name | Description |
|---|---|
| event | Connection or authentication event. |
| recordtime | Time the event occurred. |
| remotehost | Name or IP address of remote host. |
| remoteport | Port number for remote host. |
| pid | Process ID associated with the statement. |
| dbname | Database name. |
| username | User name. |
| authmethod | Authentication method. |
| duration | Duration of connection in microseconds. |
| sslversion | Secure Sockets Layer (SSL) version. |
| sslcipher | SSL cipher. |
| mtu | Maximum transmission unit (MTU). |
| sslcompression | SSL compression type. |
| sslexpansion | SSL expansion type. |
| iamauthguid | The IAM authentication ID for the CloudTrail request. |
| application_name | The initial or updated name of the application for a session. |
User log
Records details for the following changes to a database user:
- Create user
- Drop user
- Alter user (rename)
- Alter user (alter properties)
| Column name | Description |
|---|---|
| userid | ID of user affected by the change. |
| username | User name of the user affected by the change. |
| oldusername | For a rename action, the original user name. For any other action, this field is empty. |
| action | Action that occurred. Valid values:AlterCreateDropRename |
| usecreatedb | If true (1), indicates that the user has create database privileges. |
| usesuper | If true (1), indicates that the user is a superuser. |
| usecatupd | If true (1), indicates that the user can update system catalogs. |
| valuntil | Password expiration date. |
| pid | Process ID. |
| xid | Transaction ID. |
| recordtime | Time in UTC that the query started. |
User activity log
Logs each query before it is run on the database.
| Column name | Description |
|---|---|
| recordtime | Time the event occurred. |
| db | Database name. |
| user | User name. |
| pid | Process ID associated with the statement. |
| userid | User ID. |
| xid | Transaction ID. |
| query | A prefix of LOG: followed by the text of the query, including newlines. |
Amazon Redshift enhanced VPC routing
When you use Amazon Redshift enhanced VPC routing, Amazon Redshift forces all COPY and UNLOAD traffic between your cluster and your data repositories through your Amazon VPC. By using enhanced VPC routing, you can use standard VPC features, such as VPC security groups, network access control lists (ACLs), VPC endpoints, VPC endpoint policies, internet gateways, and Domain Name System (DNS) servers, as described in the Amazon VPC User Guide. You use these features to tightly manage the flow of data between your Amazon Redshift cluster and other resources. When you use enhanced VPC routing to route traffic through your VPC, you can also use VPC flow logs to monitor COPY and UNLOAD traffic.
If enhanced VPC routing is not enabled, Amazon Redshift routes traffic through the internet, including traffic to other services within the AWS network.