07 Apr, 2023

PostGres Structured Query Language (PostgreSQL)

Penetration Testing as a service (PTaaS)

Tests security measures and simulates attacks to identify weaknesses.

PostgreSQL is an open-source relational database management system that uses a client-server model. It uses the SQL language to manage and query data and can be accessed through various protocols, including TCP/IP and Unix domain sockets. The protocol supports various data types and features, including ACID-compliant transactions, triggers, and stored procedures. The PostgreSQL protocol uses a message-based protocol over TCP/IP, with messages being sent back and forth between the client and server to handle database operations.

PGSQL common ports

Clients typically use this port to connect to a PostgreSQL server and perform operations such as creating, modifying, and querying databases. In addition to TCP port 5432, PostgreSQL also uses other ports for specific purposes, such as:

TCP port 5433: used for secure (SSL-encrypted) connections to a PostgreSQL server

TCP port 5434: used for a second instance of a PostgreSQL server running on the same host

TCP port 5435: used for a third instance of a PostgreSQL server running on the same host

Standard  commands from unauthorised:

psql -U my_login -h 10.0.0.101 postgres

Tools for using protocol PGSQL

Manual Testing Tools:

  • pgAdmin 4 – pgAdmin 4 is a free and open-source administration and management tool for the PostgreSQL database. It provides a graphical user interface for managing databases, tables, and other objects, and allows users to perform tasks like creating queries, generating reports, and monitoring performance.

  • psql – psql is a command-line tool that is included with PostgreSQL. It allows users to interact with the database directly from the command line, and can be used to perform tasks like querying data, creating tables, and managing users.

  • DBeaver – DBeaver is a free and open-source database management tool that supports multiple databases, including PostgreSQL. It provides a graphical user interface for managing the database, and includes features like SQL editing, schema browsing, and data visualization.

  • Navicat for PostgreSQL – Navicat is a commercial database management tool that provides a graphical user interface for managing PostgreSQL databases. It includes features like data modeling, query building, and data synchronization.

  • pgcli – pgcli is a command-line interface for PostgreSQL that provides auto-completion, syntax highlighting, and other features to make interacting with the database more efficient.

  • HeidiSQL – HeidiSQL is a free and open-source database management tool that supports multiple databases, including PostgreSQL. It provides a graphical user interface for managing the database, and includes features like SQL editing, schema browsing, and data visualization.

  • OmniDB – OmniDB is a web-based database management tool that provides a graphical user interface for managing PostgreSQL databases. It includes features like SQL editing, schema browsing, and data visualization.

  • pgweb – pgweb is a web-based administration tool for PostgreSQL that provides a graphical user interface for managing databases, tables, and other objects. It includes features like SQL editing, data visualization, and user management.

  • SQL Workbench/J – SQL Workbench/J is a free and open-source database management tool that supports multiple databases, including PostgreSQL. It provides a graphical user interface for managing the database, and includes features like SQL editing, schema browsing, and data visualization.

  • pgBadger – pgBadger is a log analyzer for PostgreSQL that helps users identify performance bottlenecks and other issues in the database. It provides graphical reports and charts based on the database logs.

Automated Testing Tools:

  • pgTAP – pgTAP is a testing framework for PostgreSQL that provides a set of functions and macros for writing tests in SQL. It allows users to write tests for database schemas, functions, and other objects.

  • pg_regress – pg_regress is a regression testing tool for PostgreSQL that allows users to write and run tests for the database. It includes features like parallel testing, output formatting, and failure reporting.

  • Pgtcl-ng – Pgtcl-ng is a Tcl/Tk extension for PostgreSQL that provides a testing framework for writing automated tests in Tcl. It includes features like test fixture management, result reporting, and test automation.

  • pgTapTest – pgTapTest is a web-based testing framework for PostgreSQL that provides a graphical user interface for writing and running tests. It includes features like test case management, result reporting, and test automation.

  • pg_prove – pg_prove is a command-line tool for running automated tests on PostgreSQL databases. It allows users to write tests in SQL or in a custom format, and includes features like test result reporting and test suite management.

  • pgTAPish – pgTAPish is a Python-based testing framework for PostgreSQL that provides a set of functions and classes for writing tests. It includes features like test fixture management, result reporting, and test automation.

  • pg_qualstats – pg_qualstats is a tool for analyzing queries in PostgreSQL to identify performance bottlenecks and other issues. It provides statistics on query execution times, query plans, and other metrics.

  • pgbench – pgbench is a benchmarking tool for PostgreSQL that allows users to test the performance of the database under different workloads. It includes features like transaction rate measurement and result reporting.

  • pg_stat_statements – pg_stat_statements is a tool for analyzing query statistics in PostgreSQL to identify performance issues. It provides statistics on query execution times, query plans, and other metrics.

  • pgAudit – pgAudit is a tool for auditing PostgreSQL databases to identify security issues and other concerns. It provides a detailed log of all database activity, including queries, transactions, and user actions.

Recon or Non Standard command

Information schema queries: These allow you to query the metadata of the database to gather information about tables, columns, indexes, and other objects.

pg_catalog queries: This catalog contains information about the internal structure of the database and can be queried to gather information about system tables and indexes.

SQL injection attacks: If a vulnerable application is using PostgreSQL as its backend, SQL injection attacks can be used to gather information or perform unauthorized operations on the database.

Non-standard extensions: PostgreSQL supports various extensions that can provide additional functionality, such as geographic information system (GIS) support. These extensions may require non-standard commands to be used to interact with them.

Last five known CVE for IKE

 CVE-2021-29625: Adminer is open-source database management software. A cross-site scripting vulnerability in Adminer versions 4.6.1 to 4.8.0 affects users of MySQL, MariaDB, PgSQL and SQLite. XSS is in most cases prevented by strict CSP in all modern browsers. The only exception is when Adminer is using a `pdo_` extension to communicate with the database (it is used if the native extensions are not enabled). In browsers without CSP, Adminer versions 4.6.1 to 4.8.0 are affected. The vulnerability is patched in version 4.8.1. As workarounds, one can use a browser supporting strict CSP or enable the native PHP extensions (e.g. `mysqli`) or disable displaying PHP errors (`display_errors).

 CVE-2020-25269: An issue was discovered in InspIRCd 2 before 2.0.29 and 3 before 3.6.0. The pgsql module contains a use after free vulnerability. When combined with the sqlauth or sqloper modules, this vulnerability can be used for remote crashing of an InspIRCd server by any user able to connect to a server.

 CVE-2015-7766: PGSQL:SubmitQuery.do in ZOHO ManageEngine OpManager 11.6, 11.5, and earlier allows remote administrators to bypass SQL query restrictions via a comment in the query to api/json/admin/SubmitQuery, as demonstrated by “INSERT/**/INTO.”

 CVE-2015-4644: The php_pgsql_meta_data function in pgsql.c in the PostgreSQL (aka pgsql) extension in PHP before 5.4.42, 5.5.x before 5.5.26, and 5.6.x before 5.6.10 does not validate token extraction for table names, which might allow remote attackers to cause a denial of service (NULL pointer dereference and application crash) via a crafted name. NOTE: this vulnerability exists because of an incomplete fix for CVE-2015-1352. 

 CVE-2015-1352: The build_tablename function in pgsql.c in the PostgreSQL (aka pgsql) extension in PHP through 5.6.7 does not validate token extraction for table names, which allows remote attackers to cause a denial of service (NULL pointer dereference and application crash) via a crafted name. 

Connection with enumeration

To connect and perform enumeration on a PostgreSQL database, you can use the psql command-line tool. Here’s a quick overview of the process:

  1. Open a terminal window and type psql -h <hostname> -U <username> to connect to the PostgreSQL database using the specified hostname and username. You may be prompted for a password, depending on the authentication method used.

  2. Once connected, you can use various commands to enumerate the database, such as \dt to list all tables, \du to list all users, \dv to list all views, and \df to list all functions.

  3. You can also use SQL queries to retrieve specific information from the database. For example, SELECT * FROM pg_catalog.pg_user will retrieve a list of all users in the database.

  4. It’s important to only use enumeration techniques in an ethical and authorized manner, and to follow security best practices to protect the database from unauthorized access or modification.

Bruteforce connection:

nmap -p 5432 –script pgsql-brute <host>

Useful information

– Supports a wide range of data types, including integer, numeric, boolean, character, text, date/time, timestamp, and many others.

– Supports SQL (Structured Query Language) and allows users to execute complex queries against their data.

– Supports both synchronous and asynchronous replication, allowing users to replicate data to multiple nodes for increased availability and scalability.

– Rich set of features for data integrity and consistency, including foreign key constraints, triggers, and transaction support.

– Supports full-text search, enabling users to search for text-based data in their database.

– Large and active community of developers and users, providing extensive documentation, forums, and other resources.

– Available on various platforms, including Windows, Linux, macOS, and Unix-like systems.

– Open-source and free to use, making it an attractive choice for organizations looking to reduce costs.

Known banners

“Welcome to PostgreSQL” – This is the default banner displayed by PostgreSQL when connecting to a server using the psql command-line tool.

“PostgreSQL server” – This banner is often used by PostgreSQL servers to identify themselves to clients connecting to them.

“PostgreSQL” – This is a simple banner used by some PostgreSQL servers to identify themselves.

“Welcome to psql” – This banner is displayed by the psql command-line tool when connecting to a PostgreSQL server.

“pgAdmin” – This is a banner displayed by the pgAdmin graphical user interface tool for managing PostgreSQL databases.

“psycopg2” – This is a banner displayed by the psycopg2 library for Python, which is used to connect to and interact with PostgreSQL databases.

“JDBC” – This is a banner displayed by the PostgreSQL JDBC driver for Java, which is used to connect to and interact with PostgreSQL databases.

“ODBC” – This is a banner displayed by the PostgreSQL ODBC driver, which is used to connect to and interact with PostgreSQL databases using the Open Database Connectivity (ODBC) standard.

Books for studies the PostGres Structured Query Language (PGSQL) protocol

PostgreSQL: Up and Running by Regina Obe and Leo Hsu – This book provides an introduction to PostgreSQL, covering topics such as installation, configuration, and basic SQL commands.

Practical PostgreSQL by John Worsley and Joshua D. Drake – This book provides a comprehensive guide to PostgreSQL, covering topics such as database design, administration, and advanced SQL.

PostgreSQL High Performance Cookbook by Chitij Chauhan and Dinesh Kumar – This book provides practical solutions for improving the performance of PostgreSQL databases.

PostgreSQL 9.0 High Performance by Gregory Smith – This book provides a detailed guide to optimizing the performance of PostgreSQL databases, covering topics such as query tuning, indexing, and replication.

PostgreSQL Administration Essentials by Hans-Jürgen Schönig – This book provides a comprehensive guide to administering PostgreSQL databases, covering topics such as backup and recovery, monitoring, and security.

PostgreSQL 9 Administration Cookbook by Simon Riggs and Hannu Krosing – This book provides practical solutions for administering PostgreSQL databases, covering topics such as replication, backup and recovery, and performance tuning.

PostgreSQL Developer’s Handbook by Ewald Geschwinde and Hans-Jürgen Schönig – This book provides a comprehensive guide to developing applications with PostgreSQL, covering topics such as data modeling, SQL programming, and server-side programming.

Mastering PostgreSQL 11 by Hans-Jürgen Schönig – This book provides an in-depth guide to PostgreSQL 11, covering topics such as installation and configuration, SQL programming, and database administration.

PostgreSQL 11 Administration Cookbook by Simon Riggs and Gianni Ciolli – This book provides practical solutions for administering PostgreSQL 11 databases, covering topics such as replication, backup and recovery, and performance tuning.

PostgreSQL 13 Administration Cookbook by Simon Riggs and Gianni Ciolli – This book provides practical solutions for administering PostgreSQL 13 databases, covering topics such as backup and recovery, performance tuning, and security.

List of Payload for the PostGres Structured Query Language (PGSQL) protocol

  • Startup message: The initial message sent from the client to the server containing connection parameters, such as user name, database name, client encoding, etc.

  • Authentication message: Sent from the server to the client to authenticate the user.

  • Password message: Sent from the client to the server containing the user’s password.

  • SQL statement messages: These messages include:

Query message: Used to send a single SQL statement to the server for execution.

Batch message: Used to send multiple SQL statements to the server for execution in a single batch.

Describe message: Used to request information about a query result, such as column names and data types.

Bind message: Used to associate parameter values with placeholders in a prepared statement.

Execute message: Used to execute a previously prepared statement with bound parameter values.

Parse message: Used to parse a SQL statement into an internal representation.

Sync message: Used to synchronize the client and server state.

  • Data messages: These messages include:

Row description message: Sent by the server to describe the columns in a query result.

Data row message: Sent by the server to send a single row of data in a query result.

Command complete message: Sent by the server to indicate the completion of a SQL command, along with any relevant statistics.

Ready for query message: Sent by the server to indicate that it’s ready to receive the next SQL command.

  • Termination message: Sent by the client or server to terminate the connection.

Mitigation

  1. Ensure that the latest security patches and updates are installed for PostgreSQL to fix any known vulnerabilities.

  2. Use SSL/TLS encryption for PostgreSQL connections to ensure that data in transit is secure.

  3. Use strong passwords, multi-factor authentication, and restrict access to the database to authorized personnel only.

  4. Assign the minimum required privileges to users and roles to limit the damage that can be caused if an account is compromised.

  5. Regularly review PostgreSQL logs for unusual activity or unauthorized access attempts.

  6. Use firewalls to restrict access to PostgreSQL servers from external networks.

  7. Regularly backup PostgreSQL databases to ensure that data can be recovered in case of a security incident or system failure.

  8. Implement intrusion detection/prevention systems to monitor and detect potential security threats in real-time.

Conclusion

PostgreSQL is a powerful and widely used open-source database management system that provides a range of advanced features for managing data. However, as with any complex software system, it is important to be aware of the potential security risks and vulnerabilities that can arise. By implementing a range of security measures, such as using secure authentication, SSL/TLS encryption, regular updates, and intrusion detection/prevention systems, network administrators can help mitigate these risks and ensure the confidentiality, integrity, and availability of their data. Overall, PostgreSQL remains a highly regarded and trusted database system for a wide range of applications and industries.

Other Services

Ready to secure?

Let's get in touch