Introduction to PostgreSQL Encryption
PostgreSQL provides encryption at different levels and gives flexibility in protecting records and data from different attacks such as database server theft, illegal administrator and insecure network etc. Encryption is required for securing sensitive data such as banking databases and financial databases etc. Basically, encryption means we encoded teat or data from one form to another form with a decryption key. The decryption key is also private and large, so only you can understand. When we encrypted data, that means we converted plain text to ciphertext, this encryption you can only understand with the help of decryption keys. PostgreSQL has an option to provide password storage encryption to secure databases; it also provides encryption for specific columns from tables.
Syntax:
create extension statement with pgcrypto;
Explanation:
- In the above syntax, we use the create extension statement with pgcrypto command.
How is Encryption Carried Out in PostgreSQL?
Different organizations deal with different types of data such as financial transactions, or we can consider medical records; this is very secure information we need to store into the database. So security is the main thing for ensuring the security of data.
PostgreSQL has a different encryption option as follows:
1. Encryption of Data at Rest
Data at rest means we store unuseful data on disk. When we host a database on a cloud environment, that means we give all access permission to the user, so at that time, we required encryption to protect data on disk from theft.
2. Full Disk Encryption
The basic concept in Full Disk Encryption means that we protect all files and temporary storage that contain data. Sometimes it is difficult to protect a particular file or temporary storage because selecting a file or data is not an easy task, so that reason PostgreSQL provides a Full Disk Encryption method. The main advantage of this method is that there is no special attention required.
3. File-Based Encryption
In file-based encryption, it encrypted file and directories by using a stackable cryptographic file system. PostgreSQL encrypted file and directories by using pg_crypto. In this approach, we use the public key cryptography method. The main disadvantage of this approach is that it leaves some files encrypted, and those file attackers easily recover.
4. Instance Level Encryption
In this approach, the buffer concept is used, in which all files are stored in the disk as data at rest encrypted. Each writes operation from the shard buffer the data in disk automatically encrypted. In this approach, we use two different commands to encrypt the data, such as the initdb command is used to initialize the database and pgcrypto.keysetup _parameter command is used to fetch the encrypted key from the server.
5. Client-Side Encryption
Sometimes, the database administrator cannot trust a client at that time; the database administrator used the client-side encryption method to encrypt data from a client. After applying the client-side encryption method, the unencrypted data is not shown on the server.
6. Encrypting Data Across A Network
In this approach, the SSL connection encrypted all data before sending it to the network. The database administrator specifies the host and required SSL connection, so it uses different configuration files for that purpose.
Example of PostgreSQL Encryption
Given below is the example of PostgreSQL Encryption:
Code:
create extension pgcrypto;
Explanation:
- In the above example, we use the create extension statement with pgcrypto command.
- The pgcrypto command is used to keep the encryption method.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
After that, we encrypted the password by using hashing and blowfish technique; the hashing uses 4 or 8 rounds.
Code:
select crypt('admin', gen_salt('bf', 8));
Explanation:
- In the above statement, we use a select statement with a crypt parameter, in which we encrypted the admin password by using hashing and blowfish technique.
- Here we use 8 rounds to encrypt the password.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
For the further process, we required a table, so we created a table by using the following statement.
Code:
create table demo_sample(emp_id int, encryption_pass varchar (150));
Explanation:
- With the help of the above statement, we created a demo_sample with different attributes such as emp_id and encryption_pass with the data type.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
The above snapshot shows we successfully created a demo_sample table, and the below snapshot shows the actual created table as follows.
Output:
After that, we insert a record into demo_sample by using the following statement as follows.
Code:
insert into demo_sample (emp_id,encryption_pass) values (1,'$2a$08$kBHePoCfMR7WGDocDKY04.9ZOgKwhkea8dDJzE1phufJRKJ7qoqAe');
insert into demo_sample (emp_id,encryption_pass) values (2,'$2a$08$hIm4hJ5yG66gV1VtkXciRec1zrUjRIGicHxcj7FTeClG0DKKj5LuC’);
Explanation:
- With the help of the above statement, we inserted a record into the demo_sample table with different values such as emp_id and encrypted password as shown above statement.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
Suppose we need to check if the password is correct or wrong, then we can use the following statement as follows.
Code:
select * from demo_sample where emp_id = 2 and encryption_pass = crypt('admin', encryption_pass);
Explanation:
- In the above statement, we use the select and where clause to check the password is correct or wrong; here, we check emp_id =2 and encrypted password as shown above statement.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
When we use the hacker command at that time, it does not return any single row from the encrypted password.
Let’s see how we can use hacker commands as follows.
Code:
select * from demo_sample where emp_id = 2 and encryption_pass = crypt('hacker', encryption_pass);
Explanation:
- In the above statement, we just change the word as hacker instead of the admin password, as shown in the above statement. After executing the above statement, it does not return any value of rows.
- We illustrate the outline of the excess outcome of the above declaration by using the following snapshot.
Output:
Conclusion
From the above article, we have seen the basic syntax of creating extensions in PostgreSQL and different commands related to encryption. We have additionally discovered how we can enforce them in PostgreSQL with different examples of every technique. From this article, we have seen how we can handle encryption in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL Encryption. Here we discuss the introduction, how encryption is carried out in PostgreSQL? and example. You may also have a look at the following articles to learn more –
