What's the default superuser username/password for postgres after a new install?

Question

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?

Probably very relevant - ["I'm installing PostgreSQL and don't know the password for the postgres user"](http://wiki.postgresql.org/wiki/FAQ#I.27m_installing_PostgreSQL_on_Windows_or_OS_X_and_don.27t_know_the_password_for_the_postgres_user). — Milen A. Radev, Nov 11 '11 at 22:48

user716468 · Accepted Answer · 2017-01-05T01:49:56.110

633

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:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
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.

edited Jan 05 '17 at 01:49

answered Oct 28 '11 at 06:25

user716468

6,454
1
14
5

14

so, how do you lock user postgres back? – ultrajohn Jul 04 '12 at 04:30
4

@ultrajohn - depends on distribution you use, but you can edit /etc/passwd and put * instead of the password – lzap Sep 12 '12 at 09:50
3

`/etc/shadow` is where the password is kept @lzap. – Gringo Suave Sep 20 '12 at 18:56
2

Right, you can either set /sbin/nologin in /etc/passwd or put * instead of the password in /etc/shadow. – lzap Sep 21 '12 at 09:37
38

passwd --lock postgres – Stéphane Jul 25 '13 at 18:04
This is not programmatic. It's not any more "secure" just because you have to manually type in the password... – Cerin Jun 30 '14 at 23:23
if after login to postgres, it don't required password, you can set password by this command: "alter user postgres password 'apassword';" – ghanbari Feb 19 '16 at 10:33
Still the best answer, nigh ten years on. – chb Jun 01 '21 at 16:50
It works with PostgreSQL 15.3-1 on Ubuntu 23.04 . – Vy Do May 17 '23 at 09:40
To lock the password for user postgres, use `passwd -l postgres`. This disables login for that user without changing the actual password. To unlock, use the `-u` option. – Fjor Jun 30 '23 at 01:42

score 194 · Answer 2 · edited Jan 14 '16 at 13:50

194

Enter on the command line:

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

You'll see:

Enter new password: 
Enter it again:

edited Jan 14 '16 at 13:50

vijay

105
4

answered Mar 16 '11 at 15:39

dcrawkstar

2,074
1
12
4

10

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

score 70 · Answer 3 · edited Sep 14 '12 at 07:24

70

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;

edited Sep 14 '12 at 07:24

Rob Bednark

215
1
2
9

answered Feb 06 '10 at 05:02

David Mackintosh

14,293
7
49
78

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 06 '10 at 05:15
5

@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 07 '13 at 22:59
2

Use `sudo` instead of `su`. – reinierpost Aug 08 '14 at 13:30
11

I had to run `sudo su - postgres` :\ – Joseph Yaduvanshi Oct 08 '14 at 14:08
3

@Jim Schubert: you can also run `sudo -u postgres`. – reinierpost Nov 24 '14 at 09:28
2

sudo -u postgres psql - works also so you can do it in one step. – Ominus Nov 20 '17 at 13:35

score 25 · Answer 4 · edited Sep 28 '15 at 20:03

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

After installation, open <PostgreSQL PATH>\data\pg_hba.conf.
Modify these two lines, and change "md5" to "trust":

host all all 127.0.0.1/32 md5

host all all ::1/128 md5
Restart the PostgreSQL service (might not be necessary).
(Optional) Open a command prompt, and change code page to 1252:

cmd.exe /c chcp 1252
Log in to PostgreSQL. Non password will be needed (notice the uppercase -U parameter):

psql -U postgres
(Optional, recommended for security reasons) Change the postgres user's password:

\password postgres

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

score 6 · Answer 5 · answered Mar 07 '14 at 13:44

6

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

psql -U postgres my_database

Where my_database is your database name.

answered Mar 07 '14 at 13:44

Marcio Mazzucato

162
2
6

What's the default superuser username/password for postgres after a new install?

5 Answers5

Linked