How to setup and use PostgreSQL on Debian 9.4 and Ubuntu 16.04

PostgreSQL is a renowned open source relational database management system. It however isn't just relational, but object-relational. This means that it'll even take user defined objects and their methods. The RDMS also holds many different datatypes, and might this still not be enough, you can just add your own! These are just a couple of the advantages to the PostgreSQL RDMS. In this guide we'll be going over how to install and use PostgreSQL step by step, so you also can hopefully make use of this marvel yourself.

Before we start, we need a Linux Debian or Ubuntu installation. We'll skip that step in this tutorial since you can easily get a default Linux installation on one of your containers on the www.cloudcontainers.net website. This tutorial is based on the cloud containers created on the my.cloudcontainers.net page. This means that you are by default the root user, so all of the commands below don't make use of sudo. If however, you are not the root user on your system, you'll have to add "sudo" in front of the commands found in the guide below.

 

To start off, it's always good practice to update your packages first:

apt update && apt upgrade -y

 

Ubuntu's default package manager will contain PostgreSQL 9.5; For the purposes of this tutorial it doesn't matter, but you could change the version by adding the following repository and its key:

Optional for Ubuntu:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" && apt update

 

Install PostgreSQL

Next we install PostgreSQL using the following command. 

Note that if you have added the repository, you'll have to specify the preferred version: postgresql-9.6

apt install postgresql

 

Verify the installation, and version:

dpkg-query -l | grep postgresql

Your output should be roughly similar to this:

 

Accessing the PostgreSQL command line

On Debian the PostgreSQL server will run automatically, on Ubuntu you might need to start it before entering the terminal:

systemctl start postgresql

 

Next, switch user:

su - postgres

You can log off and go back to root using the exit command.

 

Now you're logged in as postgres, you can start the console:

psql

You should now be logged in to the console! You can exit it using \q

 

 

Creating new PostgreSQL roles

To create other users, to manage permissions for example, we can run the following script. You'll be asked some questions concerning the new role you're creating.

In the Unix terminal as user postgres (su - postgres):

createuser username

Or in the PostgreSQL terminal (psql):

CREATE USER username;

 

 

In the PostgreSQL terminal (psql), you can get an overview of all roles and delete them using the following two commands respectively:

\du DROP ROLE role_name

 

Modify a role's privileges

We can give a new role certain privileges right from its evocation, or we can define them later:

CREATE USER new_user WITH role_attributes; ALTER USER new_post WITH role_attributes;

 

This is a list of privileges you might want to give or take away from your specified roles:

•  SUPERUSER
•  NOSUPERUSER
•  CREATEDB
•  NOCREATEDB
•  CREATEROLE
•  NOCREATEROLE
•  CREATEUSER
•  NOCREATEUSER
•  INHERIT
•  NOINHERIT
•  LOGIN
•  NOLOGIN
•  REPLICATION
•  NOREPLICATION
•  CONNECTION LIMIT connlimit
•  PASSWORD 'password'
•  ENCRYPTED
•  UNENCRYPTED
•  VALID UNTIL 'timestamp'

 

As an example, this is what it'll look like. You might want to change your user's password anyway, else they wouldn't be able to log in as there's a "missing password entry".

CREATE USER kees WITH PASSWORD 'tuinhekje123'; ALTER USER kees WITH PASSWORD 'schutting1';

 

 

Log in as new PostgreSQL user

Now you've created your new user and provided him with a password, you probably want to know how to login.

When logging in, PostgreSQL will look for a username that's in your operating system and will be looking for a database with the same name too. Since this is not the case for our new user called "kees", we'll need to specify some additional options.

psql -U username -d database_name -h 127.0.0.1 -W

Example:

psql -U kees -d postgres -h 127.0.0.1 -W

 

 

Modify a role's permissions on a new table

To create an example table, we use the following command:

CREATE TABLE contacts ( name varchar(25), phone char(10), met date);

 

Usually the only role to be able to affect newly created tables, is the owner (and superusers as well). You might want to change this if you've just made several different roles. We do this by granting them permissions like this:

GRANT permission ON table_name TO role_name;

example"

GRANT ALL ON contacts TO kees;

We have now granted kees full permissions. You can use the term "... TO PUBLIC;" to grant all users on the system a certain permission. 

You can REVOKE permissions the same way you GRANT them, just replace GRANT for REVOKE.

Below is a list of permissions you might want to give to/revoke from your newly created users:

•  SELECT
•  UPDATE
•  INSERT
•  DELETE
•  TRUNCATE
•  REFERENCES
•  TRIGGER
•  EXECUTE
•  USAGE
•  CREATE
•  CONNECT
•  TEMPORARY
•  ALL

 

Extra PostgreSQL commands

\? : PostgreSQL's help page

\h : SQL help

\q : Quit the PostgreSQL terminal

\d : List all available tables

\du : List all roles/users

\password username : Change the specified users' password.

\c database_name : Connect to the database

\conninfo : Displays some information on your current database connection.

 

 

Congratulations, you now know how to create and manage a PostgreSQL database!

Comments are turned off.