Add group and user assignment information to okta_application table. Closes #39

[c0d3r-arnab]

Example query results

Results

``` > select name, id, label, created, status, sign_on_mode from okta_application; +---------------------+----------------------+----------------------+----------------------+--------+----------------+ | name | id | label | created | status | sign_on_mode | +---------------------+----------------------+----------------------+----------------------+--------+----------------+ | okta_browser_plugin | 0oa1kcigd9Kob07k05d7 | Okta Browser Plugin | 2021-08-26T04:26:36Z | ACTIVE | OPENID_CONNECT | | saasure | 0oa1kchdmerpZir9y5d7 | Okta Admin Console | 2021-08-26T04:26:28Z | ACTIVE | OPENID_CONNECT | | oidc_client | 0oa1mf49z9iZurdzA5d7 | Service Client Name | 2021-08-31T12:31:44Z | ACTIVE | OPENID_CONNECT | | okta_enduser | 0oa1kchdrfcXTbEzV5d7 | Okta Dashboard | 2021-08-26T04:26:36Z | ACTIVE | OPENID_CONNECT | | oidc_client | 0oa1mf9l3dw26foa25d7 | Test Private KeyPair | 2021-08-31T12:46:30Z | ACTIVE | OPENID_CONNECT | +---------------------+----------------------+----------------------+----------------------+--------+----------------+ > select name, id, label, created, status, sign_on_mode from okta_application where sign_on_mode = 'SAML_2_0'; +------+----+-------+---------+--------+--------------+ | name | id | label | created | status | sign_on_mode | +------+----+-------+---------+--------+--------------+ +------+----+-------+---------+--------+--------------+ > select id, label, name, sign_on_mode, status from okta_application as app where filter = 'user.id eq "00u1kcigdvWtR96HY5d7"'; +----------------------+----------------------+-------------+----------------+--------+ | id | label | name | sign_on_mode | status | +----------------------+----------------------+-------------+----------------+--------+ | 0oa1mf9l3dw26foa25d7 | Test Private KeyPair | oidc_client | OPENID_CONNECT | ACTIVE | | 0oa1mf49z9iZurdzA5d7 | Service Client Name | oidc_client | OPENID_CONNECT | ACTIVE | +----------------------+----------------------+-------------+----------------+--------+ > select id, label, name, sign_on_mode, status from okta_application where filter = 'group.id eq "00g33kzj6xOZvlBUQ5d7"'; +----------------------+----------------------+-------------+----------------+--------+ | id | label | name | sign_on_mode | status | +----------------------+----------------------+-------------+----------------+--------+ | 0oa1mf9l3dw26foa25d7 | Test Private KeyPair | oidc_client | OPENID_CONNECT | ACTIVE | +----------------------+----------------------+-------------+----------------+--------+ > select name, id, label, jsonb_pretty(assigned_users) as assigned_users from okta_application; +---------------------+----------------------+----------------------+-------------------------------------------------------------------------------------------+ | name | id | label | assigned_users | +---------------------+----------------------+----------------------+-------------------------------------------------------------------------------------------+ | okta_enduser | 0oa1kchdrfcXTbEzV5d7 | Okta Dashboard | [ | | | | | ] | | saasure | 0oa1kchdmerpZir9y5d7 | Okta Admin Console | [ | | | | | { | | | | | "id": "00u1kcigdvWtR96HY5d7", | | | | | "scope": "USER", | | | | | "_links": { | | | | | "app": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1kchdmerpZir9y5d7" | | | | | }, | | | | | "user": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/users/00u1kcigdvWtR96HY5d7" | | | | | } | | | | | }, | | | | | "status": "ACTIVE", | | | | | "created": "2021-08-26T04:26:37Z", | | | | | "profile": { | | | | | }, | | | | | "syncState": "DISABLED", | | | | | "lastUpdated": "2021-08-26T04:26:37Z", | | | | | "statusChanged": "2021-08-26T04:26:37Z" | | | | | } | | | | | ] | | okta_browser_plugin | 0oa1kcigd9Kob07k05d7 | Okta Browser Plugin | [ | | | | | ] | | oidc_client | 0oa1mf9l3dw26foa25d7 | Test Private KeyPair | [ | | | | | { | | | | | "id": "00u1kcigdvWtR96HY5d7", | | | | | "scope": "USER", | | | | | "_links": { | | | | | "app": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7" | | | | | }, | | | | | "user": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/users/00u1kcigdvWtR96HY5d7" | | | | | } | | | | | }, | | | | | "status": "ACTIVE", | | | | | "created": "2021-12-10T07:13:42Z", | | | | | "profile": { | | | | | "name": "Arnab Ghosh", | | | | | "email": "arnab@turbot.com", | | | | | "gender": null, | | | | | "locale": "en-US", | | | | | "region": null, | | | | | "country": null, | | | | | "picture": null, | | | | | "profile": null, | | | | | "website": null, | | | | | "locality": null, | | | | | "nickname": null, | | | | | "zoneinfo": "America/Los_Angeles", | | | | | "birthdate": null, | | | | | "formatted": null, | | | | | "given_name": "Arnab", | | | | | "family_name": "Ghosh", | | | | | "middle_name": null, | | | | | "postal_code": null, | | | | | "phone_number": null, | | | | | "street_address": null | | | | | }, | | | | | "syncState": "DISABLED", | | | | | "credentials": { | | | | | "userName": "arnab@turbot.com" | | | | | }, | | | | | "lastUpdated": "2021-12-10T09:34:59Z", | | | | | "statusChanged": "2021-12-10T07:13:42Z" | | | | | }, | | | | | { | | | | | "id": "00u33m8oarePFyaTm5d7", | | | | | "scope": "USER", | | | | | "_links": { | | | | | "app": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7" | | | | | }, | | | | | "user": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/users/00u33m8oarePFyaTm5d7" | | | | | } | | | | | }, | | | | | "status": "ACTIVE", | | | | | "created": "2021-12-10T10:49:28Z", | | | | | "profile": { | | | | | "name": "Soura Ghosh", | | | | | "email": "arnabg57@gmail.com", | | | | | "gender": null, | | | | | "locale": "en-US", | | | | | "region": null, | | | | | "country": null, | | | | | "picture": null, | | | | | "profile": null, | | | | | "website": null, | | | | | "locality": null, | | | | | "nickname": null, | | | | | "zoneinfo": "America/Los_Angeles", | | | | | "birthdate": null, | | | | | "formatted": null, | | | | | "given_name": "Soura", | | | | | "family_name": "Ghosh", | | | | | "middle_name": null, | | | | | "postal_code": null, | | | | | "phone_number": null, | | | | | "street_address": null | | | | | }, | | | | | "syncState": "DISABLED", | | | | | "lastUpdated": "2021-12-10T10:49:28Z", | | | | | "statusChanged": "2021-12-10T10:49:28Z" | | | | | } | | | | | ] | | oidc_client | 0oa1mf49z9iZurdzA5d7 | Service Client Name | [ | | | | | { | | | | | "id": "00u1kcigdvWtR96HY5d7", | | | | | "scope": "USER", | | | | | "_links": { | | | | | "app": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1mf49z9iZurdzA5d7" | | | | | }, | | | | | "user": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/users/00u1kcigdvWtR96HY5d7" | | | | | } | | | | | }, | | | | | "status": "ACTIVE", | | | | | "created": "2021-12-10T07:13:42Z", | | | | | "profile": { | | | | | "name": "Arnab Ghosh", | | | | | "email": "arnab@turbot.com", | | | | | "gender": null, | | | | | "locale": "en-US", | | | | | "region": null, | | | | | "country": null, | | | | | "picture": null, | | | | | "profile": null, | | | | | "website": null, | | | | | "locality": null, | | | | | "nickname": null, | | | | | "zoneinfo": "America/Los_Angeles", | | | | | "birthdate": null, | | | | | "formatted": null, | | | | | "given_name": "Arnab", | | | | | "family_name": "Ghosh", | | | | | "middle_name": null, | | | | | "postal_code": null, | | | | | "phone_number": null, | | | | | "street_address": null | | | | | }, | | | | | "syncState": "DISABLED", | | | | | "lastUpdated": "2021-12-10T07:13:42Z", | | | | | "statusChanged": "2021-12-10T07:13:42Z" | | | | | } | | | | | ] | +---------------------+----------------------+----------------------+-------------------------------------------------------------------------------------------+ > select name, id, label, jsonb_pretty(assigned_groups) as assigned_groups from okta_application; +---------------------+----------------------+----------------------+----------------------------------------------------------------------------------------------------------------------+ | name | id | label | assigned_groups | +---------------------+----------------------+----------------------+----------------------------------------------------------------------------------------------------------------------+ | okta_enduser | 0oa1kchdrfcXTbEzV5d7 | Okta Dashboard | [ | | | | | ] | | saasure | 0oa1kchdmerpZir9y5d7 | Okta Admin Console | [ | | | | | ] | | okta_browser_plugin | 0oa1kcigd9Kob07k05d7 | Okta Browser Plugin | [ | | | | | ] | | oidc_client | 0oa1mf49z9iZurdzA5d7 | Service Client Name | [ | | | | | ] | | oidc_client | 0oa1mf9l3dw26foa25d7 | Test Private KeyPair | [ | | | | | { | | | | | "id": "00g33kzj6xOZvlBUQ5d7", | | | | | "_links": { | | | | | "app": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7" | | | | | }, | | | | | "self": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/apps/0oa1mf9l3dw26foa25d7/groups/00g33kzj6xOZvlBUQ5d7" | | | | | }, | | | | | "group": { | | | | | "href": "https://dev-21314997.okta.com/api/v1/groups/00g33kzj6xOZvlBUQ5d7" | | | | | } | | | | | }, | | | | | "profile": { | | | | | }, | | | | | "lastUpdated": "2021-12-10T10:24:03Z" | | | | | } | | | | | ] | +---------------------+----------------------+----------------------+----------------------------------------------------------------------------------------------------------------------+ ```