This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

HTTPS service

The HTTPS service lets clients securely access and manage a Vertica database with a REST API. This service listens on port 8443 and runs on all nodes.

Most HTTPS service endpoints require authentication, and only the dbadmin user can authenticate to the HTTPS service. The following endpoints serve documentation on the endpoints and do not require authentication (unless your TLSMODE is VERIFY_CA):

  • /swagger/ui
  • /swagger/{RESOURCE}
  • /api-docs/oas-3.0.0.json

This service encrypts communications with mutual TLS (mTLS). To configure mTLS, you must alter the server TLS configuration with a server certificate and a trusted Certificate Authority (CA). For mTLS authentication, each client request must include a certificate that is signed by the CA in the server TLS configuration and specifies the dbadmin user in the Common Name (CN). For additional details about these TLS components and Vertica, see TLS protocol.

Password authentication

The following command connects to the HTTPS service from outside the cluster with the username and password:

$ curl --insecure --user dbadmin:db-password https://10.20.30.40:8443/endpoint

Certificate authentication

Client requests authenticate to the HTTPS service with a private key and certificate:

$ curl https://10.20.30.40:8443/endpoint \
    --key path/to/client_key.key \
    --cert path/to/client_cert.pem \

When the Vertica server receives the request, it verifies that the client certificate is signed by a trusted CA and specifies the dbadmin user. To establish this workflow, you must complete the following:

  • Alter the server TLS configuration with a server certificate and a CA.
  • Generate a client certificate that is signed by the CA in the server TLS configuration. The client certificate SUBJECT must specify the dbadmin user.
  • Grant TLS access to the database.

Create a CA certificate

A CA is a trusted entity that signs and validates other certificates with its own certificate. The following example generates a self-signed root CA certificate:

  1. Generate or import a private key. The following command generates a new private key:

          
    => CREATE KEY ca_private_key TYPE 'RSA' LENGTH 4096;
    CREATE KEY
    
    

  2. Generate the certificate with the following format. Sign the certificate the with the private key that you generated or imported in the previous step:

          
    => CREATE CA CERTIFICATE ca_certificate
    SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica Root CA'
    VALID FOR days_valid
    EXTENSIONS 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsComment' = 'Vertica generated root CA cert'
    KEY ca_private_key;
    
    

    For example:

          
    => CREATE CA CERTIFICATE SSCA_cert
    SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=Vertica Root CA'
    VALID FOR 3650
    EXTENSIONS 'nsComment' = 'Self-signed root CA cert'
    KEY SSCA_key;
    
    

Create the server certificate

The server private key and certificate verify the Vertica server's identity for clients:

  1. Generate the server private key:

          
    => CREATE KEY server_private_key TYPE 'RSA' LENGTH 2048;
    CREATE KEY
        
    
  2. Generate the server certificate with the following format. Include the server_private_key, and sign it with the CA certificate:

          
    => CREATE CERTIFICATE server_certificate
    SUBJECT '/C=country_code/ST=state_or_province/L=locality/O=organization/OU=org_unit/CN=Vertica server certificate'
    SIGNED BY ca_certificate
    KEY server_private_key;
    CREATE CERTIFICATE
    
    

    For example:

          
    => CREATE CERTIFICATE server_certificate
    SUBJECT '/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=Vertica server certificate'
    SIGNED BY ca_certificate
    KEY server_private_key;
    CREATE CERTIFICATE
    
    

Alter the TLS configuration

After you generate the server certificate, you must alter the server's default TLS configuration with the server certificate and its CA. When you change the server TLS configuration, the HTTPS service restarts, and the new keys and certificates are added to the catalog and distributed to the nodes in the cluster:

  1. Alter the default server configuration. Mutual TLS requires that you set TLSMODE to TRY_VERIFY or VERIFY_CA. If you use VERIFY_CA, all endpoints (including the documentation-related endpoints /swagger/ui, /swagger/{RESOURCE}, and /api-docs/oas-3.0.0.json) require authentication:

          
    => ALTER TLS CONFIGURATION server CERTIFICATE server_certificate ADD CA CERTIFICATES ca_certificate TLSMODE 'VERIFY_CA';
    ALTER TLS CONFIGURATION
    
  2. Verify the changes on the TLS configuration object:

     => SELECT name, certificate, ca_certificate, mode FROM TLS_CONFIGURATIONS WHERE name='server';
       name  |    certificate     | ca_certificate |    mode
     --------+--------------------+----------------+------------
      server | server_certificate | ca_certificate | VERIFY_CA
     (1 row)
    

Create the client certificate

The client private key and certificate verifythe client's identity for requests. Generate a client private key and a client certificate that specifies the dbadmin user, and sign the client certificate with the same CA that signed the server certificate.

The following steps generate a client key and certificate, and then make them available to the client:

  1. Generate the client key:

          
       => CREATE KEY client_private_key TYPE 'RSA' LENGTH 2048;
       CREATE KEY
       
    

  2. Generate the client certificate. Mutual TLS requires that the Common Name (CN) in the SUBJECT specifies a database username:

          
       => CREATE CERTIFICATE client_certificate
       SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=OpenText/OU=Vertica/CN=dbadmin/emailAddress=example@example.com'
       SIGNED BY ca_certificate
       EXTENSIONS 'nsComment' = 'Vertica client cert', 'extendedKeyUsage' = 'clientAuth'
       KEY client_private_key;
       CREATE CERTIFICATE
       
    

  3. On the client machine, export the client key and client certificate to the client filesystem. The following commands use the vsql client:

    $ vsql -At -c "SELECT key FROM cryptographic_keys WHERE name = 'client_private_key';" -o client_private_key.key
    $ vsql -At -c "SELECT certificate_text FROM certificates WHERE name = 'client_certificate';" -o client_cert.pem
    

    In the preceding command:

    • -A: enables unaligned output.
    • -t: prevents the command from outputting metadata, such as column names.
    • -c: instructs the shell to run one command and then exit.
    • -o: writes the query output to the specified filename.

    For details about all vsql command line options, see Command-line options

  4. Copy or move the client key and certificate to a location that your client recognizes.

    The following commands move the client key and certificate to the hidden directory ~/.client-creds, and then grants the file owner read and write permissions with chmod:

    $ mkdir ~/.client-creds
    $ mv client_private_key.key ~/.client-creds/client_key.key
    $ mv client_cert.pem ~/.client-creds/client_cert.pem
    $ chmod 600 ~/.client-creds/client_key.key ~/.client-creds/client_cert.pem
    

Create an authentication record

Next, you must create an authentication record in the database. An authentication record defines a set of authentication and the access methods for the database. You grant this record to a user or role to control how they authenticate to the database:

  1. Create the authentication record. The tls method requires that clients authenticate with a certificate whose Common Name (CN) specifies a database username:
          
       => CREATE AUTHENTICATION auth_record METHOD 'tls' HOST TLS '0.0.0.0/0';
       CREATE AUTHENTICATION
       
    
  2. Grant the authentication record to a user or to a role. The following example grants the authentication record to PUBLIC, the default role for all users:
          
       => GRANT AUTHENTICATION auth_record TO PUBLIC;
       GRANT AUTHENTICATION
       
    

After you grant the authentication record, the user or role can access HTTPS service endpoints.

1 - HTTPS endpoints

The HTTPS service exposes general-purpose endpoints for interacting with your database. While most endpoints require authentication with either certificates or the dbadmin's password, the following endpoints for documentation do not:

  • /v1/version
  • /swagger/ui
  • /swagger/{RESOURCE}
  • /api-docs/oas-3.0.0.json

To view a list of all endpoints, enter the following URL in your browser:

https://database_hostname_or_ip:8443/swagger/ui?urls.primaryName=server_docs

/v1/metrics (GET)

Vertica exposes time series metrics for Prometheus monitoring and alerting. These metrics create a detailed model of your database behavior over time to provide valuable performance and troubleshooting insights.

To retrieve time series metrics for a node, send a GET request to /v1/metrics:

$ curl https://host:8443/v1/metrics \
    --key path/to/client_key.key \
    --cert path/to/client_cert.pem \

Vertica scrapes metrics from the node and outputs the metrics in Prometheus text-based exposition format. This format applies context-specific labels to each metric to help group metrics when you visualize your data. It also describes the metric type—Vertica provides counter, gauge, and histogram metric types. The following example outlines the output format:

# HELP metric-name metric-defintion
# TYPE metric-name metric-type
metric-name{label-key="label-value"[, ...]} metric-value

For example, the following example shows a snippet of the request response that provides details about the vertica_resource_pool_memory_size_actual_kb metric:

$ curl https://10.20.30.40:8443/v1/metrics \
    --key path/to/client_key.key \
    --cert path/to/client_cert.pem \
...
# HELP vertica_resource_pool_memory_size_actual_kb Current amount of memory (in kilobytes) allocated to the resource pool by the resource manager.
# TYPE vertica_resource_pool_memory_size_actual_kb gauge
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="metadata",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 84381
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="blobdata",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 0
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="jvm",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 0
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="sysquery",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 336970
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="tm",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 336970
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="general",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 5981079
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="recovery",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 0
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="dbd",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 0
vertica_resource_pool_memory_size_actual_kb{node_name="v_vmart_node0001",pool_name="refresh",revive_instance_id="114b25c4aab6fec8c26b121cff2b52"} 0
...

To get a cluster-wide view of your metrics, you must call the /v1/metrics endpoint on each node in your cluster.

For a comprehensive list of metrics, see Prometheus metrics.

Grafana dashboards

You can visualize data exposed at /v1/metrics with Grafana dashboards. Vertica provides the following dashboards for metrics that use a Prometheus data source:

You can also download the source for each dashboard from the vertica/grafana-dashboards repository.

2 - Prometheus metrics

The following table describes the metrics available at https://host:8443/v1/metrics/.

Name Type Description
vertica_allocator_total_size_bytes gauge Amount of bytes consumed in an allocator pool.
vertica_build_info gauge Shows information about the Vertica build through labels.
vertica_cpu_aggregate_usage_percentage gauge Aggregate CPU usage, expressed as a percentage of total CPU capacity.
vertica_data_size_compressed_mb gauge Total compressed size (in megabytes) of the data.
vertica_data_size_estimation_error_mb gauge Margin of error (in megabytes) of the estimated raw data size.
vertica_db_info gauge Shows information about the current database through labels.
vertica_depot_evictions_bytes counter Total size (in bytes) of depot evictions.
vertica_depot_evictions_total counter Number of depot evictions.
vertica_depot_fetch_queue_size gauge Number of files in the depot's fetch queue.
vertica_depot_fetches_bytes counter Total size (in bytes) of successful depot fetches.
vertica_depot_fetches_failures_total counter Number of failed depot fetch requests.
vertica_depot_fetches_ms histogram Time (in milliseconds) that it takes to fetch files into the depot.
vertica_depot_fetches_requests_total counter Number of depot fetch requests.
vertica_depot_lookup_hits_total counter Number of cache hits when finding a file in the depot.
vertica_depot_lookup_requests_total counter Number of attempts to find a file in the depot.
vertica_depot_max_size_bytes gauge Maximum size (in bytes) of the depot.
vertica_depot_size_bytes gauge Number of bytes currently used in the depot.
vertica_depot_uploads_bytes counter Number of bytes uploaded to persistent storage.
vertica_depot_uploads_failures_total counter Number of failures during upload attempts to persistent storage.
vertica_depot_uploads_in_progress_bytes gauge Number of bytes in running requests that are uploading a file to persistent storage.
vertica_depot_uploads_in_progress_counter gauge Number of requests currently uploading a file to persistent storage.
vertica_depot_uploads_ms histogram Time (in milliseconds) it took to upload files to persistent storage.
vertica_depot_uploads_queued_bytes gauge Number of bytes in queued requests to upload a file to persistent storage.
vertica_depot_uploads_queued_counter gauge Number of queued requests to upload a file to persistent storage.
vertica_depot_uploads_requests_total counter Number of file upload attempts to persistent storage.
vertica_depot_usage_percent gauge Current size of the depot, expressed as a percentage of max depot size.
vertica_disk_storage_free_mb gauge Number of megabytes of free storage available.
vertica_disk_storage_free_percent gauge Amount of free storage available, expressed as a percentage of total disk storage.
vertica_disk_storage_latency_seek_per_second gauge Measures a storage location's performance in seeks/sec. 1/latency is the time that it takes to seek to the data.
vertica_disk_storage_throughput_mb_per_second gauge Measures a storage location's performance in MBps. 1/throughput is the time that it takes to read 1MB of data.
vertica_disk_storage_total_mb gauge Number of megabytes of total disk storage.
vertica_disk_storage_used_mb gauge Number of megabytes of disk storage in use.
vertica_errors counter Number of errors, by error level and error code.
vertica_estimated_data_size_raw_mb gauge Estimation (in megabytes) of the total raw data size. This is computed each time there is an audit.
vertica_file_system_attempted_operations_total gauge Number of attempted file system operations.
vertica_file_system_data_reads_total gauge Number of read operations, such as S3 GET requests, to download files.
vertica_file_system_data_writes_total gauge Number of write operations, such as S3 PUT requests, to upload files.
vertica_file_system_downstream_bytes gauge Number of bytes received.
vertica_file_system_failed_operations_total gauge Number of failed filesystem operations.
vertica_file_system_metadata_reads_total gauge Number of requests to read metadata. For example, S3 list bucket and HEAD requests are metadata reads.
vertica_file_system_metadata_writes_total gauge Number of requests to write metadata. For example, S3 POST and DELETE requests are metadata writes.
vertica_file_system_open_files_counter gauge Number of currently open files.
vertica_file_system_overall_average_latency_ms gauge Average HTTP request latency in milliseconds.
vertica_file_system_overall_downstream_throughput_mb_s gauge Average downstream throughput in megabytes per second.
vertica_file_system_overall_upstream_throughput_mb_s gauge Average upstream throughput in megabytes per second.
vertica_file_system_reader_counter gauge Number of currently running read operations.
vertica_file_system_retries_total gauge Number of retry events.
vertica_file_system_total_request_duration_ms gauge Sum of HTTP request latency in milliseconds.
vertica_file_system_upstream_bytes gauge Number of bytes sent.
vertica_file_system_writer_counter gauge Number of currently running writer operations.
vertica_is_readonly gauge Returns whether the nodes are read-only.
vertica_last_audit_end_time gauge Time (in milliseconds) that the last audit ended.
vertica_last_catalog_sync_seconds gauge Number of seconds elapsed since the most recent catalog sync.
vertica_license_node_count gauge If the license limits the number of nodes, the number of nodes that the license allows.
vertica_license_size_mb gauge If the license limits the size of the database, the number of megabytes that license allows.
vertica_locked_users gauge Number of users that are locked out of their accounts.
vertica_login_attempted_total counter Number of login attempts.
vertica_login_failure_total counter Number of failed login attempts.
vertica_login_success_total counter Number of successful login attempts.
vertica_planned_file_reads_bytes counter Total number of bytes read in requests for files (estimated during query planning).
vertica_planned_file_reads_requests_total counter Total number of read requests for files (estimated during query planning).
vertica_process_memory_usage_percent gauge Total Vertica process memory usage, expressed as a percentage of total usable RAM.
vertica_projections_not_up_to_date_total gauge Number of projections that are not up to date.
vertica_projections_segmented_total gauge Number of segmented projections.
vertica_projections_total gauge Number of projections.
vertica_projections_unsafe_total gauge Number of projections whose K-safety is less than the database K-safety.
vertica_projections_unsegmented_total gauge Number of unsegmented projections.
vertica_query_requests_attempted_total counter Number of attempted query requests.
vertica_query_requests_failed_total counter Number of failed query requests.
vertica_query_requests_processed_rows_total counter Number of processed rows for each query type.
vertica_query_requests_succeeded_total counter Number of successful query requests.
vertica_query_requests_time_ms histogram Time (in milliseconds) that it takes to execute query requests in the resource pool.
vertica_queued_requests_failed_reservation_total counter Number of queued requests whose resource reservation failed in the resource pool.
vertica_queued_requests_max_memory_kb gauge Maximum memory requested for a single queued request in the resource pool.
vertica_queued_requests_total gauge Number of requests that are queued in the resource pool.
vertica_queued_requests_total_memory_kb gauge Total memory requested for all queued requests in the resource pool.
vertica_queued_requests_wait_time_ms histogram Length of time (in microseconds) that a resource pool queues queries.
vertica_resource_pool_general_memory_borrowed_kb gauge Amount of memory (in kilobytes) that running requests borrow from the GENERAL pool.
vertica_resource_pool_max_concurrency gauge MAXCONCURRENCY parameter setting for the resource pool. When set to -1, the resource pool can have an unlimited number of concurrent execution slots. When set to 0, queries are prevented from running in the pool.
vertica_resource_pool_max_memory_size_kb gauge MAXMEMORYSIZE parameter setting (in kilobytes) for the resource pool.
vertica_resource_pool_max_query_memory_size_kb gauge MAXQUERYMEMORYSIZE parameter setting (in kilobytes) for the resource pool. When set to -1, the resource pool borrows any amount of available memory from the GENERAL pool, up to vertica_resource_pool_max_memory_size_kb.
vertica_resource_pool_memory_inuse_kb gauge Amount of memory (in kilobytes) acquired by requests running against the resource pool.
vertica_resource_pool_memory_size_actual_kb gauge Current amount of memory (in kilobytes) allocated to the resource pool by the resource manager.
vertica_resource_pool_planned_concurrency gauge PLANNEDCONCURRENCY parameter setting for the resource pool.
vertica_resource_pool_priority gauge PRIORITY parameter setting for the resource pool.
vertica_resource_pool_query_budget_kb gauge Amount of resource pool memory (in kilobytes) that queries are currently tuned to use. When equal to -1, queries are prevented from running in the pool.
vertica_resource_pool_queue_timeout gauge QUEUETIMEOUT parameter setting for the resource pool.
vertica_resource_pool_queueing_threshold_kb gauge Limits the amount of memory (in kilobytes) that a resource pool makes available to all requests before it queues requests.
vertica_resource_pool_running_query_count gauge Number of queries currently executing in the pool.
vertica_resource_pool_runtime_priority_threshold gauge RUNTIMEPRIORITYTHRESHOLD parameter setting for the resource pool.
vertica_sessions_blocked_counter gauge Number of sessions that are blocked waiting for locks.
vertica_sessions_running_counter gauge Number of active sessions.
vertica_storage_containers_count gauge Total number of storage containers.
vertica_subcluster_info gauge Shows information about a subcluster through labels.
vertica_system_disk_io_completed_per_second gauge Number of successful I/O requests completed per second.
vertica_system_disk_io_in_progress_counter gauge Number of I/O requests currently in process.
vertica_system_disk_io_read_kb_per_second gauge Measures the I/O bandwidth used to read from disk in KBps.
vertica_system_disk_io_usage_percent gauge Percentage of time the disk is processing I/O.
vertica_system_disk_io_write_kb_per_second gauge Measures the I/O bandwidth used to write to disk in KBps.
vertica_system_memory_usage_percent gauge Total system memory usage, expressed as a percentage of total usable RAM.
vertica_tm_operations_attempted_total counter Number of attempted tuple mover operations.
vertica_tm_operations_completed_total counter Number of completed tuple mover operations.
vertica_tm_operations_failed_total counter Number of aborted tuple mover operations.
vertica_tm_operations_ros_count_total gauge Total number of ROS containers in the tuple mover operation.
vertica_tm_operations_ros_used_bytes_total gauge Total size (in bytes) of all ROS containers in the mergeout operation.
vertica_tm_operations_running_total gauge Number of running tuple mover operations.
vertica_total_nodes_count gauge Total number of nodes.
vertica_transactions_completed_total counter Number of completed transactions.
vertica_transactions_failed_total counter Number of failed transactions.
vertica_transactions_started_total counter Number of transactions that have started.
vertica_up_nodes_count gauge Number of nodes that have Vertica running and can accept connections.