Make Deployment Type 2 work with DBs on localhost

[kgodey]

No description provided.

[mathemancer]

More detail about deployment type 2 in this issue: #2508

[Anish9901]

Method 1 (Connecting via Unix Socket):

Setup (Works for Linux and MacOS not sure about Windows)

The host config file of Postgres pg_hba.conf allow only peer connection to connect to the Unix socket without authentication(even if the user has a password set for their role). The peer authentication works only for local connections, more info here. Since we are connecting via a docker container it doesn't qualify for a local connection, Therefore, the pg_hba.conf requires changing to authenticate non-peer connections, this can be done by altering

local all all peer

to

local all all md5

or

local all all scram-sha-256

The next step is to mount the directory which contains the socket file .s.PGSQL.<port_no> as a volume in the appropriate docker container by for linux it is /var/run/postgresql by default the location of this directory can be known by executing \conninfo in the psql shell.
If running mathesar in dev mode the volume to be added to the volumes section of the mathesar_service_dev is:

- /var/run/postgresql:/var/run/postgresql

Now the only thing left to do is to set-up appropriate environment variables for connecting to the host database this will include setting up the connection string as such:

MATHESAR_DATABASES=(mathesar_tables|postgresql://<user_name>:<password>@/<host_db_name>)

If the user also wants to use the host_db to store the mathesar_django database the connection string should be something like so:

DJANGO_DATABASE_URL=postgres://<user_name>:<password>@/<django_db_name>

If the host db is already using port 5432 we'll also have to change the postgres port that we expose to the host to something arbitrary this can be done as such:

POSTGRES_PORT=5555

And that's it, you have Mathesar connected to the db on the host machine.

---

Method 2 (Connecting via TCP/IP):

Postgres by default only listen to requests from localhost we also want it to listen to request from our docker container i.e host.docker.internal which resolves to the IP of the host on which docker lives on. (172.17.0.1) is the default gateway IP of the docker0 interface on the host. This interface acts like a 2-way bridge and allows the containers to talk to the internet and also allows us to interact with the containers from the host network.
Now since we use docker-compose to setup our project a network interface named mathesar_default is created on the host's system which has the IP (172.18.0.x/16) we want this container to have access to the host's databases.
So the postgres files that need changing are postgresql.conf and pg_hba.conf.

Setup

Inside the postgresql.conf file add the IP of docker0 (172.17.0.1) in the listen_addresses like so: (don't forget to uncomment it)

listen_addresses = 'localhost, 172.17.0.1'

Inside the pg_hba.conf file add the IP of the appropriate container e.g mathesar_service_dev container which is assigned (172.18.0.4/16) in my case, this IP can be found using docker network inspect mathesar_default.

host all all 172.18.0.4/16 scram-sha-256

These are all the changes that are needed to be done in the host Postgres's internal files
Now, we have to configure our docker-compose file and add an extra host for the appropriate container mathesar_service_dev in this case, it can be done like so:

extra_hosts:
      - "host.docker.internal:172.17.0.1"

The last thing to do now is to set-up appropriate env variables the connection string should look like this:

MATHESAR_DATABASES='(mathesar_tables|postgresql://<user_name>:<password>@localhost:<port-no>/<host_db_name>)'

If the user also wants to use the host_db to store the mathesar_django database the connection string should be something like so:

DJANGO_DATABASE_URL=postgres://<user_name>:<password>@localhost:<port-no>/<django_db_name>

We have successfully established a connection to the host_db.

---

Method 3 (Using SSH):

Not researched yet.

---

Conclusion

Method 2 when first proposed by me to Brent was rejected by him as it included fiddling around with the user's internal Postgres files, and I was told to research Method 1(which also includes altering pg_hba.conf ) however I think all of these methods(don't know about Method 3) will require some changing in the internal Postgres files as long as our project is dockerized.

[pavish]

I'm going to move this issue to the 'Next release' milestone.

@Anish9901 @mathemancer If you both think this will be complete by the time for the 0.1.2 release, we can add it to the release milestone.