Postgresql linux postgres user password

What’s the default superuser username/password for postgres after a new install?

I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?

5 Answers 5

CAUTION The answer about changing the UNIX password for «postgres» through «$ sudo passwd postgres» is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account «postgres» is locked, which means it cannot be logged in using a password. If you use «sudo passwd postgres», the account is immediately unlocked. Worse, if you set the password to something weak, like «postgres», then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo «postgres/postgres» to log into your UNIX system.

What you should do is follow Chris James‘s answer:

sudo -u postgres psql postgres # \password postgres Enter new password: 

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

1. By running the «psql» command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql . Note that sudo -u does NOT unlock the UNIX user.
2. by TCP/IP connection using PostgreSQL’s own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account «postgres». Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Источник

Postgresql linux postgres user password

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

Режим аутентификации для этой учетной записи в PostgreSQL установлен в ident , то есть позволяет авторизоваться только под пользователем postgres . В свою очередь учетная запись postgres создается как заблокированная для аутентификации и поэтому не имеет какого-либо предустановленного пароля.

Конечно же пароль можно установить принудительно, например через passwd postgres . При этом запись разблокируется, что при установке слабого пароля сделает уязвимой всю систему.

Одним из способов получения локального доступа к серверу через учетную запись postgres (без ее разблокировки) для первоначальной настройки является редактирование файла pg_hba.conf (который находится в папке /etc/postgresql/[версия]/main ) с целью разрешения локального соединения без проверки пароля:

# IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust 

После редактирования файла необходимо перезагрузить сервер:

/etc/init.d/postgresql restart 

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

psql -U postgres -h localhost 

Соответственно для удаленного доступа к серверу, например через dbeaver , можно использовать связку с SSH .

Источник

Postgresql: How do I set the password for the user ‘postgres’ when I can’t access the postgresql database?

I’m running postgresql 10.12 on Ubuntu 18.04. I’d like to experiment with a software package that uses postgres. This means I should figure out how to set up users, passwords and databases under postgres. Postgres is running, but there’s no way to log in to it. I’m pretty sure there is a user called ‘postgres’. Logging in as this user without providing a password fails. Also, attempting to use the passwords ‘postgres’ or ‘root’ fail. How do I change the password for the user ‘postgres’ without being able to access the database?

Typically the operating system user postgres is allowed to log in without a password. See client authentication in the manual.

1 Answer 1

This is a newbie-level recipe to reset the superuser password, which works on all fresh installations of PostgreSQL on Linux.

Go to the shell and switch user to postgres

 (in user shell) sudo su - postgres 

 (in postgres shell) psql postgres postgres 

 (in postgres psql) ALTER USER postgres PASSWORD 'newsecret'; 

 (in user shell) psql -h localhost postgres postgres 

Note on remote postgres servers

In step 1 above, you can use ssh , kubectl exec , aws ssm or anything like that, if you have this kind of shell access.

Best Practice note

Above recipe (though it answers the OP question) is not a good practice. The best approach is:

1. Read and understand client auth ->https://www.postgresql.org/docs/current/client-authentication.html
2. Do not use postgres database user (or any other superuser!) for applications/development. Create your own user instead. For the simplest setup, use this:

 (in psql shell) CREATE USER myapp PASSWORD 'secret'; CREATE DATABASE myapp; ALTER DATABASE myapp OWNER TO myapp; -- alternative if you want to keep default ownership: -- GRANT ALL ON DATABASE myapp TO myapp; 

Note on Managed postgres solutions

This answer applies only for self-managed PostgreSQL, where you have superuser shell access. It will not work for managed solutions like Aurora, CloudSQL or alike — use cloud provider tools to reset db passwords in that case.

Источник

How to Set the Default User Password in PostgreSQL

Firstly, it is important to understand that for most Unix distributions, the default Postgres user neither requires nor uses a password for authentication. Instead, depending how Postgres was originally installed and what version you are using, the default authentication method will either be ident or peer .

ident authentication uses the operating system’s identification server running at TCP port 113 to verify the user’s credentials.

peer authentication on the other hand, is used for local connections and verifies that the logged in username of the operating system matches the username for the Postgres database.

Login and Connect as Default User

For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.

If you successfully connected and are viewing the psql prompt, jump down to the Changing the Password section.

If you received an error stating that the database “postgres” doesn’t exist, try connecting to the template1 database instead and if successful, continue to Changing the Password.

$ sudo -u postgres psql template1 

Authentication Error

If you receive an authentication error when attempting to connect to the psql client, you may need to alter the Postgres authentication config file (pg_hfa.conf).

Open the config file, typically located at /etc/postgresql/#.#/main/pg_hba.conf , where #.# is the Postgres version you are using:

$ sudo nano /etc/postgresql/9.3/main/pg_hba.conf 

The auth config file is a list of authentication rules. Scroll down the file until you locate the first line displaying the postgres user in the third column (if such a line exists). Uncomment the line if necessary (remove the semicolon), or otherwise if the line is missing entirely, add the following line to the top of the file and save your changes:

This authentication rule simply tells Postgres that for local connections established to all databases for the user postgres , authenticate using the peer protocol.

Note: Some older versions of Postgres prefer the default authentication method of ident, but most modern installations will utilize peer as specified above instead. You may need to test both if your results differ.

Now with your configuration file updated, repeat the steps in the Login and Connect as Default User section to try to connect to as the default postgres user. Once successful, proceed with changing the password.

Changing the Password

With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:

postgres=# ALTER USER postgres PASSWORD 'myPassword'; ALTER ROLE 

If successful, Postgres will output a confirmation of ALTER ROLE as seen above.

Finally, exit the psql client by using the \q command.

You’re all done. The default postgres user now has a password associated with the account for use in your other applications.

Источник

PostgreSQL Default Username and Password

In this brief post, we will cover some methods you can use to configure a password for the default user account in PostgreSQL Server.

PostgreSQL, commonly known as Postgres is one of the most popular free and open source relational database management systems. PostgreSQL adopts a large scale features of ANSI SQL including but not limited to foreign keys, triggers, views, transactional integrity and more.

If you are coming from MySQL, you may be familiar with the default username and password configured in MySQL Server. Therefore, you may wonder what is the default username and password in PostgreSQL.

Unfortunately, PostgreSQL does not offer a default username and password. In most cases, you need to configure the auth credentials during the installation process. If you used an unattended installer, you may skip on credential configuration.

Method 1 — Use psql to Set the Password for the postrges User.

The default system account in PostgreSQL is postgres .

You can check if the postgres user exists in the system by running the command:

cat /etc/passwd | grep postgres 

The command above should show the postgres user, including the shell.

To set the password for the postgres user, run the command below to login to the PostgreSQL shell.

Finally, run the command belw to set a password for the postgres user:

postgres=# ALTER USER postgres PASSWORD 'password'; 

Where password is the password you wish to set for the postgres user.

Источник