Repository: mathesar-foundation/mathesar
Issue category: Security Vulnerability

Issue:
The requirement of superuser postgresql access is problematic
## Problem
Mathesar needs a Postgres superuser to function correctly, from the docs at https://docs.mathesar.org/installation/build-from-source/ 

## Proposed solution
The mathesar user should not require superuser access. 

## Additional context
The superuser is a global permission meaning that a user that has superuser permission will be able to access (and do stuff) not on the mathesar but *all* the databases of the RDBMS. Considering that many production systems have a single RDBMS hosting many application this is a major problem since the mathsar user won't have any access boundaries on the same RDBMS. The mathesar user access can be unlimited but *must* be bounded without the mathesar database.


Hints:
Thanks for reporting this, @spapas. This is already a high-priority issue for us to resolve, requiring a superuser is one of the compromises we made to get our alpha version out of the door.
Hello friends, any news on this issue? This is very important for us and we can't actually use mathesar in procution until the supertuser requirement is dropped. 

Would it be possible to clarify a bit why the mathesar usr needs global superuser access ? What would happen if we removed the RDBMS superuser access on the mathesar user and made him an owner of the corresponding databases (i.e mathesar_data and mathesar_django) ? 

Thank you
Hi @spapas 

We are working on this issue under the [Installations Improvement Project](https://wiki.mathesar.org/en/projects/installation-improvements-0_1_3). This will be part of Release v0.1.3 (coming next month)
Thank you @rajatvijay for answering me & giving a delivery estimate!

Kind regards,
Serafeim

Patch:
diff --git a/db/install.py b/db/install.py
index 43440b398d..1372659c63 100644
--- a/db/install.py
+++ b/db/install.py
@@ -1,5 +1,6 @@
+from psycopg.errors import InsufficientPrivilege
 from sqlalchemy import text
-from sqlalchemy.exc import OperationalError
+from sqlalchemy.exc import OperationalError, ProgrammingError
 
 from db import engine
 from db.sql import install as sql_install
@@ -54,12 +55,20 @@ def _create_database(database_name, hostname, username, password, port, skip_con
             username, password, hostname, root_database, port,
             connect_args={"connect_timeout": 10}
         )
-        with root_db_engine.connect() as conn:
-            conn.execution_options(isolation_level="AUTOCOMMIT")
-            conn.execute(text(f'CREATE DATABASE "{database_name}"'))
-        root_db_engine.dispose()
-        print(f"Created DB is {database_name}.")
-        return True
+        try:
+            with root_db_engine.connect() as conn:
+                conn.execution_options(isolation_level="AUTOCOMMIT")
+                conn.execute(text(f'CREATE DATABASE "{database_name}"'))
+            root_db_engine.dispose()
+            print(f"Created DB is {database_name}.")
+            return True
+        except ProgrammingError as e:
+            if isinstance(e.orig, InsufficientPrivilege):
+                print(f"Database {database_name} could not be created due to Insufficient Privilege")
+                return False
+        except Exception:
+            print(f"Database {database_name} could not be created!")
+            return False
     else:
         print(f"Database {database_name} not created!")
         return False
diff --git a/docs/docs/installation/build-from-source/index.md b/docs/docs/installation/build-from-source/index.md
index c221d31d7e..4b403beee5 100644
--- a/docs/docs/installation/build-from-source/index.md
+++ b/docs/docs/installation/build-from-source/index.md
@@ -63,25 +63,25 @@ Then press <kbd>Enter</kbd> to customize this guide with your domain name.
     sudo -u postgres psql
     ```
 
-1. Mathesar needs a Postgres superuser to function correctly. Let's create a superuser.
+1. Let's create a Postgres user for Mathesar
 
     ```postgresql
-    CREATE USER mathesar WITH SUPERUSER ENCRYPTED PASSWORD '1234';
+    CREATE USER mathesar WITH ENCRYPTED PASSWORD '1234';
     ```
 
     !!! warning "Customize your password"
         Be sure to change the password `1234` in the command above to something more secure and private. Record your custom password somewhere safe. You will need to reference it later.
 
-1. Next, we have to create a database for storing Mathesar metadata.
+1. Next, we have to create a database for storing Mathesar metadata. Your PostgreSQL user will either need to be a `SUPERUSER` or `OWNER` of the database. In this guide, we will be setting the user to be `OWNER` of the database as it is slightly restrictive compared to a `SUPERUSER`.
 
     ```postgresql
-    CREATE DATABASE mathesar_django;
+    CREATE DATABASE mathesar_django OWNER mathesar;
     ```
 
 1. Now we let us create a database for storing your data.
 
     ```postgresql
-    CREATE DATABASE your_db_name;
+    CREATE DATABASE your_db_name OWNER mathesar;
     ```
 
 1. Press <kbd>Ctrl</kbd>+<kbd>D</kbd> to exit the `psql` shell.
diff --git a/docs/docs/installation/docker/index.md b/docs/docs/installation/docker/index.md
index 22362e6801..9c1c29c3b2 100644
--- a/docs/docs/installation/docker/index.md
+++ b/docs/docs/installation/docker/index.md
@@ -24,8 +24,11 @@ You'll need to install **[Docker](https://docs.docker.com/desktop/)** v23+
 #### Database for Mathesar's internal usage
 You'll need to:
 
-- Create a PostgreSQL database for Mathesar's internal usage.
-- Create a database user for Mathesar to use. The user should be a `SUPERUSER`, [see PostgreSQL docs for more information](https://www.postgresql.org/docs/13/sql-createrole.html).
+- Create or have a PostgreSQL user for Mathesar to use.
+- Create a PostgreSQL database for Mathesar's internal usage owned by that database user. (See the PostgreSQL [docs](https://www.postgresql.org/docs/13/ddl-priv.html) for more information.)
+    
+    Alternatively, you can make the user a `SUPERUSER` which will give the user access to all the databases. (See the see PostgreSQL [docs](https://www.postgresql.org/docs/13/sql-createrole.html) for more information.)
+
 - Ensure that this database can accept network connections from the machine you're installing Mathesar on.
 - Have the following information for this database handy before installation:
     - Database hostname
@@ -40,11 +43,11 @@ Have the following information for all databases you'd like to connect to Mathes
 - Database hostname
 - Database port
 - Database name
-- Database username (should be a `SUPERUSER`, see above)
+- Database username (should be `SUPERUSER` or `OWNER` of the database, see above)
 - Database password
 
-!!! warning "Database creation"
-    Whenever the Docker container is started, we will attempt to create any databases in this list that don't already exist. So you don't need to ensure that they are created before installation.
+!!! info "Databases are automatically created"
+    You don't need to create these databases before installation. Whenever the Docker container is started, Mathesar will attempt to create any specified databases that don't already exist, so long as the user has [`CREATEDB` privilege](https://www.postgresql.org/docs/13/sql-createrole.html).
 
 ## Installation Steps
 
diff --git a/docs/docs/snippets/docker-compose-prerequisites.md b/docs/docs/snippets/docker-compose-prerequisites.md
index f9947c9289..fd8e061db0 100644
--- a/docs/docs/snippets/docker-compose-prerequisites.md
+++ b/docs/docs/snippets/docker-compose-prerequisites.md
@@ -24,7 +24,7 @@ You can create a new PostgreSQL database while setting up Mathesar or use our UI
 To connect Mathesar to an existing database:
 
 - The external database should be able to accept network connections from your Mathesar server.
-- You'll need to set up a database user for Mathesar to use. The user should be a `SUPERUSER`, [see PostgreSQL docs for more information](https://www.postgresql.org/docs/13/sql-createrole.html).
+- You'll need to set up a database user for Mathesar to use. The user should own that database, [see PostgreSQL docs for more information](https://www.postgresql.org/docs/13/ddl-priv.html)
 - Have the following information handy before installation:
     - Database hostname
     - Database port


Test patch: