How to get retool user id?

With Retool's new pricing, we're finally going to be able to build the large internal app that's currently in react, to Retool. Great decision :+1:

The way we're currently authenticating our Retool users to our external APIs involves using their Retool current_user.id value. For that to work, when we initially onboard a new user to Retool we need to get their Retool user id; in the past, we would get this by manually checking the audit logs once the user has signed up on Retool. That won't scale though, it's manual and brittle/a bit dangerous if something gets copy/pasted wrong.

Is there some other way we can get a Retool user's id?

Ideally, we'd be able to use Retool's own API to create a user, and the API response would include the newly created user's id. Doesn't look like that's available via API yet, so what are our options?

I asked a similar question at Retool Office Hours yesterday and the answer was currently no, unless you are Self-Hosted. They can query their user table directly to get this (ask elsewhere how to do this, I only know it is possible.)

One of us should add a feature request.

One of the other participants (sorry I did not catch who or I would give attribution) had the germ of an idea which I was able to grow into a workaround yesterday. When your user first logs into an app (maybe an onboarding app everyone signs into first) you run a js query.

  1. Have a user table which has their email (you control that at least) and a retool_id field.
  2. Query your users table by the current_user.id
  3. If you get no results, query your user table by email.
  4. If you get a result, update your user table with the retool_id
  5. If you get no result, the user is not in your user table yet or their email is wrong

Here is how I implemented that for my use case (external clients given access to their portal):

// Set 'Keep variable reference inside the query in sync' and 'Run on Page Load Option' options in Advanced Tab
let customer
// Get the customer by the logged in Retool User
customer = await qryCustomerByRetoolId.trigger()
if (customer.length > 0) {
  await currentCustomer.setValue(customer[0])
  return
}

// If we do not find that customer by a Retool ID then we look for them by email
customer = await qryCustomerByEmail.trigger()
if (customer.length > 0) {
  // If we find them this way then set the Retool ID in that record.
  // This will always happen when they first co to one of their pages and should not happen again.
  await qrySetRetoolId.trigger({additionalScope: {customer_id: customer[0].id}})
  // Remember the customer's record to use in the app
  await currentCustomer.setValue(customer[0])
  return
}
-- qryCustomerByRetoolId
select * from customer
where retool_id={{current_user.id}}
-- qryCustomerByEmail
select * from customer
where poc_email={{current_user.email}}

-- qrySetRetoolId

If I fail to get an email match I display this Alert component:

The account reps can manually add the retool_id.

Now when a client logs into their portal, I can link their retool_id to their customer_id and then get their records.

2 Likes

We have a similar situation we are trying to figure out, bradlys suggestion would work fantastically for step two. Step one would be adding a retool user within a retool app.

We need the ability for end users to invite users. I could do this inside the "Admin" app but Retool would need to have some sort of API so that this doesn't have to be a manual task for the account administrator.

Hey! There are a few threads out there requesting a user API - here are two that I know of from the feature request section (one, two). They don't specify a user invite API but that has been requested elsewhere as well. We have a couple of internal tickets for tracking the requests and I've linked this thread to them so we can pass it along if they're included.

Thanks for posting your thoughts on a workaround here @bradlymathews!

Also, to answer the question about how to do it on self-hosted. You can configure a Postgres resource to hit the storage database for your instance (this is separate from Retool Database) using the same credentials that are in your instance's environment variables.

The default setup would look something like this:

There's a users table in the database that should have the information you need:

Thanks, and thanks to everyone else.

I'll add a few things that could help if you're thinking of using this data for secure use-cases:

  1. Using current_user.sid rather than id would seem to add a decent bit of additional security. Whereas id is an int that obviously can be easily guessed by an attacker, sid is a unique string.
  2. In Settings -> Beta make sure to check "Prevent query variable spoofing" I think this feature was added in response to my question Is the current_user global secure? (Note to Retool -- this should be out of Beta, it's an important piece of security)

Along with IP whitelisting and of course users' needing to log in to Retool, hopefully this all creates some basic decent auth.

I think I am missing something here and I could use your help understanding.

The current_user.id is read from the currently logged in user, so they would have to be logged in already to see it. Anyone else logged in could not spoof the id in the query I am running to get the customer record using current_user.id. So I am not seeing how even sequential ids like 1, 2, 3, 4 vs UUIDs would be any less secure. Nothing is hitting the querystring and and I believe comm between the client and Retool is encypted so no man-in-the-middle could get the id if they could even use it.

Where is the real-world kink in the chain - if someone correctly guessed a retool id, how could they actually it exploit in my scenario? I am sincerely asking - I am not ignorant of security, but I know there is always a lot more to learn!

1 Like

Anyone else logged in could not spoof the id in the query I am running to get the customer record using current_user.id

They could, before I asked this question: Is the current_user global secure? and Retool fixed it. They could now, if you forget to turn on the "Beta" anti-spoofing option or another admin accidentally turns it off. And they could in the future, if Retool itself has a bug.

In all of those scenarios, using sid instead of id would probably stop the attack from taking place. Are these scenarios likely? No, but that's the case with nearly all security-related incidents. "We never thought this was even possible...."

In any use case where an id is being used as even a tiny part of the "security" aspects of the use case, I don't think anyone would purposefully choose an integer over a uuid :slight_smile:

I'll also add that I'm personally thinking of malicious API calls originating outside of Retool. Should these ever be able to succeed? No. IP whitelisting and login creds should stop them in their tracks. But if in the event we totally screw allll of that up, the very last line of defense requiring a hacker guess sid makes me sleep a lot better than an integer!

1 Like