Setting up a PostgreSQL Database Server

Peter Boy Версія F38-F41 Last review: 2024-09-25

Postgresql is the recommended database management system of Fedora. It is a key functionality that is part of the Release criteria.

Fedora 40 and 41 both include PostgreSQL 16.3. So there is no need to update the database when updating to Release 41.

1. Storage preparation

In Fedora, PostgreSQL stores the databases and other runtime files in the /var/lib/pgsql directory.

Following the Fedora storage concept, a dedicated file system on a logical volume, mounted at the appropriate position in the directory tree, takes over this function.

A convenient descriptive name for both the logical volume and the file system is pgsql. The volume group that contains the logical volume is named fedora in a default installation. A suitable initial size depends on the individual use case, of course. If you are unsure about the required size, 50 GiB is a good starting point in many cases.

In any case, the system administrator must adapt the following command sequence according to the local requirements! With a Linux LVM and Software raid, XFS can autonomously determine its optimal configuration values. With some hardware raids, intervention by the administrator can also be useful for this.

 […]# vgs
 […]# lvcreate -L 50G -n pgsql  fedora
 […]# mkfs.xfs -L pgsql /dev/mapper/fedora-pgsql
 […]# mkdir /var/lib/pgsql
 […]# echo "UUID=$(blkid -s UUID -o value /dev/mapper/fedora-pgsql)  /var/lib/pgsql        xfs     defaults        0 0"  >> /etc/fstab
 […]# mount -a
 […]# df -h

2. Basic installation

Just one package – postgresql-server – already provides a complete and comprehensive server at your disposal. All the many other Postgresql related packages provide additional options that are only useful or needed for specific special needs.

The package provides the pure server functionality. Fedora additionally loads the packages postgresql, a CLI client program granting interactive access to the server, and postgresql-private-libs, containing shared libraries used by each of those packages, as dependencies.

 […]# dnf install postgresql-server
 ...
 ======================================================================================
  Package                              Architectur     Version            Repository
 ======================================================================================
 Installing:
   postgresql-server                   x86_64          16.3-3.fc41        fedora
 Installing dependencies
   postgresql                          x86_64          16.3-3.fc41        fedora
   postgresql-private-libs             x86_64          16.3-3.fc41        fedora

 Transaction Summary
 ======================================================================================
 Install  3 packages

The installer should have adjusted all SELinux labels in the pgsql directory already created. Check:

 […]# ls -alZ  /var/lib/pgsql/
 drwx------.  4 postgres postgres system_u:object_r:postgresql_db_t:s0   54  ...  .
 drwxr-xr-x. 45 root     root     system_u:object_r:var_lib_t:s0       4096  ...  ..
 drwx------.  2 postgres postgres system_u:object_r:postgresql_db_t:s0       ...  backups
 -rw-r--r--.  1 postgres postgres system_u:object_r:postgresql_db_t:s0       ...  .bash_profile
 drwx------.  2 postgres postgres system_u:object_r:postgresql_db_t:s0       ...  data

If the installation program missed something, fix it executing

  […]# restorecon  -vFr /var/lib/pgsql
  […]# ls -alZ  /var/lib/pgsql/

It is also a prerequisite that exclusively the user postgres has access to the directory pgsql and its subdirectories. Usually the installer takes care of it. Fix it if necessary.

  […]# chown -R  postgres:postgres /var/lib/pgsql
  […]# chmod -R  700 /var/lib/pgsql

When all the requirements are met, perform the initialization of the database cluster. This is a prerequisite for all further activities.

  […]# postgresql-setup --initdb

3. Configuration and initialization

Fedora preconfigures Postgresql in a way that in any case ensures reliable and maintainable operation on the one hand and secure operation as far as possible on the other.

Authentification options

Administrative access

For admin access Fedora postgresql is configured to obtain the host’s operating system user name from the kernel and using it as the allowed database user name. Therefore, as soon as someone can authentiate on the host as user postgres, that person has administrative privileges on the postgresql server without any additional password prompt. The only one who can do that by default, is root. Root can configure additional users to be able to su to postgres. In any case, in a whatever emergency, if any then the system administrator is able to quickly access postgresql server unhindered and salvage what can still get salvaged.

If local regulations make it necessary to replace these procedures with a dedicated authentication by postgresql itself, one of the other procedures can be configured later. In general, however, it is not advisable to make any changes. Once root is compromised, there are a lot of completely different problems to get tackled.

Client user access

In the initial configuration postgresql restricts any authentication to peer as above described or ident (i.e. asking a ident server). In most cases you need an authentication based on a kind of password, mostly still md5 despite its security issues. The details depend on the prospective clients. As a typical use case we will accept connections from the internal network to VMs, by default 192.169.122.0/24.

Addionally you may want to use the localhost interface to connect. You have to change the (default) ident authentification to somethine else, most likely to md5. You have to specify the numeric ip in your connect string (i.e. -h 127.0.0.1). The term -h localhost invokes a Unix socket connection.

Alternatively, you may install an identd server. Have look at dnf info oidentd.

The configuration is done in the file pg_hba.conf in the data subdirectory. Edit the file to match the pattern below.

  […]# vim  /var/lib/pgsql/data/pg_hba.conf
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  # "local" is for Unix domain socket connections only
  local   all             all                                     peer
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            ident
  # IPv4 internal network connections:                                  # <- modification!
  host    all             all             192.168.122.1/24        md5   # <- modification!
  # IPv6 local connections:
  host    all             all             ::1/128                 ident
  # Allow replication connections from localhost, by a user with the

Add further entries according to the need, as far as they are already known. Additions are possible at any time later, but require a reload of the server, i.e. at least a short service interruption.

For further details see the PostgreSQL documentation, chapter 20.3. Authentication Methods

Connection options

Now you are allowed to authenticate from machines on the internal network, but you still can’t connect from the internal network to the PostgreSQL server. The default configuration restricts connection initially to the local host to avoid any security vulnerabilities in the first place.

Connections granted are configured in ~/data/postgresql.conf. To grant access to VMs on the internal network as well as from local host, edit the file near the beginning to match the pattern below.

  […]# vim  /var/lib/pgsql/data/postgresql.conf
  ...
  #------------------------------------------------------------------------------
  # CONNECTIONS AND AUTHENTICATION
  #------------------------------------------------------------------------------

  # - Connection Settings -

  listen_addresses = 'localhost, 192.168.122.111'
  #listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                          # comma-separated list of addresses;
                                          # defaults to 'localhost'; use '*' for all
                                          # (change requires restart)
  #port = 5432                            # (change requires restart)
  max_connections = 100                   # (change requires restart)
  ...

Of course, you have to adjust the ip address 192.168.122.111 in the example above.

An entry of listen_addresses = '*' enables connections from any address. It is probably rather a bad idea and relies solely on authentication restrictions and, if present, a supporting firewall configuration.

4. Using PostgreSQL as a permanent service

You are now ready to start the PostgreSQL server.

  […]# systemctl start postgresql
  […]# systemctl status postgresql

If no errors are reported, try to connect as user postgres using the psql cli client. Once connected, start commands with backslash, e.g. \? to get help or \q to quit.

  […]# su - postgres
  […]$ psql
  psql (16.3)
  Enter »help« ...

  postgres=# \dg
                                        List of roles
   Role name |                            attributes                       | member of
  -----------+-------------------------------------------------------------+--------------
   postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS  | {}

  postgres-# \q
  […]$

If everything works as expected, enable autostart of postgresql,

  […]# systemctl enable postgresql

4. A system administrator’s quick PostgreSQL cheat sheet

Get access to a postgresql database and get a postgres terminal prompt

[...]# sudo -i -u postgres psql
psql (16.3)
Type "help" for help.

postgres=#

List all databases

postgres=# \l

Connect to a database

postgres=# \c <database_name_from_above_list>

List all tables in the database connected to

postgres=# \dt

List all users / roles

postgres=# \du

Create a new user / role

postgres=# CREATE USER new_user_name WITH PASSWORD 'password';

Change password of a user / role

postgres=# ALTER USER user_name WITH PASSWORD 'new_password';

Enjoy a powerful DBMS.

Want to help? Learn how to contribute to Fedora Docs ›