Security - PostgreSQL

 

=====================================================================

====================================================================

================IMPLEMENTATION=====================

-bash-4.1$ psql -p 5432

Password: 

psql.bin (10.9)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of 

-----------+------------------------------------------------------------+-----------

 edbstore  |                                                            | {}

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create user edbuser password '123456';

CREATE ROLE

postgres=# create database edb1;

CREATE DATABASE

postgres=# create database edb2;

CREATE DATABASE

postgres=# 

-bash-4.1$ pwd

/opt/PostgreSQL/10

-bash-4.1$ cd data/

-bash-4.1$ vi pg_hba.conf 

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   edb1            edbuser                                 md5

local   edb2            edbstore                                trust

local   all             postgres                                reject

# IPv4 local connections:

:wq

-bash-4.1$ pg_ctl -D ../data/ reload

server signaled

-bash-4.1$ psql -p 5432 -d edb1 -U edbuser

Password for user edbuser: 

psql.bin (10.9)

Type "help" for help.

edb1=> \q

-bash-4.1$ psql -p 5432 -d edb2 -U edbstore

psql.bin (10.9)

Type "help" for help.

edb2=> \q

-bash-4.1$ psql -p 5432 -d edb2 -U postgres

psql.bin: FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "edb2", SSL off

-bash-4.1$ psql -p 5432 -d edb1 edbstore

psql.bin: FATAL:  no pg_hba.conf entry for host "[local]", user "edbstore", database "edb1", SSL off

-bash-4.1$ 

-bash-4.1$ psql -p 5432

psql.bin: FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "postgres", SSL off

psql -p 5433 -U postgres -d postgres -h 192.168.2.1

172.18.4.174

IN CASE IF YOU USE "edbstore" CLUSTER

vi postgresql.conf

listen_addresses='*'

:wq

vi pg_hba.conf

under ip4 block

host    all        all        0.0.0.0/0    trust

:wq

pg_ctl -D edbstore restart

connect as root user

root# service iptables stop

FINALLY CONNECT FROM WINDOWS PGADMIN TOOL