Скачать или смотреть How to Change the authentication method for PostgreSQL Superuser

Learn how to change the authentication method for the PostgreSQL superuser on Debian 10, ensuring a smooth connection process and enhanced security.
---
This video is based on the question https://stackoverflow.com/q/66351630/ asked by the user 'user14131782' ( https://stackoverflow.com/u/14131782/ ) and on the answer https://stackoverflow.com/a/66354964/ provided by the user 'leo95batista' ( https://stackoverflow.com/u/13066106/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Change authentication method for postgres superuser

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Change the Authentication Method for PostgreSQL Superuser

If you're working with PostgreSQL on Debian and trying to switch the way you authenticate as the superuser, you may encounter some hurdles. Many users default to the 'peer' authentication method, which allows access without a password. This method might not be ideal for everyone, particularly if you're looking for enhanced security. In this post, we'll address a common problem: how to change the authentication method for the postgres superuser and resolve password authentication issues that arise in the process.

Understanding the Problem

You may be in a situation where:

You log into your PostgreSQL database using the postgres user without needing a password because of the 'peer' authentication method.

You attempt to set a password for your postgres user using the ALTER USER command.

After changing the authentication method in your pg_hba.conf file from 'peer' to 'scram-sha-256', you can no longer log in, receiving a "password authentication failed" error.

This situation can be quite frustrating, but it can be resolved by following a systematic approach.

Steps to Change the Authentication Method

Here's how to successfully change the authentication method for your PostgreSQL superuser:

1. Open a Terminal Window

Start by accessing your terminal where you'll input the necessary commands.

2. Navigate to the PostgreSQL Bin Directory

Change your current directory to the PostgreSQL binary directory. This will allow you to run the PostgreSQL command-line tools directly.

[[See Video to Reveal this Text or Code Snippet]]

(Note: Depending on your installation, the path may differ. Ensure you reference your specific PostgreSQL bin directory.)

3. Switch to the postgres User

To execute commands as the postgres user, enter the following command:

[[See Video to Reveal this Text or Code Snippet]]

This command changes your active user to postgres, allowing you to perform administrative tasks.

4. Launch the PostgreSQL Command Line

Now, run the PostgreSQL command line by entering:

[[See Video to Reveal this Text or Code Snippet]]

5. Set a Password for the PostgreSQL User

To set or change the password, enter the command below, replacing your_username and new_password with your desired values:

[[See Video to Reveal this Text or Code Snippet]]

If you do this correctly, you should see a response indicating that the command was successful (i.e., ALTER ROLE should be displayed).

6. Exit the PostgreSQL Command Line

To exit the command line interface, type:

[[See Video to Reveal this Text or Code Snippet]]

7. Modify the pg_hba.conf File

Next, locate the pg_hba.conf file. This file controls the authentication methods used by PostgreSQL.

[[See Video to Reveal this Text or Code Snippet]]

8. Update the Authentication Method

In the pg_hba.conf file, find the relevant line for your postgres user and change the authentication method from 'peer' to 'scram-sha-256' (or 'md5', depending on your desired security level). For example, you might change it to look like this:

[[See Video to Reveal this Text or Code Snippet]]

Make sure to check and keep the necessary lines intact; you may only need to adjust the authentication method.

9. Save the Changes

After making the edits, save the changes in the text editor. If you used nano, you can do this by pressing CTRL + O, then Enter, followed by CTRL + X to exit.

10. Restart the PostgreSQL Service

Finally, you need to restart your PostgreSQL service for the changes to take effect. You can do this using the following command:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Changing the authentication method for the PostgreSQL superuser can improve your