Как сменить пароль postgres на windows

To request a new password for the postgres user (without showing it in the command):

sudo -u postgres psql -c "password"

answered Mar 3, 2018 at 4:05

This was the first result on google, when I was looking how to rename a user, so:

ALTER USER <username> WITH PASSWORD '<new_password>';  -- change password
ALTER USER <old_username> RENAME TO <new_username>;    -- rename user

A couple of other commands helpful for user management:

CREATE USER <username> PASSWORD '<password>' IN GROUP <group>;
DROP USER <username>;

Move user to another group

ALTER GROUP <old_group> DROP USER <username>;
ALTER GROUP <new_group> ADD USER <username>;

answered Apr 21, 2016 at 20:53

Use this:

password

Enter the new password you want for that user and then confirm it.
If you don’t remember the password and you want to change it, you can log in as «postgres» and then use this:

ALTER USER 'the username' WITH PASSWORD 'the new password';

This is similar to other answers in syntax, but it should be known that you can also pass the MD5 hash value of the password, so you are not transmitting a plain text password.

Here are a few scenarios of unintended consequences of altering a users password in plain text.

1. If you do not have SSL and are modifying remotely you are transmitting the plain text password across the network.
2. If you have your logging configuration set to log DDL statements log_statement = ddl or higher, then your plain text password will show up in your error logs.
3. If you are not protecting these logs, it’s a problem.
4. If you collect these logs/ETL them and display them where others have access, they could end up seeing this password, etc.
5. If you allow a user to manage their password, they are unknowingly revealing a password to an administrator or low-level employee tasked with reviewing logs.

With that said, here is how we can alter a user’s password by building an MD5 hash value of the password.

• PostgreSQL, when hashing a password as MD5, salts the password with the user name and then prepends the text «md5» to the resulting hash.

• Example: «md5″+md5(password + username)

• In Bash:

  echo -n "passwordStringUserName" | md5sum | awk '{print "md5"$1}'
  

  Output:

  md5d6a35858d61d85e4a82ab1fb044aba9d
  

• In PowerShell:

  [PSCredential] $Credential = Get-Credential
  
  $StringBuilder = New-Object System.Text.StringBuilder
  
  $null = $StringBuilder.Append('md5');
  
  [System.Security.Cryptography.HashAlgorithm]::Create('md5').ComputeHash([System.Text.Encoding]::ASCII.GetBytes(((ConvertFrom-SecureStringToPlainText -SecureString $Credential.Password) + $Credential.UserName))) | ForEach-Object {
      $null = $StringBuilder.Append($_.ToString("x2"))
  }
  
  $StringBuilder.ToString();
  
  ## OUTPUT
  md5d6a35858d61d85e4a82ab1fb044aba9d
  

• So finally our ALTER USER command will look like

  ALTER USER UserName WITH PASSWORD 'md5d6a35858d61d85e4a82ab1fb044aba9d';
  

• Relevant links (note I will only link to the latest versions of the documentation. For older, it changes some, but MD5 is still supported a ways back.)

• create role

• The password is always stored encrypted in the system catalogs. The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. The method of encryption is determined by the configuration parameter password_encryption. If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption (since the system cannot decrypt the specified encrypted password string, to encrypt it in a different format). This allows reloading of encrypted passwords during dump/restore.

• Configuration setting for password_encryption

• PostgreSQL password authentication documentation

• Building PostgreSQL password MD5 hash value

And the fully automated way with Bash and expect (in this example we provision a new PostgreSQL administrator with the newly provisioned PostgreSQL password both on OS and PostgreSQL run-time level):

  # The $postgres_usr_pw and the other Bash variables MUST be defined
  # for reference the manual way of doing things automated with expect bellow
  #echo "copy-paste: $postgres_usr_pw"
  #sudo -u postgres psql -c "password"
  # The OS password could / should be different
  sudo -u root echo "postgres:$postgres_usr_pw" | sudo chpasswd

  expect <<- EOF_EXPECT
     set timeout -1
     spawn sudo -u postgres psql -c "\password"
     expect "Enter new password: "
     send -- "$postgres_usr_pwr"
     expect "Enter it again: "
     send -- "$postgres_usr_pwr"
     expect eof
EOF_EXPECT

  cd /tmp/
  # At this point the 'postgres' executable uses the new password
  sudo -u postgres PGPASSWORD=$postgres_usr_pw psql 
    --port $postgres_db_port --host $postgres_db_host -c "
  DO $$DECLARE r record;
     BEGIN
        IF NOT EXISTS (
           SELECT
           FROM   pg_catalog.pg_roles
           WHERE  rolname = '"$postgres_db_useradmin"') THEN
              CREATE ROLE "$postgres_db_useradmin" WITH SUPERUSER CREATEROLE
              CREATEDB REPLICATION BYPASSRLS
 PASSWORD '"$postgres_db_useradmin_pw"' LOGIN ;
        END IF;
     END$$;
  ALTER ROLE "$postgres_db_useradmin" WITH SUPERUSER CREATEROLE
  CREATEDB REPLICATION BYPASSRLS
PASSWORD  '"$postgres_db_useradmin_pw"' LOGIN ;
 "

I was on Windows (Windows Server 2019; PostgreSQL 10), so local type connections (pg_hba.conf: local all all peer) are not supported.

The following should work on Windows and Unix systems alike:

1. backup pg_hba.conf to pg_hba.orig.conf e.g.
2. create pg_hba.conf with only this: host all all 127.0.0.1/32 trust
3. restart pg (service)
4. execute psql -U postgres -h 127.0.0.1
5. enter (in pgctl console) alter user postgres with password 'SomePass';
6. restore pg_hba.conf from 1. above

answered Mar 5, 2021 at 13:46

In general, just use the pgAdmin UI for doing database-related activity.

If instead you are focusing more in automating database setup for your local development, CI, etc.

For example, you can use a simple combination like this.

(a) Create a dummy super user via Jenkins with a command similar to this:

docker exec -t postgres11-instance1 createuser --username=postgres --superuser experiment001

This will create a super user called experiment001 in you PostgreSQL database.

(b) Give this user some password by running a NON-Interactive SQL command.

docker exec -t postgres11-instance1 psql -U experiment001 -d postgres -c "ALTER USER experiment001 WITH PASSWORD 'experiment001' "

PostgreSQL is probably the best database out there for command line (non-interactive) tooling. Creating users, running SQL, making backup of database, etc.

In general, it is all quite basic with PostgreSQL, and it is overall quite trivial to integrate this into your development setup scripts or into automated CI configuration.

Using pgAdmin 4:

Menu Object → Change password…

Most of the answers were mostly correct, but you need to look out for minor things. The problem I had was that I didn’t ever set the password of «postgres», so I couldn’t log into an SQL command line that allowed me to change passwords. These are the steps that I used successfully (note that most or all commands need sudo or root user):

• Edit the pg_hba.conf file in the data directory of the DB cluster you’re trying to connect to.

  • The folder of the data directory can be found by inspecting the systemd command line, easily obtained with systemctl status postgresql@VERSION-DB_CLUSTER. Replace VERSION with your psql version and DB_CLUSTER with the name of your database cluster. This may be main if it was automatically created, so, e.g., postgresql@13-main. Alternatively, my Bash shell provided auto-complete after entering postgresql@, so you could try that or look for the PostgreSQL services in the list of all services (systemctl -a). Once you have the status output, look for the second command line after CGroup, which should be rather long, and start with /usr/lib/postgresql/13/bin/postgres or similar (depending on version, distro, and installation method). You are looking for the directory after -D, for example /var/lib/postgresql/13/main.

• Add the following line: host all all 127.0.0.1/32 trust. This allows for all users on all databases to connect to the database via IPv4 on the local machine unconditionally, without asking for a password.

  This is a temporary fix and don’t forget to remove this line again later on. Just to be sure, I commented out the host all all 127.0.0.1/32 md5 (md5 may be replaced by scram-sha-256), which is valid for the same login data, just requiring a password.

• Restart the database service: systemctl restart postgresql@... Again, use the exact service you found earlier.

• Check that the service started properly with systemctl status postgresql@....

• Connect with psql, and very importantly, force psql to not ask for a password. In my experience, it will ask you for a password even though the server doesn’t care, and will still reject your login if your password was wrong. This can be accomplished with the -w flag.

  The full command line looks something like this: sudo -u postgres psql -w -h 127.0.0.1 -p 5432. Here, postgres is your user and you may have changed that. 5432 is the port of the cluster-specific server and may be higher if you are running more than one cluster (I have 5434 for example).

• Change the password with the password special command.

• Remember to remove the password ignore workaround and restart the server to apply the configuration.