Changing the password for Postgresql
- sudo -u postgres psql
- Access the postgresql with the default user postgres with (sudo)'root' credential
- ALTER USER postgres PASSWORD 'newpassword';
- This command will reset the password for the specified user name 'postgres'
- The same command can be used to reset any of the User who has forgotten their password
- psql -U postgres -h localhost
- This will connect to the default database 'postgres' which is available in the same instance as we have provided 'localhost' for '-h' after providing your password
- If incase you want to connect to another database other than your default database then you can specify the database to the end of this command as mentioned below
- psql -U postgres -h localhost NewDatabase
- By default, we will only have postgres as the database in any new instance. To create a new database you can use the command provided above to connect to 'postgres' database. Once you get connected to database you should see the screen displaying the database name as 'postgres=#'.
- Create Database NewDatabase;
- After the database is created you can connect to that NewDatabase and perform all other database functions.
Configuring postgres to allow accessing with the IP address
Postgresql will by default be accessed by providing localhost as the hostname. To enable connecting using the IP address, you must follow the below steps,
1. Modify the postgresql.conf file listen addresses
To modify the postgresql.conf file:
a. Open a Terminal window
b. su - postgres
c. Type cd /usr/local/pgsql/bin
Note: Depending on your install environment the path may vary. You can also try to check the folder path '/var/lib/pgsql/'
d. Type ./pg_ctl stop -D /usr/local/pgsql/data -m smart to stop PostgreSQL.
Where the path listed after –D is the path to the data directory
If this doesnt work, you can also try the below command
sudo systemctl stop postgresql.service
e. Update the /usr/local/pgsql/data/postgresql.conf file.
Example: vi /usr/local/pgsql/data/postgresql.conf
f. Search for the line #listen_addresses = 'local_host'
g. Edit the value to ‘*’ this will start postmaster with the TCP/IP connectivity option.
Example: listen_addresses = '*'
Note: If you are using vi as the editor press i to insert text
Note: This has the PostgreSQL engine listening to any TCP/IP sockets. You need to setup the permission for the specific TCP/IP address you are to connect.
h. Save the changes.
Note: If you are using vi as the editor press i to insert then press :wq! to save the changes.
2. Verify the pg_hba.conf file has authentication set to trust
This solution will verify that the authentication method used by PostgreSQL set to trust, which will allow anyone who is authorized to access the PostgreSQL server to connect to the database.
To verify the pg_hba.conf file set to allow trust authentication:
a. Open a Terminal window
b. su - postgres
c. Type cd /usr/local/pgsql/data
Note: Depending on your install environment the path may vary
d. Update the /usr/local/pgsql/data/pg_hba.conf file to allow TCP/IP connectivity to the database.
Example: vi /usr/local/pgsql/data/pg_hba.conf
e. Verify the host entry as shown below. It should be set to trust and not md5.
host all all 127.0.0.1/32 trust
Note: If you are not using localhost to connect to PostgreSQL and will instead be using the hostname, you will need to add an entry for the associated IP for the hostname. See example below.
Example :
Add the following entry to the bottom of the file
host all all x.x.x.x/32 trust
Where the x.x.x.x is the IP address of the target machine. This line allows all users on the target machine to connect to PostgreSQL. You can adjust the configuration to map to the standards within your organization.
f. Restart PostgreSQL
a. Cd to /usr/local/pgsql/bin
b. Enter ./pg_ctl start -D /usr/local/pgsql/data start
