CREATE NOTIFIER

Creates a push-based notifier to send event notifications and messages out of Vertica.

Creates a push-based notifier to send event notifications and messages out of Vertica.

Syntax

CREATE NOTIFIER [ IF NOT EXISTS ] notifier-name ACTION 'notifier-type'
    [ ENABLE | DISABLE ]
    [ MAXPAYLOAD 'integer{K|M}' ]
    MAXMEMORYSIZE 'integer{K|M|G|T}'
    [ TLS CONFIGURATION tls-configuration ]
    [ TLSMODE 'tls-mode' ]
    [ CA BUNDLE bundle-name [ CERTIFICATE certificate-name ] ]
    [ IDENTIFIED BY 'uuid' ]
    [ [NO] CHECK COMMITTED ]
    [ PARAMETERS 'adapter-params' ]

Parameters

IF NOT EXISTS

If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

notifier-name
This notifier's unique identifier.
ACTION 'notifier-type'
String, the type of notifier, one of the following:

  • URL, with the following format, that identifies one or more target Kafka servers:

    kafka://kafka-server-ip-address:port-number

    To enable failover when a Kafka server is unavailable, specify additional hosts in a comma-delimited list. For example:

    kafka://192.0.2.0:9092,192.0.2.1:9092,192.0.2.2:9092

  • syslog: Notifications are sent to syslog. To use notifiers of this type, you must set the SyslogEnabled parameter:

    => ALTER DATABASE DEFAULT SET SyslogEnabled = 1

    Events monitored by this notifier type are not logged to MONITORING_EVENTS nor vertica.log.

  • sns: Notifications are sent to a Simple Notification Service (SNS) endpoint.

ENABLE | DISABLE
Specifies whether to enable or disable the notifier.

Default: ENABLE.

MAXPAYLOAD 'integer{K|M}'
The maximum size of the message, up to 10^9 bytes, specified in kilobytes or megabytes.

The following restrictions apply:

  • MAXPAYLOAD cannot be greater than MAXMEMORYSIZE.

  • If you configure syslog to send messages to a remote destination, ensure that MaxMessageSize (in /etc/rsyslog for rsyslog) is greater than or equal to MAXPAYLOAD.

  • The MAXPAYLOAD for SNS notifiers cannot exceed 256KB.

Defaults:

  • Kafka: 1M

  • syslog: 1M

  • SNS: 256K

MAXMEMORYSIZE 'integer{K|M|G|T}'
The maximum size of the internal notifier, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes.

MAXMEMORYSIZE must be greater than MAXPAYLOAD.

If the size of the message queue exceeds MAXMEMORYSIZE, the notifier drops excess messages.

TLS CONFIGURATION tls-configuration

The TLS CONFIGURATION to use for TLS.

Notifiers support the following TLS modes:

  • DISABLE

  • TRY_VERIFY (behaves like VERIFY_CA)

  • VERIFY_CA

  • VERIFY_FULL

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

  • SNSCAFile or AWSCAFile

  • SNSCAPath or AWSCAPath

  • SNSEnableHttps

TLSMODE 'tls-mode'

Specifies the type of connection between the notifier and an endpoint, one of the following:

  • disable (default): Plaintext connection.

  • verify-ca: Encrypted connection, and the server's certificate is verified as being signed by a trusted CA.

If you set this parameter to verify-ca, the generated TLS Configuration will be set to TRY_VERIFY, which has the same behavior as VERIFY_CA.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

  • SNSCAFile or AWSCAFile

  • SNSCAPath or AWSCAPath

  • SNSEnableHttps

CA BUNDLE bundle-name

Specifies a CA bundle. The certificates inside the bundle are used to validate the Kafka server's certificate if the TLSMODE requires it.

If a CA bundle is specified for a notifier that currently uses disable, which doesn't validate the Kafka server's certificate, the bundle will go unused when connecting to the Kafka server. This behavior persists unless the TLSMODE is changed to one that validates server certificates.

Changes to contents of the CA bundle take effect either after the notifier is disabled and re-enabled or after the database restarts. However, changes to which CA bundle the notifier uses takes effect immediately.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

  • SNSCAFile or AWSCAFile

  • SNSCAPath or AWSCAPath

  • SNSEnableHttps

CERTIFICATE certificate-name

Specifies a client certificate for validation by the endpoint.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

  • SNSCAFile or AWSCAFile

  • SNSCAPath or AWSCAPath

  • SNSEnableHttps

IDENTIFIED BY uuid
Specifies the notifier's unique identifier. If set, all the messages published by this notifier have this attribute.
[NO] CHECK COMMITTED
Specifies to wait for delivery confirmation before sending the next message in the queue.

Some messaging systems, like syslog, do not support delivery confirmation.

For SNS notifiers, CHECK COMMITTED must be specified, and NO CHECK COMMITTED behaves like CHECK COMMITTED.

PARAMETERS 'adapter-params'
Specifies one or more optional adapter parameters that are passed as a string to the adapter. Adapter parameters apply only to the adapter associated with the notifier.

For Kafka notifiers, refer to Kafka and Vertica configuration settings.

For syslog notifiers, specify the severity of the event with eventSeverity=severity, where severity is one of the following:

  • 0: Emergency

  • 1: Alert

  • 2: Critical

  • 3: Error

  • 4: Warning

  • 5: Notice

  • 6: Informational

  • 7: Debug

Most syslog implementations, by default, do not log events with a severity level of 7. You must configure syslog to record these types of events.

Parameters cannot be set for SNS notifiers.

Privileges

Superuser

Encrypted notifiers for SASL_SSL Kafka configurations

Follow this procedure to create or alter notifiers for Kafka endpoints that use SASL_SSL. Note that you must repeat this procedure whenever you change the TLSMODE, certificates, or CA bundle for a given notifier.

  1. Create a TLS Configuration with the desired TLS mode, certificate, and CA certificates.

  2. Use CREATE or ALTER to disable the notifier and set the TLS Configuration:

    => ALTER NOTIFIER encrypted_notifier
    DISABLE
    TLS CONFIGURATION kafka_tls_config;

  3. ALTER the notifier and set the proper rdkafka adapter parameters for SASL_SSL:

    => ALTER NOTIFIER encrypted_notifier PARAMETERS
    'sasl.username=user;sasl.password=password;sasl.mechanism=PLAIN;security.protocol=SASL_SSL';

  4. Enable the notifier:

    => ALTER NOTIFIER encrypted_notifier ENABLE;

Examples

Kafka notifiers

Create a Kafka notifier:

=> CREATE NOTIFIER my_dc_notifier
    ACTION 'kafka://172.16.20.10:9092'
    MAXMEMORYSIZE '1G'
    IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
    NO CHECK COMMITTED;

Create a notifier with an adapter-specific parameter:

=> CREATE NOTIFIER my_notifier
    ACTION 'kafka://127.0.0.1:9092'
    MAXMEMORYSIZE '10M'
    PARAMETERS 'queue.buffering.max.ms=1000';

Create a notifier that uses an encrypted connection and verifies the Kafka server's certificate with the CA certificates in the notifier_tls_config object:

=> CREATE NOTIFIER encrypted_notifier
    ACTION 'kafka://127.0.0.1:9092'
    MAXMEMORYSIZE '10M'
    TLS CONFIGURATION 'notifier_tls_config'

Syslog notifiers

The following example creates a notifier that writes a message to syslog when the Data collector (DC) component LoginFailures updates:

  1. Enable syslog notifiers for the current database:

    => ALTER DATABASE DEFAULT SET SyslogEnabled = 1;

  2. Create and enable a syslog notifier v_syslog_notifier:

    => CREATE NOTIFIER v_syslog_notifier ACTION 'syslog'
    ENABLE
    MAXMEMORYSIZE '10M'
    IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
    PARAMETERS 'eventSeverity = 5';

  3. Configure the syslog notifier v_syslog_notifier for updates to the LoginFailures DC component with SET_DATA_COLLECTOR_NOTIFY_POLICY:

    => SELECT SET_DATA_COLLECTOR_NOTIFY_POLICY('LoginFailures','v_syslog_notifier', 'Login failed!', true);

    This notifier writes the following message to syslog (default location: /var/log/messages) when a user fails to authenticate as the user Bob:

    Apr 25 16:04:58
vertica_host_01
vertica:
    Event Posted:
        Event Code:21
        Event Id:0
        Event Severity: Notice [5]
        PostedTimestamp: 2022-04-25 16:04:58.083063
        ExpirationTimestamp: 2022-04-25 16:04:58.083063
        EventCodeDescription: Notifier
        ProblemDescription: (Login failed!)
    {
       "_db":"VMart",
       "_schema":"v_internal",
       "_table":"dc_login_failures",
       "_uuid":"f8b0278a-3282-4e1a-9c86-e0f3f042a971",
       "authentication_method":"Reject",
       "client_authentication_name":"default: Reject",
       "client_hostname":"::1",
       "client_label":"",
       "client_os_user_name":"dbadmin",
       "client_pid":523418,
       "client_version":"",
       "database_name":"dbadmin",
       "effective_protocol":"3.8",
       "node_name":"v_vmart_node0001",
       "reason":"REJECT",
       "requested_protocol":"3.8",
       "ssl_client_fingerprint":"",
       "ssl_client_subject":"",
       "time":"2022-04-25 16:04:58.082568-05",
       "user_name":"Bob"
    }#012
    DatabaseName: VMart
    Hostname: vertica_host_01

For details on syslog notifiers, see Configuring reporting for syslog.

See also