turbot/steampipe-plugin-okta

Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.

steampipe-plugin-okta's Issues

Add table okta_auth_server

References
https://developer.okta.com/docs/reference/api/authorization-servers/

occasionally fails with "ERROR: relation "okta_user" does not exist (SQLSTATE 42P01)"

Describe the bug
I am running regular reports with Steampipe from a scheduleg Github Action.
I have a custom docker action in which the Dockerfile pre-installs the this Okta plugin.
The action entrypoint then does a steampipe service start then steampipe query "<query-as-action-arg>".

This sometimes work just fine, and sometimes (roughly 50% of the time actually) fails, this is the output that I get in GHA's log:

Steampipe service is running:

Database:

  Host(s):            localhost, 127.0.0.1, 172.17.0.2
  Port:               9193
  Database:           steampipe
  User:               steampipe
  Password:           ********* [use --show*** reveal]
  Connection string:  postgres://steampipe@localhost:9193/steampipe

Managing the Steampipe service:

  # Get status of the service
  steampipe service status
	 
  # View database password for connecting from another machine
  steampipe service status --show-password
  
  # Restart the service
  steampipe service restart
  
  # Stop the service
  steampipe service stop

Warning: executeQueries: query 1 of 1 failed: ERROR: relation "okta_user" does not exist (SQLSTATE 42P01)

Any idea of what might this be, or pointers on how I can further debug this?

Steampipe version (steampipe -v)
v0.20.9

Plugin version (steampipe plugin list)
v0.9.0

To reproduce
I can provide a more detailed setup for the action if needed for further analysis.

Expected behavior
Expected the okta_user table to exist given that the plugin is pre-installed during the action's docker build

Additional context
n/a

RPC API Failures when using Okta Private Key Authentication

Describe the bug
When using private key authentication to Okta, queries on all tables fail with a mysterious error code.

Steampipe version (steampipe -v)
v0.21.1

Plugin version (steampipe plugin list)
hub.steampipe.io/plugins/turbot/okta@latest | 0.10.1

To reproduce
Reproduction Steps:

1. Have Okta create a new private key for the Steampipe application. Key begins with "-----BEGIN PRIVATE KEY-----". Save to "original.pem"
2. Convert newline control characters to \n string literals. Key now fits on exactly one line.
3. Paste private key into private_key field in okta.spc.
4. Execute steampipe query then one of the basic user table examples from Steampipe Hub.

Error: RSA private key is of the wrong type (SQLSTATE HV000)

+------+----+------+-------------+---------+
| name | id | type | description | profile |
+------+----+------+-------------+---------+
+------+----+------+-------------+---------+

I get the above error messages and zero row results.
5. Execute openssl rsa -in original.pem -out rsa.pem
6. Execute diff original.pem rsa.pem. Every line in the diff shows as different for me. The converted RSA key starts with "-----BEGIN RSA PRIVATE KEY-----". (My mistake earlier was thinking that if I just changed the header, that it would change the key format too. It doesn't.)
7. Execute steampipe query then one of the basic user table examples from Steampipe Hub. The authentication errors have gone but replaced with something not very helpful.

Error: The API returned an error:  (SQLSTATE HV000)

+------+----+------+-------------+---------+
| name | id | type | description | profile |
+------+----+------+-------------+---------+
+------+----+------+-------------+---------+

8. Bumping the error logging to TRACE doesn't change much. Execute: export STEAMPIPE_LOG_LEVEL=TRACE; steampipe query to up the logging levels.
9. All I see in the logs is:

2023-11-15 18:07:04.935 UTC [INFO]  hub: goFdwIterateForeignScan Next returned error: rpc error: code = Unknown desc = The API returned an error:  (0xc000001140)
2023-11-15 18:07:04.935 UTC [208163] ERROR:  rpc error: code = Unknown desc = The API returned an error:

It looks like there's supposed to be something after The API returned an error: but there's nothing there.

Expected behavior
The end user reports that when using Okta tokens, the queries work just fine.

Additional context
Turbot Community Slack thread

Add table okta_network_zone

References
https://developer.okta.com/docs/reference/api/zones/#base-network-zone-properties

Support for `key-id` with `private-key` OAuth2 authentication

Is your feature request related to a problem? Please describe.
Okta requires that there should be a key-id with the private-key when authenticating with OAuth2.0

Describe the solution you'd like
A KeyId field in okta.spc which is used by the Okta client

Describe alternatives you've considered
We can use token based authentication.

Additional context

1. https://github.com/okta/okta-sdk-golang?tab=readme-ov-file#oauth-20-with-private-key
2. https://pkg.go.dev/github.com/okta/okta-sdk-golang/v2/okta#WithPrivateKeyId

assigned_groups field in okta_application is limited to the first 20 groups

Describe the bug
When getting the 'assigned_groups' field of from okta_application table, the data is limited to the first 20 groups and the api calls are not pagenating. Also there is no way to pass the query param 'limit' (ie: “/api/v1/apps/${APPID}/groups?limit=200”) to work around the current default pagenation size of 20 (to reduce api calls).

https://developer.okta.com/docs/reference/api/apps/#list-groups-assigned-to-application

Steampipe version (steampipe -v)
v0.10.0

Plugin version (steampipe plugin list)
v0.2.0

To reproduce
have an okta application with more than 20 groups assigned to it and query its assigned groups

select jsonb_pretty(assigned_groups) as groups from okta.okta_application where filter = 'group.id eq "xxxxxxxxx"' AND label = 'foo'

query is formatted with the where and condition due to issue #40

Expected behavior
all groups to be returned in the 'assigned_groups' field

Additional context
Use case is to get all group assignments for an application and then convert the returned group ids to the group name for auditing/reporting.

I believe something similar to

steampipe-plugin-okta/okta/table_okta_group.go

Lines 183 to 200 in 4a2daf2

	groupMembers, resp, err := client.Group.ListGroupUsers(ctx, groupId, &query.Params{})
	if err != nil {
	logger.Error("listGroupMembers", "get group", err)
	return nil, err
	}
	// paging
	for resp.HasNextPage() {
	var nextgroupMembersSet []*okta.User
	resp, err = resp.Next(ctx, &groupMembers)
	if err != nil {
	logger.Error("listOktaGroups", "list group paging", err)
	return nil, err
	}
	groupMembers = append(groupMembers, nextgroupMembersSet...)
	}
	return groupMembers, nil

will need to be done
around

steampipe-plugin-okta/okta/table_okta_application.go

Line 207 in 4a2daf2

groups, _, err := client.Application.ListApplicationGroupAssignments(ctx, appId, &query.Params{})

but not sure (not too familiar with this code base still)

equiv functionality we are trying to reproductince in steampipe is

!/bin/bash
set -e
APPID='SOME_APP_ID'
OKTA_API_TOKEN='SOME_API_TOKEN'

function okta_api() {
  API="$1"
curl -s -X GET \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-H "Authorization: SSWS ${OKTA_API_TOKEN}" \
"https://foo.okta.com/${API}"
}

#get application group assignments
okta_api "api/v1/apps/${APPID}/groups?limit=200" > blob.json

#convert groupids to names
for I in $(jq -r '.[].id' blob.json); do
  NAME=$(okta_api "api/v1/groups/${I}" | jq -r .profile.name | tr ' ' '_')
  jq -r '.[] | select(.id == "'${I}'") | .profile.samlRoles[]' blob.json > ${NAME}.txt
done 

Bug: `okta_user` and `okta_factor` table fails with `context canceled` error.

Describe the bug
okta_user and okta_factor table fails with context canceled errors.
It seems to fail due to a timeout issue for a larger number of users in the account.

There should be a better mechanism to retry for such errors.

 WITH OKTA_MFA as (
  SELECT 
    user_id
  FROM
    okta_factor
  WHERE
    status = 'ACTIVE'
  AND
    factor_type IN ('push','token:software:totp')
  ORDER BY
    user_id, factor_type
), 
OKTA_USERS as (
  SELECT
    id,
    email,
    status,
    last_login
  FROM
    okta_user
  ORDER BY
    id, email
)
SELECT
    U.email as resource,
    CASE
        WHEN U.status <> 'ACTIVE' THEN 'skip'
        WHEN COUNT(F.user_id) = 0 THEN 'alarm'
        ELSE 'ok'
    END AS status,
    CASE
        WHEN U.status <> 'ACTIVE' THEN 'User ' || u.email || ' is not active.'
        WHEN COUNT(F.user_id) = 0 THEN 'User ' || u.email || ' does not have MFA configured.'
        ELSE 'User ' || u.email || ' is ok'
    END AS reason,
    U.email,
    U.last_login
FROM
    OKTA_USERS U
LEFT JOIN OKTA_MFA F on F.user_id = U.id
GROUP BY
    U.email,
    U.status,
    U.last_login

2022-09-09 07:50:11.972 UTC [WARN] PluginManager setPluginCacheSizeMap: 6 connections.
2022-09-09 07:50:11.972 UTC [WARN] Total cache size 0Mb
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814946: listOktaUsers: list_users_error="Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled"
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: listOktaUsers: list_users_error="Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled"
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: streamRows error chan select: Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled
2022-09-09 07:50:14.481 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709814927: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:14.481 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709814927: streamRows error chan select: Get "https://xxxxx.okta.com/api/v1/users?limit=200": context canceled
2022-09-09 07:50:14.481 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709814927: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:24.004 UTC [WARN] PluginManager setPluginCacheSizeMap: 6 connections.
2022-09-09 07:50:24.004 UTC [WARN] Total cache size 0Mb
2022-09-09 07:50:58.971 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.972 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: streamRows error chan select: context deadline exceeded
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: error chan select: context deadline exceeded
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [WARN] steampipe-plugin-okta.plugin: [WARN] 1662709826246: Execute call failed - cancelling pending item in cache
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context deadline exceeded"
2022-09-09 07:50:58.973 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaUsers: list_users_paging_error="context canceled"
2022-09-09 07:50:58.974 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled"
2022-09-09 07:50:58.975 UTC [ERROR] steampipe-plugin-okta.plugin: [ERROR] 1662709826246: listOktaFactors: list_factors_error="context canceled" (edited) 

Steampipe version (steampipe -v)
Example: v0.3.0

Plugin version (steampipe plugin list)
Example: v0.5.0

To reproduce
Steps to reproduce the behavior (please include relevant code and/or commands).

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Slack thread

v0.1.0

Add table okta_factor

References
https://developer.okta.com/docs/reference/api/factors/#get-factor

https://developer.okta.com/docs/reference/api/factors/#list-enrolled-factors

https://developer.okta.com/docs/reference/api/factors/#list-security-questions

Add table okta_threatInsight_configuration

References
https://developer.okta.com/docs/reference/api/threat-insight/

Title column for user's is always nil

Describe the bug
A clear and concise description of what the bug is.

Steampipe version (steampipe -v)
Example: v0.7.3

Plugin version (steampipe plugin list)
Example: v0.0.1

To reproduce
Steps to reproduce the behavior (please include relevant code and/or commands).

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

Add table okta_idp_discovery_policy

References
https://developer.okta.com/docs/reference/api/policy/#idp-discovery-policy

Add table okta_mfa_policy

References
https://developer.okta.com/docs/reference/api/policy/#multifactor-mfa-enrollment-policy

Add table okta_user_schema

References
https://developer.okta.com/docs/reference/api/schemas/#getting-started

v0.1.0

Okta Timeout - Error: rpc error: code = DeadlineExceeded desc = okta: exceeded allowed timeout (SQLSTATE HV000)

Describe the bug

The Request timeout of 30secs at the HTTP level is too short for some queries.

"Error: rpc error: code = DeadlineExceeded desc = okta: exceeded allowed timeout (SQLSTATE HV000)"

Steampipe version (steampipe -v)
steampipe 0.21.4

**Plugin version hub.steampipe.io/plugins/turbot/okta@latest | 0.11.0 | okta

To reproduce
SELECT
factor_type
FROM
okta_factor
WHERE
provider = 'OKTA'
GROUP BY
factor_type;

Expected behavior

the count of each factor type.

Additional context
may be this part of the code.

steampipe-plugin-okta/okta/connect.go

Line 73 in e7e4d26

_, client, err := okta.NewClient(ctx, okta.WithRequestTimeout(30), okta.WithRateLimitMaxRetries(5))

Add table okta_trusted_origin

References
https://developer.okta.com/docs/reference/api/trusted-origins/

Update tables for better use of API filters, context cancellation in list calls and page limiting for limit clause in query

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

High API usage

Describe the bug
We are hitting rate limiting and triggering alerts when doing "straight forward" lookups with the new okta_app_assigned_group table. This may be happening with other tables too but I have only verified it with this tabled based on our testing.

I set logging to trace and change the okta url to localhost to see what it was doing and it looks like it is getting all apps which would be costly with the size of our org and I am passing an app_id qualifier.

> 2022-01-20T09:48:00.939-0800 [TRACE] steampipe: Got Session with PID: 86911
2022-01-20T09:48:00.939-0800 [TRACE] steampipe: Session with PID: 86911 - returning
> 
> select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<redacted>' limit 1;
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: resolveQuery select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<redacted>' limit 1; args <empty>
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: Got Session with PID: 86911
2022-01-20T09:48:04.109-0800 [TRACE] steampipe: Session with PID: 86911 - returning
Error: Get "https://localhost/api/v1/apps": dial tcp [::1]:443: connect: connection refused (SQLSTATE HV000)
> 

I cant find where the list api (https://developer.okta.com/docs/reference/api/apps/#list-applications) call is being done (is it related to the parent hydrate code?)

I believe for this table if app_id is specified it should just make a paginated call to api/v1/apps/<app_id>/groups for each app_id specified

also

steampipe-plugin-okta/okta/table_okta_app_assigned_group.go

Line 117 in 80bfd02

appId := d.KeyColumnQuals["user_id"].GetStringValue()

Should that be app_id and not user_id? If you could explain how user_id is related that would be appreciated. I am trying to get up to speed so I can make some PRs.

Steampipe version (steampipe -v)
Example: v0.11.2

Plugin version (steampipe plugin list)
Example: v0.4.0

To reproduce
run a query with on okta_app_assigned_group with app_id qualifier

select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = '<someappid>' limit 1;

Expected behavior
Not listing ALL apps before getting results

Additional context
none

profile data not available in okta_application table

Describe the bug
With the removal of the 'assigned_groups' field in the 'okta_application' table the profile data we used to have access to is no longer available.

Steampipe version (steampipe -v)
Example: v0.11.2

Plugin version (steampipe plugin list)
Example: v0.3.0

To reproduce
With the 0.2 version of the plugin we were able to run:

Begin transaction;
create temp table if not exists t1 on commit drop as select jsonb_array_elements(assigned_groups)#>>'{id}' as groupid, jsonb_array_elements(assigned_groups)#>>'{profile,samlRoles}' as roles from okta.okta_application where filter = 'group.id eq "xxxxxxxxxxxx"' AND label = 'foo';
create temp table if not exists t2 on commit drop as select name,id from okta.okta_group where id in (select groupid from t1);
select t2.name,t1.groupid, jsonb_array_elements_text(t1.roles::jsonb) as samlRole from t1,t2 where t1.groupid = t2.id;
commit;

which would give us the first 20 group assignments (see issue #47) for an app with the associated samlRoles assigned to each group
Note: with issue #40 fixed we dont have to use the "where filter" clause anymore (we can just do "where id = ....")

Expected behavior
Some way yo access the profile.SamlRoles data in the okta_application table or okta_app_assigned_group (or maybe a whole other table)

Additional context
Add any other context about the problem here.

Add an Okta Devices table

Is your feature request related to a problem? Please describe.
With okta moving more towards a device trust model, it would be good to be able to put a device to a user

Describe the solution you'd like

Be able to relate a user to a device and see device information (encryption, managed, etc)

Describe alternatives you've considered

https://support.okta.com/help/s/article/Howto-Get-a-List-of-All-Managed-and-NotManaged-Devices-in-Okta?language=en_US

Additional context
Add any other context or screenshots about the feature request here.

Add table okta_signon_policy

References
https://developer.okta.com/docs/reference/api/policy/#okta-sign-on-policy

Add table okta_app_assigned_user

Table for user assignment details for applications

API references:
https://pkg.go.dev/github.com/okta/okta-sdk-golang/[email protected]/okta#ApplicationResource.ListApplicationUsers
https://pkg.go.dev/github.com/okta/okta-sdk-golang/[email protected]/okta#ApplicationResource.GetApplicationUser

Terraform reference:
https://registry.terraform.io/providers/okta/okta/latest/docs/resources/app_user

Add table okta_domain

References
https://developer.okta.com/docs/reference/api/domains/#get-all-domains

branch having some initial code - https://github.com/turbot/steampipe-plugin-okta/compare/new-table

The `profile` column in the `okta_application` table does not contain any information

Describe the bug
For any rows from the okta_application table, the profile column never has data

Steampipe version (steampipe -v)
v0.10.0

Plugin version (steampipe plugin list)
v0.1.0

To reproduce
Run select name, profile from okta_application while having at least 1 application

Expected behavior
The profile column should have data or not be defined if the API doesn't return any data for it

Additional context
Application type - https://github.com/okta/okta-sdk-golang/blob/master/okta/application.go#L35

Remove columns assigned_users and assigned_groups from table okta_application

With respect to the issue #47, the columns assigned_users and assigned_groups should be removed from the table okta_application.

Two new tables should be created instead. Issues related to the new tables:

1. #48
2. #49

Add limit and context cancellation handling to current tables

Is your feature request related to a problem? Please describe.
When listing results from various tables, too many results are returned or iterated.

Describe the solution you'd like
All tables that support limiting results should:

• Set the default page size to the max allowed
• If a limit is passed in that's lower than the default, set the page size to limit to reduce returned results, e.g., https://github.com/turbot/steampipe-plugin-okta/blob/main/okta/table_okta_application.go#L71-L82
• Handle context cancellations when iterating through the results (in the initial call, and when paging), e.g., https://github.com/turbot/steampipe-plugin-okta/blob/main/okta/table_okta_application.go#L110-L113, https://github.com/turbot/steampipe-plugin-okta/blob/main/okta/table_okta_application.go#L127-L130

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Add table okta_app_assigned_group

Table for application group assignment details

API references:
https://pkg.go.dev/github.com/okta/okta-sdk-golang/[email protected]/okta#ApplicationResource.ListApplicationGroupAssignments
https://pkg.go.dev/github.com/okta/okta-sdk-golang/[email protected]/okta#ApplicationResource.GetApplicationGroupAssignment

Terraform reference:
https://registry.terraform.io/providers/okta/okta/latest/docs/resources/app_group_assignments

Update steampipe plugin sdk version

Update steampipe plugin sdk version to v1.8.3

Enhance the table `okta_device` by adding the optional quals

Is your feature request related to a problem? Please describe.
Add the supported optional quals to the table okta_device.

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Minor bug in application list

This should be okta.Application, not okta.App ( the latter is an interface and breaks here )

steampipe-plugin-okta/okta/table_okta_application.go

Line 106 in 9b37bc3

var nextApplicationSet []*okta.App

Fix GET API call in table okta_application

Describe the bug
The GET API call is throwing an error: cannot unmarshal object into Go value of type okta.App

Steampipe version (steampipe -v)
Example: v0.10.0

Plugin version (steampipe plugin list)
Example: v0.1.0

To reproduce

> select * from okta_application where id = '0oa1kchdmerpZir9y5d7'
Error: json: cannot unmarshal object into Go value of type okta.App

Recompile plugin with steampipe-plugin-sdk-v1.8.2 and Go version 1.17

References
Add any related links that will help us understand the resource, including vendor documentation, related GitHub issues, and Go SDK documentation.

Add group and user assignment information to `okta_application` table (or create separate tables)

Is your feature request related to a problem? Please describe.
I'd like to see group and user assignments for an application.

Describe the solution you'd like
Using the information from https://github.com/okta/okta-sdk-golang/blob/master/okta/application.go#L749 and https://github.com/okta/okta-sdk-golang/blob/master/okta/application.go#L749, add the response data in the appropriate place

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.