GCP BigQuery Read & Write permissions

Ask the community
,
1

Does anyone know how to properly configure an external data collection so any user can write to a database hosted in Google Cloud BigQuery? The use case is BigQuery is where I’m storing specific user information so I can perform further analysis on the data. I followed the steps on the velo site(VERY helpful)[1]. However, I cannot find documentation on how to customize permissions to be Read/Write instead of just Read. Right now the aforementioned documentation simply states " 5. To create a PERMISSIONS secret, instead of entering a Secret value , click Browse and select the .json file that contains your permission settings." - I need to know how to create a proper PERMISSIONS secret to give any user write permissions.

Thanks in advance!

[1] Velo: Integrate Your Google Cloud BigQuery Database with Your Wix Site | Help Center | Wix.com

2

If you look a little further up in that same documentation, it shows you an example of a JSON permissions object. In this example, admin and member have READ permissions and only admin had WRITE

Example PERMISSIONS value:  
{
  "collectionPermissions": [
    {
      "id": "Contacts",
      "read": ["Admin", "Member"],
      "write": ["Admin"]
    }
  ]
}
3

Oh wow…I do recall seeing that…any way. Do I “simply” need to add “Member” to the “write”: row and upload the respective JSON following the SECRETS steps?

1 Like
4

No worries! It’s a LOOOONG tutorial and perhaps needs some clarity in that area.

So, disclaimer I have not set up GCP myself, but yes - that should be everything that’s needed. If it doesn’t work, let me know so I can look further and potential advise on updates to this doc if needed

1 Like
5

Ok, that worked like a dream (I believe). I’m getting a good config response from the endpoint[1]. However, permissions still don’t seem to be updated on the site. Do you know if there is a waiting period or something else I have to do to get the permissions “consumed” or persisted on my wix site?

700×894 22.1 KB

6

Can I assume you followed the rest of the tutorial past this point to the end with no other issues? And how are you testing that the permissions are working/not working?

7

I did follow the rest of the tutorial and I can confirm my site/GCP connection works as expected. I have narrowed it down to this…In the directions/tutorial it states " id: The collection ID". So I first assumed it should be the name of the External Collection in wix (in my case that name is ‘gcpOperationalDB’). However, that did not work. By “did not work” I mean in the wix UI it still says that just the Admin has read-only permissions. So I thought maybe the id was referring to the GCP schema given that this is in GCP. So I changed the id value to “self-tape-may.self_tape_may_data”, but I’m still seeing that Admin has all of the permissions and Member doesn’t have any. Screen shot attached.

So I guess I have two questions:

  1. What does “id” in the JSON file refer to? Wix collection name or GCP schema?
  2. Is the “member” role a default role or do I need to configure that somewhere in Wix?

1940×897 198 KB

8

The “id” is referring to your collection ID which may differ from the collection name and is case sensitive. You can get to this by clicking on collection settings in the 3 dot menu next to the collection name in your editor. In this example, my name and id are the same, but this is not always true.

I would also test the functionality as well (versus just checking the UI) as it’s possible that the UI is not reflecting properly but the permissions are correctly supplied. If so, that would be a bug

1 Like
9

Ok - So I have verified, to the best of my knowledge, that I am utilizing the proper Collection ID. The problem I have is that I’m referring to an external collection and so the Collection ID is not accessible how you are describing. In my code I have been able to reference the collection utilizing the name of “gcpOperationalDB/view-stm-leaderboard”. I have tried that with the JSON[1], but it still doesn’t seem to be working.

[1]

[
  {
    "id": "gcpOperationalDB/view-stm-leaderboard",
    "read": [
      "Admin",
      "Member"
    ],
    "write": [
      "Admin",
      "Member"
    ]
  }
]
10

Is the content manager displaying your external tables?

If you don’t know what I mean by that, look at the section “Connecting to your Wix site” in the tutorial

11

Yes it is. I have been successful in developing a lot of the features and functionality of my side (code can be found here[1]). When I go into content manager and try to follow the steps to view the Collection Name and Collection ID the option of navigating to the “Collection settings” is greyed out (screen shot below[2]).

[1] https://github.com/JHGelpi/selftapemay/blob/main/userProfilev2.js
[2]

1394×423 47.9 KB

1413×877 52.7 KB

1 Like
12

Okay, in gcp, is this the table(collection) ID? view-stm-leaderboard

And I assume you also tried that without the path?

13

Yes - there is a table named ‘view-stm-leaderboard’. I did try just the table name and that didn’t work. I have the same results of it being “read only”.

1 Like
14

okay, I am going to have to ask someone else at this point. I have a feeling somehting in the docs is not quite clear but I am sadly out of ideas at this point. Getting a response may take some time so if this is blocking you in a critical way can you open a support ticket and also send me that number for reference?

that can be done here if you choose to: https://www.wix.com/contact

15

one more thing to note, per the docs you will not see the permissions reflected in the editor at this time. So it’s possible one of your configurations was correct but unless tested you would not have known (if that helps problem solve)

docs quote:
" Note: Customizing permissions for external databases is currently a developer preview feature, and may change. Changes to permissions settings are not reflected in the Wix Editor."

1 Like
16

ok, thank you. I will be opening a ticket because I need to go live with this site in a few weeks time and we need to perform some testing prior to that. I’ll open a ticket and send it to you. Thank you so much. One more question…

Where can I view the roles that exist or are assigned to members of my site? All this time I have been assuming that the “member” role is automatically assigned to anyone who creates a login. Is it possible that the “member” role is not being assigned to people who create a login on the site?

17

Ok - I will go back and double - check all the configurations with actual interactions to see. Thank you.

18

If you are using the members area and haven’t added any custom logic, then your new members should be given a role of member. You can see where to check member roles in the dashboard here: https://support.wix.com/en/article/site-members-managing-your-member-roles

Definitely link this forum thread in your support request so that they know what we have tried here as well.

19

Good news! :-). I have been able to get the permissions to work with a single object. However, I need to grant read/write permissions to multiple collections. Do you happen to know the structure for the JSON to enable permissions on multiple collections? I have tried this[1] and it does not work.

[1]
Attempt #1
{
“collectionPermissions”: [
{
“id”: “viewSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”],
}
{
“id”: “tblSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
}
]
}

Attempt #2
{
“collectionPermissions”: [
{
“id”: “viewSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”],

  "id": "tblSTMParticipantData", 
  "read": ["Admin", "Member"], 
  "write": ["Admin", "Member"] 
}

]
}

20

@amandam I believe we can call this thread resolved! Thank you so much for all of your help. To summarize my final solutions for future reference…

  1. The JSON structure for multiple tables in an external GCP BigQuery is below[1]. I think it would be helpful if the documentation referenced in my original post outlined what needs to be done for multiple tables (or at least the JSON structure).
  2. I learned that even if your intent is to simply READ from a BigQuery view you still need to make it Read/Write if you want objects (e.g. a textbox) to reference it and be editable. This creates a bit of a quandry because views by definition are read only. My solution to this was to query the view on page load and drop the necessary data into the objects w/o joining the objects to the actual view.
  3. When using GCP BigQuery (and presumably any cloud provider) for your database you will run into record locks if you are trying to update an existing record. The way I got around it is I created a view of the data that pulls the max row for a given user. This means I only had to append new records to my BigQuery table which is not a problem at all and I completely avoid record lock (aka Streaming Buffer conflicts).

[1]
{
“collectionPermissions”: [
{
“id”: “viewSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
},
{
“id”: “view-stm-leaderboard”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
},
{
“id”: “tblSTMParticipantData”,
“read”: [“Admin”, “Member”],
“write”: [“Admin”, “Member”]
}
]
}