How to Set the Default User Password in PostgreSQL

Firstly, it is important to understand that for most Unix distributions, the default Postgres user neither requires nor uses a password for authentication. Instead, depending how Postgres was originally installed and what version you are using, the default authentication method will either be ident or peer .

ident authentication uses the operating system’s identification server running at TCP port 113 to verify the user’s credentials.

peer authentication on the other hand, is used for local connections and verifies that the logged in username of the operating system matches the username for the Postgres database.

Login and Connect as Default User

For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.

If you successfully connected and are viewing the psql prompt, jump down to the Changing the Password section.

If you received an error stating that the database “postgres” doesn’t exist, try connecting to the template1 database instead and if successful, continue to Changing the Password.

Authentication Error

If you receive an authentication error when attempting to connect to the psql client, you may need to alter the Postgres authentication config file (pg_hfa.conf).

Open the config file, typically located at /etc/postgresql/#.#/main/pg_hba.conf , where #.# is the Postgres version you are using:

The auth config file is a list of authentication rules. Scroll down the file until you locate the first line displaying the postgres user in the third column (if such a line exists). Uncomment the line if necessary (remove the semicolon), or otherwise if the line is missing entirely, add the following line to the top of the file and save your changes:

This authentication rule simply tells Postgres that for local connections established to all databases for the user postgres , authenticate using the peer protocol.

Note: Some older versions of Postgres prefer the default authentication method of ident, but most modern installations will utilize peer as specified above instead. You may need to test both if your results differ.

Now with your configuration file updated, repeat the steps in the Login and Connect as Default User section to try to connect to as the default postgres user. Once successful, proceed with changing the password.

Changing the Password

With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:

If successful, Postgres will output a confirmation of ALTER ROLE as seen above.

Finally, exit the psql client by using the \q command.

You’re all done. The default postgres user now has a password associated with the account for use in your other applications.

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop.

The default value is ‘postgres’, but sure enough I forgot what password I used when I originally installed it.

I have googled and found a post related to resetting your password. I followed the steps, but the end result is a bit different then mentioned in the post. I used—

to reset the password for my database but instead of a success message I am getting:

«System error 5 has occurred. Access is denied.»

system error. How do I avoid this error and reset the password?

3 Answers 3

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE , though you can still configure other accounts).

I have used net user postgres postgres to reset the password for my database but instead of a success message I am getting «System error 5 has occurred. Access is denied.»

You’ve reset (or tried to reset) the service account password. PostgreSQL won’t run as Administrator for security reasons and the installer generally sets it up with a «postgres» user account in PostgreSQL 9.1 and older 1 . On Windows you can’t start a service as a user without saving the password of the user in the registry, so that’s what the installer does.

If you change the password for the Windows user account postgres , the PostgreSQL service can no longer start. So don’t do that, you’ll have to fix the service configuration to store the updated password.

Thankfully I think another mistake prevented you from doing that. It looks like you’re probably running your command prompt without using «Run as Administrator» on an unprivileged Windows user account or a machine with UAC, so it isn’t running with the access permissions required to change the password for the postgres user.

Before you try to change that password, make sure it’s really what you want to do. What’s the problem you’re trying to solve here? Are you attempting to install a database update or something else that’s asking for the password for the postgres Windows user?

Most likely you’re just trying to log in to the database. For that, you use the (unfortunately completely unrelated) password stored in the database its self. Since you’ve lost/forgotten it you’ll have to reset it:

• Find your pg_hba.conf , usually in C:\Program Files\PostgreSQL\9.1\data\pg_hba.conf
• If necessary, set the permissions on it so that you can modify it; your user account might not be able to do so until you use the security tab in the properties dialog to give yourself that right by using an admin override. Alternately, find notepad / notepad++ in your start menu, right click, choose «Run as administrator», then use File->Open to open pg_hba.conf that way.

Edit it to set the «host» line for user «postgres» on host «127.0.0.1/32» to «trust». You can add the line if it isn’t there; just insert:

before any other lines. (You can ignore comments, lines beginning with # ).

Restart the PostgreSQL service from the Services control panel (start->run-> services.msc )

connect using psql or PgAdmin-III or whatever you prefer

ALTER USER postgres PASSWORD ‘postgres’

remove the line you added to pg_hba.conf or change it back

restart PostgreSQL again.

1. 9.2 now uses the NETWORKSERVICE account, which doesn’t require a password, so this problem goes away.

default postgres user and password

I’m just new to postgresql db management. I’ve been playing around with a db that I didn’t create. Trying to understand the different roles that have been created.

Does the fact that I can log in doing the following mean that the postgres user has no password set up?

and I can see that there is a password set. I found this article: http://www.postgresql.org/message-id/4D958A35.8030501@hogranch.com which seeme to confirm that the postgres user is there by default. and you have to explicitly set a password. It’s the second part about the password that I’m not sure about. Is it blank by default or set to something?

I know that if pg_hba.conf is set to trust everything from 127.0.0.1, then you can log in from the local server without specifying a password. That might be what’s happening in my case. i will test by changing the pg_hba.conf file. But it’d be nice to know what the default password is for postgres user.

postgres/.pgpass file as well. – bma Nov 19 ’13 at 21:52

1 Answer 1

pg_roles is not the view that can tell whether a user has a password or not, because the password field is always set to ******** no matter what.

This comes from the definition of this view (taken from version 9.3):

Note how the rolpassword column is hardcoded to reveal nothing (We may wonder why it’s there at all. Maybe for backward compatibility?)

On the other hand , there is a pg_shadow view that displays passwords as they’re stored, in a column named passwd . This view is only readable by a superuser (typically: the postgres user).

How to Set up a Password as a Default User in PostgreSQL

PostgreSQL often called just Postgres, is an object-relational database management system with an emphasis on flexibility and standards compliance.

Before we begin, I feel it’s crucial to explain that for almost all Unix distributions, the default Postgres user neither uses nor needs a password for authentication purposes. As an alternative, the default authentication operation isВ identВ orВ peer, but it varies based on how Postgres was first installed on your machine and what version you are running.

Ident authenticationВ works by using the OS’ own identification server running at TCP port 113 to authenticate the user’s login credentials. On the other hand, peer verification is only utilized for local connections and authentications that the logged in the username of the OS matches the username in the Postgres database.

Login as a default user

In almost all systems, the standard Postgres user isВ «postgres»В and they do not need a password at all for verification. So, in order to add a password for default users in PostgreSQL, we need to first log in and connect as theВ «postgres»В user.

If you are successful in your attempt to connect as «postgres» and are seeing theВ psqlВ prompt, then the next step is to go down to the section that saysВ «Changing the Password». You may get an error that says that the database «postgres» isn’t there, and if that happens, try connecting to theВ template1В database instead and if it works, continue toВ Changing the Password.

Also, you may get an authentication error when trying to connect to theВ psqlclient, in that case, you might have to modify theВ Postgres authentication config file (pg_hfa.conf). Enter the config file (it is usually found in atВ /etc/postgresql/#.#/main/pg_hba.conf, whereВ #.#В is the current Postgres version you are using). The authentication configuration file is basically a list of authentication parameters.

You have to go down the file until you find the first line displaying theВ postgresВ user in the third column. Remove the semicolon if you have to, or add if the line is missing entirely, then add this line to the top of the file «local all postgres peer» and save the file. This simple modification will instruct Postgres that for any local connections established to and all databases for the userВ «postgres» (which is you, in case you don’t remember), it must verify using theВ peer protocol.

Keep in mind that older versions of Postgres may favor the default verification method of ident, but for the current version, it will use peer as said above instead.

After you’re done with your configuration file and it has been properly modified, repeat the steps I laid out at the beginning of the article to connect as the defaultВ postgres user. If it works you can go ahead with changing the password.

How to change the default password

If you have managed successfully establish a stable connection to Postgres at theВ psqlВ prompt it’s time to change the password. Use theВ ALTER USERВ command to modify the password. If it worked, Postgres will show a text ofВ ALTER ROLEВ as seen above.
The confirmation text looks like this: «postgres=# ALTER USER postgres PASSWORD ‘myPassword’;ALTER ROLE»

Once you’re done you can leave theВ psqlВ client by using theВ \qВ command.

Пароль пользователя postgres — как задать и изменить пароль

Команды по администрированию базами и пользователями выполняются от имени системного пользователя postgres

root может стать им выполнив su — postgres

Затем можно без пароля попасть в интерфейс БД psql

Или то же самое одной командой

Пользователь может создать базу

Затем добавить пользователя и задать для него пароль

=# create user appadmin with encrypted password ‘jdfh8jhtghnjkfrvhyu’;

После этого пользователю нужно дать права для работы с базой данных

=# grant all privileges on db1 mydb to appadmin;

Изменить пароль пользователя Postgres

Пользователя можно создавать и задавать ему пароль двумя раздельными командами

sudo -u postgres createuser anotheruser

Вторая служит для изменения паролей уже существующих пользователей, выполняется из консоли psql

=# alter user anotheruser with encrypted password ‘NEW_STRONG_PASSWORD’;

Непосредственно для системного пользователя postgres пароль не нужен, им может стать root выполнив su как показано ранее. Если нужна авторизация root может установить для postgres новый пароль

Затем пароль нужно ввести дважды, отображаться он не будет.

Пользователь appadmin — не системный, он существует только в postgresql.

Подключаться к базе из консоли от имени этого пользователя нужно указывая имя базы и ключ -W

psql -h myhost -d db1 -U appadmin -W

Последний ключ не обязателен, но без него в интерактивном режиме в некоторых версиях СУБД не будет запрашиваться пароль, пароль должен запрашиваться.

Про создание дампов баз данных Postgres и их загрузку.