如何更改PostgreSQL用户的密码?
当前回答
以及Bash和expect的完全自动化方式(在本例中,我们在OS和PostgreSQL运行时级别为新的PostgreSQL管理员提供新设置的PostgreQL密码):
# 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_pw\r"
expect "Enter it again: "
send -- "$postgres_usr_pw\r"
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 ;
"
其他回答
使用此项:
\password
输入该用户的新密码,然后确认。如果您不记得密码,并且想更改密码,您可以以“postgres”登录,然后使用此选项:
ALTER USER 'the username' WITH PASSWORD 'the new password';
以及Bash和expect的完全自动化方式(在本例中,我们在OS和PostgreSQL运行时级别为新的PostgreSQL管理员提供新设置的PostgreQL密码):
# 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_pw\r"
expect "Enter it again: "
send -- "$postgres_usr_pw\r"
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 ;
"
将用户“postgres”的密码更改为“postgress”:
# ALTER USER postgres WITH ENCRYPTED PASSWORD '<NEW-PASSWORD>';
对于我在Ubuntu 14.04(Trusty Tahr)上的案例,安装了PostgreSQL 10.3:我需要遵循以下步骤
su-postgres将用户切换到postgrespsql以进入PostgreSQL shell\密码,然后输入密码Q退出shell会话然后,通过执行exit并配置pg_hba.conf(我的位于/etc/postgresql/10/main/pg_hba.coff),切换回root,确保您有以下行本地所有postgres md5通过service PostgreSQL Restart重新启动PostgreSQL服务现在切换到postgres用户并再次输入PostgreSQL shell。它将提示您输入密码。
更改密码:
sudo -u postgres psql
Then
\password postgres
现在输入新密码并确认。
然后\q退出。
推荐文章
- 如何关闭mysql密码验证?
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何添加列,如果不存在PostgreSQL?
- 如何在Postgres中获得两个字段的MIN() ?
- 截断Postgres数据库中的所有表
- 如何连接列在Postgres选择?
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?