Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

7 Answers 7

up vote -2 down vote accepted

Please see below; this answer may potentially introduce serious security issues.


I'm not a postgres user (I use the other one) but to reset the (Ubuntu 9.10) password for postgres, how about

$ sudo passwd postgres

and give it a new password.

share|improve this answer
18  
It is not the right way. user postgres is authenticated by "indent", so he does not need a password at all. sudo -u postgres psql and you are good to go. –  Tyler Long Oct 8 '12 at 3:22
13  
This answer is dangerous and should be removed, or at least have its acceptance as "best answer" revoked in favour of user716468's answer below. –  Joe Carroll Mar 30 '13 at 11:00
    
just saw the green check and tried it. it worked, now i feel bad, since i saw the answer below. how can i roll back the above command? –  Milla Well Nov 4 '13 at 12:07
1  
@MillaWell I did the same. Fortunately just starting out so yum remove postgresql*; userdel postgres; yum install postgresql* Alternatively you can set a very strong passwd for the postgres user. Also see the comments on the answer below. –  stormdrain Nov 7 '13 at 20:29
2  
if it cannot be removed, i request users to downvote this atleast!! –  shivshnkr Nov 28 '13 at 17:54

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 PostgreSQL:

  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
4  
so, how do you lock user postgres back? –  ultrajohn Jul 4 '12 at 4:30
3  
@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
2  
/etc/shadow is where the password is kept @lzap. –  Gringo Suave Sep 20 '12 at 18:56
1  
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
10  
passwd --lock postgres –  Stéphane Jul 25 '13 at 18:04

Enter on the command line:

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

You'll see:

Enter new password: 
share|improve this answer
2  
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';
# GRANT ALL PRIVILEGES ON DATABASE mydb TO someuser;
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
1  
@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

For windows users, type

net user accountname newpassword

to change your password

share|improve this answer

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

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.