[BUG] Issue with setting up x-ms-pageable in Custom Connector

remyblok commented 1 year ago

Type of Connector

Custom Connector

Name of Connector

Tribe CRM

Describe the bug

I'm building a Custom Connector on the Odata service of Tribe CRM. The default page size is 100 rows. This is also the maximum number of rows that can be retrieved in one call. For listing rows I want to use Pagination to retrieve more rows.

I've added the following to the OpenAPI definition to my list rows.

x-ms-pageable:
    nextLinkName: '@odata.nextLink'

By default the Tribe CRM API does not have a @odata.nextLink element. So with Custom Code I construct the correct URL and add the @odata.nextLink in the JSON response. This works OK.

I can now enable pagination in the action in Power Automate and set the threshold to like 5000. But the result is just 200 records, or better said the first two pages.

First it calls the Custom Connector to retrieve the first page with 100 records. The Custom Code kicks in and adds the next link to the output.
Then the second page is retrieved immediately from the URL provided by the next Link. It does not call my custom connector or my custom code.

How do I know? I ended up with a Unauthorized error when pagination was enabled, it retrieves OK when a single page is retrieved. When I manually put the correct authorization in the query string when constructing the next link the error went away. So no Authorization headers were added by the Custom Connector. This second page also did not have the nextLink added to the output. Also, I added additional Custom Code for debugging, to make sure that my Custom Code is indeed not called.

Is this a security bug?

No, this is not a security bug

What is the severity of this bug?

Severity 1 - Connector is broken or there's a very serious issue

To Reproduce

Create a Custom Connector based on the Tribe CRM Odata api
Enable paging using x-ms-pageable
Use Custom Code to add an @odata.nextLink to the output of the List Operation
Use the connectors in Power Automate and enable paging

Expected behavior

When pagination is enabled, retrieving each page should be handled by the Custom Connector. This includes any authentication, policies or Custom Code that needs to run in order to retrieve the next page correctly.

Environment summary

I have a dev environment in Power Platform where I develop my Custom Connector. I moved the connector to a Sandbox environment. Both environments have the same result.

Additional context

What have I tried and/or found?

I've looked at the x-ms-pageable docs in Autorest This mentioned that you can also supply 'operationName'. But trying to do this I get a validation error in de Custom Connector editor.

Specified swagger has the following errors: 'Definition is not valid.  
Error: 'Critical : paths/~1odata~1{entityName}/get/x-ms-pageable/operationName : 
The property 'operationName' is not allowed and must be removed.

I've looked at the underlying JSON Schema of the Custom connector here on GitHub. There it document x-ms-pageable as well (and indeed does not include 'operationName'). It mentions the following as description for the nextlink url:
```
The name of the property at the root of the response that has the next link url.
Usage: Be sure to use the policy template (id: 'updatenextlink') to enable the correct routing of this link.
```
I'm unable to find any reference or documentation to policy template 'updatenextlink'.
I've tried adding a 'Next' operation to my OpenAPI definition, as is indicated being the default operationName on x-ms-pageable. But you cannot have multiple operations on the same path with the same HTTP Verb. So I'm not sure how I would add this to my OpenAPI definition.
```
The combination of path and method must be unique to each operation. 
There are 2 operations sharing this path and method including: ListRecords, ListRecordsNext
```

Who can help me get the paging working for my Custom Connector? What am I doing wrong? It needs to Custom Code in order to add the @odata.nextLink in de response. So it needs to call my Custom Connector to do so.

Swagger definition:

swagger: '2.0'
info:
  title: Tribe CRM
  description: Connector for Tribe CRM
  version: '1.0'
host: api.tribecrm.nl
basePath: /v1/
schemes:
  - https
consumes: []
produces: []
paths:
  /odata/{entityName}:
    get:
      responses:
        '200':
          description: List of Rows Changed
          schema:
            type: object
            properties:
              '@odata.nextLink':
                type: string
                title: ''
                x-ms-visibility: internal
              value:
                type: array
                items:
                  type: object
      summary: List rows
      description: >-
        This action allows you to list the rows in a Tribe CRM table that match
        the selected options.
      operationId: ListRecords
      x-ms-visibility: important
      parameters:
        - name: entityName
          in: path
          required: true
          type: string
          description: Table name
          x-ms-summary: Table name
          x-ms-visibility: important
        - name: $select
          in: query
          required: false
          type: string
          x-ms-summary: Select columns
          description: >-
            Enter a comma-separated list of column unique names to limit which
            columns are listed
          x-ms-visibility: important
        - name: $filter
          in: query
          required: false
          type: string
          x-ms-summary: Filter rows
          description: >-
            Enter an OData style filter expression to limit which rows are
            listed
          x-ms-visibility: important
        - name: $expand
          in: query
          required: false
          type: string
          x-ms-summary: Expand Query
          description: Enter an Odata style expand query to list related rows
          x-ms-visibility: important
        - name: $orderby
          in: query
          required: false
          type: string
          x-ms-summary: Sort By
          description: Columns to sort by in OData orderBy style (excluding lookups)
          x-ms-visibility: advanced
        - name: $top
          in: query
          required: false
          type: number
          x-ms-summary: Row count
          description: Enter the number of rows to be listed (default = 100, max = 100)
          x-ms-visibility: advanced
        - name: $skip
          in: query
          required: false
          type: number
          x-ms-summary: Skip
          description: >-
            Enter the number of rows to be skipped. For pagination use in
            combination with Sort By.
          x-ms-visibility: advanced
definitions: {}
parameters: {}
responses: {}
securityDefinitions:
  oauth2-auth:
    type: oauth2
    flow: accessCode
    authorizationUrl: https://auth.tribecrm.nl/oauth2/auth
    tokenUrl: https://auth.tribecrm.nl/oauth2/token
    scopes:
      read write offline: read write offline
security:
  - oauth2-auth:
      - read write offline
tags: []

Custom Code:

#nullable enable
public partial class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        // Fix doing requests from the Custom Connector test pane
        var authHeader = Context.Request.Headers.Authorization;
        Context.Request.Headers.Clear();
        Context.Request.Headers.Authorization = authHeader;

        try
        {
            switch (this.Context.OperationId)
            {
                case "ListRecords":
                    var listPaginationProcessor = new ListPaginationProcessor(Context);
                    return await listPaginationProcessor.Process(CancellationToken).ConfigureAwait(false);

                default:
                    return await Context.SendAsync(Context.Request, CancellationToken).ConfigureAwait(false);
            }
        }
        catch (Exception ex)
        {
            Context.Logger.Log(LogLevel.Critical, ex, "Error while processing Operation ID '{operationId}'", Context.OperationId);
            var response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
            response.Content = new StringContent(ex.ToString());
            return response;
        }
    }

    private class ListPaginationProcessor
    {
        private IScriptContext _context;

        public ListPaginationProcessor(IScriptContext context)
        {
            _context = context;
        }

        public async Task<HttpResponseMessage> Process(CancellationToken cancellationToken)
        {
            // decode so we can fix + to %20
            UriBuilder builder = new UriBuilder(_context.Request.RequestUri!);
            builder.Query = WebUtility.UrlDecode(builder.Query);
            _context.Request.RequestUri = builder.Uri;

            // do the actual request to Tribe CRM with the updated URL
            HttpResponseMessage response = await _context.SendAsync(_context.Request, cancellationToken).ConfigureAwait(false);

            if (!response.IsSuccessStatusCode)
                return response;

            // Build the nextLink URI
            var queryString = HttpUtility.ParseQueryString(builder.Query);

            if (queryString["$orderby"] == null)
                return response;

            JObject content = JObject.Parse(await response.Content.ReadAsStringAsync());
            JArray? rows = content["value"] as JArray;

            if (!(queryString["$top"] is string topString && topString is not null && int.TryParse(topString, out int top)))
                top = rows?.Count ?? 100;

            if (!(queryString["$skip"] is string skipString && skipString is not null && int.TryParse(skipString, out int skip)))
                skip = 0;

            skip += top;

            queryString["$top"] = top.ToString();
            queryString["$skip"] = skip.ToString();
            //queryString["access_token"] = _context.Request.Headers.Authorization.Parameter;

            // Recreate the query string, then decode again because of the + to %20 conversion
            builder.Query = HttpUtility.UrlDecode(queryString.ToString());

            // add the next link only if the current page is a complete page
            if (rows?.Count == top)
            {
                content.AddFirst(new JProperty("@odata.nextLink", builder.ToString()));
                response.Content = CreateJsonContent(content.ToString());
            }

            return response;
        }
    }
}

troystaylor commented 1 year ago

I have handled paging before as part of a client project. I am unable to share that code, but the direction I would point you towards is having the custom code retrieve a set number of records (5000?) before returning one response. If you look here, you will see x-ms-pageable is not fully supported: https://learn.microsoft.com/en-us/connectors/custom-connectors/openapi-extensions

remyblok commented 12 months ago

I have looked into the suggestion above. I have code that does this, bit it struggles to keep within the 5 seconds even with all kind of optimizations where I get multiple pages in parallel.

I also looked further and apparently I did not do a good enough search in this repo. There are multiple connectors that use the updatenextlink-policy. most of them are Certified connector. There is one Independent Publisher connector that uses updatenextlink. This is in the AzureADApplication-connector.

The AzureADApplications-connector is created by @paulculmsee and @Amjed-Ayoub. Can any of you comment on this if the updatenextlink-policy is wortking for you? How did you get paging working in the ListApplications-operations?

ImkeF commented 10 months ago

Would love to get this going as well, so any hints are more than welcome.

Also, here is a suggestion to officially support x-ms-pageable in custom connectors: https://ideas.powerautomate.com/d365community/idea/db595779-e186-ee11-a81c-6045bdbcd0a8

Please vote if you agree.

remyblok commented 10 months ago

I have voted :) Thanks for creating the Idea. Unfortunately I do not have more information on this :(

SOE-YoungS commented 10 months ago

I've also got an open ticket with Microsoft about pagination.

We're trying to enable pagination for calls to the Microsoft Graph API endpoint, however, in our case, when pagination is enabled on the action after adding to our custom connectors swagger file for the get users action,

x-ms-pageable:
    nextLinkName: '@odata.nextLink'

We see the "pagination" option in the actions settings. However, when pagination is enabled we get the following error,

 CompactToken parsing failed with error code: 80049217

Yet when disabled, authentication works fine.

Trying to pin the problem down is like extracting teeth (painful).

Can anyone shed any light on the issue we're seeing? If I can get it working, I'll document the steps I took and will post it back here for anyone else having issues with pagination!

ImkeF commented 10 months ago

Thanks @SOE-YoungS , this is exactly the behaviour that I am experiencing as well.

SOE-YoungS commented 10 months ago

Thanks @SOE-YoungS , this is exactly the behaviour that I am experiencing as well.

I've come to the conclusion that the only way to "properly" handle "x-ms-pageable" actions is to use the paconn tool to create the connector.

So, I set about converting my connectors swagger to "swagger.json" format. It already had "x-ms-pageable" defined.

Next up, I created the two files required to create a connector with paconn by renaming my "swagger.json" file to "apiDefinition.swagger.json" and a blank "apiProperties.json" file.

I then set about adding the required JSON to "apiProperties.json", to enable me to upload the connector to PA. Once uploaded, I configured the auth I needed (Azure Active Directory) and saved the connector.

I then used paconn to download the connector & edited the apiProperties.json file.

The crucial bit here is that the following needs to be added to the "apiProperties .json",

"policyTemplateInstances": [
      {
        "templateId": "updatenextlink",
        "title": "Updates next link to make paging work",
        "parameters": {
          "x-ms-apimTemplate-operationName": [
            "{OperationId1}",
            "{OperationId2}"
          ],
          "x-ms-apimTemplateParameter.nextLinkPropertyName": "@odata.nextLink"
        }
      }
    ]

Note: Edit {OperationId*} to match your target "OperationId(s)".

Once this was added, I reuploaded my connector via paconn, then tested it out.

Success! Pagination works, without error!

ImkeF commented 10 months ago

Wow, that looks very promising @SOE-YoungS ! Thanks a lot for writing down the steps, very helpful.

However, I am getting 504 error: "The gateway did not receive a response within the specified time period" I am connected through paconn successfully, have downloaded the connector and adjusted the apiProperties.json. Using the "UI" to trigger the upload: "Please select an environment" is showing up and displaying my environments. So the connection is there. Just that the create or update commands return this error now. Do you have any idea why?

SOE-YoungS commented 10 months ago

Wow, that looks very promising @SOE-YoungS ! Thanks a lot for writing down the steps, very helpful.

However, I am getting 504 error: "The gateway did not receive a response within the specified time period" I am connected through paconn successfully, have downloaded the connector and adjusted the apiProperties.json. Using the "UI" to trigger the upload: "Please select an environment" is showing up and displaying my environments. So the connection is there. Just that the create or update commands return this error now. Do you have any idea why?

I couldn't honestly say. I didn't have that issue.

When it displays the list of environments, are you selecting your default environment?

I have an open ticket for ours currently as creating / modifying connectors within it hasn't worked since the "copilot" updates were released last week.

Targeting other environments works fine, but our default env is throwing errors constantly which has somewhat hindered us.

ImkeF commented 10 months ago

Thanks @SOE-YoungS , this seems to be an issue with my tenant. I tried it in a different tenant and there I could deploy the connector without issues. So thanks again for that!

However, when executing with pagination set on, I am then getting { "error": { "code": "InvalidAuthenticationToken", "message": "The authentication scheme of Key is not supported." } }

Without pagination works fine, but with it, this error appears. Looks a bit like an issue with the underlying API itself?

SOE-YoungS commented 10 months ago

Auth scheme of "Key"? Should this be "API Key"? in which case, you may be missing some single / double quotes in the schema somewhere.

ImkeF commented 10 months ago

Thanks again for bearing with me :) There is no "Key" in my jsons. I am using Azure Active Directory ("aad") as the identity provider:

{
  "properties": {
    "connectionParameters": {
      "token": {
        "type": "oauthSetting",
        "oAuthSettings": {
          "identityProvider": "aad",
          "clientId": "xxxxxxx",
          "scopes": [],
          "redirectMode": "Global",
          "redirectUrl": "https://global.consent.azure-apim.net/redirect",
          "properties": {
            "IsFirstParty": "False",
            "AzureActiveDirectoryResourceId": "https://service.flow.microsoft.com/",
            "IsOnbehalfofLoginSupported": true
          },
          "customParameters": {
            "loginUri": {
              "value": "https://login.microsoftonline.com"
            },
            "tenantId": {
              "value": "common"
            },
            "resourceUri": {
              "value": "https://service.flow.microsoft.com/"
            },
            "enableOnbehalfOfLogin": {
              "value": "false"
            }
          }
        }
      },
      "token:TenantId": {
        "type": "string",
        "metadata": {
          "sourceType": "AzureActiveDirectoryTenant"
        },
        "uiDefinition": {
          "constraints": {
            "required": "false",
            "hidden": "true"
          }
        }
      }
    },
    "iconBrandColor": "#007ee5",
    "capabilities": [],
    "publisher": "Power BI Service"
,
  "policyTemplateInstances": [
    {
      "templateId": "updatenextlink",
      "title": "Updates next link to make paging work",
      "parameters": {
        "x-ms-apimTemplate-operationName": [
          "ListFlows"
        ],
        "x-ms-apimTemplateParameter.nextLinkPropertyName": "nextLink"
      }
    }
  ]
}
}

And in the Definition file the relevant sections look like so:

"securityDefinitions": {
    "oauth2-auth": {
      "type": "oauth2",
      "flow": "accessCode",
      "tokenUrl": "https://login.windows.net/common/oauth2/authorize",
      "scopes": {},
      "authorizationUrl": "https://login.microsoftonline.com/common/oauth2/authorize"
    }
  },
  "security": [
    {
      "oauth2-auth": []
    }
  ]

.. using delegated permissions.

Trying to grab Power Automate data from a basically undocumented API: Using Logic Apps endpoints on this URL: https://emea.api.flow.microsoft.com/providers/Microsoft.ProcessSimple

SOE-YoungS commented 10 months ago

You don't appear to have any scopes defined. I believe you'll need at least "User.Read" to enable login.

You can configure and test the auth via the UI to ensure you have that correct, then once tested as working, switch to paconn & download the connector again, insert the snippet to enable the pagination into "apiProperties.json", then update the connector.

Note: If you make any changes in the future to the connector via the PA UI, you will need to run that second step again after clicking "update connector" in the PA UI.

ImkeF commented 10 months ago

Hi @SOE-YoungS , just to let you know: Your solution works absolutely fine on my Graph connectors! Pagination works perfect. So thanks again for that - really super work!! Also there without any scopes in the security definition. I have the scopes in my app, but never use them in the definition of the custom connectors.

But of course, will give the scopes a try in the definition of the Power Automate candidate asap.

ImkeF commented 10 months ago

No luck with the scopes in the security definition. Also, the action works if pagination is disabled. So not an auth-problem per se. Looks that for the call to the nextLink the auth is going wrong.

SOE-YoungS commented 10 months ago

@ImkeF it dawned on me this evening that the api you are calling may not support '@odata.nextLink' usage.

Having looked at the TribeCRM.nl postman collection, I've come to the conclusion that your C# should work.

Looking deeper at it, I have a feeling that in your case pagination is not working, because "$orderby" may not be set (it's flagged as required for pagination in the API documentation).

Documentation
https://api.tribecrm.nl/v1/odata/Relation_Person?$top=10&$skip=10&$orderby=ID
An API call will return a maximum of 100 entities. If more entities are needed, pagination need to be used.

**Add top, skip and orderby parameters to the query url to add pagination.**

$top

The top parameter will set the maximum number of entitities that will be return. The maximum is 100

$skip

The skip parameter will set the number of entities that is skipped before the entity data is selected.

$orderby

The orderby parameter sets the order in which the entities are collected. This is needed to get an uniform result with multiples calls to paginate the data.

A best practice is to use ID as orderby field.

For example

A call with https://api.tribecrm.nl/v1/odata/Relation_Person?$top=10&$skip=10&$orderby=ID

Will return person 11 till 20 orderd by ID as a response. The first 10 persons orderd by ID are skipped.

When using Top and Skip , orderby has to be used to force the ordering of the entries.

Secondly, I think there is an issue with your $skip logic.

skip = 0;

shouldn't this be,

skip = skip ?? 0;

microsoft / PowerPlatformConnectors