当前位置: 首页 > postgresql > 正文

postgresql忘记密码处理办法

忘记数据库密码是很常见的事情,下面收集了一写postgresql忘记密码的处理方式,如下:

1. unix like 系统

  • edit pg_hba.conf to allow trust authorization temporarily
  • Reload the config file (pg_ctl reload)
  • Connect and issue ALTER ROLE / PASSWORD to set the new password
  • edit pg_hba.conf again and restore the previous settings
  • Reload the config file again

此类系统忘记密码时,比较好办,只要可以登录数据库安装的机器

即可。
1.1修改 pg_hba.conf 文件,将第一行改为 trust

local  all         all         127.0.0.1/32          md5

改为

local  all         all         127.0.0.1/32          trust

1.2修改密码

psql -d template1 -U postgres -c "alter role postgres password ‘passwd’;"

1.3将pg_hba.conf的内容改回来即可

2. windows系统,下面是一篇博客,详细说明了windows上忘记密码的处理方式

原文:http://pgsnake.blogspot.com/2010/07/postgresql-passwords-and-installers.html

Postgres, Passwords and Installers

By far the most common issues we see reported with the “one-click” PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I’ll explain what the passwords are, why we need them, and how to reset them.

Superuser Password

The Superuser Password is used by Postgres to secure the built-in “postgres” superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdmin, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD ‘VeryVerySecret’;

The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.

Service Password

The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called “postgres”. On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.

On Windows however, things are slightly different. In a normal installation of Postgres we’ll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.

Note: If you change the superuser password in the future, that does not change the service password as well.

When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.

If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:

net user postgres *

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on “Command Prompt” in the Start menu and choose “Run as administrator” from the context menu.

You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.

We get a lot of people asking us to reset their service password, as they mistakenly think it’s related to either their Postgres Community Login, or their account on the EnterpriseDB website. It’s not – and we cannot change it for you!

Uninstallation

If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn’t a huge problem unless you come to reinstall Postgres at a later time and realise you’ve completely forgotten what the password was set to – in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:

net user postgres /delete

Update from Craig Ringer (19/11/2012): Note that this command must be run in a command prompt with Administrative rights. Remember, under UAC, even if you have local admin access you have to explicitly run programs with admin rights. Shift-right-click on “Command Prompt” in the Start menu and choose “Run as administrator” from the context menu.

Summary

So to recap, we have Superuser passwords which are used to secure the database server’s superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.

For a walkthrough of the installation process, please see this guide.

If you need any further assistance having read this post, please use the postgresql-general@postgresql.org mailing list, or the EnterpriseDB Forums.

    分享到:

本文固定链接: http://klwang.info/pgsql-password-come-back/ | 数据库|Linux|软件开发

该日志由 klwang 于2013年03月12日发表在 postgresql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: postgresql忘记密码处理办法 | 数据库|Linux|软件开发
【上一篇】
【下一篇】

postgresql忘记密码处理办法:等您坐沙发呢!

发表评论

*
快捷键:Ctrl+Enter