The goal of this game is to get the passwords for the various user accounts in the PostgreSQL cluster. Retriving a password for a user unlocks the ability to get the password for the next user. Usernames are user1, user2, user3, and so on. As soon as you have the password for the next user, connect as that user and proceed with the next level. You should also continue to work in the last database which was used for the previous level.
It is not required to solve one level after the other, as most of them can be solved individually (but some cannot). As of now, the levels are very beginner friendly but there might be more sophisticated levels in the future. If you have ideas for more levels, please let me know by creating an issue.
This is heavily inspired by https://overthewire.org/wargames/bandit/ which does more or less the same on the operating system level, so all the credits for the idea in general go there. The reason for all this is, to get people trained on basic PostgreSQL stuff. You are supposed to do some research on your own if you face concepts or tools you don't know yet.
The PostgreSQL instance is reachable over the internet on an ipv6 address: 2a01:4f9:c012:24f2::1
The password for user1 is the name of the PostgreSQL logo and the database to connect to is called "db1".
The password for user2 is in a user defined table in the database "db1".
The password for user3 is the error message you'll get when you try to create a new table in schema s1 as user2.
The password for user4 is the combination of relkind, relchecks and relhasindex attributes of the table which contained the password for user2.
The password for user5 is the comment of the only available extension.
The password for user6 is the startup cost of a sequential scan against the the table which contained the password for user2.
The password for user7 is the work_mem setting of user user4.
The password for user8 is the data type of the only column of the table which contained the password for user2 concatenated with the column name.
The password for user9 is the name of the last partition of the only partitioned table in the database.
The password for user10 is the combination of the currently connected user, the current database and the session user. You can get this password with one single sql statement.
The password for user11 is the Locale Provider of the current database.
The password for user12 is 10.325TB in bytes
The password for user13 is the size of an empty row in PostgreSQL in bytes.
The password for user14 is the category description of the "between" keyword.
The password for user15 is the number of characters in the pg_ident.conf file.
The password for user16 can be retrieved by calling a user defined function.
The password for user17 is the descrition of the "LibpqwalreceiverConnect" wait event.
user17 is allowed to connect to database "db2". The password for user18 is the error message PostgreSQL is reporting when user17 is selecting from the materialited view.
The password for user19 is the number of rows in the materialized view.
user19 is allowed to connecto to database "db3". The password for user20 is in the only user defined table.
user20 is allowed to connect to database "db4". The password for user21 is the start value of the only user defined sequence combined with the data type name of that sequence.
The password for user22 is the last row of the only user defined table odered by the only column in that table using the 'brx-IN-x-icu' collation.
user22 is allowed to connect to database "db5". The password for user23 is the number of bytes of the text which is in the only row in the only user defined table.
user23 is allowed to connect to database "db6". The password for user24 can be retrieved as follows:
- run an explain analyze of a select * against the only user defined table asking for values of the second column which match '%22df63d33%'.
- The password for user24 is the operator in the index condition
user24 is allowed to connect to database "db7". The password for user25 can be retrieved as follows:
- add a column with name 'd' of data type "boolean" to the only user defined table
- the password is the message PostgreSQL returns when the column is added
user25 is allowed to connect to database "db8". The password for user26 is the number of files in $PGDATA.
user26 is allowed to connecto to "db8". The password for user27 is the utc offeset of timeszone "Pacific/Pitcairn"
user27 us allowed to connect to "db8". The password for user28 can be retrieved as follows: