Securing a PostgreSQL API | Dreamfactory

API abuse was once one of the most infrequent vectors for cyber security attacks. It has now moved up the list to be one of the most frequently used techniques. In a time when data is a company’s competitive edge, more and more organizations are embracing APIs to integrate various databases and applications effectively. With the amount of information shared between these APIs, securing the data and user access to that information has become increasingly important. Here, we’ll look at a few security challenges you may face in securing a PostgreSQL API and how to deal with them.

What Is PostgreSQL?

PostgreSQL (also known as Postgres) is a popular enterprise-grade relational database management system (RDBMS). As a free and open-source database, the source code for PostgreSQL is available for anyone to view on Github and even contribute code if they wish. 

PostgreSQL is the back-end database for various use cases, including web applications, web services, and mobile applications. As a traditional RDBMS, it supports relational SQL querying. For several versions now, PostgreSQL has also supported non-relational querying through JSON. This has made it a popular choice for developers who deploy REST APIs and exchange messages in JSON format.

PostgreSQL was formerly known as POSTGRES. That name was chosen to indicate that it was an evolution of the older Ingres database system. In 1996, POSTGRES was renamed PostgreSQL to illustrate its SQL support. Its SQL implementation is similar to that of the Oracle RDBMS. PostgreSQL also features the PG/SQL procedural language comparable to Oracle's PL/SQL.

Designed initially solely for Unix-like systems, PostgreSQL today runs on all major platforms, such as Windows, macOS, and Linux.

Common PostgreSQL API Attacks

Attackers have various tactics to exploit PostgreSQL APIs. Regardless of the technique used, the effects can be devastating.

Man in the Middle

Man in the Middle (MitM) attacks refer to hackers intercepting communication between two parties. MitM attacks often target login credentials to gain unauthorized access to systems. These types of attacks are also used to steal personal information, spy on victims, or corrupt data.

Distributed Denial of Service Attacks

Not all attacks are about stealing information. A distributed denial-of-service (DDoS) attack disrupts normal traffic to a server. Attackers use this method to overwhelm the server with a flood of internet traffic. This malicious traffic sometimes consists of fake packets, incoming messages, or requests for connections to a PostgreSQL API.

SQL Injection Attacks on PostgreSQL APIs

Like many others, PostgreSQL databases are subject to this attack. A SQL injection attack occurs when an attacker “injects” their SQL statements into the original application’s SQL code. The goal is to change the query’s original intent to gain access to the underlying server. This attack is also sometimes used to perform a DDoS attack. 

SQL injection attacks are best prevented by following a strict set of principles. A good resource for learning all these techniques can be found at OWASP SQL Injection Prevention Cheat Sheet. The OWASP is a standard that outlines best practices on web security.

User Security Management: Less is Best

Security experts consistently warn that the vast majority of security breaches involve the use of privileged credentials. Privileged credentials refer to the authorization to bypass security methods. 

While granting API access to third parties is important, some discretion should be used. The recommended practice is to allow only the least privilege required to perform a specific action on the API. This principle is often referred to as the principle of least privilege (POLP). 

Benefits of the Principle of Least Privilege (POLP)

POLP reduces the risk of attackers exploiting credentials to compromise sensitive information. This principle helps prevent compromises such as malware from spreading to other areas of the system. Additionally, POLP promotes better stability in the system. By employing this principle, the entire system’s availability will be minimally impacted, if at all.

Physical Access

POLP should also be applied to physical access. It's nearly impossible to prevent someone with physical access to your Postgres server from gaining access to the data. Following strong physical security measures can mitigate risk.

Servers should be located in a secure facility where only authorized personnel can enter the server room. Monitoring should be used to validate who is entering and exiting the room. In a co-location facility, make sure the provider has a strictly enforced security policy. If you use a cloud provider for your PostgreSQL service, check their documentation to understand the level of security they offer.

Server Connection Configuration

There are two ways to connect to a Postgres server for standard database connections: the Unix domain socket or a TCP/IP socket. Unix domain sockets are only available from the servers on which they reside and appear as files to the operating system. This is often referred to as a "localhost connection." The Postgres config files should be configured so that only administrative users can access them to connect to the database.

TCP/IP socket connections are far more common, as this is how you connect to a server remotely. The server should only be configured to listen for and accept connections on networks that are actually required for operation. By default, a source code build of Postgres will only accept connections from localhost or a loopback address. Some pre-built versions of Postgres override this setting, so you should always check these settings after installation. Disable unneeded connections, like the Java JDBC driver, if it's not in use. 

Securing PostgreSQL APIs: The Role of JSON Web Tokens

JSON Web Tokens (JWT) represent a compact, lightweight and self-contained method for sharing information between systems using a JSON object. APIs recognize JWTs as trusted because they are digitally signed.

Authentication & Authorization

The most common use for a JWT is authentication and authorization. When APIs send a request, the receiving endpoint uses the information in the JWT to determine which routes, services, and resources are permitted with that token.

Information Exchange

JSON web tokens are an effective way for APIs to transmit information. They are digitally signed using public/private key pairs. Given that JWTs are self-contained units, they reduce network round-trip time.

Role-Based Access in Securing a PostgreSQL API

Roles tie together end-user access to applications and services. Roles govern HTTP access to the REST API endpoints in DreamFactory. When an end-user authenticates and receives a JWT token, their role determines which API endpoints they can access.

Securing a PostgreSQL API With Single Sign-On

Single Sign-on (SSO) is a way to grant access to multiple systems using a single set of login credentials. SSO saves the user time, as they no longer need to sign into various systems to accomplish their job.

Authentication & Authorization in Securing a PostgreSQL API

Developers have access to various protocols and methods for securing a PostgreSQL API. Each of the below methods works independently or hand-in-hand to accomplish the same purpose: authentication and authorization.

OAuth

OAuth is a protocol for allowing unrelated servers to permit authenticated users to access each other’s data without sharing the initial login credentials. An example of this is when a user logs in to a website. That site may allow users to sign in using their credentials from another website or service.

OAuth2 is also an option, but it requires a different security methodology. As OAuth2 delegates all security concerns to HTTPS, security vulnerabilities can develop via improper SSL or TLS configuration, leading to a MitM attack. On the other hand, OAuth2 is considered much easier to work with. Whereas the original OAuth only handled web workflows, OAuth2 also works with non-web clients.

Cross-Origin Resource Sharing (CORS)

Cross-Origin Resource Sharing is another HTTP method that permits systems to determine the level of access for each request. CORS works by adding HTTP headers to specify the appropriate permissions.

Active Directory

Active Directory refers to Microsoft’s directory service that defines users, groups, roles, authentication, and policy management. Authentication and authorization protocols use Active Directory to determine a user’s permissions.

Lightweight Directory Access Protocol (LDAP)

LDAP (Lightweight Directory Access Protocol) is an open-source alternative to Active Directory. LDAP is cross-platform, which makes it possible for systems built on different platforms to communicate.

Security Assertion Markup Language (SAML) 

SAML is a markup language that allows identity providers to share auth information with service providers. This approach removes the burden of performing authentication from the service provider. Authentication is handled by an identity provider who performs authentication and passes that information to the service provider. SAML is one method of implementing SSO.

Securing a PostgreSQL API with DreamFactory

Today companies must contend with ever-growing security threats to their APIs. A lapse in any area could expose sensitive customer information, ruin a company’s reputation, and ultimately impact revenue. DreamFactory provides a comprehensive catalog of tools to help secure your PostgreSQL API. Our iPaaS (Internet Platform as a Service) offering provides an API platform for creating and managing all your APIs.

If you're ready to learn how DreamFactory helps businesses remain agile by automating the creation of secure APIs, sign up today for a 14-day free trial. 

Related Reading