Table: aws_transfer_user - Query AWS Transfer Family users using SQL

AWS Transfer Family is a secure transfer service that enables you to transfer files into and out of AWS storage services.

Table Usage Guide

The aws_transfer_user table in Steampipe provides you with information about users inside defined servers in the AWS Transfer Family service. This table allows you, as a DevOps engineer, to query user-specific details, including home directories, ssh keys, usernames and IAM roles.

Examples

Basic info

Explore which AWS Transfer users are defined in a server

select
  arn,
  server_id,
  user_name
from
  aws_transfer_user;
where
  server_id = "s-xxxxxxxxxxxxxxxxx";

select
  arn,
  server_id,
  user_name
from
  aws_transfer_user;
where
  server_id = "s-xxxxxxxxxxxxxxxxx";

Sort users descending by SSH public key count

select
  arn,
  server_id,
  user_name,
  ssh_public_key_count
from
  aws_transfer_user;
where
  server_id = "s-xxxxxxxxxxxxxxxxx"
order by
  ssh_public_key_count desc;

select
  arn,
  server_id,
  user_name,
  ssh_public_key_count
from
  aws_transfer_user;
where
  server_id = "s-xxxxxxxxxxxxxxxxx"
order by
  ssh_public_key_count desc;

Get home directory mappings for users

select
  server_id,
  user_name,
  home_directory_mappings -> 0 ->> 'Entry' as entry_home_directory,
  home_directory_mappings -> 0 ->> 'Target' as target_home_directory
from
  aws_transfer_user
where
  server_id = 's-xxxxxxxxxxxxxxxxx';

select
  server_id,
  user_name,
  json_extract(home_directory_mappings, '$[0].Entry') as entry_home_directory,
  json_extract(home_directory_mappings, '$[0].Target') as target_home_directory
from
  aws_transfer_user
where
  server_id = 's-xxxxxxxxxxxxxxxxx';

Find user_name across multiple servers

select
  server_id,
  user_name,
  arn
from
  aws_transfer_user
where
  server_id in (
    select
      server_id
    from
      aws_transfer_server
  )
  and user_name = 'my_user_to_search';

select
  server_id,
  user_name,
  arn
from
  aws_transfer_user
where
  server_id in (
    select
      server_id
    from
      aws_transfer_server
  )
  and user_name = 'my_user_to_search';

Count users by server_id descending

select
  count(*) as total_users,
  server_id
from
  aws_transfer_user
group by
  server_id
order by
  total_users desc;

select
  count(*) as total_users,
  server_id
from
  aws_transfer_user
group by
  server_id
order by
  total_users desc;

Schema for aws_transfer_user

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The AWS Account ID in which the resource is located.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
arn	text		The Amazon Resource Name (ARN) for the user.
home_directory	text		Specifies the landing directory (folder) for a user when they log in to the server.
home_directory_mappings	jsonb		The landing directory (folder) for a user when they log in to the server using the client.
home_directory_type	text		The type of landing directory (folder) you mapped for your users to see when they log in to the server.
partition	text		The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
region	text		The AWS Region in which the resource is located.
role	text		The Amazon Resource Name (ARN) of the AWS Identity and Access Management (IAM) role that controls your users' access to your Amazon S3 bucket or Amazon EFS file system.
server_id	text	=	The ID of the server that the user is attached to.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
ssh_public_key_count	bigint		The number of SSH public keys stored for the user on the server.
ssh_public_keys	jsonb		The public SSH keys stored for the user on the server.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.
user_name	text	=	Specifies the name of the user whose ARN was specified. User names are used for authentication purposes.

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)" -- aws

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

steampipe_export_aws --config '<your_config>' aws_transfer_user