How To Secure Data In A Postgres Database: A Guide
DbVisualizer
Posted on April 6, 2023
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 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.
Step 1: Connecting Postgres to DbVisualizer
Start DbVisualizer and click create a connection` button.
Search and select the Postgres driver from the popup menu on the left side of your screen.
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
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.
Inside the connection tab tree, open the Databases tab tree.
Right-click on the Databases tab tree and select Create Database option.
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.”
If you look at the connection you just created, you will see the database securitydb has been created.
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.
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.
Then select securitydb as the database where you want to create the table.
After that, select 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.
Right-click on the tables tab and refresh to see the table created.
You should now see that the customers table has been created in the securitydb database.
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';
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.
After that, right-click on the Database Connection DataSecurity and select the Edit Database Connection option as shown below.
A new tab for editing the database connection opens as shown below.
Enter the username and password to access your PostgreSQL instance as shown below.
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.
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.
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.
Click the next button at the bottom of the pop-up window continuously until you reach the new table details window as shown below.
Change the table name from newtable to transactions as shown below.
Click the next button at the bottom and then import the data file by clicking the import button as shown below.
Once the data file has been imported, right-click on the tables tab tree and refresh to see the new transactions table created.
You should now be able to see the imported data, as shown below.
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.
Refresh Procedures tab tree and you should see that the transfer money stored procedure has been 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.
GRANT EXECUTE ON PROCEDURE Transfer_Money TO john;
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;
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);
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.
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.
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:
- Generate a private key and a certificate signing request (CSR) using a tool such as OpenSSL.
- Submit the CSR to a certificate authority (CA) to obtain a certificate.
- Install the private key and the certificate on the Postgres server.
- Edit the Postgres configuration file (postgresql.conf) to enable SSL/TLS and specify the location of the private key and certificate files.
- 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:
- Set up an SSH server on the machine that is running the PostgreSQL database. You can use the OpenSSH software for this purpose.
- 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.
- 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.
- 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:
- 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.
- 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".
- 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, 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.
Posted on April 6, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
