Смена пароля postgres linux

Update Your PostgreSQL Password in Linux

PostgreSQL supports many client authentication methods, but in this case we’re only going to concern ourselves with two: password and md5.

Note: The default authentication method for PostgreSQL is ident. If you’d like to change the PostgreSQL authentication method from ident to md5, then visit the linked tutorial!

Before Getting Started

• These instructions are intended specifically for changing a password in PostgreSQL.
• I’ll be working from a Liquid Web Core Managed CentOS 7 server, and I’ll be logged in as root.
• PostgreSQL is installed per our tutorial on: How to Install and Connect to PostgreSQL on CentOS 7.

Step #1: Switch to the PostgreSQL User: postgres

If you’re working from a default PostgreSQL installation, then PostgreSQL will be configured with the user postgres.

Since we’re logged in as root, and we’re assuming that root doesn’t have a user for PostgreSQL, switch to the default PostgreSQL user: postgres.

… then attempt a connection to PostgreSQL.

… enter your password at the prompt.

… the correct, valid response will be similar to the following.

psql (9.3.9)
Type «help» for help.

Step #2: Add/Change the PostgreSQL Password for the User: postgres

Use the following command to change the PostgreSQL password for your current user, which is now postgres.

Enter your new password, and then enter it again to confirm it.

Enter new password:
Enter it again:

Now quit the PostgreSQL interface.

Bonus Information!

You can do all of step one in exactly one command.

Источник

PostgreSQL: Resetting password of PostgreSQL on Ubuntu [closed]

In Ubuntu, I installed PostgreSQL database and created a superuser for the server. If I forgot the password of the postgresql superuser, how can I reset it (the password) for that user? I tried uninstalling it and then installing it again but the previously created superuser is retained.

1 Answer 1

Assuming you’re the administrator of the machine, Ubuntu has granted you the right to sudo to run any command as any user.
Also assuming you did not restrict the rights in the pg_hba.conf file (in the /etc/postgresql/9.1/main directory), it should contain this line as the first rule:

# Database administrative login by Unix domain socket local all postgres peer 

(About the file location: 9.1 is the major postgres version and main the name of your «cluster». It will differ if using a newer version of postgres or non-default names. Use the pg_lsclusters command to obtain this information for your version/system).

Anyway, if the pg_hba.conf file does not have that line, edit the file, add it, and reload the service with sudo service postgresql reload .

Then you should be able to log in with psql as the postgres superuser with this shell command:

Once inside psql, issue the SQL command:

ALTER USER postgres PASSWORD 'newpassword'; 

In this command, postgres is the name of a superuser. If the user whose password is forgotten was ritesh , the command would be:

ALTER USER ritesh PASSWORD 'newpassword'; 

Keep in mind that you need to type postgres with a single S at the end

If leaving the password in clear text in the history of commands or the server log is a problem, psql provides an interactive meta-command to avoid that, as an alternative to ALTER USER . PASSWORD :

It asks for the password with a double blind input, then hashes it according to the password_encryption setting and issue the ALTER USER command to the server with the hashed version of the password, instead of the clear text version.

Источник

How to Set PostgreSQL User Password

In this PostgreSQL tutorial, I will show the step to how to set the PostgreSQL user password. Also, I will show you how to set the PostgreSQL user password using a configuration file.

I will let you know about the command to set the password for any user in PostgreSQL.

How to Set PostgreSQL User Password

In PostgreSQL, a user called ‘postgres’ is the default user, and the password for this user isn’t set when the first time you install PostgreSQL. But a user without a password means your data isn’t secure anymore and anyone can access your data.

So you need to change or set the password for the default user or any existing user in PostgreSQL. So here we will take some of the examples to understand how to set the password for these kinds of users in PostgreSQL.

The syntax is given below.

ALTER USER username PASSWORD 'user_new_password';

• ALTER USER: The command to modify the user attributes. Here the password attribute will be modified.
• username: The user whose password you want to set.
• PASSWORD: It is the input option or keyword to specify the new password for the user.
• user_new_password: This is the user’s new password that you want to set.

To change the password of the default user ‘postgres’ in PostgreSQL. First, you need to log in, so use the below command.

If the above command asks for a password, just hit enter from your keyboard because the password is none for this user.

After executing the above command, it takes you to the psql prompt that is shown in the below picture.

Now use the below command to set the password for the default user ‘postgres’.

ALTER USER postgres PASSWORD 'postgres@456'

After executing the above command, a message appears ‘ALTER ROLE’ which means the password for the user ‘postgres’ updated.

Enter the below command in the psql prompt to exit.

Now you can log in with user ‘postgres’ with a new password that you have set.

How to Set PostgreSQL User Password using Configuration File

PostgreSQL has an access policy configuration file called pg_hba.conf and this file is used for client authentication. Sometimes you don’t have access to your password or maybe you forgot the password for the particular user in PostgreSQL.

So you have one option to set the password through this file pg_hba.conf. Follow the below steps to set the password for the user.

First, find the file pg_hba.conf and your system wherever PostgreSQL is installed, On windows you can find it at ‘C:\Program Files\PostgreSQL\14\data’ as shown in the below picture.

Open the file and change the method to ‘trust’ as shown in the below picture.

Then restart the PostgreSQL server from the services as shown in the below picture.

Open the command prompt and log in with the default user ‘postgres’ to set the password using the below command.

After executing the above command, it takes you to the psql prompt without asking for any password.

Then run the below command to set the password for the user ‘postgres’.

ALTER USER postgres PASSWORD '@postgres222';

If you see the message ‘ALTER ROLE’ as indicated as a green arrow in the above picture that means the password for the user ‘postgres’ is updated. Then exit the psql prompt using the below command.

Now again open the file pg_hba.conf and the method to ‘md5’ as shown in the below picture.

Again Restart the PostgreSQL server from the services of Windows. After restarting the server you can log in with the new password that you have set for the user ‘postgres’.

Conclusion

In this PostgreSQL tutorial, you have learned how to set the password for the user in PostgreSQL, then covered the two ways to set the password, one using psql prompt and the other through pg_hba.conf file.

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Источник

Пароль пользователя postgres

Пароль пользователя postgres задается при добавлении пользователя изначально, обе операции выполняются одной командой. Пароль в любой момент можно поменять.

Как задать пароль при добавлении пользователя и БД

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

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

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

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

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

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

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

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

=# grant all privileges on database ‘db1’ to appadmin;

Postgres будет выводить сообщения об успешном выполнении каждой из операций:

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

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

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

Сменим пароль для созданного ранее пользователя appadmin

Сервис также выдает сообщение ALTER ROLE, которое говорит о том, что операция прошла успешно.

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

Затем пароль нужно ввести дважды, отображаться он не будет. На правах пользователей в самой СУБД это не скажется.

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

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

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

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

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

Источник