# Expose database server (PostgreSQL) ## 1. Install PostgreSQL If you are running Linux, you can install it using your package manager from your Distribution repository. Check the official installation [docs](https://wiki.postgresql.org/wiki/Detailed_installation_guides) for more details if you are using Windows or macOS. ```bash $ sudo apt install postgresql-12 # for debian based distros. ``` Once installed, ```bash $ psql --version psql (PostgreSQL) 12.2 (Debian 12.2-4) ``` ## 2. Reserve an endpoint We can reserve an endpoint for this specific use case. ```bash $ loclx endpoint reserve --region ap --port 5432 ✓ Reserving an Endpoint... endpoint is reserved in ap -> ap-2.loclx.io:5432 ``` ## 3. Add an alias domain to your endpoint If you want to use your own domain name, you will need to add a `CNAME DNS` record for your domain to the endpoint reserved for your PostgreSQL Server, take this screenshot for reference. Here we have created a sub-domain **psql.xtestgo.xyz** as an alias to **ap-2.loclx.io**. ![](https://349678347-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LoSLso3EDTO5AwTtGuJ%2F-M8Aifmrs3WuqgFWE-my%2F-M8AnChF1stDqEz0JYGp%2Fpsql-2.png?alt=media\&token=b71333d8-e091-431d-a7f9-00d143798a0f) ## 4. Expose your PostgreSQL port PostgreSQL runs default in port 5432, lets run LocalXpose to expose that port. ```bash $ loclx tunnel tcp --reserved-endpoint ap-2.loclx.io:5432 --to :54 ✓ Creating TCP tunnel... Tunneling TCP: ap-2.loclx.io:5432 --> :5432 ``` If you want LocalXpose to start during boot and start exposing this port, You can create a daemon by writing a systemd unit file similar to [here](https://docs.localxpose.io/tutorials/access-your-raspberry-pi-remotely#5-create-a-unit-file). ## 5. Connect to your PostgreSQL Server Next, you can connect to your PostgreSQL database from anywhere you want. ```bash $ psql -U test -h psql.xtestgo.xyz test_db Password for user test: psql (12.2 (Debian 12.2-4)) Type "help" for help. test_db=# ``` {% hint style="info" %} Here we use the host `'-h'` option with our domain as argument, but if you don't have a domain you can use the endpoint provided by LocalXpose. Also note the `username` & `database_name` as `test` & `test_db` respectively. {% endhint %} ## 6. Fiddle around with your Database ```bash test_db-# \dt List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | company | table | postgres public | department | table | postgres (2 rows) ``` ```bash test_db=# select * from company; id | name | age | address | salary ----+------+-----+---------+-------- 1 | Tom | 23 | SKR | 2300 2 | Rick | 18 | BKT | 4200 3 | Bob | 32 | NY | 1600 ``` ## 5. Configure SSL with Let's Encrypt (Recommended) You might want to configure PostgreSQL to use SSL. Follow the following guide to setup ssl with postgresql, Here we use [let's encrypt](https://letsencrypt.org/) free certificates, but if you have your own, you may use it. ### i. Get SSL certificates from Let's Encrypt [LocalXpose](https://localxpose.io/) has built-in [Let's Encrypt](https://letsencrypt.org/), you can use it to get SSL certs in few seconds. ```bash $ sudo loclx letsencrypt --domain psql.xtestgo.xyz Please make sure of the following rules: 1. There is no a running tunnel using the domain which you want to issue a certificate for 2. The port 54538 is free ✓ Creating certificate... TLS Certificate and private key are created successfully, You will find them in /root/localxpose ``` Once done, you will find your certificates, in the path mentioned. ```bash $ ls $HOME/localxpose psql.xtestgo.xyz-cert.pem psql.xtestgo.xyz-key.pem ``` {% hint style="warning" %} You might need to verify permissions of those certificate files. File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root. {% endhint %} ### ii. Update postgresql.conf Next, go ahead and update `/etc/postgresql/12/main/postgresql.conf` with few of the newer parameters. Some operating system may have a different `postgresql.conf`path, You can find it with the following command. ```bash $ sudo -u postgres psql -U postgres -c "SHOW config_file" config_file ----------------------------------------- /etc/postgresql/12/main/postgresql.conf (1 row) ``` Find the SSL section in the `postgresql.conf` file and change it like so. ```bash $ sudo nano /etc/postgresql/12/main/postgresql.conf # - SSL - ssl = on ssl_cert_file = '/root/localxpose/psql.xtestgo.xyz-cert.pem ' ssl_key_file = '/root/localxpose/localxpose/psql.xtestgo.xyz-key.pem' ssl_prefer_server_ciphers = on ``` Save the config file once done. ### iii. Update pg\_hba.conf `pg_hba.conf` is in the same path as `postgresql.conf` file, you will need to allow external connections by adding the following lines. ```bash $sudo nano /etc/postgresql/12/main/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD hostssl all all 0.0.0.0/0 md5 ``` Next save the file and restart PostgreSQL Server. ```bash $sudo systemctl restart postgresql ``` ### iv. Connect to your Database Similar to one mentioned above you can now connect to your postgresql database, and your conection should be encrypted with SSL. ```bash $psql -U test -h psql.xtestgo.xyz test_db Password for user test: psql (12.2 (Debian 12.2-4)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. test_db=# ``` {% hint style="warning" %} If you don't see a message regarding SSL information, you might want to check PostgreSQL log file located on`/var/log/postgresql/postgresql-12-main.log`. {% endhint %} You can see the current connections by querying `pg_catalog.pg_stat_ssl.` ```bash SELECT * from pg_catalog.pg_stat_ssl; pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn ------+-----+---------+------------------------+------+-------------+-----------+---------------+----------- 4496 | f | | | | | | | 4498 | f | | | | | | | 4527 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | | | 4494 | f | | | | | | | 4493 | f | | | | | | | 4495 | f | | | | | | | (6 rows) ``` You can now proceed to use your Postgresql database. Similarly you can connect to your database using the same endpoint/domain with other tools like [Dbeaver](https://dbeaver.io/) or [pgAdmin.](https://www.pgadmin.org/)