Design a site like this with
Get started

PostgreSQL: Security Standards & Best Practices

We all know that in the world of technology, there are people that will use it for good and others that will use it for bad.  You can’t check your email from unknown senders; you can’t go to the gas station without carefully examining the credit card terminal, even with your cell phone – you can’t click on random links while surfing the Web without getting a virus. You don’t want to think about it because it’s scary.  In a matter of seconds, your information can be halfway across the world.  Therefore, you have no choice but to think about it. Once your data has been compromised, there is no turning back.  You can only do damage control.  

The truth is, achieving perfect security with any system or device is impossible.  Nevertheless, if you have a lot of controls in place, hackers will only be able to get so far before they run into a brick wall or are detected and terminated.  

PostgreSQL database is used by countless businesses to manage highly sensitive information that must have layers and layers of security.  Protecting this data should be the priority of every business.  No one wants bad press because of a security breach with their system.  Consumers are frantic whenever this happens.  Then the ripple effect of contacting financial institutions begins, and in turn, the business loses the confidence of their customers.

To effectively secure the PostgreSQL database means you have to think like a bad guy to prevent the bad guy from getting in.  This is sad but very true.  This person may not be a professional hacker.  It could be the office cleaning lady that takes advantage of an employee’s unlocked computer screen while they unsuspectedly take a lunch break.  

Risk management steps must be executed.  It requires for you to evaluate areas that can potentially lead to a deliberate attack or an unintentional breach of information, and then develop strategies to prevent it from happening.  Here are the key areas that must be considered to avoid unauthorized access from happening to you.

Firewall Protection

The first line of defense for PostgreSQL comes from the devices placed in front of the server.  Proper security of the server is like putting up a barb wire fence around your database.  Users will have to get through the barricade before they can even “knock on the door” of the database.  All traffic going in and out of your network and database should be controlled and monitored.  Your knowing the who, what, when, why, and where is essential.  If you can’t answer all of these at all times with your system, there’s a problem, and you need to rethink your infrastructure to make sure that appropriate auditing extensions are in place. 

Proper firewall installation and setup is essential.  They contain threat detection algorithms to uncover suspicious behavior, such as a user being able to type 8,000 words per minute.  Of course, that’s a huge red flag that a bot is trying to hack into the system, and the attack will swiftly be interjected.  

You must never assume that if a hacker can get through the firewall, they cannot gain root access to PostgreSQL.  If you are familiar with the Dirty Cow bug, then you can understand this dangerous line of thinking.  This bug was found in the Linux operating system which allowed for basic users to alter their privileges to root user and gain unauthorized access to highly sensitive data.  This incident occurred only a few years ago, and experts claim to have resolved it. Overall, a firewall adds a valuable layer of defense around your database, and it should not be taken for granted.  

VPC Protection

If you choose to place your database inside a VPC, there is much to consider.  The data center of your choice should have a strict and controlled parameter containing video surveillance, penetration detection, and failover processes.  Asking the right questions is crucial when selecting the right data center for any business.  Believe it or not, there are data centers out there do not have extensive physical and environmental security processes in place.  What’s even more shocking is that businesses don’t often ask about it.  So, you have to be an investigator and ask the right questions.  If security standards are not up to par, you should choose another data center that maintains a higher level of security.  

After choosing your data center, make sure that your VPC is configured correctly.  The database should be strategically placed in a private subnet with no internet access and all traffic routed through a virtual private gateway.  Access to the gateway should be controlled and monitored.  Multi-factor authentication is the best way to protect from unauthorized access.  It should also be fault-tolerant, and alerts should be sent to the appropriate personnel if changes are to occur.  If an employee is no longer with the business, you should revoke their rights to access both the VPC and PostgreSQL.

Security Updates

As a developer, you want to ensure that you are signed up for pgsql-announce mailing service.  It provides notification when there are database updates.  This is critical because some updates are security related and should be applied immediately.  PostgreSQL Global Development Group searches for vulnerabilities within the database and releases updates to prevent security attacks.  They encourage users to reach out if they have discovered any security issues.  A complete list of vulnerabilities can be found on their website with detailed information, including the update containing the fix.  

Also, operating system security patches occur all the time and should also be taken seriously.  When you become aware of them, install as soon as you are able if your operating system is vulnerable because you don’t want to take the time to install updates, you’re dimensioning the layers of security that you put in place.  

Root Login & Passwords

The root user of the database has full rights and privileges to everything within the system.  Under no circumstances should you ever use this password to complete everyday tasks.  These credentials should be stored in a safe place and should contain a long string of characters that is impossible for you to memorize.  You should log in as the root user once to get the database set up, and then create administrative and user roles for everyday use. By giving each user their unique credentials, you can effectively monitor activity within the database for auditing purposes. In addition, password rotation should be required every 90 days, and the same password should not be used again.  Again, the larger the number of characters for user passwords, the more secure each password is.  

Usability User Access

Restricting privileges within a database is standard practice when you have multiple users.  Even so, you still want to make sure that your employees can get their job done effectively.  Time and time again there are instances where employees create ways to avoid security procedures out of frustration.  The methods that are put in place make it difficult to work and slows down their natural workflow.  You then create a work environment where everyone is comfortable with “getting around the system” to increase production, which only pleases management in the long-run.  You don’t want this to happen.  Usability with the highest security standards is vital because you will have happy employees while maintaining the security of the system.  

Role-Based Access

Role-based access control only allows each business employee the ability to access only the information to get their job done.  A basic example of this would be restrictions given to a cashier at a grocery store. There is no reason why a cashier should have access to payroll documentation in Human Resources.  It just doesn’t make sense, and it’s a security risk.

With PostgreSQL, some employees may be configured to have read-only access while others may be given the ability to make physical changes to the data.  It’s the superuser’s responsibility to enter the appropriate limitations for each user, and then it’s the database’s job to enforce these restrictions.  It, of course, will grant or revoke access based on the privileges of the user. This is a great way to keep users within their respective departments.

One common problem that can arise is the sharing of password credentials for one reason or another. Over time, it’s natural for you to build up a trust that your colleagues are going to uphold company standards. This is certainly understandable- great and trusting relationships create a great work environment.  You may even feel confident enough to write your password on a sticky note and give it to someone when they have problems gaining access.  But what if this person takes the sticky note and leaves it in plain view in their desk? What stops this person from giving your information to someone they trust?  What happens when someone gets fired, and they have your password? Their credentials will get revoked but yours will not.  It’s something that all employees should think about because something small can turn into a huge problem.  

Have you ever heard of the company Code Spaces?  In 2014, something very similar happened to them.  A hacker logged into Code Spaces AWS account with user credentials and deleted all the companies primary and backup data.  All the instances housing their data was permanently lost, forcing them to go out of business.  This could have been prevented if multifactor authentication were put in place by management, which requires root users to verify their identity using multiple security methods.  In this case, it was not, and it became the demise of the entire company.

Audit Logging

While using this database, you want to ensure that you have audit logging is in place.  A third-party agency will come into the business at some point to review the database accounts and their function.  Your business will have to produce accurate logs based on their request to prove that your business is complying with government, financial, ISO, or other organization compliance standards and laws. 

Information security is critical when you are storing highly sensitive data for thousands or even millions of consumers.  Security threats change so rapidly and can be either an internal or external attack. Therefore, tracking all activity is essential.  Application extensions are available for this purpose.  You will be able to provide auditors with information that occurred within your database on a specific day and time.  Not being able to provide the data will draw red flags.

It is essential to realize that every business does not execute all security practices that are out there. It’s nearly impossible because every business is functioning completely different.  However, it is crucial to know the benefits and the risks, so that as your business scales, you can implement proper procedures and systems.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s