[Feature]: Need per user authentication for datasources

[sumitsum]

Is there an existing issue for this?

• [X] I have searched the existing issues

Summary

Ask here is that the datasource for each end user should use a different set of credentials based on the user. A use case here is Postgres RLS (Row Level Security). To use RLS, the query must be run with the user credentials of each logged in user.

Why should this be worked on?

Adds new capability.

[unknown1337]

Thanks a lot for adding this issue @sumitsum after our chat. One important sidenote from your side:

1. note that an Appsmith postgres connection creates a persistent connection for EACH user. If many users are online in parallel then many connections are open (which is not the case for a REST api through e.g. supabase)

[bobhenkel]

This would be a great feature. I have users setup in my postgres database and I have roles with extensive permission control that I would like enforced when they query my postgres data source.

[bobhenkel]

I may have found a workaround for this that gives the desired behavior. While maybe not as ideal as passing in the user creds to the connection string, I think it might let me continue my solution.

First I tried to use the Postgres SET ROLE command before my query in the appsmith query box.

SET ROLE '{{appsmith.user.email}}';
select age, name, sales from sales;

The problem with this is appsmith looks at the data returned from the SET ROLE command and returns an empty result(affectedRows 0) because SET ROLE doesn't return anything(it's not supposed to). It appears this causes appsmith to not show the results of the actual query on line 2 above.

So my solution is to push all of this into a stored function in the database and pass it the appsmith username that matches a role I have in Postgres.

CREATE ROLE "frank" WITH 
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT
    LOGIN
    NOREPLICATION
    NOBYPASSRLS
    CONNECTION LIMIT -1;

GRANT USAGE ON SCHEMA demo TO frank;
grant select on demo.sales to frank;

CREATE OR REPLACE FUNCTION demo.get_sales(email text)
RETURNS SETOF demo.sales AS $$
BEGIN
    EXECUTE format('SET ROLE %I', email);
    RETURN QUERY SELECT * FROM demo.sales;
END $$ LANGUAGE plpgsql;

-- Revoke from all users
REVOKE EXECUTE ON FUNCTION demo.get_sales(text) FROM PUBLIC;

-- Grant to a specific users
GRANT EXECUTE ON FUNCTION demo.get_sales(text) TO frank;

With that in place, you can put this query in the appsmith query box to get the needed results of switching the role to the currently active user using the appsmith app. Make sure if you use this that your database role name matches the {{appsmith.user.email}}

SELECT * FROM demo.get_sales('{{appsmith.user.email}}');

I would still appreciate it highly if I could get this approach to work in appsmith. I imagine people without extensive database backgrounds would appreciate it even more to avoid all the extra "stuff" that is needed.

SET ROLE frank;
select age, name, sales from sales;

This approach at least doesn't require a user to create a special stored function for all their queries.

[sumitsum]

Thanks @bobhenkel for sharing a detailed potential workaround. Please do share if you are able to implement the workaround.

[JyotishVyavasthapak]

Is there any progress on this? It would be a very useful feature as I have complex security implemented in the DB I would like to utilise through appsmith.

[infinitetrooper]

User requests from Atom Learning and Funding societies for such a feature. They want users to call an auth-api after SSO and use the token from that API in the following API calls.

[Avinint]

It would be great. As a new user I thought I had to add the bearer token in the Authentication tab which is obviously not the case. That tab is quite misleading.