Server Fault is a question and answer site for system and network administrators. 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

I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?

share|improve this question
up vote 322 down vote accepted

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password: 

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

  1. By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql.

  2. by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

share|improve this answer
so, how do you lock user postgres back? – ultrajohn Jul 4 '12 at 4:30
@ultrajohn - depends on distribution you use, but you can edit /etc/passwd and put * instead of the password – lzap Sep 12 '12 at 9:50
/etc/shadow is where the password is kept @lzap. – Gringo Suave Sep 20 '12 at 18:56
Right, you can either set /sbin/nologin in /etc/passwd or put * instead of the password in /etc/shadow. – lzap Sep 21 '12 at 9:37
passwd --lock postgres – Stéphane Jul 25 '13 at 18:04

Enter on the command line:

$ sudo -u postgres psql postgres
postgres=# \password postgres

You'll see:

Enter new password: 
Enter it again:
share|improve this answer
This is what's needed to use a tool like pgadminIII (when setting up a server profile) immediately after Postgres itself is installed. Thanks! – limist Jul 10 '11 at 16:55

You manipulate postgres through the user postgres, as so:

# su - postgres
$ createdb mydb
$ psql -s mydb
# create user someuser password 'somepassword';
share|improve this answer
When I su to postgres, it's asking for a password. I don't recall inputting one before. Is there a way to reset that password for postgres? – Thierry Lam Feb 6 '10 at 5:15
@ThierryLam You must be root to su to the postgres user without entering a password. On most systems the Postgres Unix account is locked (no password will work), which means only root may su to that account. – voretaq7 Nov 7 '13 at 22:59
Use sudo instead of su. – reinierpost Aug 8 '14 at 13:30
I had to run sudo su - postgres :\ – Jim Schubert Oct 8 '14 at 14:08
@Jim Schubert: you can also run sudo -u postgres. – reinierpost Nov 24 '14 at 9:28

In Windows, do the following (IMPORTANT: Use a Windows administrator account):

  1. After installation, open <PostgreSQL PATH>\data\pg_hba.conf.

  2. Modify these two lines, and change "md5" to "trust":

    host all all md5

    host all all ::1/128 md5

  3. Restart the PostgreSQL service (might not be necessary).

  4. (Optional) Open a command prompt, and change code page to 1252:

    cmd.exe /c chcp 1252

  5. Log in to PostgreSQL. Non password will be needed (notice the uppercase -U parameter):

    psql -U postgres

  6. (Optional, recommended for security reasons) Change the postgres user's password:

    \password postgres

    and change "trust" back to "md5" in pg_hba.conf.

share|improve this answer

Probably very relevant - "I'm installing PostgreSQL and don't know the password for the postgres user".

share|improve this answer
Welcome to Server Fault! We really do prefer that answers have content, not pointers to content. This may theoretically answer the question however, it would be preferable to include the essential parts of the answer here, and provide the link for reference. This post will be removed if not improved. Thank you! – Chris S Nov 18 '14 at 20:23
Happy 5th anniversary! – PandaLion98 Jun 14 '15 at 13:10

If you are trying to access the PostgreSQL shell, you can type:

psql -U postgres my_database

Where my_database is your database name.

share|improve this answer

protected by Chris S Nov 18 '14 at 20:24

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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