Database restriction in SQL Lab.

[SanjayJosh]

Make sure these boxes are checked before submitting your issue - thank you!

• [ ✅] I have checked the superset logs for python stacktraces and included it here as text if any
• [ ✅] I have reproduced the issue with at least the latest released version of superset
• [ ✅ ] I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.24.0

I added a presto datasource named as PrestoVizs in this manner:
presto://presto_server_url:8080/catalog_name/db_name , where db_name is mlp_visualizations in this case .
to the list of databases.
However, In SQL Lab , all databases in the catalog are exposed.

Is there any way to restrict the schemas, or in this case the 'databases' from my presto catalog exposed in SQL Lab editor just to the dbname ?
I tried this with an alpha + sql_lab user, apart from admin as well. Is there anyway there is a security role to do this?
Or does one have to change the source code to restrict to just a single schema? If so can you point me in the direction for the same?

[mistercrunch]

There's no way to restrict that at the moment.

[SanjayJosh]

I am thinking of changing/ hardcoding the GET api call for /schemas/<db_id> for now.

[mistercrunch]

You could add a config entry callable filter_schemas(database, user, schemas) that returns a subset of the schemas.

[SanjayJosh]

Alright. Where does that go though?

[mistercrunch]

Add an entry like here, call it SQL_LAB_FILTER_SCHEMAS = None:
https://github.com/apache/incubator-superset/blob/master/superset/config.py#L333

Override in your local superset_config.py

Hook it up in /schemas/<db_id>

[pbr0ck3r]

Supersets allows you to limit access to a database. So why does it not filter out the databases you do not have permissions for. Say you have two databases, x and y. And your user only has access to x, by setting the rule database access on x. Why does supersets not filter out y in the list of databases in the sql editor?

The scenario I am running into is that I have multiple users who work in the same company but should not have access to certain databases. Like I shouldn't be able to see the finances database in the dropdown if I work in development department. I shouldn't even know that database exists. It definitely seems like a security concern.

I would say this is similar to the following issue. #1434

The logic for the fix to not show databases for slices/dashboards needs to be applied to the sql editor.

[pbr0ck3r]

@mistercrunch after doing some more research I have come to the understand that Supersets loads the databases into the localStorage when opening the SQL Editor. It loads them from... /databaseasync/api/read. That backend needs to filter the databases the current user has access to based on their roles.

[mistercrunch]

For the record, to get this to work, you'll have to implement a DatabaseFilter, similar to this SliceFilter https://github.com/apache/incubator-superset/blob/master/superset/views/core.py#L87, and bind it to the DatabaseModelView

[pbr0ck3r]

@mistercrunch created a PR for this issue. #6356

[Damoddhar]

Same issue in superset 0.27.0 version. Is there a way to prevent the restricted user from seeing the other data sources in sqllab

[pbr0ck3r]

@Damoddhar No there is not. The PR I committed and linked above fixes the issue.

[Damoddhar]

@pbr0ck3r No problem. I got database restriction in sqllab and list of databases when clicking Sources >>Databases.
Using your code changes and I did small changes in your code

[tomaszj]

Hi folks, is it possible to progress the PR above to close the issue? It'd be really helpful for me. If there's a way to contribute, let me know!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.