How to disable schema modifications

[notramo]

I want to use Mathesar for browsing the database of a Lucky app. The built-in (Avram) ORM migration feature is used for managing the schema, and schema modifications from Mathesar can cause crashes, as the web server expects a different schema.

[pavish]

@notramo You can create a new 'Standard' Mathesar user from the Users administration page, and provide 'Editor' access to the schemas where you'd like disallow schema modifications for the new user. Editor access allows editing the rows in a table (and all DML operations), and disables schema modifications (DDL operations).

Here are the steps:

1. Click on the gear icon on the top right of the application and select Administration.
   

2. In the left sidebar, click on Users. You'll see the Users administration page. Click on 'Add user' button.
   

3. Add the user details, and choose the 'Standard' role for the user.
   

4. Open the schemas to which you'd like to disable DDL modifications and click on 'Manage Access' button.
   

5. Add permissions to the newly created user to the schema, with 'Editor' access.
   

6. Login with the new user account, and you can find that schema modifications are not allowed for that user.

You can find more documentation here. Let me know if this satisfies your requirement.

[notramo]

Thanks for the detailed explanation. I will take a look.
Are these accounts Postgres roles or these are stored in the Matesar metadata?

[pavish]

Are these accounts Postgres roles or these are stored in the Matesar metadata?

These are stored only in the Mathesar internal metadata. They are not Postgres roles.

Improving our permission system and having better integration with Postgres roles, is on our roadmap. If you have specific usecases or requirements, please let us know. It'll help us with our product direction. Thanks!

[notramo]

In order to improve the security of the database, I would create 2 Postgres roles: 1 with schema modification capabilities for Lucky, and the other without schema permissions for Mathesar. This way, only the Editor and lower permissions would make sense, as no matter how many permissions are given to the Mathesar internal metadata accounts, the Postgres role would not allow Mathesar to make any schema modifications. Is it possible currently?

[pavish]

@notramo

I would create 2 Postgres roles: 1 with schema modification capabilities for Lucky, and the other without schema permissions for Mathesar

This way, only the Editor and lower permissions would make sense, as no matter how many permissions are given to the Mathesar internal metadata accounts, the Postgres role would not allow Mathesar to make any schema modifications. Is it possible currently?

Yes, it's possible, with some manual steps.

• You would have to create an user for Mathesar in your Postgres instance, with CONNECT and CREATE privileges to the database that you wish to connect to Mathesar.

  • During installation, Mathesar creates 3 schemas in your DB, which contains functions and types that are required for Mathesar to operate, namely: msar, __msar, and mathesar_types.
  • The CREATE privilege is required to create these schemas. After installation, you could remove this privilege, if you want to.
  • Note that the CREATE privilege is only required on the database level inorder to create schemas, and does not affect other existing DB objects.
  • If you have already installed Mathesar, and now wish to change the PG user role you've provided to it:
    • you would have to drop the schemas msar, __msar, and mathesar_types, and
    • reinstall Mathesar with the new user role.
    • This is to ensure that these schemas are owned by the PG user that Mathesar uses.
    • There are other manual steps to avoid uninstallation, but this is the fastest option to get to a clean working state.

• The Mathesar admin user will still be able to see all operations on the UI. But while performing the DDL operations on restricted DB objects, they will be met with errors thrown from the DB due to insufficient privileges.

  • This is expected. Please use the user with 'Editor' access level for a better UX.
  • It is in our roadmap to eventually reflect privileges from the DB to the UI, to improve this user experience.

If you need further help with this (including calls with our core team), please let us know.

[pavish]

Hey @notramo, just checking-in to see if your requirements are met. Did the steps I mentioned above work for you, and do you have further questions?

[notramo]

I haven't tried this long guide yet.

[seancolsen]

I'm closing this because it's been open for a while and it seems like @pavish has provided instructions which should help solve the issue you were facing. Feel free to comment if you still need more help, @notramo and we can re-open this issue if needed.