====== PostgreSQL ======
[[https://www.postgresql.org/|PostgreSQL]] is an open-source relational database written in C, it originally was designed as a successor to the [[https://en.wikipedia.org/wiki/Ingres_(database)|Ingress]] database, but later gained SQL support and was renamed to PostgreSQL in 1996. 

===== Installation ===== 
Install the ''postgresql'' package using Bulge. ''bulge i postgresql''

<bootnote warning>
If you're intending to run the PostgreSQL server, be sure to either reboot or run ''systemd-sysusers'' & ''systemd-tmpfiles --create'' before doing so.
</bootnote>

===== Initial Configuration =====
Before you can start using the PostgreSQL server, you'll need to generate the database cluster as the ''postgres'' user: ''sudo -u postgres initdb -D /srv/pgsql/data'' 

If successful, the output should look similar to below: 
<code>
The files belonging to this database system will be owned by user "postgres". 
This user must also own the server process. 
The database cluster will be initialized with locale "C". 
The default database encoding has accordingly been set to "SQL_ASCII". 
The default text search configuration will be set to "english". 
Data page checksums are disabled. 
fixing permissions on existing directory /srv/pgsql/data ... 
ok creating subdirectories ... 
ok selecting dynamic shared memory implementation ... 
posix selecting default max_connections ... 100 
selecting default shared_buffers ... 128MB 
selecting default time zone ... UTC 
creating configuration files ... ok 
running bootstrap script ... ok 
performing post-bootstrap initialization ... ok 
syncing data to disk ... ok 
initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. 
Success. You can now start the database server using: /usr/bin/pg_ctl -D /srv/pgsql/data -l logfile start 
</code>

You'll now be able to start the server with the systemd service: ''systemctl start postgresql.service''

===== Useful Configuration ===== 
==== Require password for login ==== 
Modify ''pg_hba.conf'' to set the authentication methods for each user you'd like to use password authentication (or modify "all" to affect all users) to ''scram-sha-256'' or ''md5'' (**LESS SECURE**, only for legacy applications). 
<code>
/srv/pgsql/data/pg_hba.conf

...
local all user scram-sha-256
...
</code>

You'll also need to modify ''postgresql.conf'' to set the password encryption method, it must be same as ''pg_hba.conf''. 
<code>
/srv/pgsql/data/postgresql.conf

...
password_encryption = scram-sha-256
...
</code>

Restart the server using systemd ''systemctl restart postgresql.service'' and then set user passwords by running ''ALTER USER user WITH ENCRYPTED PASSWORD 'password';'' in the PostgreSQL console. 

==== Allow remote users to connect ==== 
In situations where you want database users to be able to connect over the network, and don't want to do [[https://stackoverflow.com/a/16838293/16892221|SSH tunneling]], you can configure PostgreSQL to accept remote connections. 

<bootnote critical>
It is highly recommended you also follow the [[postgresql#Require password for login|Require password]] for login configuration steps.
</bootnote>
 

Modify ''postgresql.conf'' to your needs: 
<code>
/srv/pgsql/data/postgresql.conf

...
listen_addresses='localhost,local_ip_address'
...
</code>
<bootnote tip>
You can use ''*'' to listen on all addresses.
</bootnote>


Then in ''pg_hba.conf'' add a line like the following:
<code>
/srv/pgsql/data/pg_hba.conf

...
host all all ip_address scram-sha-256
...
</code>
<bootnote tip>
You can accept all connections by using ''0.0.0.0/0'' as the IP address.
</bootnote>


Reboot the server via systemd ''systemctl restart postgresql.service'' for the changes to take effect.

==== File Locations ==== 
Listed below are the PostgreSQL configuration files that are used by the server, if you need an sample configuration file for any reason, you can find them at ''/usr/share/postgresql/<file>.sample''. 

  * pg_hba.conf - ''/srv/pgsql/data/pg_hba.conf''
  * pg_ident.conf - ''/srv/pgsql/data/pg_ident.conf''
  * postgresql.conf - ''/srv/pgsql/data/postgresql.conf
''