Install and Configure PostgreSQL on Ubuntu 16.04

Posted on June 24th, 2019

First of all, PostgreSQL is an open source relational database. And it is loved by millions of people around the world because of the performance it provides in some cases. It is straightforward to install and configure, just like our good old friend MySQL, But PostgreSQL handles the authentication and authorization differently. We’ll discuss that topic further in this guide. In this guide, we will install PostgreSQL on Ubuntu 16.04 server and play around a little to understand how it exactly works.

Similar to MySQL, PostgreSQL answers the structured query language. Which means, it will not require too much of a learning curve to start working in PostgreSQL. Also, If you are using the PHP frameworks like Laravel, you can use PostgreSQL just by changing the database driver in the configuration file, no extra work needed!

Now, Let’s get started with the actual tutorial. We are going to install PostgreSQL on a Ubuntu 16.04 server. You must have root access to the server to install packages on the system.

Considering that you have a server ready with root access, we can get started with the tutorial.

Install PostgreSQL on Ubuntu 16.04

It is very easy to install PostgreSQL in this case because the package is already available in the repositories of the operating system. It means that we don’t have to install it from the source and also, we don’t have to add any new repository to the source list.

To install PostgreSQL on your Ubuntu server, execute the following commands as root or as a user with sudo privileges.

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

The first command is used to update the repository list in our system, and the second command is used to install PostgreSQL on our system.

Once the installation is complete, you can start using PostgreSQL if you have experience with PostgreSQL. But if you are installing it for the first time on your server, keep following the guide because we will configure a test role and a test database to see how exactly it works.

Accessing the PostgreSQL prompt

PostgreSQL uses something called ident for authentication. It means that PostgreSQL will match a role (a user in PostgreSQL) with a Linux/Unix user account. And it will allow access to the role directly from the user account if the username matches.

For example, let’s say there is a role in our PostgreSQL called “john” and there is one user account in our server called “john”. In that case, our system user “john” will be able to log in to PostgreSQL prompt directly from his account as a “john” role.

To manage all the roles and databases, PostgreSQL will create a new user account in our server called “postgres” during installation. Now, we want to access our PostgreSQL prompt so that we can manage our roles and databases. In that case, we have to log in as a “postgres” user in our system, or we can take advantage of the “sudo” command here.

sudo -u postgres psql

The -u flag in this command will make our command psql run as a postgres user. As we discussed previously, the user account in our system can access the PostgreSQL prompt if it matches the role. And that’s what we did in this case.

Now, as we are logged in, Let’s create a new role (user) and a database in PostgreSQL, Just to get started with PostgreSQL.

Set up a Password for PostgreSQL account

We have to set up a password for the postgres user in our system to make sure that other users with sudo privileges can’t access the PostgreSQL prompt directly. It will help us avoid unauthorized logins by other sudoers in our system.

To set the password for postgres user in our system, execute the following command.

sudo passwd postgres

It will tell you to enter the new password twice. Once the new password is set for our system user, we have to set a new password for our database user too! Execute the following set of commands to log in to our PostgreSQL prompt.

su – postgres
psql

Now, Execute the following command in PostgreSQL prompt to set a password for our postgres database user.

postgres=# ALTER USER postgres WITH PASSWORD ‘your_password‘;

Do not forget to replace your password in the query. Once the password is set, we can move on to make new roles and databases in our PostgreSQL server. Execute the \q command in your PostgreSQL prompt to exit.

Now, Let’s create a new role in our PostgreSQL server.

Create a New role in PostgreSQL

We plan to create a test user and grant all privileges to that user on a test database. It means that our test user can manage tables and data within the test database. But our test user would not be able to manage anything outside the test database.

Assuming that you are logged in as a root user in your server right now, execute the following command to login as a postgres user. If you are already logged in as a Postgres user, there is nothing to do.

su – postgres

Notice that if you are not a root user, logging in as a Postgres user will require a password. It’s because we set the password in the previous step. Once you are in, execute the following command to create a new user/role in PostgreSQL.

createuser USERNAME –interactive

It will ask you some questions. In this case, here is how I answered those questions.

Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Right now, I do not want this role to be a superuser, which means, a role can perform any operation in our PostgreSQL server, Pretty much like a sudo user in Linux. I also don’t want this role to create new databases because we will create a database for that role and grant all privileges on that database. And I also don’t want this role to create other roles in our PostgreSQL server.

If you want your test role to perform any of these actions, answer y instead of n . As our user is ready, we can create a test database and grant all privileges to our test role/user.

Create a new database in PostgreSQL

Creating a new database is very straightforward in PostgreSQL. It requires us to execute just a single command. So, execute the following command to create a test database in our PostgreSQL server.

createdb DB_NAME

Now, It’s time to grant all privileges on our test database to our test user. To perform this task, we have to log in to our PostgreSQL prompt once again. To log in to the PostgreSQL prompt, execute the following command as a postgres Linux user.

psql

Or, execute the following command if you are logged in as a root user or any other user with sudo privileges.

sudo -u postgres psql

Once you are in, execute the following command to grant all privileges on our test database to our test user/role.

GRANT ALL PRIVILEGES ON DATABASE DB_NAME TO USERNAME;

Now, Our test user USERNAME can manage all the tables inside the DB_NAME database. It’s the best way to work with database users.

If you want to allow access to PostgreSQL prompt to our new role/user, we just have to add a new user in your Operating system using adduser or useradd command in Linux. And then, he/she can log in to the PostgreSQL prompt using psql command.

 

Conclusion: So, this is how you can install PostgreSQL on Ubuntu 16.04 server. PostgreSQL is one of the best relational databases available in the market right now. It is very easy to work with and it is very powerful. The only thing that might bug you while getting started is, authentication and authorization. But once you will understand how it works, it will become easier than ever to manage roles/users on your PostgreSQL server.

If you are facing any issue installing PostgreSQL on Ubuntu, please contact our support! We are very happy to help!

Leave a Reply