Expose database server (PostgreSQL)
This tutorial will show you how you can expose your database (PostgreSQL as an example) with LocalXpose, along with enabling SSL on your PostgreSQL instance.
If you are running Linux, you can install it using your package manager from your Distribution repository. Check the official installation docs for more details if you are using Windows or macOS.
$ sudo apt install postgresql-12
# for debian based distros.
Once installed,
$ psql --version
psql (PostgreSQL) 12.2 (Debian 12.2-4)
We can reserve an endpoint for this specific use case.
$ loclx endpoint reserve --region ap --port 5432
✓ Reserving an Endpoint...
endpoint is reserved in ap -> ap-2.loclx.io:5432
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.
PostgreSQL runs default in port 5432, lets run LocalXpose to expose that port.
$ 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.
Next, you can connect to your PostgreSQL database from anywhere you want.
$ 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=#
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.test_db-# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)
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
You might want to configure PostgreSQL to use SSL. Follow the following guide to setup ssl with postgresql, Here we use let's encrypt free certificates, but if you have your own, you may use it.
$ 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.
$ ls $HOME/localxpose
psql.xtestgo.xyz-cert.pem psql.xtestgo.xyz-key.pem
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.
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.confpath, You can find it with the following command.$ 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.$ 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.
pg_hba.conf is in the same path as postgresql.conf file, you will need to allow external connections by adding the following lines.$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.
$sudo systemctl restart postgresql
Similar to one mentioned above you can now connect to your postgresql database, and your conection should be encrypted with SSL.
$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=#
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.You can see the current connections by querying
pg_catalog.pg_stat_ssl.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)