PostgreSQL 12 : Remote Connection

     
It's possible to connect to PostgreSQL Server only from Localhost by default like here but if you'd like to connect to PostgreSQL from Remote hosts, change settings like follows.

 
[1] . There are many authentication methods on PostgreSQL, though.

 
On this examaple, Configure MD5 password method.
[root@www ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 59: uncomment and change

listen_addresses = '*
'
[root@www ~]# vi /var/lib/pgsql/data/pg_hba.conf
# add to the end

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
# specify network range you allow to connect on [ADDRESS] section
# if allow all, specify [0.0.0.0/0]
host    all             all             10.0.0.0/24             md5

[root@www ~]# systemctl restart postgresql

[2] .  If Firewalld is running, allow PostgreSQL service.

 
[root@www ~]# firewall-cmd --add-service=postgresql --permanent
success
[root@www ~]# firewall-cmd --reload
success


[3] . To connect to a PostgreSQL Database with MD5 password, set password for each PostgreSQL user.
# connect to own database

[cent@www ~]$ psql -d testdb

psql (12.1)
Type "help" for help.

# set or change own password
testdb=> \password
Enter new password:
Enter it again:
testdb=> \q

# also possible to set or change password for any users with PostgreSQL admin user

[postgres@www ~]$ psql -c "alter user cent with password 'password';"

ALTER ROLE


[4] . Verify settings to connect to PostgreSQL Database with MD5 password from remote hosts.

 
[root@node01 ~]# psql -h www.srv.world -d testdb -U cent

Password for user cent:   # password
psql (12.1)
Type "help" for help.

testdb=> # connected

Comments

Post a Comment

Popular posts from this blog

Java : Variables Declaring

[1]. Java Variables Variables are containers for storing data values. In Java, there are different types of variables, for example: String - stores text, such as "Hello". String values are surrounded by double quotes int - stores integers (whole numbers), without decimals, such as 123 or -123 float - stores floating point numbers, with decimals, such as 19.99 or -19.99 char - stores single characters, such as 'a' or 'B'. Char values are surrounded by single quotes boolean - stores values with two states: true or false [2]. Declaring (Creating) Variables To create a variable, you must specify the type and assign it a value: Syntax type variable = value ; Where type is one of Java's types (such as int or String), and variable is the name of the variable (such as x or name). The equal sign is used to assign values to the variable. To create a variable that should store text, look at the following example: Example Create a variable called  name  of type  Stri...
Read more

Install DNF in RHEL/CentOS 7

  Install DNF in RHEL/CentOS 7 DNF stands for Dandified yum. DNF is a software package manager for RPM-based Linux distributions such as Fedora, RHEL and CentOS. It is the next upcoming major version of Yum. DNF is first introduced in Fedora and has replaced to become the default package manager of the Fedora distributions. DNF is same as Yum that installs, updates and removes packages on RPM bas4ed Linux systems. DNF is introduced for improving the bottlenecks of Yum such as performance, Memory usages, Dependency resolution, speed, and some other factors. The latest stable release of DNF is 1.0 and it is written in Python.   Installation of DNF in RHEL/CentOS 7 1) To install DNF on RHEL/CentOS 7 systems, you need to set up and enable epel YUM REPO before installing DNF. # yum install epel-release 2) Install DNF # yum install DNF 3) You can now start to run commands using DNF. To view the man page you can use the following command: # dnf –help
Read more

SQL Self JOIN

SQL Self JOIN A self JOIN is a regular join, but the table is joined with itself. Self JOIN Syntax SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; T1 and T2 are different table aliases for the same table.
Read more