User Management - PostgreSQL

Scenario

For this project you will assume the role of database administrator at a data analytics consulting company. You have been assigned to a project where you need to setup, test and optimize the data platform. The platform includes various on-premises database servers like MySQL, PostgreSQL, and an open-source tool such as Datasette, as well as optional cloud-based databases like IBM DB2. Your job is to configure, tune, secure, backup and monitor those databases and keep them running at peak performance.

Project

---

Let’s pretend you’re the DBA so you’ll need to perform user management tasks and handle the backup of the databases on a PostgreSQL server

Tasks

1. Installation & Provisioning
2. Configuration
3. User Mangement
4. Backup

Setup

---

We will be using an open-source cloud IDE platform to access the PostgreSQL database which is running in a Docker container.

Database Script

Download db Script

• Start the PostgreSQL Server - Create an instance on the cloud
• Download the db setup file from: https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0231EN-SkillsNetwork/labs/Final%20Assignment/postgres-setup.sh

# Once the server is started - Download the Script
wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0231EN-SkillsNetwork/labs/Final%20Assignment/postgres-setup.sh

Script Files Details

Here is what the bash file contained

# Here is the content of the .sh file:
#download the data file
wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0231EN-SkillsNetwork/labs/Final%20Assignment/vehicle-data.csv

#download the sql file

wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DB0231EN-SkillsNetwork/labs/Final%20Assignment/setup.sql

#run the sql file

psql --username=postgres --host=localhost -f setup.sql

#import the csv file

cat vehicle-data.csv | psql --username=postgres -d tolldata --host=localhost -c "copy toll.tolldata from STDIN WITH (FORMAT csv);"

Here is the setup.sql script

CREATE DATABASE tolldata;
\c tolldata;
CREATE SCHEMA toll;

CREATE TABLE toll.tolldata(
row_id integer,
timestamp varchar(25),
vehicle_id integer,
vehicle_type varchar(10),
payment_type integer,
category_id varchar(10),
primary key (row_id)
);

Here is part of the data file vehicle-data.csv

1,Thu Aug 19 21:54:38 2021,125094,car,2,VC965
2,Sat Jul 31 04:09:44 2021,174434,car,2,VC965
3,Sat Aug 14 17:19:04 2021,8538286,car,2,VC965
4,Mon Aug  2 18:23:45 2021,5521221,car,2,VC965
5,Thu Jul 29 22:44:20 2021,3267767,car,2,VC965
6,Sat Aug 14 03:57:47 2021,8411850,car,2,VC965
7,Thu Aug 12 03:41:22 2021,6064250,car,2,VC965

• Once the bash script is executed all those files are downloaded and some are created
• Open PostgreSQL CLI

Execute Bash file

# Create Database by executing the bash file or wait and see section Create db from script below
bash postgres-setup.sh

Create db from script

• Create db from setup.sql
• Run the setup.sql file
• Or execute .sh file as shown above

postgres=# postgres=# \i setup.sql
CREATE DATABASE
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1), server 13.2)
You are now connected to database "tolldata" as user "postgres".
CREATE SCHEMA
CREATE TABLE
tolldata-#

It still didn’t load the data in the table

• Opened pgAdmin
• Left file tree > Expand PostgreSQL
• Scroll down to Database: tolldata
• Schemas > Expand > toll (table name)
• Scroll down to Tables > Scroll down to tolldata
• Right click > Import/Export Data
• Import
• Format csv
• Filename: Click on folder > either go to var/lib/pgadmin or choose the unlocked /tmp directory
• Choose the … dots on the right side
• Import > file vehicle-data.csv from local or from server
• Might have to wait till it populates the directory > Select or go back to the Import screen if it takes time, till the Select button is active.
• Select and wait till you receive a confirmation that data is loaded

Task 1 - Max Connections

Using CLI

• Find the configuration file: postgresql.conf
• For some reason the file is nowhere to be found so
• I edited it’s permission with chmod +x postgres-setup.sh
• Still didn’t appear in the file tree
• Will try to open it with nano : sudo nano /var/lib/postgresql/data/pgdata/postgresql.conf

postgres=# SHOW config_file;
                   config_file                   
-------------------------------------------------
 /var/lib/postgresql/data/pgdata/postgresql.conf
(1 row)

postgres=# SHOW max_connections;
 max_connections 
-----------------
 100
(1 row)

From pgAdmin

Task 2 - User Management

Create User

Create a user named backup_operator

postgres=# CREATE USER backup_operator WITH PASSWORD 'backup_operator_password';
CREATE ROLE

Create Role

Create a role named backup

postgres=# CREATE ROLE backup;
CREATE ROLE

Grant Privileges to Role

Grant the following privileges to the role backup

• CONNECT ON tolldata DATABASE.
• SELECT ON ALL TABLES IN SCHEMA toll.

postgres=# GRANT CONNECT ON DATABASE tolldata TO backup;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA toll TO backup;

Grant Role to User

Grant the role backup to backup_operator

postgres=# GRANT backup TO backup_operator;
GRANT ROLE

Task 3 - Backup

Backup the database using PGADMIN

Backup the database tolldata into a file named tolldatabackup.tar, select the backup format as Tar

• Open pgAdmin
• Scroll down to tolldata
• Right click on it and backup to a tar tolldatabackup.tar file

Db2 Duplicate

• These images are of the same process done in Db2 cloud server