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. <\/p>\n\n\n\n
I have hence, set a list of steps, we need to do to achieve the goal. <\/p>\n\n\n\n
Set PostgreSQL to Allow Remote Addresses<\/strong><\/p>\n\n\n\n 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. <\/p>\n\n\n\n Find the directive ‘listen_address’ and uncomment it, if it is commented. Also, set this to wildcard like the following:<\/p>\n\n\n\n Create a PostgreSQL User with Encrypted Password<\/strong><\/p>\n\n\n\n Open up your psql console from the user postgres and run the query given below:<\/p>\n\n\n\n You should always use ‘with encrypted password’, as this would avoid storing your password in plaintext instead of md5.<\/p>\n\n\n\n Now, grant the user on the database, you would like it to access:<\/p>\n\n\n\n Add Remote Access for User in PostgreSQL<\/strong><\/p>\n\n\n\n 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:<\/p>\n\n\n\n Here 27.147.176.2 is the IP from where I would like to access the database.<\/p>\n\n\n\n Restart PostgreSQL<\/strong><\/p>\n\n\n\n Now, restart your PostgreSQL instance<\/p>\n\n\n\n You should be now set. <\/p>\n\n\n\n Few Additional Things For Reference<\/strong><\/p>\n\n\n\n From the Postgres console (psql), run the following query:<\/p>\n\n\n\n where username is the username you would like the password changed. Remember to use encrypted password keyword to save the password in md5.<\/p>\n\n\n\n 3. How to change the user to a superuser?<\/p>\n\n\n\n You may use the following query:<\/p>\n\n\n\n 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 … Continue reading “How to Allow Remote User Access in PostgreSQL”<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[412,523],"tags":[524,614,615,616],"_links":{"self":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/821"}],"collection":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/comments?post=821"}],"version-history":[{"count":2,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/821\/revisions"}],"predecessor-version":[{"id":823,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/posts\/821\/revisions\/823"}],"wp:attachment":[{"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/media?parent=821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/categories?post=821"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mellowhost.com\/blog\/wp-json\/wp\/v2\/tags?post=821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}nano \/var\/lib\/pgsql\/9.6\/data\/postgresql.conf<\/pre>\n\n\n\n
listen_address = \"*\"<\/pre>\n\n\n\n
su - postgres\npsql<\/pre>\n\n\n\n
create user new_user with encrypted password 'testu1234'<\/pre>\n\n\n\n
grant all privileges on database my_prod to new_user;<\/pre>\n\n\n\n
Edit the file in nano\n\nnano \/var\/lib\/pgsql\/9.6\/data\/pg_hba.conf\n\nAdd the following line\n\nhost all new_user 27.147.176.2\/32 md5<\/pre>\n\n\n\n
systemctl restart postgresql-9.6.service<\/pre>\n\n\n\n
From the Postgres console (psql), run the following command:
\\du
<\/li>ALTER USER username WITH ENCRYPTED PASSWORD 'password';<\/pre>\n\n\n\n
ALTER USER new_user WITH SUPERUSER;<\/pre>\n","protected":false},"excerpt":{"rendered":"