How To Secure Data In A Postgres Database: A Guide
Published: 2023-04-06 06:16:42

**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.**

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](https://www.postgresql.org/download/) and download it for your operating system. You can follow [this guide](https://www.youtube.com/watch?v=IYCNAjyoHs8&t=5s) to install PostgreSQL on Windows, [this guide](https://www.youtube.com/watch?v=-LwI4HMR_Eg&t=599s) to install it on Linux, and [this guide](https://www.youtube.com/watch?v=EZAa0LSxPPU) to install it on macOS.

To install DbVisualizer, navigate to the [DbVisualizer download page](https://www.dbvis.com/download/). 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.
<br \>
![Image 1 - Comparing DbVisualizer free and paid version.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e027q3lnj9gvmswvse9r.png)
<figure><figcaption>Image 1 - Comparing DbVisualizer free and paid version.</figcaption></figure>
<br \>

## Step 1: Connecting Postgres to DbVisualizer
Start DbVisualizer and click `create a connection`` button.
<br \>
![Image 2 - Creating a database connection in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/txsvt1tce4bc613524bb.png)
<figure><figcaption>Image 2 - Creating a database connection in DbVisualizer.</figcaption></figure>
<br \>
Search and select the Postgres driver from the popup menu on the left side of your screen.
<br \>
![Image 3 - Searching and selecting Postgres driver in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wk70oyp2jjt5ylo4s9s6.png)
<figure><figcaption>Image 3 - Searching and selecting Postgres driver in DbVisualizer.</figcaption></figure>
<br \>
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
<br \>
![Image 4 - Object view tab for the Postgres connection.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qbl0hr5w8bztj8ff04rv.png)
<figure><figcaption>Image 4 - Object view tab for the Postgres connection.</figcaption></figure>
<br \>
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.
<br \>
![Image 5 - Opening database connection tab tree.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/19pvdbdowccl6uv8b7q3.png)
<figure><figcaption>Image 5 - Opening database connection tab tree.</figcaption></figure>
<br \>
Inside the connection tab tree, open the Databases tab tree.
<br \>
![Image 6 - opening the database tab tree.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ems5yiwbm7y2qge0dgca.png)
<figure><figcaption>Image 6 - opening the database tab tree.</figcaption></figure>
<br \>
Right-click on the `Databases` tab tree and select `Create Database` option.
<br \>
![Image 7 - right-clicking databases tab tree and selecting create database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bnau11sko9ffbdfp1hlo.png)
<figure><figcaption>Image 7 - right-clicking databases tab tree and selecting create database.</figcaption></figure>
<br \>
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.”
<br \>
![Image 8 - filling database fields.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i5zlzqb81tkv6lb103a8.png)
<figure><figcaption>Image 8 - filling database fields.</figcaption></figure>
<br \>
If you look at the connection you just created, you will see the database `securitydb` has been created.
<br \>
![Image 9 - created securitydb database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qjzvmblamd20hvuhded.png)
<figure><figcaption>Image 9 - created securitydb database</figcaption></figure>
<br \>

## 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.

```
GRANT privilege | ALL
ON table_name
TO role_name;
```
From the syntax above, a privilege which can be `SELECT`, `INSERT`, `UPDATE`, `DELETE` 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.

```
create table customers (
    customer_id int,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null,
    phone varchar(25) not null,
    primary key(customer_id)
)
```
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.
<br \>
![Image 10 - opening SQL editor in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tjdtnluvh8qtdt1lcm4l.png)
<figure><figcaption>Image 10 - opening SQL editor in DbVisualizer.</figcaption></figure>
<br \>
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.
<br \>
![Image 11 - selecting DataSecurity as Database connection.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l4iaj3n9wx2f461x812l.png)
<figure><figcaption>Image 11 - selecting DataSecurity as Database connection.</figcaption></figure>
<br \>
Then select `securitydb` as the database where you want to create the table.
<br \>
![Image 12 - selecting securitydb as database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s1ba6ls2875w8o1sqhyb.png)
<figure><figcaption>Image 12 - selecting securitydb as database.</figcaption></figure>
<br \>
After that, select public as the schema.
<br \>
![Image 13 - selecting public as the schema.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bub4jxfo9994ih5mzvqb.png)
<figure><figcaption>Image 13 - selecting public as the schema.</figcaption></figure>
<br \>
Add the create customers table SQL query to the SQL editor and Press the `(Ctrl+Enter)` keys to run the SQL query.
<br \>
![Image 14 - running SQL query on DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d3wosiogzuw2idovczdn.png)
<figure><figcaption>Image 14 - running SQL query on DbVisualizer.</figcaption></figure>
<br \>
Right-click on the tables tab and refresh to see the table created.
<br \>
![Image 15 - refreshing objects tree to see the table created.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jfsla3fsujaurdo9j67b.png)
<figure><figcaption>Image 15 - refreshing objects tree to see the table created.</figcaption></figure>
<br \>
You should now see that the `customers` table has been created in the `securitydb` database.
<br \>
![Image 16 - customers table created.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uvt5d379biut7x546fen.png)
<figure><figcaption>Image 16 - customers table created.</figcaption></figure>
<br \>
**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.

```
create role john
login
password '12345';
```

![Image 17 - running create role SQL query on DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wr0cmfxaw4prf4sr7qty.png)
<figure><figcaption>Image 17 - running create role SQL query on DbVisualizer.</figcaption></figure>
<br \>
**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.
<br \>
![Image 18 - Login out user postgres.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x8d9n6ieztxv1nypgrhv.png)
<figure><figcaption>Image 18 - Login out user postgres.</figcaption></figure>
<br \>
After that, right-click on the Database Connection `DataSecurity` and select the `Edit Database Connection` option as shown below.
<br \>
![Image 19 - selecting the edit database connection option.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ifuzr6e5ashfe3vcr0ee.png)
<figure><figcaption>Image 19 - selecting the edit database connection option.</figcaption></figure>
<br \>
A new tab for editing the database connection opens as shown below.
<br \>
![Image 20 - edit database connection tab.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a9t5uz74ln9gqtxlal1u.png)
<figure><figcaption>Image 20 - edit database connection tab.</figcaption></figure>
<br \>
Enter the username and password to access your PostgreSQL instance as shown below.
<br \>
![Image 21 - filling in the username and password.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a15b6nwduwiwhiwuh8c9.png)
<figure><figcaption>Image 21 - filling in the username and password.</figcaption></figure>
<br \>
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.

```
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.
<br \>
![Image 22 - SQL query error.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wkgaqleuzjvfrhznzp5d.png)
<figure><figcaption>Image 22 - SQL query error.</figcaption></figure>
<br \>
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.

```
GRANT SELECT
ON customers
TO john;
```
Since only `SELECT` privilege is granted to user John, he can now only select data from the customers table and cannot `INSERT`, `UPDATE` 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.

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](https://docs.google.com/spreadsheets/d/1eZ6ysTxB1RkFypfxHAE1o_juvZzGw5fv/edit#gid=1477240791).
<br \>
![Image 23 - Selecting the Import Table Data option](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/26mf1q3t3ldryzzkhqqn.png)
<figure><figcaption>Image 23 - Selecting the Import Table Data option</figcaption></figure>
<br \>
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.
<br \>
![Image 24 - Import data file pop-up window](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bj62uf4jhhz37l58q2hi.png)
<figure><figcaption>Image 24 - Import data file pop-up window</figcaption></figure>
<br \>
Click the next button at the bottom of the pop-up window continuously until you reach the new table details window as shown below.
<br \>
![Image 25 - New table details window](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s0b1nh7cjxeul6twsr48.png)
<figure><figcaption>Image 25 - New table details window</figcaption></figure>
<br \>
Change the table name from newtable to transactions as shown below.
<br \>
![Image 26 - Adding Table Name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ydavni1h9ba90nlnyedw.png)
<figure><figcaption>Image 26 - Adding Table Name</figcaption></figure>
<br \>
Click the next button at the bottom and then import the data file by clicking the import button as shown below.
<br \>
![Image 27 - Importing data file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xfpvqkkcx7iafr04g1yo.png)
<figure><figcaption>Image 27 - Importing data file</figcaption></figure>
<br \>
Once the data file has been imported, right-click on the tables tab tree and refresh to see the new transactions table created.
<br \>
![Image 28 - Refreshing tables tab tree](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fks392of1vxba0rsse4z.png)
<figure><figcaption>Image 28 - Refreshing tables tab tree</figcaption></figure>
<br \>
You should now be able to see the imported data, as shown below.
<br \>
![Image 29 - Imported table data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nkaqrigwz5lo1punyfln.png)
<figure><figcaption>Image 29 - Imported table data</figcaption></figure>
<br \>
**Step 2:** Create a stored procedure called Transfer_Money that transfers a specified amount of money from one account to another as shown below.

```
CREATE PROCEDURE Transfer_Money(
    sender varchar,
    receiver varchar,
    amount dec
)
LANGUAGE SQL
AS $$
UPDATE transactions
SET balance
= CASE
WHEN customer_name = sender THEN balance - amount
WHEN customer_name = receiver THEN balance + amount
ELSE balance
END
WHERE customer_name IN(sender, receiver)
$$;
```
Run the SQL query on the DbVisualizer editor to create the stored procedure as shown below.
<br \>
![Image 30 - Creating the stored procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qgmlnuts22rk5yok7f1n.png)
<figure><figcaption>Image 30 - Creating the stored procedure</figcaption></figure>
<br \>
Refresh Procedures tab tree and you should see that the transfer money stored procedure has been created.
<br \>
![Image 31 - Transfer money stored procedure created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfmn6mxm1f9xeub2r3al.png)
<figure><figcaption>Image 31 - Transfer money stored procedure created</figcaption></figure>
<br \>
**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.

```
GRANT EXECUTE ON PROCEDURE Transfer_Money TO john;
```
<br \>
![Image 32 - Granting user john permission to call stored procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hxy7g5d0s7ngk0am0sh4.png)
<figure><figcaption>Image 32 - Granting user john permission to call stored procedure</figcaption></figure>
<br \>
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.

```
GRANT SELECT, UPDATE
ON transactions
TO john;
```

![Image 33 - Granting user john permission to select and update a table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pkprjstg2s0mgey4vpq2.png)
<figure><figcaption>Image 33 - Granting user john permission to select and update a table</figcaption></figure>
<br \>
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.

```
CALL Transfer_Money('John Doe', 'Nick Adams', 200);
```

![Image 34 - User john calling Transfer Money stored procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fk7pf6fndys8topwbzjs.png)
<figure><figcaption>Image 34 - User john calling Transfer Money stored procedure</figcaption></figure>
<br \>
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.
<br \>
![Image 35 - Transactions table before calling the stored procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zm0k9xbpl5a9x34o5m6v.png)
<figure><figcaption>Image 35 - Transactions table before calling the stored procedure</figcaption></figure>
<br \>
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.
<br \>
![Image 36 - Transactions table after calling the stored procedure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2mgpvab804cnebwb00rv.png)
<figure><figcaption>Image 36 - Transactions table after calling the stored procedure</figcaption></figure>
<br \>
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.

## 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](https://www.dbvis.com/thetable/), 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 is a web developer and technical writer creating easy-to-understand technical articles.