07 Apr, 2023

Post-Ingres (POSTGRES)

Penetration Testing as a service (PTaaS)

Tests security measures and simulates attacks to identify weaknesses.

POSTGRES stands for “Post-Ingres” and is a relational database management system (RDBMS) that was initially developed at the University of California, Berkeley in the 1980s as a successor to the Ingres database. It is an open-source system and is known for its extensibility, robustness, and ability to handle complex queries and large volumes of data. POSTGRES has since evolved into several different open-source database management systems, including PostgreSQL, which is one of the most popular and widely used RDBMSs in the world today.

POSTGRES common ports

5432/TCP: This is the default port used by PostgreSQL to listen for client connections. It is the most commonly used port for PostgreSQL and is used for both local and remote connections.

5433/TCP: This is another TCP port that can be used by PostgreSQL to listen for client connections. It is less commonly used than port 5432 and is typically used as an alternative port when the default port is already in use.

5432/UDP: This is the UDP port used by PostgreSQL to broadcast information about the database cluster. It is used to discover other nodes in the cluster and exchange information about the status of the nodes.

Standard commands from unauthorized users

SELECT: This command is used to retrieve data from tables in the database. If an unauthorized user executes this command, they may be able to retrieve sensitive information from the database.

INSERT: This command is used to insert new data into tables in the database. If an unauthorized user executes this command, they may be able to insert malicious data into the database, which could compromise the integrity of the data.

UPDATE: This command is used to update existing data in tables in the database. If an unauthorized user executes this command, they may be able to modify sensitive data in the database.

DELETE: This command is used to delete data from tables in the database. If an unauthorized user executes this command, they may be able to delete important data from the database.

Tools for using protocol POSTGRES

Manual Tools:

  • pgAdmin: is a popular open-source administration and management tool for PostgreSQL. It provides a graphical user interface for managing and administering PostgreSQL databases.

  • psql: is the command-line tool for interacting with PostgreSQL databases. It allows users to execute SQL commands, create and modify tables, and manage database users and permissions.

  • Navicat: is a commercial database management tool that supports PostgreSQL, as well as other relational databases. It provides a graphical user interface for managing and editing PostgreSQL databases.

  • DBeaver: is a free and open-source database management tool that supports PostgreSQL. It provides a graphical user interface for managing and editing PostgreSQL databases, as well as other relational databases.

  • DataGrip: is a commercial database management tool that supports PostgreSQL, as well as other relational databases. It provides a graphical user interface for managing and editing PostgreSQL databases.

  • OmniDB: is a free and open-source web-based database management tool that supports PostgreSQL. It provides a graphical user interface for managing and editing PostgreSQL databases.

  • HeidiSQL: is a free and open-source database management tool that supports PostgreSQL, as well as other relational databases. It provides a graphical user interface for managing and editing PostgreSQL databases.

  • SQL Workbench/J: is a free and open-source database management tool that supports PostgreSQL, as well as other relational databases. It provides a graphical user interface for managing and editing PostgreSQL databases.

  • PostgREST: is a free and open-source web server that automatically generates a RESTful API from a PostgreSQL database schema. It allows users to query the database using HTTP requests.

  • pgBadger: is a free and open-source PostgreSQL log analyzer that generates reports on database performance and activity. It provides insights into slow queries, errors, and other performance issues.

Automated Tools:

  • pgTAP: is a free and open-source unit testing framework for PostgreSQL. It allows users to write automated tests for SQL queries and functions.

  • pg_regress: is a PostgreSQL regression testing tool that allows users to run automated tests on their database schema and functions.

  • pg_prove: is a command-line tool for running automated tests on a PostgreSQL database. It supports the Test Anything Protocol (TAP) for test reporting.

  • pgbench: is a benchmarking tool for PostgreSQL that allows users to simulate database workloads and measure performance metrics.

  • HammerDB: is a free and open-source database benchmarking tool that supports PostgreSQL, as well as other relational databases. It allows users to simulate database workloads and measure performance metrics.

  • Apache JMeter: is a free and open-source load testing tool that supports PostgreSQL, as well as other databases. It allows users to simulate high loads on a database and measure performance metrics.

  • Loader.io: is a cloud-based load testing tool that supports PostgreSQL, as well as other databases. It allows users to simulate high loads on a database and measure performance metrics.

  • Tsung: is a free and open-source distributed load testing tool that supports PostgreSQL, as well as other databases. It allows users to simulate high loads on a database and measure performance metrics.

  • JDBC Load Tester: is a commercial load testing tool for PostgreSQL, as well as other databases. It allows users to simulate high loads on a database and measure performance metrics.

  • SQL Power Injector: is a free and open-source SQL injection testing tool that supports PostgreSQL, as well as other databases. It allows users to test databases for vulnerabilities and security issues.

Browser plugins:

  • Postman: is a free and open-source API testing tool that can be used to test HTTP requests to a PostgreSQL RESTful API.

  • RESTClient: is a free and open-source Firefox plugin that can be used to test HTTP requests to a PostgreSQL RESTful API.

  • Advanced REST Client: is a free and open-source Chrome plugin that can be used to test HTTP requests to a PostgreSQL RESTful API.

Last five known CVE for POSTGRES

CVE-2022-46792 – Hasura GraphQL Engine before 2.15.2 mishandles row-level authorization in the Update Many API for Postgres backends. The fixed versions are 2.10.2, 2.11.3, 2.12.1, 2.13.2, 2.14.1, and 2.15.2. (Versions before 2.10.0 are unaffected.)

CVE-2022-3971 – A vulnerability was found in matrix-appservice-irc up to 0.35.1. It has been declared as critical. This vulnerability affects unknown code of the file src/datastore/postgres/PgDataStore.ts. The manipulation of the argument roomIds leads to sql injection. Upgrading to version 0.36.0 is able to address this issue. The name of the patch is 179313a37f06b298150edba3e2b0e5a73c1415e7. It is recommended to upgrade the affected component. VDB-213550 is the identifier assigned to this vulnerability.

CVE-2022-35942 – Improper input validation on the `contains` LoopBack filter may allow for arbitrary SQL injection. When the extended filter property `contains` is permitted to be interpreted by the Postgres connector, it is possible to inject arbitrary SQL which may affect the confidentiality and integrity of data stored on the connected database. A patch was released in version 5.5.1. This affects users who does any of the following: – Connect to the database via the DataSource with `allowExtendedProperties: true` setting OR – Uses the connector’s CRUD methods directly OR – Uses the connector’s other methods to interpret the LoopBack filter. Users who are unable to upgrade should do the following if applicable: – Remove `allowExtendedProperties: true` DataSource setting – Add `allowExtendedProperties: false` DataSource setting – When passing directly to the connector functions, manually sanitize the user input for the `contains` LoopBack filter beforehand.

CVE-2022-34434 – Cloud Mobility for Dell Storage versions 1.3.0 and earlier contains an Improper Access Control vulnerability within the Postgres database. A threat actor with root level access to either the vApp or containerized versions of Cloud Mobility may potentially exploit this vulnerability, leading to the modification or deletion of tables that are required for many of the core functionalities of Cloud Mobility. Exploitation may lead to the compromise of integrity and availability of the normal functionality of the Cloud Mobility application.

CVE-2022-31625 – In PHP versions 7.4.x below 7.4.30, 8.0.x below 8.0.20, and 8.1.x below 8.1.7, when using Postgres database extension, supplying invalid parameters to the parametrized query may lead to PHP attempting to free memory using uninitialized data as pointers. This could lead to RCE vulnerability or denial of service.

Useful information

– PostgreSQL is an open-source relational database management system (RDBMS) that runs on all major operating systems and is ACID-compliant since 2001.

– PostgreSQL is a general-purpose OLTP database that is well-suited for managing protocols like e-commerce, CRMs, and financial ledgers. Its SQL compliance and query optimizer also make it useful for general-purpose analytics on data. 

– PostgreSQL has powerful add-ons like the popular PostGIS geospatial database extender. 

– There are many websites available that offer learning resources for PostgreSQL, including PGTune, an online tool that helps generate configurations for PostgreSQL based on maximum performance for a given hardware configuration. 

– PostgreSQL was originally called POSTGRES and aimed to add the fewest features needed to support multiple data types. In 1996, it was renamed PostgreSQL to illustrate its support for SQL querying language. PostgreSQL is still commonly abbreviated as Postgres.

– PostgreSQL is supported by an extensive market for Software-as-a-Service (SaaS) solutions used to run Postgres in the cloud and on-premises.

Known banners

PostgreSQL 11.9 on x86_64-apple-darwin19.4.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit

PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0

PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

PostgreSQL 10.15, compiled by Visual C++ build 1914, 64-bit

PostgreSQL 9.6.22 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0, 64-bit

Books for studies POSTGRES

PostgreSQL: Up and Running, 3rd Edition by Regina O. Obe and Leo S. Hsu – This book is a practical guide to PostgreSQL that covers everything from installation and configuration to advanced topics like replication and partitioning. It includes real-world examples and tips for optimizing performance. The authors have received positive reviews for their clear writing style and deep knowledge of PostgreSQL.

Mastering PostgreSQL 12 by Hans-Jürgen Schönig – This book is a comprehensive guide to PostgreSQL that covers topics like data modeling, query optimization, and security. It is aimed at intermediate to advanced users and includes practical examples and exercises. The author is a well-known PostgreSQL expert and has received positive reviews for his clear explanations and helpful examples.

PostgreSQL: Introduction and Concepts by Bruce Momjian – This book is an in-depth introduction to PostgreSQL that covers topics like SQL, data types, and transaction management. It is aimed at both beginners and experienced users and includes practical examples and exercises. The author is a core contributor to PostgreSQL and has received positive reviews for his clear explanations and insights into the inner workings of the database.

Practical PostgreSQL by John C. Worsley and Joshua D. Drake – This book is a practical guide to PostgreSQL that covers topics like data modeling, SQL, and administration. It includes real-world examples and tips for optimizing performance. The authors have received positive reviews for their clear writing style and practical advice.

PostgreSQL 11 Administration Cookbook, 2nd Edition by Simon Riggs and Gianni Ciolli – This book is a collection of recipes for PostgreSQL administration tasks like backup and recovery, replication, and performance tuning. It includes practical examples and tips for solving common problems. The authors have received positive reviews for their clear explanations and helpful examples.

List of Payload for POSTGRES

  • A JSON object containing the data to be inserted or updated in the database.

  • A string of comma-separated values (CSV) representing the data to be inserted or updated in the database.

  • A SQL statement that performs a specific action on the database, such as an INSERT, UPDATE, or DELETE operation.

  • A GraphQL query that specifies the data to be retrieved from the database.

  • A binary payload, such as an image or file, to be stored in the database.

  • A message payload in a custom format, depending on the specific requirements of the application using the database.

Mitigation

  1. PostgreSQL regularly releases new versions that include bug fixes, security patches, and performance enhancements. Keeping your PostgreSQL installation up-to-date can help mitigate known vulnerabilities.

  2. PostgreSQL allows you to set up granular access control using roles and permissions. By assigning roles to users and granting them appropriate permissions, you can limit access to sensitive data and prevent unauthorized modifications.

  3. PostgreSQL supports SSL/TLS encryption, which encrypts data in transit between the client and the server. Enabling SSL/TLS can help mitigate the risk of eavesdropping and man-in-the-middle attacks.

  4. Parameterized queries help prevent SQL injection attacks by separating the SQL code from user-supplied data. This can help mitigate the risk of malicious code execution and data theft.

  5. Monitoring your PostgreSQL logs and database activity can help you detect suspicious behavior and potential attacks. You can use tools like PostgreSQL Audit to monitor database activity and log files for suspicious events.

Conclusion

PostgreSQL is a powerful and versatile open-source relational database management system that offers a wide range of features and capabilities. It is known for its reliability, scalability, and robustness, and is widely used by developers, data analysts, and businesses of all sizes. PostgreSQL supports a variety of programming languages, including SQL, Python, and Java, and can be used in a variety of applications, from web development to analytics and data warehousing.

Other Services

Ready to secure?

Let's get in touch