Linux postgresql set password

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.

Источник

Install and configure PostgreSQL

PostgreSQL (also known as Postgres) is an object-relational database system that has the features of traditional commercial database systems with enhancements to be found in next-generation database management systems (DBMS).

Installation

To install PostgreSQL, run the following command in the command prompt:

sudo apt install postgresql 

The database service is automatically configured with viable defaults, but can be customised based on your specific needs.

Configuration

PostgreSQL supports multiple client authentication methods. In Ubuntu, peer is the default authentication method used for local connections, while scram-sha-256 is the default for host connections (this used to be md5 until Ubuntu 21.10). Please refer to the PostgreSQL Administrator’s Guide if you would like to configure alternatives like Kerberos.

The following discussion assumes that you wish to enable TCP/IP connections and use the MD5 method for client authentication. PostgreSQL configuration files are stored in the /etc/postgresql//main directory. For example, if you install PostgreSQL 14, the configuration files are stored in the /etc/postgresql/14/main directory.

Tip:
To configure IDENT authentication, add entries to the /etc/postgresql/*/main/pg_ident.conf file. There are detailed comments in the file to guide you.

By default only connections from the local system are allowed, to enable all other computers to connect to your PostgreSQL server, edit the file /etc/postgresql/*/main/postgresql.conf . Locate the line: #listen_addresses = ‘localhost’ and change it to * :

Note:
‘*’ will allow all available IP interfaces (IPv4 and IPv6), to only listen for IPv4 set ‘0.0.0.0’ while ‘::’ allows listening for all IPv6 addresses.

For details on other parameters, refer to the configuration file or to the PostgreSQL documentation for information on how they can be edited.

Now that we can connect to our PostgreSQL server, the next step is to set a password for the postgres user. Run the following command at a terminal prompt to connect to the default PostgreSQL template database:

sudo -u postgres psql template1 

The above command connects to PostgreSQL database template1 as user postgres. Once you connect to the PostgreSQL server, you will be at an SQL prompt. You can run the following SQL command at the psql prompt to configure the password for the user postgres.

ALTER USER postgres with encrypted password 'your_password'; 

After configuring the password, edit the file /etc/postgresql/*/main/pg_hba.conf to use scram-sha-256 authentication with the postgres user, allowed for the template1 database, from any system in the local network (which in the example is 192.168.122.1/24) :

hostssl template1 postgres 192.168.122.1/24 scram-sha-256 

Note:
The config statement ‘hostssl’ used here will reject tcp connections that would not use ssl. Postgresql in Ubuntu has the ssl feature built in and configured by default, so it works right away. On your postgresql server this uses the certificate created by ‘ssl-cert’ package which is great, but for production use you should consider updating that with a proper certificate from a recognized CA.

Finally, you should restart the PostgreSQL service to initialise the new configuration. From a terminal prompt enter the following to restart PostgreSQL:

sudo systemctl restart postgresql.service 

Warning:
The above configuration is not complete by any means. Please refer to the PostgreSQL Administrator’s Guide to configure more parameters.

You can test server connections from other machines by using the PostgreSQL client as follows, replacing the domain name with your actual server domain name or IP address:

sudo apt install postgresql-client psql --host your-servers-dns-or-ip --username postgres --password --dbname template1 

Streaming replication

PostgreSQL has a nice feature called Streaming Replication which provides the capability to continuously ship and apply the Write-Ahead Log (WAL) XLOG records to some number of standby servers in order to keep them current. Here is presented a very basic and simple way to replicate a PostgreSQL server (main) to a standby server.

First, create a replication user in the main server to be used from the standby server:

sudo -u postgres createuser --replication -P -e replicator 

Let’s configure the main server to turn on the streaming replication. Open the file /etc/postgresql/*/main/postgresql.conf and make sure you have the following lines:

listen_addresses = '*' wal_level = replica 

Also edit the file /etc/postgresql/*/main/pg_hba.conf to add an extra line to allow the standby server connection for replication (that is a special keyword) using the replicator user:

host replication replicator scram-sha-256 

Restart the service to apply changes:

sudo systemctl restart postgresql 

Now, in the standby server, let’s stop the PostgreSQL service:

sudo systemctl stop postgresql 

Edit the /etc/postgresql/*/main/postgresql.conf to set up hot standby:

Back up the current state of the main server (those commands are still issued on the standby system):

sudo su - postgres # backup the current content of the standby server (update the version of your postgres accordingly) cp -R /var/lib/postgresql/14/main /var/lib/postgresql/14/main_bak # remove all the files in the data directory rm -rf /var/lib/postgresql/14/main/* pg_basebackup -h -D /var/lib/postgresql/14/main -U replicator -P -v -R 

After the above this will have done a full single pass copying the content of the main database onto the local system being the standby. In the pg_basebackup command the flags represent the following:

• -h : The hostname or IP address of the main server
• -D : The data directory
• -U : The user to be used in the operation
• -P : Turns on progress reporting
• -v : Enables verbose mode
• -R : Creates a standby.signal file and appends connection settings to postgresql.auto.conf

Finally, let’s start the PostgreSQL service on standby server:

sudo systemctl start postgresql 

To make sure it is working, go to the main server and run the following command:

sudo -u postgres psql -c "select * from pg_stat_replication;" 

As mentioned, this is a very simple introduction, there are way more great details in the upstream documentation about the configuration of replication as well as further High Availability, Load Balancing, and Replication.

To test the replication you can now create a test database in the main server and check if it is replicated in the standby server:

sudo -u postgres createdb test # on the main server sudo -u postgres psql -c "\l" # on the standby server 

You need to be able to see the test database, that was created on the main server, in the standby server.

Backups

PostgreSQL databases should be backed up regularly. Refer to the PostgreSQL Administrator’s Guide for different approaches.

Resources

• As mentioned above, the PostgreSQL Administrator’s Guide is an excellent resource. The guide is also available in the postgresql-doc package. Execute the following in a terminal to install the package:

sudo apt install postgresql-doc 

This package provides further man pages on postgresql ‘dblink’ and ‘server programming interface’ as well as the html guide that you’d find upstream. To view the guide enter xdg-open /usr/share/doc/postgresql-doc-*/html/index.html or point your browser at it.

• For general SQL information see the O’Reilly books Getting Started with SQL: A Hands-On Approach for Beginners by Thomas Nield as an entry point and SQL in a Nutshell as a quick reference.
• Also, see the PostgreSQL Ubuntu Wiki page for more information.

Источник