Assumptions for user role

[mathemancer]

In order to make sure we're all operating under the same assumptions, we need to specify precisely what privileges a user who installs Mathesar on a preexisting DB can be expected to have. (and what modifications to their DB they should be willing to make). I think that a necessary (maybe not sufficient) set of privileges (ROLEs) would be the rights to run:

• CREATE SCHEMA in some database to create a schema for mathesar functions and types, and other schemata for mathesar tables
• CREATE FUNCTION (in the schema for mathesar functions/types)
• CREATE DATABASE (to create the DB for Django tables)
• CREATE TYPE (in the function/type schema above)
• CREATE TABLE (in the schema where mathesar tables will live)
• ALTER TABLE (in the mathesar tables schema)

I also think we should be able to assume they're willing to create functions and types in a separate schema (i.e., namespaced away from any functions/types they may previously have created). Without that, the functionality would be reduced to a pretty lackluster DB client that's missing a bunch of features.

[mathemancer]

Note that I'm not assuming the installing user has privileges to install anything on the underlying server. For example, in Amazon Web Service's RDS managed DBs, a user can use the CREATE TYPE command, but can't necessarily use CREATE EXTENSION for whatever they want, since they won't have the permissions necessary to install the required extension on the underlying server itself.

[mathemancer]

Correcting myself from above: For the CREATE DATABASE privileges, these are only necessary on some database server, not necessarily the same one where the preexisting data is stored. It's only necessary to have these on the preexisting DB if the user wants to use that DB server for both the mathesar Django tables, and their Mathesar data tables.

[mathemancer]

@kgodey 's comment, copied from #128 :

@mathemancer I don't think I understand fully which types require extensions / what our current custom type implementation is. It does require making a new schema, right?

My other concern is the portability of the database. We've talked about Mathesar creating a database that people could use easily to power other applications. If we're making a custom type, will that be easy to work with? Let me know what you think.

I'm also concerned about us working well with existing Postgres databases, we should be able to offer filter, sort, group by, calendar view, etc. functionality for existing datetime fields without requiring a composite type. It seems easiest to ensure that by starting with using Postgres' datetime type first and then extending to a composite type later.