Ubuntu дээр Postgresql гаднаас хандалттай болгож PGAdmin-аар холбогдох

Дусал нэвтэрхий толь-с
01:59, 2 Арваннэгдүгээр сар 2022-ий байдлаарх Almas (Яриа | оруулсан хувь нэмэр) хэрэглэгчийн хийсэн залруулга

Эрх авах

1. Login as postgres user using su / sudo command, enter: 

   sudo su
   su - postgres


postgresql тохиргоо (postgresql.conf)

2. Edit the file: 

   vim /etc/postgresql/[version_number]/main/postgresql.conf

Find configuration line that read as follows: 

   listen_addresses='localhost'

Дээрх мөрийг комэнт аут хийгээд доорхыг оронд нь нэмнэ. Энэ нь бүх хаягнаас хандах боломжийг нээнэ: 

   listen_addresses='*'

OR

Болж өгвөл * биш яг хандах IP хаягнуудаа таслалаар зааглан оруулах нь дээр 

   listen_addresses = '10.10.1.105,127.0.0.1,localhost'


postgresql тохиргоо (pg_hba.conf)

3. Edit the file: 

   vim /etc/postgresql/[version_number]/main/pg_hba.conf

Append the following configuration lines to give access to X.X.X.0/24 network: 

#X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх байдлаар:
host    dbName     dbUsername    X.X.X.0/24   md5

X.X.X.X, dbName, dbUsername тохирох утгуудыг оруулна. Анхаар: host гэсэн тохиргоо нь plain холболт тул секюрити эрсдэлтэй тул hostssl тохируулах хэрэгтэй. Доор зааврыг харна уу!!


postgresql дахин ачааллах

4. Ингээд postgresql дээрх тохиргоо болно. 

   systemctl restart postgresql

Firewall дээр порт нээх

5. ufw дээр порт нээж өгөх. 

   ufw allow 5432

OR 

   ufw allow from 10.10.1.0/24 to any port 5432

OR 

   ufw allow from 10.10.1.105 to any port 5432


Аль болох IP зааж өгвөл аюулгүй байдлын хувьд илүү.


Мөн буцааж хаах тохиолдолд: 

   ufw status numbered

тушаалаар харж байгаад урд талын дугаараар нь жишээ нь 5 дахь дүрмийг устгах бол: 

   ufw delete 5

гэх мэт буцааж арилгана.

Ингээд хандах боломжтой болно.

pgAdmin 4-с хандах

6. pgAdmin 4-с хандахдаа:

Servers баруун дараа Register > Server...

Нээгдсэн цонхонд Name дээр ямар нэгэн нэр оруулаад Connection tab дээр тохиргоо хийнэ:

  • Hostname
  • Port
  • Username
  • Password

утгуудыг тохируулаад холбогдоход холбогдох ёстой.

postgresql SSL тохируулах

7. SSL тохируулах

Generate SSL Certificates for PostgreSQL server

  • Login as the postgres user and change the directory to the data directory:
   sudo su
   su - postgres
   cd /var/lib/postgresql/[version_number]/main
  • Create a Certificate Signing Request (CN=X.X.X.X) CNs are important!!! IP or Domain if there is. -days 3650 шаардлагатай бол өөрчлөөрэй. CompanyName, DepartmentName, example@example.com, X.X.X.X зэрэг утгуудын оронд өөрийн тохирох утгаа оруулна.
   umask u=rw,go= && openssl req -days 3650 -new -text -nodes -subj '/C=MN/ST=Ulaanbaatar/L=Ulaanbaatar/O=CompanyName/OU=DepartmentName/emailAddress=example@example.com/CN=X.X.X.X' -keyout server.key -out server.csr
  • Generate self-signed certificate
   umask u=rw,go= && openssl req -days 3650 -x509 -text -in server.csr -key server.key -out server.crt
  • Also make the server certificate to be the root-CA certificate
   umask u=rw,go= && cp server.crt root.crt
  • Remove the now-redundant CSR
   rm server.csr

Ингээд postgresql.conf файлд доорх тохиргоог хийнэ 

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

Жич: Тест орчинд бол шууд ssl-cert-snakeoil ашиглаж болох ч хугацаа нь бага бас илүү нууцлал багатай байдаг гэнэ.

Enforcing SSL/TLS

pg_hba.conf файлд доорх мөрүүдийг нэмнэ: 

vim pg_hba.conf
<pre>

<pre>
#X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх байдлаар:
hostssl    dbName     dbUsername    X.X.X.0/24   md5
hostnossl all            all             X.X.X.0/24     reject

OR 

#зөвхөн 1 X.X.X.X IP-аас хандахаар тохируулах үед
hostssl    dbName     dbUsername    X.X.X.X/32   md5
hostnossl all            all             X.X.X.0/24    reject

X.X.X.X, dbName, dbUsername тохирох утгуудыг оруулна. Энэ зааврын дээд хэсэгт дурдсан дагуу host гээд plain холболтоор холбох боломжтой төстэй мөрийг өмнө нь оруулсан бол тэр мөрөө хасах хэрэгтэй.


postgresql дахин ачааллаж тохиргоог идэвхжүүлэх

   systemctl restart postgresql


Test the Connection

Ингээд доорх байдлаар холбогдоод үзэхэд SSL connection гэж харагдаж байвал болсон гэсэн үг. X.X.X.X, dbName, dbUsername тохирох утгуудыг оруулна: 

psql "host=X.X.X.X dbname=dbName user=dbUsername"
Password for user dbUsername: 
psql (14.5, server 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Type \Q to exit the PostgreSQL client. 

   postgres=# \q
   root@~#


Client сертификаттай холболт тохируулах (optional)

Зөвхөн нууц үг биш давхар хамгаалж секюритиг чангатгах боломжтой.

Generate client certificates to be used by clients/connections Create a Certificate Signing Request (CN=dbUsername гэдэг дээр өгөгдлийн баазын хэрэглэгчийн нэр) -days 3650 шаардлагатай бол өөрчлөөрэй. CompanyName, DepartmentName, example@example.com, dbUsername зэрэг утгуудын оронд өөрийн тохирох утгаа оруулна. 

   umask u=rw,go= && openssl req -days 3650 -new -nodes -subj '/C=MN/ST=Ulaanbaatar/L=Ulaanbaatar/O=CompanyName/OU=DepartmentName/emailAddress=example@example.com/CN=dbUsername' -keyout client.key -out client.csr

Create a signed certificate for the client using our root certificate 

   umask u=rw,go= && openssl x509 -days 3650 -req  -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt

Remove the now-redundant CSR 

   rm client.csr

Ингээд root.crt, client.crt, client.key файлуудыг хуулж авна. Эдгээр файлуудыг дараа нь холбогдохдоо ашиглана. Эдгээр файлуудаа мэдээж маш сайн нууцлах шаардлагатай ба ил задгай дамжуулж болохгүй. 


#X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх байдлаар:
hostssl    dbName     dbUsername    X.X.X.0/24   md5 clientcert=1
hostnossl all            all             X.X.X.0/24     reject

OR 

#зөвхөн 1 X.X.X.X IP-аас хандахаар тохируулах үед
hostssl    dbName     dbUsername    X.X.X.X/32   md5 clientcert=1
hostnossl all            all             X.X.X.0/24    reject

clientcert=1 нэмэгдсэнийг анхаарна уу.


Test the Connection with client certificate

Connect to the database from another machine with the PostgreSQL client installed. Replace dbName, X.X.X.X, dbUsername values with yours. 

   psql "host=X.X.X.X dbname=dbName user=dbUsername sslmode=verify-ca sslcert=client.crt sslkey=client.key sslrootcert=root.crt"

You should see the PostgreSQL prompt. 

   Password for user dbUsername:
   psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   postgres=#


Type \Q to exit the PostgreSQL client. 

   postgres=# \q
   root@~#

Харин pgAdmin 4-с холбогдохдоо SSL tab дээр 3 файлаа тохируулж өгнө.

"https://wiki.dusal.net/index.php?title=Ubuntu_дээр_Postgresql_гаднаас_хандалттай_болгож_PGAdmin-аар_холбогдох&oldid=537" хуудаснаас авсан