"pgsql" schema is not showing up in postgres dataset

[NavinAsokan]

Bug description

The bug is that when I successfully created a new postgres connection, All the schemas are listed except the schema name "pgsql". This is a legacy system if we change the schema name then this will be a massive change from the database backend. Superset version is 3.1.0

Screenshots/recordings

Superset version

master / latest-dev

Python version

3.11

Node version

I don't know

Browser

Safari

Additional context

Superset version is 3.1.0

Checklist

• [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
• [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
• [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot[bot]]

Hello @NavinAsokan! I'm here to assist you with any bugs, questions, and contributions. Let's work together to resolve the issue you're encountering.

The issue with the "pgsql" schema not showing up in Superset could be related to the security manager's filtering logic, which determines schema accessibility based on user permissions. Superset's security manager uses the get_schemas_accessible_by_user method to filter schemas according to the user's access rights. If the "pgsql" schema is not included in the user's permissions, it will not be displayed ^[1].

To resolve this, you should check the following:

1. User Permissions: Ensure that the user has the necessary permissions to access the "pgsql" schema. This includes checking both direct schema access permissions and permissions derived from accessible datasources.

2. Security Manager Configuration: Verify the configuration of the security manager to ensure that it correctly reflects the permissions for the "pgsql" schema.

3. Schema Access: Confirm that the "pgsql" schema is included in the schema_access permissions for the user.

By addressing these areas, you should be able to determine why the "pgsql" schema is not appearing and make the necessary adjustments to display it.

To continue talking to Dosu, mention @dosu.

[NavinAsokan]

Hi, Please share if we need to modify the manager.py under superset installation. We also crated a new schema in the database. It is listed in the postgres dataset schema list. We have user level access to "pgsql" schema

[dosubot[bot]]

To adjust schema visibility, you typically don't need to modify the manager.py file directly. Instead, you should focus on configuring user permissions correctly through Superset's security manager. This involves ensuring that the user has the appropriate permissions to access the desired schema, such as the "pgsql" schema in your case. You can manage these permissions through Superset's user interface or by using the security manager's methods to add or modify permissions programmatically. If you find that the existing permissions setup is insufficient, you might consider reviewing the logic in manager.py to understand how permissions are applied, but direct modifications are generally not recommended unless necessary.

To continue talking to Dosu, mention @dosu.

[NavinAsokan]

please share the steps to assign this permission using superset user interface

[dosubot[bot]]

To assign schema access permissions using the Superset user interface, follow these steps:

1. Navigate to Security: In the Superset menu, go to Security and select List Roles.

2. Select or Create a Role: Find the role you want to modify or create a new one by clicking the + sign. If creating a new role, give it a name.

3. Assign Permissions: In the role's settings, look for the Permissions section. Use the typeahead feature to search for and select the specific schema permissions you want to assign. Permissions are typically in the format [database].[schema].

4. Assign Users to the Role: Ensure that the users who need access to the "pgsql" schema are assigned to this role. You can do this in the same role settings page.

5. Save Changes: After assigning the necessary permissions and users, save your changes.

By following these steps, you can manage schema access permissions directly through the Superset user interface without modifying the code.

To continue talking to Dosu, mention @dosu.

[NavinAsokan]

We are unable to see the access to schema "pgsql" in the permission list. Remaining other schemas are visible. Pls suggest

[dosubot[bot]]

If the "pgsql" schema is not visible in the permissions list while other schemas are, it might be due to specific conditions or configurations affecting its visibility. Here are a few things to check:

1. Schema Permissions: Ensure that the "pgsql" schema has the correct permissions set in Superset. You can verify this by checking the role permissions in the Superset UI under Security > List Roles. Make sure the role associated with the user has the [database].[schema] permission for "pgsql".

2. Security Manager Configuration: Superset's security manager uses the get_schemas_accessible_by_user method to determine schema visibility based on user permissions. If the "pgsql" schema is not included in the user's permissions, it will not be displayed ^[1].

3. Default Catalog: If the schema is associated with a non-default catalog, it might be excluded from the permissions list. The logic in the downgrade_schema_perms function indicates that schemas not associated with the default catalog may have their permissions deleted ^[2].

4. Configuration Settings: Check if there are any specific configurations or settings in Superset that might affect schema visibility. This includes ensuring that the security configurations are correctly set up to allow access to the "pgsql" schema ^[3].

By reviewing these areas, you should be able to identify why the "pgsql" schema is not appearing in the permissions list and make the necessary adjustments.

To continue talking to Dosu, mention @dosu.

[NavinAsokan]

For the below option, I am not getting the rights to assign schema for pgsql to user. I can see rights to assign other schemas that are visible which is the cause for raising this bug. The pgsql schema is setup the same way as other schemas. No change in setup. Pls suggest

Schema Permissions: Ensure that the "pgsql" schema has the correct permissions set in Superset. You can verify this by checking the role permissions in the Superset UI under Security > List Roles. Make sure the role associated with the user has the [database].[schema] permission for "pgsql".

[dosubot[bot]]

If the "pgsql" schema is set up the same way as other schemas but is not appearing in the permissions list, consider the following possibilities:

1. Permission Synchronization: Ensure that the permissions for the "pgsql" schema are synchronized with Superset. Sometimes, changes in the database might not immediately reflect in Superset. You can try refreshing the metadata or restarting the Superset server to see if this resolves the issue.

2. Schema Naming or Pattern: Check if there is any specific logic in Superset that handles schema visibility differently for certain schema names or patterns. Although I couldn't find specific logic for schema name patterns, it's worth reviewing any custom configurations or scripts that might affect schema visibility.

3. Database Connection and Catalog: Verify that the database connection and catalog settings are correctly configured. If the "pgsql" schema is associated with a non-default catalog, it might be excluded from the permissions list due to the logic in the downgrade_schema_perms function, which deletes permissions for non-default catalogs ^[1].

4. Superset Logs: Check the Superset logs for any errors or warnings related to schema permissions. This might provide insights into why the "pgsql" schema is not appearing.

If none of these steps resolve the issue, consider reviewing the Superset codebase or configurations for any custom logic that might affect schema visibility.

To continue talking to Dosu, mention @dosu.

[NavinAsokan]

Restarted superset . Still the issue persist. Checked the logs. Logs didn't provide much information on this

[villebro]

Postgres is very widely used in Superset, so I would be very surprised if this is a bug in either the Postgres connector or Superset.

A few thoughts:

• Check that you have adequate permissions in Superset. Start with a user that has Admin role to ensure it's not a perm issue.
• Validate your connection string for the referenced catalog. Are you sure the catalog in Superset is the same as the one you're using in the other database IDE?

[NavinAsokan]

Thanks for the update. Yes. I am using Admin permission to login to superset and using the same connection string as that of the database IDE. The logs didn't provide much information as well while listing the dataset postgres schemas in superset. Is there any way we can provide more verbose info from the log?

[villebro]

Can you post a redacted copy of your connection string in Superset and one for the IDE? Also, does the schema dropdown show other schemas? And do you see an error toast after it stops loading?

[NavinAsokan]

From superset : postgresql+psycopg2://db_name:XXXXXXXXXX@hostname:port#/username
From IDE: url: jdbc:postgresql://hostname:port#/db_name

[villebro]

I see you have username catalog in the Superset URL, but db_name in the IDE. If you're referencing different catalogs, you'll likely not find the schemas you see in db_name in the username catalog. IIUC, your Superset connection string should be

postgresql+psycopg2://username:XXXXXXXXXX@hostname:port#/db_name

(make sure the pwd is also updated to reflect username creds)

[NavinAsokan]

My bad on the representation for superset url. I wrongly mentioned that it is in the other format. We already have this format

postgresql+psycopg2://username:XXXXXXXXXX@hostname:port#/db_name.

Still unable to get the pgsql schema name in the schema list. Kindly suggest

[villebro]

Please provide as much additional context:

• double check that username and db_name are the same in both Superset and IDE
• any error toasts showing up during/after schema population?
• how many schemas are expected, how many are showing?
• is only a particular schema missing, or are others missing, too?

etc. Superset doesn't filter out schemas called pgsql, so the likely culprit is the creds that are being used. Again, Postgres is very actively used in Superset, and this is the first time I've heard of individual schemas missing from the schema dropdown unless there are permission issues.

[NavinAsokan]

double check that username and db_name are the same in both Superset and IDE -- Checked the username/db_name are same any error toasts showing up during/after schema population? - No error how many schemas are expected, how many are showing? - except pgsql schema all other schemas are visible is only a particular schema missing, or are others missing, too? -except pgsql schema all other schemas are visible

[villebro]

Highly interesting.. I will try to create a pgsql schema in my Postgres instance today to see if it's missing or not. I'll report back when I've tested.