API in public app throws 400 error, same api works fine in logged in mode

We have an app in retool. The apis in app are running fine in logged mode (where one can see "Edit app" and "Share" button in top bar).

But the same app throws 400 (bad request) when used in public mode (without login).

It throws a weird database error

Is the same app in edit mode hitting the same db? And if not is the public db missing the group by clause in the query that runs successfully when in edit mode? Are there any restrictions on and if there are two dbs?

The app is using the same db in both modes not two different dbs. What do you mean by "public db" should I change any settings in db to make it accessible for public apps?

Is the exact same query being used when in edit mode and in public viewable mode?

Yes, the same query is used in edit mode and public viewable mode.

What about preview mode?

Same DB in preview mode too

Without being able to access your app I can't really say what else to look for.... @Tess @Kabirdas @joeBumbaca may be able to assist as they are on the Retool team. Sorry I cannot help anymore.

1 Like

@Tess @Kabirdas @joeBumbaca any help on this?

Hi @Jagan, welcome to the forum! :wave:

Quoting @victoria on this topic:

"... does this API query have some sort of dynamic authentication settings? On public links, we actually collect no user info, which means any kind of authentication would be challenging. All authentication is associated with specific Retool user accounts, and since public app users are fully anonymous there is no user to work with that system."

What type of resource are we using for this query? :face_with_monocle:

@Paulo I understand what you guys are suspecting. The api doesn't use any user information (like getting username or userid from token, auth headers etc.) to run the query.

  json_agg(json_build_object('characteristic', characteristic, 'isPresent', "isPresent") order by "isPresent" desc) as characteristics,
   SUM(CASE WHEN psc."isPresent" = 'Yes' THEN 1 ELSE 0 END) AS featureCount
  catalog_management_product_offering po
  LEFT JOIN product_specific_characteristics psc ON po.id = psc."productId"
  LEFT JOIN sdwan_product_images pi ON po.name = pi."productName"
  GROUP BY po.id, pi."imageObjectId", po.name
  order by featureCount

This is the entire query. As you can see, there are no variables in it, no user variables fetched from token, auth headers etc.

Thank you for sharing more details on this! :slightly_smiling_face:

From looking at the SQL error you are getting, I wonder if we may have enabled Releases for the app. Perhaps the public app is showing an older version, where the query was different.

Could you check this tab?

Yeah there was a release it seems. I created another release just now and I still see some database issues.

On the first screenshot, it looks like we may have not clicked "Save & Run" on the "sdwanProducts" query. Try saving it, running it to make sure everything is still working, create a new release, and check the public app after a minute or two. If this does not solve the issue, come to Office Hours tomorrow at 11am PST. We are happy to take a closer look at what is going on.

Wow, after clicking on "Save & Run" and creating a release it works. Not sure why is it this way.

Thanks @Paulo

You are welcome! I'm glad this solved your issue. :slightly_smiling_face:
By design, queries are not save automatically to prevent accidental changes. If we create a release before clicking that button, the release does not included the updated query.