High API usage - Githubissues

tinder-tder commented 2 years ago

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 https://github.com/turbot/steampipe-plugin-okta/blob/80bfd027a163b4b241b0e24cd6af220c752c74ea/okta/table_okta_app_assigned_group.go#L117

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

e-gineer commented 2 years ago

Hey @tinder-tder ... I believe you are correct that it should be app_id not user_id on that line. Specifically, the KeyColumnQuals map has access to any data passed in via where per the column definitions:

https://github.com/turbot/steampipe-plugin-okta/blob/80bfd027a163b4b241b0e24cd6af220c752c74ea/okta/table_okta_app_assigned_group.go#L29-L44

tinder-tder commented 2 years ago

@c0d3r-arnab I just tried the issue-60 branch but I am still seeing the initial api list call when testing.

> select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id = 'xxxxxxxxxxxxxxxx' limit 1;
2022-01-24T10:38:37.832-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 = 'xxxxxxxxxxxxxxx' limit 1; args <empty>
2022-01-24T10:38:37.832-0800 [TRACE] steampipe: Got Session with PID: 46481
2022-01-24T10:38:37.832-0800 [TRACE] steampipe: Session with PID: 46481 - returning
Error: Get "https://localhost/api/v1/apps": dial tcp [::1]:443: connect: connection refused (SQLSTATE HV000)

I believe if you specify an app_id without an 'id' qual it should be calling the https://developer.okta.com/docs/reference/api/apps/#list-groups-assigned-to-application function for the parent hydrate or at least passing in the app_id as the 'id' qual to the parenthydrate function.

rajlearner17 commented 2 years ago

@tinder-tder appreciate providing continuous feedback, the current PR is for correcting the get call API fix. We are looking into this, will keep you posted.

bigdatasourav commented 2 years ago

@tinder-tder we have added the app_id as key qual in okta_app_assigned_group table and it is working fine. Please try our latest changes in this PR and provide your feedback.

tinder-tder commented 2 years ago

@rajlearner17 @bigdatasourav : thanks for the quick work, the functionality is there with the current pr. But I am still hitting API limits due to the initial apps list call same as before.

> 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;
2022-01-25 18:22:09.019 UTC [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 = 'someappid'  limit 1; args <empty>
2022-01-25 18:22:09.019 UTC [TRACE] steampipe: Got Session with PID: 47490
2022-01-25 18:22:09.019 UTC [TRACE] steampipe: Session with PID: 47490 - returning
Error: Get "https://localhost/api/v1/apps": dial tcp [::1]:443: connect: connection refused (SQLSTATE HV000)
>

cbruno10 commented 2 years ago

Hey @tinder-tder , sorry you're running into issues still with the query, even with limits and app IDs given in the query. I believe there are some improvements we can make around how we query applications and related information, so I'm taking a look at the Okta SDK + API this afternoon. I'll keep you posted in this thread with any findings.

I'm not sure if you've mentioned this elsewhere, but are you able to share roughly how many applications and user/group assignments you have?

tinder-tder commented 2 years ago

@cbruno10 as noted in the pr, https://github.com/turbot/steampipe-plugin-okta/pull/61#issuecomment-1020463607, I believe if app_id is specified you should be calling the ListApplicationGroupAssignments function (it returns an array if I am reading it right) https://github.com/okta/okta-sdk-golang/blob/cddf90a929809d0df3d86a8fecc405cdb46eacc9/okta/application.go#L537

What I think is happening is if app_id OR id isnt qualified (https://github.com/turbot/steampipe-plugin-okta/blob/0ad3a37391a95c817ece137e3879c56fe5626de6/okta/table_okta_app_assigned_group.go#L122) the logic falls back to the parent hydrate function which does a generic list all applications. Please correct me if I am wrong.

I think the logic should be (roughly)

if app_id != "" && id != "" { do current logic }
else if  app_id != "" { call ListApplicationGroupAssignments }
else { fall back to parenthydration logic }

EDIT: we have over 500 apps and thousands of user/group assignments

cbruno10 commented 2 years ago

Hey @tinder-tder , we've made some updates in https://github.com/turbot/steampipe-plugin-okta/pull/61 around how we list applications, and assigned groups and users:

Increase the default page size when listing applications in okta_application table.
Reduce page size if limit is specified in the query when listing applications in okta_application table.
For okta_app_assigned_group and okta_app_assigned_user tables, if app_id is specified, only get that application in the parent hydrate function. This should drastically reduce the number of API calls the tables make for accounts with a large number of applications. If app_id is not specified, all applications will still be listed.

So I believe these changes align with your suggestions above, though the implementation is slightly different.

Can you please give the changes in the issue-60 branch another try and let us know if you're seeing better performance? If running a query like select * from okta_app_assigned_user, I think you may still run into API limits, as we'd still attempt to list all assigned users for each application.

tinder-tder commented 2 years ago

@cbruno10 Thanks for the quick work! API usage is greatly improved. The real queries are running much faster now. Here is the debug query:

> select app_id,id as group_id,jsonb_array_elements_text(profile->'samlRoles') as roles from okta.okta_app_assigned_group where app_id in ('a','b')  ;
2022-01-26 19:28:53.134 UTC [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 in ('a','b')  ; args <empty>
2022-01-26 19:28:53.134 UTC [TRACE] steampipe: Got Session with PID: 40146
2022-01-26 19:28:53.134 UTC [TRACE] steampipe: Session with PID: 40146 - returning
Error: Get "https://localhost/api/v1/apps/b": dial tcp [::1]:443: connect: connection refused (SQLSTATE HV000)
>

So there is no longer the default list all apps which is ideal.

I think we can merge that PR and thanks again.

cbruno10 commented 2 years ago

@tinder-tder Thanks for the quick responses and testing! If you see any other strange or limiting behaviour, please let us know.

We'll be releasing these fixes, along with other improvements, later this week.

turbot / steampipe-plugin-okta

High API usage #60