Table: aiven_account_authentication - Query Aiven Account Authentications using SQL

Aiven Account Authentication is a part of Aiven's account management system that handles the verification of identities and the management of access control. The authentication process ensures that the users are who they claim to be, thus providing a secure environment for data access and manipulation. It plays a crucial role in protecting sensitive data from unauthorized access and potential security threats.

Table Usage Guide

The aiven_account_authentication table provides insights into the authentication methods used across Aiven accounts. As a security analyst, you can explore the details of each authentication method through this table, including the type of authentication, its status, and associated metadata. Utilize it to monitor and ensure the security of your Aiven accounts by verifying the authenticity of each user and their access controls.

Examples

Basic info

Explore which accounts are active and when they were created to manage access to your resources more effectively. This is useful for maintaining security and ensuring only authorized users have access.

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication;

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication;

List disabled authentication methods

Uncover the details of disabled authentication methods within your account. This can be useful for identifying potential security risks or areas for improvement within your authentication protocols.

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  not enabled;

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  enabled = 0;

List pending authentication methods

Discover the segments that have authentication methods still in the process of being configured. This is useful to ensure all methods are set up correctly and promptly for secure access.

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  state = 'pending_configuration';

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  state = 'pending_configuration';

List SAML authentication methods

Explore the SAML authentication methods in your Aiven account to determine which are enabled. This can be useful to identify potential security risks and maintain compliance with your organization's authentication policies.

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  type = 'saml';

select
  id,
  name,
  enabled,
  state,
  type,
  create_time
from
  aiven_account_authentication
where
  type = 'saml';

List expired SAML certificates

Assess the elements within your Aiven account authentication to identify expired SAML certificates. This is useful for maintaining security standards by ensuring all certificates are current and valid.

select
  id,
  name,
  enabled,
  state,
  type,
  create_time,
  saml_certificate_issuer,
  saml_certificate_not_valid_after
from
  aiven_account_authentication
where
  now() < saml_certificate_not_valid_after;

select
  id,
  name,
  enabled,
  state,
  type,
  create_time,
  saml_certificate_issuer,
  saml_certificate_not_valid_after
from
  aiven_account_authentication
where
  datetime('now') < saml_certificate_not_valid_after;

Schema for aiven_account_authentication

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=	The account ID.
auto_join_team_id	text		Automatically add users to a team, when user signs up using this authentication method.
create_time	timestamp with time zone		The create time of the authentication method.
delete_time	timestamp with time zone		The delete time of the authentication method.
enabled	boolean		If true, authentication method can be used to access account/projects in account. If false, authentication method can still be used to sign in.
id	text	=	Authentication method ID.
name	text		Authentication method name.
saml_acs_url	text		Saml acs url.
saml_certificate	text		Identity provider's certificate.
saml_certificate_issuer	text		Saml certificate issuer.
saml_certificate_not_valid_after	timestamp with time zone		Saml certificate not valid after.
saml_certificate_not_valid_before	timestamp with time zone		Saml certificate not valid before.
saml_certificate_subject	text		Saml certificate subject.
saml_entity	text		Saml entity.
saml_idp_url	text		Saml idp url.
saml_metadata_url	text		Saml metadata url.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text		The state of the authentication method.
type	text		Authentication method type.
update_time	timestamp with time zone		The update time of the authentication method.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aiven

You can pass the configuration to the command with the --config argument:

steampipe_export_aiven --config '<your_config>' aiven_account_authentication