POSTGRESQL
SECURITY

How To Secure Data In A Postgres Database: A Guide

intro

In the current business world, every business or organization gathers data to extract actionable and relevant information that helps them make informed decisions. The gathered data is stored in a database where it can be easily accessed, managed, and updated.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

There has been an increase in data theft in recent years which means businesses or organizations need to secure their data more than ever through data security. Data security refers to the protection of data from unauthorized access, use, disclosure, disruption, modification, or destruction.

In this article, you will learn how to secure data in a Postgres database by properly employing database access control, using SSL/TLS to encrypt the connection to the database, using SSH, SSO, 2FA, MFA and stored procedures.

Prerequisites

To follow through with this article, you need a database management system, and a SQL client. In this case, we will use Postgres as the database management system, and DbVisualizer as the database SQL client.

To install PostgreSQL, navigate to the PostgreSQL download page and download it for your operating system. You can follow this guide to install PostgreSQL on Windows, this guide to install it on Linux, and this guide to install it on macOS.

To install DbVisualizer, navigate to the DbVisualizer download page. Then download the recommended installer for your operating system. After that, execute the installer you have downloaded and follow the instructions that follow.

When you start DbVisualizer, you are prompted to choose the DbVisualizer plan to proceed. You can proceed with the DbVisualizer free version with limited features or get a 21-day free trial on the pro version.

Here is how DbVisualizer free version differ from pro version.

Comparing DbVisualizer free and paid version.
Comparing DbVisualizer free and paid version.

Step 1: Connecting Postgres to DbVisualizer

Start DbVisualizer and click create a connection button.

Creating a database connection in DbVisualizer.
Creating a database connection in DbVisualizer.

Search and select the Postgres driver from the popup menu on the left side of your screen.

Searching and selecting Postgres driver in DbVisualizer.
Searching and selecting Postgres driver in DbVisualizer.

An object view tab for the Postgres connection is opened.

Fill in the empty fields as shown below:

Name: DataSecurity(Note that you can use name of your choice) Database: postgres Database Userid: postgres Database Password: Enter Your Postgres password

Object view tab for the Postgres connection.
Object view tab for the Postgres connection.

Once you have filled in all the empty fields, click the connect button at the bottom.

Step 2: Creating a database on Postgres using DbVisualizer

Open the database connection DataSecurity tab tree.

Opening database connection tab tree.
Opening database connection tab tree.

Inside the connection tab tree, open the Databases tab tree.

Opening the database tab tree.
Opening the database tab tree.

Right-click on the Databases tab tree and select Create Database option.

Right-clicking databases tab tree and selecting create database.
Right-clicking databases tab tree and selecting create database.

Fill in the fields as shown below and then click the Execute button to create the database.

New Database Name:“Name for your Database.”
Owner:“Owner of the database in PostgreSQL.”
Template:“Boilerplate files containing SQL scripts that help you create objects in a database.”
Encoding:“Encoding converts data into a standard format.”
Collation:“Collation specifies how data is sorted and compared in a database.”
Filling database fields.
Filling database fields.

If you look at the connection you just created, you will see the database securitydb has been created.

Created securitydb database.
Created securitydb database.

Controlling database access permissions

Database access control is a method used to allow access to a database only to user groups who are allowed to access data in the database and restricting access to unauthorised people to strengthen the security of database infrastructure.

To ensure proper access control, we can use the GRANT statement. The GRANT statement is used to grant privileges to a role to alter database objects like tables, views and functions. Below is the syntax of the GRANT SQL statement.

Copy
        
1 GRANT privilege | ALL 2 ON table_name 3 TO role_name;

From the syntax above, a privilege which can be SELECTINSERTUPDATEDELETE and more is specified. The ALL option is used to Grant all privileges on a table to the role. After that, the name of the table is specified after the ON keyword. Finally, the name of the role granted privileges is specified.

Here are the steps you can follow to secure data in a database using the GRANT SQL statement.

Step 1: Create a new table called customers in the securitydb database using the following SQL query.

Copy
        
1 create table customers ( 2     customer_id int, 3     first_name varchar(100) not null, 4     last_name varchar(100) not null, 5     email varchar(255) not null, 6     phone varchar(25) not null, 7     primary key(customer_id) 8 )

To run the SQL query in DbVisualizer, click the SQL Commander tab at the top of your screen and select New SQL Commander, as shown below. Doing so will create a new tab to run SQL queries in.

Opening SQL editor in DbVisualizer.
Opening SQL editor in DbVisualizer.

Once the new SQL commander tab opens, Open [ choose connection ] drop-down menu and select DataSecurity as the connection containing the database where you want to create the table.

Selecting DataSecurity as Database connection.
Selecting DataSecurity as Database connection.

Then select securitydb as the database where you want to create the table.

Selecting securitydb as database.
Selecting securitydb as database.

After that, select public as the schema.

Selecting public as the schema.
Selecting public as the schema.

Add the create customers table SQL query to the SQL editor and Press the (Ctrl+Enter) keys to run the SQL query.

Running SQL query on DbVisualizer
Running SQL query on DbVisualizer

Right-click on the tables tab and refresh to see the table created.

Refreshing objects tree to see the table created.
Refreshing objects tree to see the table created.

You should now see that the customers table has been created in the securitydb database.

Customers table created.
Customers table created.

Step 2: Create a new user called John that can log in to the postgres database connection using the following SQL query. Press the (Ctrl+Enter) keys to run the SQL query.

Copy
        
1 create role john 2 login 3 password '12345';
Running create role SQL query on DbVisualizer.
Running create role SQL query on DbVisualizer.

Step 3: Login to your PostgreSQL database using the user named John. However, first log out of the current logged in user postgres by right-clicking on Database Connection DataSecurity and select the Disconnect option as shown below.

Logging out user in Postgres.
Logging out user in Postgres.

After that, right-click on the Database Connection DataSecurity and select the Edit Database Connection option as shown below.

Selecting the edit database connection option.
Selecting the edit database connection option.

A new tab for editing the database connection opens as shown below.

Edit database connection tab.
Edit database connection tab.

Enter the username and password to access your PostgreSQL instance as shown below.

Filling in the username and password.
Filling in the username and password.

Click the connect button at the bottom and now you are logged in as the user john.

Step 4: Let us now see if the user John can access data on the customers table by running the SQL query below.

Copy
        
1 SELECT * FROM customers;

Once the SQL query runs, you will get an error because the user John is not granted access to the customers table.

SQL query error.
SQL query error.

This means that user john cannot directly write SQL queries that do what he want to the customers table. Once you login as user postgres, you can allow user John to select data from the customers table by granting the user SELECT privilege using GRANT SQL statement shown below.

Copy
        
1 GRANT SELECT 2 ON customers 3 TO john;

Since only SELECT privilege is granted to user John, he can now only select data from the customers table and cannot INSERTUPDATE or DELETE any data from the table hence making sure the data is safe and secure.

Securing Database Data Using a Stored Procedure

A stored procedure is a precompiled set of SQL statements that can be executed on a database server. It is typically used to perform a specific task or set of tasks that are often used in an application.

Stored procedures can be used to improve the security of a database by limiting the types of SQL statements that can be executed on the server. By limiting access to the underlying tables and restricting the types of SQL statements that can be executed, stored procedures can help prevent unauthorized users from accessing or modifying sensitive data.

Let's assume you're a database administrator, someone responsible for maintaining, securing and operating a bank database that stores customer financial data.

As a database administrator, you want to task someone with managing customer transactions. For someone to manage these transactions, they need to be granted access to the table that contains customer financial data.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

Giving direct access to the table with customer financial data is not a good idea because someone can decide to commit fraud. To protect the customer financial data, you can create a stored procedure that allows access to some parts of the table while denying direct select, insert, update and delete operations against the table.

In this case, let us create a stored procedure that allows someone to manage customer transactions while restricting them from writing SQL queries that do what they want to the database table.

Step 1: Create a table called transactions in the securitydb database we created earlier by right-clicking on the tables tab tree and select the Import Table Data option. I have created a file containing data we can import into the table. You can download the data file by navigating to this link.

Selecting the Import Table Data option.
Selecting the Import Table Data option

A pop-up window opens up where you are prompted to import the data file you just downloaded. Once the data file is selected, click the open button as shown below.

Import data file pop-up window.
Import data file pop-up window

Click the next button at the bottom of the pop-up window continuously until you reach the new table details window as shown below.

New table details window.
New table details window

Change the table name from newtable to transactions as shown below.

Adding Table Name.
Adding Table Name

Click the next button at the bottom and then import the data file by clicking the import button as shown below.

Importing data file.
Importing data file

Once the data file has been imported, right-click on the tables tab tree and refresh to see the new transactions table created.

Refreshing tables tab tree.
Refreshing tables tab tree

You should now be able to see the imported data, as shown below.

Imported table data.
Imported table data

Step 2: Create a stored procedure called Transfer_Money that transfers a specified amount of money from one account to another as shown below.

Copy
        
1 CREATE PROCEDURE Transfer_Money( 2     sender varchar, 3     receiver varchar, 4     amount dec 5 ) 6 LANGUAGE SQL 7 AS $$ 8 UPDATE transactions 9 SET balance 10 = CASE 11 WHEN customer_name = sender THEN balance - amount 12 WHEN customer_name = receiver THEN balance + amount 13 ELSE balance 14 END 15 WHERE customer_name IN(sender, receiver) 16 $$;

Run the SQL query on the DbVisualizer editor to create the stored procedure as shown below.

Creating the stored procedure.
Creating the stored procedure

Refresh Procedures tab tree and you should see that the transfer money stored procedure has been created.

Transfer money stored procedure created.
Transfer money stored procedure created

Step 3: Grant the user john we created earlier permissions to call the Transfer_Money stored procedure by running the SQL query shown below inside of the DbVisualizer SQL editor.

Copy
        
1 GRANT EXECUTE ON PROCEDURE Transfer_Money TO john;
Granting user john permission to call stored procedure.
Granting user john permission to call stored procedure

Since the transfer money stored procedure involves updating transactions table data, we need to grant the user John SELECT and UPDATE permissions on the table by running the SQL query below.

Copy
        
1 GRANT SELECT, UPDATE 2 ON transactions 3 TO john;
Granting user john permission to select and update a table
Granting user john permission to select and update a table

Any UPDATE command requires SELECT permission because it must reference table columns to determine which rows to update.

Step 4: Log out of the postgres account and login as John. Call the Transfer_Money stored procedure and make it send $200 from the account named John Doe to the account of Nick Adams by running the SQL query shown below.

Copy
        
1 CALL Transfer_Money('John Doe', 'Nick Adams', 200);
User john calling Transfer Money stored procedure.
User john calling Transfer Money stored procedure

Before calling the stored procedure, John Doe account had a balance of $1200 while Nick Adams account had a balance of $5200 as shown below.

Transactions table before calling the stored procedure.
Transactions table before calling the stored procedure

After calling the stored procedure, $200 is transferred from John Doe account to Nick Adams account hence John Doe’s Balance is updated to $1,000 and Nick Adams balance is updated to $5400 as shown below.

Transactions table after calling the stored procedure.
Transactions table after calling the stored procedure

User John now has permission to only SELECT and UPDATE transactions table data by calling the Transfer_Money stored procedure, hence he cannot INSERT or DELETE any data from the table.

That means user john cannot commit fraud by inserting his own account details into the transactions table data and then transferring customers money to his account.

Encrypting A Connection Using SSL/TLS

SSL (Secure Sockets Layer) and TLS (Transport Layer Security) are protocols that provide secure communication over a computer network. They are commonly used to encrypt connections between a client and a server, such as a web browser and a web server.

To use SSL/TLS to encrypt a connection to a Postgres database, you will need to configure the Postgres server to use SSL/TLS and obtain a certificate. Here are the steps to do this:

  1. Generate a private key and a certificate signing request (CSR) using a tool such as OpenSSL.
  2. Submit the CSR to a certificate authority (CA) to obtain a certificate.
  3. Install the private key and the certificate on the Postgres server.
  4. Edit the Postgres configuration file (postgresql.conf) to enable SSL/TLS and specify the location of the private key and certificate files.
  5. Restart the Postgres server for the changes to take effect.

Once SSL/TLS is enabled on the server, client applications can connect to the database using SSL/TLS by specifying the "sslmode" connection parameter. The available options for this parameter are "require", "prefer", "allow", and "disable".

Securing Database Data Using SSH

SSH (Secure Shell) is a network protocol that allows you to securely connect to a remote computer over an unsecured network. It is often used to access servers and execute commands remotely, but it can also be used to establish secure connections to databases.

To use SSH to secure data in a PostgreSQL database, you can do the following:

  1. Set up an SSH server on the machine that is running the PostgreSQL database. You can use the OpenSSH software for this purpose.
  2. Connect to the SSH server from your client machine using an SSH client, such as ssh on the command line or a graphical tool like PuTTY.
  3. Once you are connected to the SSH server, you can use the psql command-line tool or DbVisualizer to connect to the PostgreSQL database. The connection will be encrypted over the SSH tunnel, providing an additional layer of security for your data.
  4. You can also set up SSH key-based authentication for connecting to the SSH server. This allows you to log in without entering a password, and is more secure than password-based authentication.

Securing Database Data Using SSO, 2FA And MFA

SSO (Single Sign-On) is a method of authentication that allows users to log in with a single set of credentials (e.g., a username and password) and access multiple applications without having to log in to each one separately.

2FA (Two-Factor Authentication) is a method of authentication that requires a user to provide two forms of identification when logging in to an account. This can include something the user knows (e.g., a password), something the user has (e.g., a mobile phone or security token), or something the user is (e.g., a fingerprint or facial recognition).

MFA (Multi-Factor Authentication) is similar to 2FA, but requires more than two forms of identification.

To use SSO, 2FA, or MFA to secure data in a PostgreSQL database, you can do the following:

  1. Set up a central authentication server that supports SSO, 2FA, or MFA. This could be a separate server or a service like Active Directory or Okta.
  2. Configure the PostgreSQL database to use the central authentication server for user authentication. This can typically be done by modifying the pg_hba.conf file and specifying the authentication method as "ldap" or "radius".
  3. When users try to log in to the PostgreSQL database, they will be prompted to enter their credentials and complete the additional authentication steps required by the central authentication server.
Dbvis download link img

Conclusion

In this article, you have learned how to secure data in a Postgres database using Database Access Control method, using SSL/TLS to encrypt connection to the database, using SSH, SSO, 2FA, MFA and Stored Procedures. You have also learned how to connect Postgres to a SQL client, create a database, import data files into Postgres and run SQL queries.

To learn more about database development and follow the newest trends in the database space, make sure to follow the DbVisualizer blog, and we will see you in the next one.

Article summary

In this article, you have learned how to secure data in a Postgres database using various methods.

Firstly, the article shows you how to use the database access control method to limit access to sensitive data by creating roles and granting or revoking access to them.

Secondly, you learn how to use stored procedures as a means of controlling and restricting database access.

Thirdly, the article covers the use of SSL or TLS to encrypt the connection between the client and the server to prevent sensitive data from being stolen.

Fourthly, you learn how to use SSH to secure the remote access to the database and protect against unauthorised access.

Lastly, the article explains how to use 2FA and MFA to provide an extra layer of security for the database access by requiring multiple credentials from users.

In conclusion, following the best practices and techniques outlined in this article, it is possible to effectively secure sensitive information stored in a Postgres database.

About the author
Bonnie
Bonnie
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗