# PostgreSQL

Some of the queries in the table below can only be run by an admin. These are marked with **(PRIV)** at the description.

### Version

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

### Comments

```sql
SELECT 1; --comment
SELECT /*comment*/1;
```

### Current User

```sql
SELECT user;
SELECT current_user;
SELECT session_user;
SELECT getpgusername();
```

### List Users

```sql
SELECT usename FROM pg_user;
```

### List Password Hashes (PRIV)

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

### List Privileges

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

### List DBA Accounts

```sql
SELECT usename FROM pg_user WHERE usesuper IS TRUE;
```

### Check if Current User is Superuser

```sql
SELECT current_setting('is_superuser')='on';
```

### Current Database

```sql
SELECT current_database();
```

### List Databases

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

### List Tables

```sql
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

```sql
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');
```

### Find Tables from Column Name

```sql
#If you want to list all the table names that contain a column LIKE '%password%':
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%';
```

### Hostname, IP Address

```sql
#Returns db server IP address (or null if using local connection) 
SELECT inet_server_addr();

#Returns db server port
SELECT inet_server_port();
```

### Create Users (PRIV)

```sql
CREATE USER test1 PASSWORD 'pass1';

#Grant some privs at the same time
CREATE USER test1 PASSWORD 'pass1' CREATEUSER;
```

### Delete Users (PRIV)

```sql
DROP USER test1;
```

### Make User DBA (PRIV)

```sql
ALTER USER test1 CREATEUSER CREATEDB;
```

### Location of DB Files (PRIV)

```sql
SELECT current_setting('data_directory');
SELECT current_setting('hba_file');
```

### Read Files (PRIV)

```sql
COPY passwords from $$c:\passwords.txt$$;
SELECT content from passwords;
```

### Write Files (PRIV)

```sql
CREATE temp table passwords (content text);
COPY (SELECT $$passwords$$) to $$c:\passwords.txt$$;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.pwny.cc/web-attacks/sql-injection-sqli/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.