Using the Retool PostgreSQL database for now, but could use MS SQL. I'm confused how to block access to certain tables at the user level or by role.
For example, let's say I have retool user johnsmith@test.com and I add him to permission group "comp_users". I then have a table in SQL called comp_payments. How do I restrict access to this table to only users in the comp_users group, or even at a per user level?
I can create users, roles and permissions in SQL, but then they aren't related to the retool users, since SQL authenticates like a service in retool it seems vs having each retool user also log into SQL directly.
I could of course filter in my SQL queries, something like:
SELECT *
FROM comp_payments
WHERE '{{ current_user.email }}' IN (SELECT email FROM user_access_table);
But that doesn't really seem secure.
Retool permissions seem very basic, like I can only allow or disallow access to the entire database/resource, vs tables specifically.
When I use Dynamics 365 as my REST API, users need to authenticate with their Microsoft 365 account, which then only allows them to view data in queries they have access to in Dynamics 365.
Maybe I'm overthinking this, but what's the easiest way to restrict access to tables in SQL by retool user?
