Configuration PostgreSQL is a bit tricky and complicated compared to MySQL for a few cases. One such case would be allowing remote user access in PGSQL. You might be interested to allow your home network to connect to your test or production database to run a few queries. To do that, if you are not using a tool like Percona Distribution for Postgres or a Cluster tool like Clustercontrol, it might get a bit complicated.
I have hence, set a list of steps, we need to do to achieve the goal.
Set PostgreSQL to Allow Remote Addresses
To do that, open up your postgresql.conf file. It is usually located under /var/lib/pgsql/9.6/data/postgresql.conf file. I am running PGSQL 9.6, hence the folder is in 9.6. If you are running any other version of PGSQL, it shall change based on the version.
Find the directive ‘listen_address’ and uncomment it, if it is commented. Also, set this to wildcard like the following:
listen_address = "*"
Create a PostgreSQL User with Encrypted Password
Open up your psql console from the user postgres and run the query given below:
su - postgres psql
create user new_user with encrypted password 'testu1234'
You should always use ‘with encrypted password’, as this would avoid storing your password in plaintext instead of md5.
Now, grant the user on the database, you would like it to access:
grant all privileges on database my_prod to new_user;
Add Remote Access for User in PostgreSQL
PostgreSQL manages a remote access file, to allow access from remote sources. The file is located in /var/lib/pgsql/9.6/data/pg_hba.conf. To allow remote access from an IP, we need to add a line in this file:
Edit the file in nano nano /var/lib/pgsql/9.6/data/pg_hba.conf Add the following line host all new_user 22.214.171.124/32 md5
Here 126.96.36.199 is the IP from where I would like to access the database.
Now, restart your PostgreSQL instance
systemctl restart postgresql-9.6.service
You should be now set.
Few Additional Things For Reference
- How to list the users of PostgreSQL from the console?
From the Postgres console (psql), run the following command:
- How to change the password for a PostgreSQL user?
From the Postgres console (psql), run the following query:
ALTER USER username WITH ENCRYPTED PASSWORD 'password';
where username is the username you would like the password changed. Remember to use encrypted password keyword to save the password in md5.
3. How to change the user to a superuser?
You may use the following query:
ALTER USER new_user WITH SUPERUSER;