> For the complete documentation index, see [llms.txt](https://cajac.gitbook.io/ctf-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cajac.gitbook.io/ctf-notes/misc/databases/sql-databases/postgresql.md). # PostgreSQL **PostgreSQL** also known as **Postgres**, is a [free and open-source](https://en.wikipedia.org/wiki/Free_and_open-source_software) [relational database management system](https://en.wikipedia.org/wiki/Relational_database_management_system) (RDBMS) emphasizing [extensibility](https://en.wikipedia.org/wiki/Extensibility) and [SQL](https://en.wikipedia.org/wiki/SQL) compliance. PostgreSQL features [transactions](https://en.wikipedia.org/wiki/Transaction_processing) with [atomicity](https://en.wikipedia.org/wiki/Atomicity_\(database_systems\)), [consistency](https://en.wikipedia.org/wiki/Consistency_\(database_systems\)), [isolation](https://en.wikipedia.org/wiki/Isolation_\(database_systems\)), [durability](https://en.wikipedia.org/wiki/Durability_\(database_systems\)) ([ACID](https://en.wikipedia.org/wiki/ACID)) properties, automatically updatable [views](https://en.wikipedia.org/wiki/View_\(SQL\)), [materialized views](https://en.wikipedia.org/wiki/Materialized_view), [triggers](https://en.wikipedia.org/wiki/Database_trigger), [foreign keys](https://en.wikipedia.org/wiki/Foreign_key), and [stored procedures](https://en.wikipedia.org/wiki/Stored_procedure). It is supported on all major [operating systems](https://en.wikipedia.org/wiki/Operating_system), including [Windows](https://en.wikipedia.org/wiki/Microsoft_Windows), [Linux](https://en.wikipedia.org/wiki/Linux), [macOS](https://en.wikipedia.org/wiki/MacOS), [FreeBSD](https://en.wikipedia.org/wiki/FreeBSD), and [OpenBSD](https://en.wikipedia.org/wiki/OpenBSD), and handles a range of workloads from single machines to [data warehouses](https://en.wikipedia.org/wiki/Data_warehouse), [data lakes](https://en.wikipedia.org/wiki/Data_lake), or [web services](https://en.wikipedia.org/wiki/Web_service) with many [concurrent users](https://en.wikipedia.org/wiki/Concurrent_user). The default port is 5432/TCP. ## Psql client ### Connect to the database To connect to the database on a non-standard port ```bash psql -h localhost -p 1234 -U christine ``` ### List databases To list available databases, use `\list` or `\l` ``` christine-# \list List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges -----------+-----------+----------+-----------------+------------+------------+--------+-----------+------------------------- christine | christine | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | postgres | christine | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | secrets | christine | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | template0 | christine | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/christine + | | | | | | | | christine=CTc/christine template1 | christine | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/christine + | | | | | | | | christine=CTc/christine (5 rows) ``` ### Connect to a database Then use `\connect` or `\c` to connect/select a database ``` christine-# \connect secrets psql (17.4 (Debian 17.4-1), server 15.1 (Debian 15.1-1.pgdg110+1)) You are now connected to database "secrets" as user "christine". secrets-# ``` ### List tables To list available tables use `\dt` ``` secrets-# \dt List of relations Schema | Name | Type | Owner --------+------+-------+----------- public | flag | table | christine (1 row) ``` ## SQL Queries ### Current database To get the current database ```sql SELECT current_database(); ``` ### Current version To get the current version ```sql select version(); ``` ### Current user To get the current user ```sql select current_user; ``` ### List databases To list available databases ```sql select datname from pg_database; ``` ### List tables To list tables in a specific database ```sql select table_name from .information_schema.tables where table_schema = 'public'; ``` ### List columns To list columns in a specific database and table ```sql select column_name, data_type from .information_schema.columns where table_name = ''; ``` ## Usage information
psql --help ```bash ┌──(kali㉿kali)-[~] └─$ psql --help psql is the PostgreSQL interactive terminal. Usage: psql [OPTION]... [DBNAME [USERNAME]] General options: -c, --command=COMMAND run only single command (SQL or internal) and exit -d, --dbname=DBNAME database name to connect to -f, --file=FILENAME execute commands from file, then exit -l, --list list available databases, then exit -v, --set=, --variable=NAME=VALUE set psql variable NAME to VALUE (e.g., -v ON_ERROR_STOP=1) -V, --version output version information, then exit -X, --no-psqlrc do not read startup file (~/.psqlrc) -1 ("one"), --single-transaction execute as a single transaction (if non-interactive) -?, --help[=options] show this help, then exit --help=commands list backslash commands, then exit --help=variables list special variables, then exit Input and output options: -a, --echo-all echo all input from script -b, --echo-errors echo failed commands -e, --echo-queries echo commands sent to server -E, --echo-hidden display queries that internal commands generate -L, --log-file=FILENAME send session log to file -n, --no-readline disable enhanced command line editing (readline) -o, --output=FILENAME send query results to file (or |pipe) -q, --quiet run quietly (no messages, only query output) -s, --single-step single-step mode (confirm each query) -S, --single-line single-line mode (end of line terminates SQL command) Output format options: -A, --no-align unaligned table output mode --csv CSV (Comma-Separated Values) table output mode -F, --field-separator=STRING field separator for unaligned output (default: "|") -H, --html HTML table output mode -P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command) -R, --record-separator=STRING record separator for unaligned output (default: newline) -t, --tuples-only print rows only -T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border) -x, --expanded turn on expanded table output -z, --field-separator-zero set field separator for unaligned output to zero byte -0, --record-separator-zero set record separator for unaligned output to zero byte Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME database user name -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) For more information, type "\?" (for internal commands) or "\help" (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation. Report bugs to . PostgreSQL home page: ```
## Resources Pentesting Postgresql - HackTricks: PostgreSQL - Hackviser: PostgreSQL - Wikipedia: psql - Linux manual page: