search

appsmithorg / appsmith

Platform to build admin panels, internal tools, and dashboards. Integrates with 25+ databases and any API.
https://www.appsmith.com
Apache License 2.0
34.19k stars 3.7k forks source link

[Feature] In a Postgres datasource, allow choosing a specific schema, and/or only display accessible tables #8101

Open bamboowonder opened 3 years ago

bamboowonder commented 3 years ago

Summary

In a Postgres datasource, Instead of automatically querying pg_catalog for all tables/schemas, allow for a specific "schema" to be chosen, with the default being "public".

Motivation

Currently, when choosing a Postgres database with many schemas besides "public", all the tables from all schemas are displayed in left panel, whether the database user has access to them or not. (This is a quirk of Postgres, that querying the pg_catalog table shows all schemas and tables whether privileges have been granted or not.)

Additional Context

There are two approaches, I've seen to this problem, ideally both may be implemented.

  1. add a Schema field to the form where datasource is setup. Default is "public".

  2. Metabase handles this beautifully, when adding a datasource, they query the privileges to only display tables that the database user has access to. It would be great if Appsmith could do this as well.

    Attached is a screenshot of the current result of adding a database with many schemas.

Screenshot from 2021-09-30 21-19-38

dncpax commented 2 years ago

an additional problem with this is that a large number of tables slows appsmith a lot... also the list of tables is ordered by table name (ignoring schema name) and that makes it super hard to find the right table...

dncpax commented 2 years ago

Looking at new version 1.6.7 the problem persists even though the UI has changed. Leaving here comments in hopes this will help future development. When creating a page for a table appsmith now shows a dropdown with all tables from all schemas, ordered by tablename. So all schemas are mixed up together. And the dropdown does not allow you to input some text to filter the list. It's practically impossible to find the table you want. And it's slow due to the large number of tables. You if try to create a query instead of a page, appsmith shows all tables on the explorer widget, which gets really slow with so many tables. They are also ordered in the same way. All of appsmith gets really slow.

My solution today: create a new empty database. Connect it to the production database by using postgres_fdw (foreign data wrapper). Create foreign tables for just the tables needed by the application. In appsmith connect to this new database - you will see only the few necessary tables. This is the only way I found to be able to work with appsmith and a "normal" production database.

Nikhil-Nandagopal commented 2 years ago

I think the core solution here is to not automatically display the tables in the entity explorer till a user asks for them. This will prevent the app from slowing down whenever Appsmith loads. This is being solved in our new deploy preview. https://appsmith-git-feat-organizeentityexp-get-appsmith.vercel.app