Setting up PostgreSQL Database Server

Peter Boy Version F35-F43 Last review: 2025-12-04

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

Fedora 43 includes PostgreSQL 18.1 If you update from F42, read the release notes or the update description on Server home page for detailed instructions.

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 called fedora or systemVG in a default installation. A suitable initial size is likely to be 50 GiB 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.

First, check for the VG name and then adjust the following commands.

 […]# vgs
    VG       #PV #LV #SN Attr   VSize VFree
    systemVG   1   1   0 wz--n- 7.55g <4.42g
 […]# lvcreate -L 50G -n pgsql  systemVG
 Logical volume "pgsql" created.
 […]# mkfs.xfs -L pgsql /dev/mapper/systemVG-pgsql
 meta-data=/dev/mapper/systemVG-pgsql isize=512    agcount=4, agsize=655360 blks
 ...
 Discarding blocks...Done.
 […]# mkdir /var/lib/pgsql
 […]# echo "UUID=$(blkid -s UUID -o value /dev/systemVG/pgsql) /var/lib/pgsql              auto    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
 ==============================================================================
 Installing:
   postgresql-server                   x86_64          18.0-1.fc43
 Installing dependencies
   postgresql                          x86_64          18.0-1.fc43
   postgresql-private-libs             x86_64          18.0-1.fc43

 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.

Database connection paths

A client can connect to the server either by a Unix socket connection or a TCP/IP connection.

If the client does not specify a hostname parameter (-h), a Unix socket connection is established. Postgres uses this method to ensure that root can always securely establish a connection, regardless of password loss, for example. The root user always has permission to assume the identity of the postgresql master user (su - postgres). By default, no other user has this option. As root, you get in any circumstances administrative access to the database.

[…]# su - postgres
[…]$ psql

Specifying the host parameter results in an attempt to establish a TCP/IP connection. Postgresql uses port 5432 by default for this purpose. In Fedora, all interfaces are protected by a firewall by default, except for localhost. A connection therefore requires opening a suitable port.

Fedora abstracts the technical details with Firewalld, so that the administrator does not need to bother with details. For most common uses there are predefined services. So, if you want the database to be accessible via the internal interface assigned to the internal zone, for example, you need just two simple instructions.

[…]$ sudo firewall-cmd --zone=internal --permanent --add-service=postgresql
[…]$ sudo firewall-cmd --reload

In case of connection issues there is most probably a SELinux configuration missing, in most case a "name_connect access on the tcp_socket" issue. Check the SELinux module in Cockpit. It helps you in detail to establish a local policy to fix it. It is easy, just a matter of 2 instructions.

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.

This capability is configured in the ~/data/pg_hba file, together with other properties.

[…]# vim  /var/lib/pgsql/data/pg_hba.conf
...
# PostgreSQL Client Authentication Configuration File
# ===================================================
...

# TYPE  DATABASE        USER        ADDRESS             METHOD
# "local" is for Unix domain socket connections only
local   all             all                             peer

It is really not a good idea to change the entry in the line ‘local all …’ in any way. Whatever you configure later in this file, do not change this line, or only do so if you are absolutely sure of what you are doing.

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 an ident server, that Fedora doesn’t install). In most cases you need an authentication based on a password. 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.

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
# PostgreSQL Client Authentication Configuration File
# ===================================================
...
...

# TYPE  DATABASE        USER        ADDRESS             METHOD
...
...
# 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    scram-sha-256   # <- 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.

To make access via the 192.168.122.1/24 host interface really work, you have to add the host’s IP address on that network to postgresql.conf.

For further details see the PostgreSQL documentation, chapter 20.1. The pg_hba.conf File.

Some of the possible authentication methods require additional settings for SELinux. For example, the PAM method requires unix_chkpwd access permissions. A typical error message is “SELinux is preventing auth from name_connect access on the tcp_socket port 5432”. The easiest way to fix this is to use the SELinux module from Cockpit..

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'         # what IP address(es) to listen on;
                                          # comma-separated list of addresses;
                                          # defaults to 'localhost'; use '*' for all
                                          # (change requires restart)
  listen_addresses = 'localhost, 192.168.122.1/24'
  #port = 5432                            # (change requires restart)
  max_connections = 100                   # (change requires restart)
  ...

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 permanent service

You are now ready to start the PostgreSQL server.

  […]# systemctl start postgresql
  […]# systemctl status postgresql
  ...
  ... systemd[1]: Started postgresql.service - PostgreSQL database server.

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 (18.0)
  Enter »help« ...

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

  postgres-# \q
  […]$ exit
  […]#

If everything works as expected, enable autostart of postgresql,

  […]# systemctl enable postgresql

Enjoy a powerful DBMS.

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