13 (1-liner) Steps to Setting up PostgreSQL and connecting with JPA Hibernate

by ·0 Comments

The steps outlined in this article outlines the steps required to setup PostgreSQL 9.5 on Ubuntu 16.04. The last two steps outline the required Java Spring configuration required to use JPA Hibernate to the PostgreSQL installed.

This article is based on the work of DigitalOcean (How To Install and Use PostgreSQL on Ubuntu 16.04).

1. Install PostgreSQL

Ubuntu 16.04 comes with PostgreSQL distribution. At the time of writing the version provided is 9.5.4.

sudo apt-get update & sudo apt-get install postgresql postgresql-contrib

2. Check that PostgreSQL was installed correctly

sudo -i -u postgres psql

Note: This will open an interactive session on the database and will be bound to a database called postgres.

3. Exit the interactive shell

postgres=# \q

Note: The command is ‘\q’ (without quotes)

4. Create a new Linux user without SSH access

PostgreSQL uses Linux users as identity for authentication. To ensure that the database is protected and that the connection string doesn’t expose a privileged user, we create a new Linux user that is locked out. As a locked-out user it will not be possible to open a shell with the server thus protecting the server.

sudo adduser -M <nologin user>

Note: The -M instructs Linux not to create a home directory for the user. There is no need for the user to have a home directory since it is not possible to login with the user.

5. Set a password for the new Linux user

sudo passwd <nologin user>

6. Lock out the Linux user

sudo usermod -L <nologin user>

7. Create PostgreSQL ident user corresponding to the Linux user created in step 4.

sudo -u postgres createuser --interactive
 
# Interactive questions
Enter name of role to add: <nologin user>
Shall the new role be a superuser? (y/n) y

8. Configure PostgreSQL for remote connections

sudo echo "listen_addresses = '*'" >> /etc/postgresql/9.5/main/postgresql.conf

9. Configure PostgreSQL to allow the ident user user created in step 7 to connect

sudo echo "host   all   <nologin user>   0.0.0.0/0  trust" >> /etc/postgresql/9.5/main/pg_hba.conf

10. Restart PostgreSQL service

sudo systemctl restart postgresql

11. Create a database for the project

sudo -u postgresql createdb <database name>

The PostgreSQL server is now configured for projects. Next setup the project to connect and make use of the PostgreSQL installed.

12. Update the POM file with PostgreSQL dependency

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4.1211.jre7</version>
</dependency>

Note: Always check Maven repository for the latest version: https://mvnrepository.com/artifact/org.postgresql/postgresql

13. Update the JPA configuration with the PostgreSQL connection and authentication

spring.datasource.url=jdbc:postgresql://<database ip>/<database name>
spring.datasource.username=<nologin user>
spring.datasource.password=<nologin user password>
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialec

That’s all is required. Now we can use JPA Hibernate in the project and have the database handled by PostgreSQL.

References: