Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Good day.

I have a PostgreSQL server.

Once i had admin login and password, then i created a new login for web application (with low permissions like select form some tables etc). And i just lost my admin login and password.

I have access (administator) to host where the server is running. Server is windows 2008 server. Is there any way to recover admin permissions? May be i somehow could create another superuser?

share|improve this question

migrated from stackoverflow.com May 27 '13 at 7:35

This question came from our site for professional and enthusiast programmers.

    
google.com/… ? – Craig Ringer May 27 '13 at 6:02

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.

share|improve this answer
    
A pg_ctl reload is sufficient, but it's sufficiently hard to do that on Windows that restarting the service via the Services control panel (services.msc) is going to be easier. – Craig Ringer May 27 '13 at 5:59
1  
Funny... if your answer is migrated I can upvote it twice, once on each site. – Craig Ringer May 28 '13 at 1:48
    
haha! thank you! – mvp May 28 '13 at 1:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.