How to connect/take over a Postgresql instance without any login?

Question

I have inherited a Postgresql server but have no records of the admin credentials or any user account.

I do have local admin access to the Windows VM that hosts this Postgresql server.

How can I gain admin access to login to the instance and db? What are my options to get control of this Postgresql server?

Answer 1

1. First let's guess that there is a superuser called postgres.

2. Find the port number of the PostgreSQL server: locate the file postgresql.conf (perhaps in C:\Program Files\PostgreSQL14円\data), edit it with a text editor and find the line that has port = (it may be commented out or not). Remember the number (I will assume it is 5432).

3. Then let's remove the requirement for a password:

   • locate the pg_hba.conf file in the data directory (perhaps in C:\Program Files\PostgreSQL14円\data, but who knows)

   • edit the file with a text editor and add this line at the beginning:

     host all all 127.0.0.1/32 trust
     

   • restart the PostgreSQL server (if you don't know how to restart a service, reboot the machine — after all, it is Windows)

Now you should be able to connect without a password. Locate psql.exe, start cmd.exe and run (substituting the correct path):

"C:\Program Files\PostgreSQL14円\bin\psql" -h 127.0.0.1 -p 5432 -U postgres -d postgres

If we guessed the username correctly, you are in now. If not, we have to start PostgreSQL in single-user mode:

1. Stop the PostgreSQL service in services.msc

2. Locate postgres.exe and the data directory (it contains pg_hba.conf and postgresql.conf), start cmd.exe and start the server with

   "C:\Program Files\PostgreSQL14円\bin\postgres" --single -D "C:\Program Files\PostgreSQL14円\data" postgres
   

   That should start the server and gibe you a prompt.

3. At the prompt, enter

   SELECT rolname FROM pg_authid WHERE rolsuper
   

   which will give you the name of the superusers.

4. Send an end-of-file to stop the server (not sure how to do that in Windows; probably Ctrl+Z)

5. Start the PostgreSQL service again and connect as above, but using the correct username with -U.

There are some corner cases that these instructions don't cover (like somebody dropped the postgres database), but the answer is long enough as it is; no need to go into forensics.