Introduction

This is a short guide on how to install PostgreSQL 17 on openSUSE Leap 16.0 RC. Most of this should work on other releases of openSUSE, but they versions may vary.

**Install PostgreSQL **

It is always recommended that you update your install with the latest distro updates. openSUSE uses zypper package manager to perform updates. You can update your install by running the following command.

zypper dup

Next, we want to install PostgreSQL.

zypper in postgresql postgresql-server

Start PostgreSQL

After you install PostgreSQL, we need to start the service before we can configure anything. This is due to starting the service creates the config files we will need to edit.

systemctl enable postgresql.service
systemctl start postgresql.service

Create A Role/User

In PostgreSQL 17, there isn't a concept of a single user like there was in early version or like there is in Microsoft SQL Server. Instead, every user is a "role". A role can even have children roles, so a role is can be a user or a group.

To create a new role we need to connect to PostgreSQL.

# Switch to the postgres user
sudo su postgres

# Enter postgres
psql

Now we can create a role.

CREATE ROLE <username> WITH PASSWORD '<passwd>';

At this point the role/user can not sign in. We want to make sure the role can sign in to PostgreSQL so it can connect to the database.

ALTER ROLE <username> WITH LOGIN;

We can validate this works by using the following command. If it says the user can not login, then something did not work correctly.

\du

Create Database

Now we can create the database. If for some reason you exited out of PostgreSQL, you can get back in it by using the commands we used previously.

# Switch to the postgres user
sudo su postgres

# Enter postgres
psql

To create the database we can do the following.

CREATE DATABASE <database> OWNER <username>;

You can create the database without an owner, but creating it with the owner will save you a step. This allows that user to connect to the database.

With the default settings, you should be able to connect from your local machine to the database using your username and password. If you want to be able to connect to the server from a remote machine, continue to the next section.

Grant Permissions

Now that the database is setup, we need to grant more permissions to the database for our user.

-- Grant usage and create permissions on <schema> schema to <username>
GRANT USAGE ON SCHEMA <schema> TO <username>;
GRANT CREATE ON SCHEMA <schema> TO <username>;

-- If <username> needs to access existing tables in <schema> schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO <username>;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> TO <username>;

-- Grant permissions on future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON TABLES TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON SEQUENCES TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON FUNCTIONS TO <username>;

Configure PostgreSQL For Remote Connectivity

Now that the service is started, we should be able to find our config files in

/var/lib/pgsql/data

The files we will need to edit are "postgresql.conf" and "pq_hba.conf". If you do not see them in the above directory, you can search for them with the following command.

find / -name postgresql.conf

Navigate to the directory the config files are in. Change the command below if they are different than the default.

cd /var/lib/pgsql/data

In order to make the server visible to remote machines, we need to make a few changes. The first change we need to make is to the postgresql.conf file.

nano postgresql.conf

This will open the nano editor. Scroll down until you find "listen_addresses" setting and the "port" setting. They are both likely commented out. We need to uncomment them and change the values to the following.

listen_addresses = '*'

port = 5432

You can change the port to something else if you want, but the default port is 5432 for PostgreSQL.

After you have made the changes, save them by holding "CTRL" and hitting the "O" key. Next hold "CTRL" and hit the "X" key to exit.

Next we need to add an exception for the firewall if it is running. We can check if it is running by using the following command.

systemctl status firewalld

If it returns information about firewalld.service and it shows active, then you need to enter the following command.

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

At this point we should be able to see the PostgreSQL instance from a remote server, but we still can not connect to it yet. We need to edit one more file before we can do that.

nano pq_hba.conf

Once you are in nano, scroll to the bottom of the config. You will see the following section.

# IPv4 local connection: 
host  all     all     127.0.0.1/32    ident

We want to update it to the following. You can add more than one record if you are connecting to this server from more than one server. Also, make sure to change the values below. The database and username are the database and username from PostgreSQL. The "remoteip" is the ip address of your remote machine you will be connecting to this machine with.

# IPv4 local connection: 
#host  all     all     127.0.0.1/32    ident
#host    <database>  <username>  <remoteip>/32   md5
host    mydb    myuser  10.10.0.50/32   md5

After you have made the changes, save them by holding "CTRL" and hitting the "O" key. Next hold "CTRL" and hit the "X" key to exit.

To complete the changes, we need to restart PostgreSQL.

systemctl restart postgresql.service

Conclusion

This short tutorial should allow you to install and configure a PostgreSQL 17 on an openSUSE Leap 16 system.

The section above for configuring the server for remote connectivity is fine for home lab of development purposes. You should likely look into more secure connections to the database for production uses.

I don't have a comments section yet, so feel free to send me feedback on this blog.