How to Access PostgreSQL from GUI

Hi, In this tutorial I will show you how to connect PostgreSQL server using GUI. Application that will we use for GUI is TablePlus. TablePlus is a series of graphical database management that we can use in Mac OS, Windows and Linux. In this tutorial I use TablePlus on Ubuntu 20.04 and install PostgreSQL on Centos 7.

 

1.     Setting postgresql.conf, pg_hba.conf and create password for PostgreSQL server

edit and uncomment file postgresql.conf with this command

$ nano /var/lib/pgsql/11/data/postgresql.conf

..

listen_addresses = ‘*’

..

 

after that edit pg_hba.conf and add your network with follow this command below.

$ nano /var/lib/pgsql/11/data/pg_hba.conf

..

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            trust

# IPv6 local connections:

host    all             all             ::1/128                 trust

# 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

host    all     all     101.xx.xx.xx/32 md5

 

Setting password with following command below.

$ su - postgres

Last login: Sab Mei  9 09:02:11 WIB 2020 on pts/1

Last failed login: Min Mei 10 20:17:50 WIB 2020 from vmi245834.contaboserver.net on ssh:notty

There were 21 failed login attempts since the last successful login.

-bash-4.2$ psql

psql (11.7)

Type "help" for help.

 

postgres=# \password

Enter new password:

Enter it again:

postgres=# \q

-bash-4.2$ exit

logout

 

now you need to restart PostgrSQL service with running command below

$ systemctl restart postgresql-11

2.     Setting firewall

after setting postgresql.conf and pg_hba, we need open firewall for Centos 7. you can adjust with your server enviroment

$firewall-cmd --zone=public --permanent –add-port=5432/tcp

$firewall-cmd --reload

3.     Setting TablePlus

open your TablePlus, then add new database, you will show the window configuration. Put your name connections, choose type database to PostgresSQL, put hostname, port, database and password. Make sure your setting true, then klik test connections.