When using header-based authentication, and trying to use getAccessToken, I get “Insufficient document access”. May you please tell me how my Grist instance should be configured for it to work?
This is within a custom widget, right? When you say header-based authentication, do you mean specifically the Authorization header? That would make sense, but I’m not actually aware of header-based support for using the tokens, just narrow support for an auth query parameter (tokens were added specifically so that working URLs for image attachments could be created in custom widgets). I could have missed/forgotten something though?
You’re right, this is within a custom widget: I’m trying to use my sql widget in my self-hosted instance.
By header-based authentication, I mean that I use GRIST_PROXY_AUTH_HEADER=x-forwarded-user.
Oh I see, got it. Hmm, I wouldn’t have expected that to make a difference. Does the document have any access rules? Specifically, does the user account you are testing with have full read access to the document? The /sql endpoint currently needs that, since we can’t determine before the fact what tables it will be accessing.
If there are no access rules, this feels like a bug.
1 Like
This document has many access rules, but I’m testing with owner account. I’m going to test with a simpler document to make sure.
OK it works with a document without any access rule. So if I get it well, there’s no way to use /sql without full read access to document?
That said, there’s something I don’t understand: if I copy my API Key from Profile Settings, then use curl or any client (Insomnia in my case) to access the /sql endpoint, it works. So why doesn’t it with getAccessToken?
While testing, I noticed that using the API Key with curl or Insomnia bypasses access rules: I can read the content of a table even though I denied reading rights on that table in Access Rules.
Access rules apply to the REST API; if you are seeing unexpected results with them, then something is up. Any chance you could share a screenshot of your rules? (only if they aren’t sensitive of course)
That’s right, currently before allowing a SELECT, the code will check the user has the right to read everything in the document. Doing something more nuanced would be possible but a challenge. Does that hamper your use-case for the endpoint?
So here is a showcase, with this document: SQL - Grist.
On this screenshot, you may see two browser windows, one with the access rules, another one with a forbidden SQL query (using getAccessToken). On the left, you may see that the curl query using my API Key is working.
I made the document world-editable in case you’d like to experiment.
Does that hamper your use-case for the endpoint?
Sure, as I thought I could use it to make a powerful search widget for all users; but nothing necessary, as I already have a workaround with the eval trick.
Thanks for the test case @jperon. I can replicate your results. What I see is that an owner of a document is allowed to use /sql even if the access rules don’t grant full read access to them. This is not totally unreasonable, since an owner has the power to change those rules, but not what I expected from reading the code. I’ll look into it.
I can see this behavior with an user who is editor (not owner) on our base.
But in any case, why does it work with Authorization: Bearer …, and not with tokenInfo.token?
Really? Would you be willing to add me as an owner on SQL - Grist so I can look it over fully? I think I’m missing something in replicating this.
Hi @jperon, is it possible that this special permission is turned on in your doc?
I see that this flag is by default turned on for an owner:
That would explain why an owner can use /sql. But an editor should not be able to if they don’t have full read access or the right to copy in entirety, that worries me.
I think I understand this too now - if a “read only” token is requested, then the access level of any requests made with it is dropped to “Viewer” level. If the “full copies” special permission is what is allowing calls to the REST API to succeed, a viewer (or editor) wouldn’t by default have this permission.
I think you’re getting closer: I didn’t have any special permission for editors, but I had set a default read restriction for those who where neither owner nor editors. By removing this restriction, I can access the /sql endpoint without setting the Bearer. But if I enable any reading restriction on any table for any user, no user can access the endpoint without setting the Bearer. Even more: if I set both the Bearer and the auth=tokenInfo.token parameter, I get insufficient document access.
So, to sum things up:
- if there is no reading restriction in Access Rules: any user can acces the endpoint with the
tokenfromgetAccessToken; - if there is any reading restriction for any user:
a. nobody can access the endpoint with thetoken;
b. those who don’t have any reading restriction can access the endpoint usingAuthorization: Bearer ….
I think it would be handy if the token gave access to those who don’t have reading restrictions. The best thing would be for this endpoint to be more granular, allowing access to what is allowed to each one, but I understand it could be a challenge and not a priority.
Really? Would you be willing to add me as an owner on SQL - Grist so I can look it over fully? I think I’m missing something in replicating this.
Sorry, I had missed this post. That said:
- I was mistaken on my statement;
- I don’t know your email to add you as an owner.
I have updated SQL widget to give the possibility to set API Key. That said, it doesn’t work as is because of CORS policy: it works on my own instance, because my widgets are hosted on the same domain as Grist itself.