PostgreSQL

PostgreSQL is an open source database management software widely used in scientific researches.

This post describe basic usage of PostgreSQL. This includes how to install PostgreSQL in Ubuntu (linux) system, create a user and a database, and use Python (psycopg2) interface.

• PostgreSQL Homepage: https://www.postgresql.org/

1. Installation

Installation in Ubuntu

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service 

Administrator account

PostgreSQL installation create a administrator account, postgres. sudo access is used to manage the database.

The administrator account can be accessed by the following commands, which activating postgres user accessing the database management via psql.

$ sudo -i -u postgres
$ psql

Once connected, you can see the PostgreSQL command prompt that looks like the screen below. SQL script can be executed with this command line interface.

postgres=# 

2. Create a user and a database

Accessing PostgreSQL as an administrator, you can create user accounts.

2.1. Create a user

CREATE ROLE new_user WITH LOGIN PASSWORD 'password';

Replace new_user and password with the intended user name and the password.

2.2. Grant privileges

In order to allow a user to access a database, privileges should be granted to the database using GRANT keyword.

GRANT CONNECT ON DATABASE new_database TO new_user;
GRANT USAGE ON SCHEMA public TO new_user;

The first line allows new_user to connect to the database new_database. The second line allow the user to use the default schema.

2.3. (Administrator) Change Authentication method

The default authentication method checks whether the database user name is the same to the system’s user name. In order to let any system user to access the database using the password, authentification method should be changed from peer to md5 in PostgreSQL configuration file.

The configuration file (pg_hba.conf) can be found in the locations: /etc/postgresql/<version>/main/, /var/lib/postgresql/<version>/data/, or in others. In the file, find the authentication section and change the METHOD from peer to md5.

You need administrator priviledge (sudo) to edit the configuration file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Restart PostgreSQL to apply the change.

sudo systemctl reload postgresql

3. Create a database

In order to grant privilege to a database, the database should exist. Database can be created with the following script.

CREATE DATABASE new_database;

The SQL usage can be found in https://www.w3schools.com/postgresql/index.php.

4. Use Python interface

PostgreSQL database can be accessed via Python interface. psycopg2 is one of widely used libraries allowing to access the database using SQL statements.

• Homepage: https://www.psycopg.org/docs/

4.1. Installation

The library can be installed using pip.

pip install psycopg2

4.2. Connect to the database

import psycopg2
conn = psycopg2.connect(
    dbname="new_database",
    user="new_user",
    password="password",
    host="localhost"
)

5. Tutorial

5.1. Connect to the database as an administrator

In linux shell, execute the following commands to access PostgreSQL.

$ sudo -i -u 
$ psql

5.2. Create a user account

CREATE ROLE test_user WITH LOGIN PASSWORD 'test_user';

5.3. Create a database

CREATE DATABASE test_database;

5.4. Grant privileges to the database

GRANT CONNECT ON DATABASE test_database TO test_user;
GRANT USAGE ON SCHEMA public TO test_user;

The first line allows the access to the database. The second lets the user to use system default schema.

5.5. Connect with test_user

psql -U test_user -W -d test_database

Type in the password test_user to login the database.

5.6. Create a table

Create a table with name test and have an attribute id. Then, fill in a value to the table.

CREATE TABLE test (id INT);
INERT INTO test (id) values (10);

5.6. Establish connection with Python

Connect to the database using psycopg2 library.

import psycopg2
conn = psycopg2.connect(
    dbname="test_database",
    user="test_user",
    password="test_user",
    host="localhost"
)

Execute SQL command to retrieve value from the database.

cur = conn.cursor()
cur.execute("SELECT * from test")
for row in cur.fetchall():
    print(row)

Exeute SQL command to insert a value to the table.

cur.execute("INSERT INTO test (id) VALUES (20)")
conn.commit()

Execute data retrieval code again to confirm record insertion.

cur.execute("SELECT * from test")
for row in cur.fetchall():
    print(row)