"Ubuntu дээр Postgresql гаднаас хандалттай болгож PGAdmin-аар холбогдох"-ны өөр хувилбарууд

02:12, 2 Арваннэгдүгээр сар 2022-ий байдлаарх одоогийн засвар

Гарчиг

1 Эрх авах
2 postgresql тохиргоо (postgresql.conf)
3 postgresql тохиргоо (pg_hba.conf)
4 postgresql дахин ачааллах
5 Firewall дээр порт нээх
6 pgAdmin 4-с хандах
7 postgresql SSL тохируулах

Эрх авах

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

X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх мөрийг нэмж өгнө:

host    dbName     dbUsername    X.X.X.0/24   md5

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

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

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

   systemctl restart postgresql

OR эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:

   systemctl restart postgresql@10-main.service

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

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

OR эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:

   systemctl restart postgresql@10-main.service

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@~#

(Харин pgAdmin 4-с холбогдохдоо SSL tab дээр sslmode=required тохируулж өгч болно.)

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 нэмэгдсэнийг анхаарна уу.

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

   systemctl restart postgresql

OR эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:

   systemctl restart postgresql@10-main.service

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 (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@~#

Харин pgAdmin 4-с холбогдохдоо SSL tab дээр sslmode=verify-ca ба сертификатын 3 файлаа тохируулж өгнө.

"Ubuntu дээр Postgresql гаднаас хандалттай болгож PGAdmin-аар холбогдох"-ны өөр хувилбарууд

02:12, 2 Арваннэгдүгээр сар 2022-ий байдлаарх одоогийн засвар

Гарчиг

Эрх авах

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

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

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

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

pgAdmin 4-с хандах

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

Generate SSL Certificates for PostgreSQL server

Enforcing SSL/TLS

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

Test the Connection

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

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

Test the Connection with client certificate

Хажуугийн цэс

Харагдацууд

Өөрийн багаж хэрэгслүүд

Залуурдлага

Хайлт

Хэрэглүүр

@@ 3-р мөр: / 3-р мөр: @@
 . Login as postgres user using su / sudo command, enter:
-    sudo su
+<pre>
-    su - postgres
+sudo su
+su - postgres
+</pre>
-=== postgresql тохиргоо 1 ===
+=== postgresql тохиргоо (postgresql.conf) ===
 . Edit the file:
-    vim /etc/postgresql/[version_number]/main/postgresql.conf
+<pre>
+vim /etc/postgresql/[version_number]/main/postgresql.conf
+</pre>
 Find configuration line that read as follows:
@@ 27-р мөр: / 31-р мөр: @@
-=== postgresql тохиргоо 2 ===
+=== postgresql тохиргоо (pg_hba.conf) ===
 . Edit the file:
      vim /etc/postgresql/[version_number]/main/pg_hba.conf
-Append the following configuration lines to give access to 10.10.1.0/24 network:
+#X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх мөрийг нэмж өгнө:
-     host ''database user'' 10.10.1.0/24 md5
+<pre>
+host    dbName     dbUsername    X.X.X.0/24   md5
+</pre>
+X.X.X.X, dbName, dbUsername тохирох утгуудыг оруулна.
+Анхаар: host гэсэн тохиргоо нь plain холболт тул секюрити эрсдэлтэй тул production орчинд hostssl тохируулах хэрэгтэй.
+Доорх зааврыг харна уу!!
-database, user хоёрын оронд "хандах баазын нэр" ба "хэрэглэгчийн нэрийг" оруулна. Бас энэ host гэсэн тохиргоо нь plain холболт тул секюрити эрсдэлтэй тул SSL тохируулах хэрэгтэй.
-Эхний ээлжинд туршиж холбож үзээд доор байгаа заавраар SSL тохируулаарай. Тэгээд host > hostssl гэж солих хэрэгтэй.
 === postgresql дахин ачааллах ===
@@ 43-р мөр: / 53-р мөр: @@
      systemctl restart postgresql
+OR
+эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:
+    systemctl restart postgresql@10-main.service
 === Firewall дээр порт нээх ===
@@ 59-р мөр: / 74-р мөр: @@
-Аль болох IP зааж өгвөл аюулгүй байдлын хувьд илүү. Мөн түр нээгээд хаах бол:
+Аль болох IP зааж өгвөл аюулгүй байдлын хувьд илүү.
+Мөн буцааж хаах тохиолдолд:
      ufw status numbered
@@ 88-р мөр: / 106-р мөр: @@
 === postgresql SSL тохируулах ===
 . SSL тохируулах
-(Эх сурвалж: https://blog.francium.tech/enabling-ssl-on-postgresql-2929bb4dca8f)
 ==== Generate SSL Certificates for PostgreSQL server ====
-Follow these steps to generate server certificate, trusted root certificate, and private key for the Postgres server.
 * Login as the postgres user and change the directory to the data directory:
@@ 101-р мөр: / 116-р мөр: @@
      cd /var/lib/postgresql/[version_number]/main
-. Generate a private key by entering a pass phrase:
+* 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 файлд доорх тохиргоог хийнэ
+<pre>
+ssl = on
+ssl_cert_file = 'server.crt'
+ssl_key_file = 'server.key'
+ssl_ca_file = 'root.crt'
+</pre>
+Жич: Тест орчинд бол шууд ssl-cert-snakeoil ашиглаж болох ч хугацаа нь бага бас илүү нууцлал багатай байдаг гэнэ.
+===== Enforcing SSL/TLS =====
-    openssl genrsa -des3 -out server.key 1024
+pg_hba.conf файлд доорх мөрүүдийг нэмнэ:
-. Remove the pass phrase to automatically start up the server using the following command:
+<pre>
+vim pg_hba.conf
+</pre>
-    openssl rsa -in server.key -out server.key
+X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх мөрүүдийг нэмнэ:
-. Run the following command to remove group and other’s permission from the private key file:
+<pre>
+hostssl    dbName     dbUsername    X.X.X.0/24   md5
+hostnossl all            all             X.X.X.0/24     reject
+</pre>
-    chmod og-rwx server.key
+OR
-. Run the following command to create a self-signed certificate:
+Эсвэл зөвхөн 1 X.X.X.X IP-аас хандахаар тохируулах үед доорх мөрүүдийг нэмнэ:
-     openssl req -new -key server.key -days 3650 -out server.crt -x509
+<pre>
+hostssl    dbName     dbUsername    X.X.X.X/32   md5
+hostnossl all            all             X.X.X.0/24    reject
+</pre>
-    Note: You will be asked to enter information that will be incorporated into your certificate request. For some fields, there will be a default value. If you enter ‘.’, the field will be left blank.
+'''X.X.X.X, dbName, dbUsername''' тохирох утгуудыг оруулна. Энэ зааврын дээд хэсэгт дурдсан дагуу host гээд plain холболтоор холбох боломжтой төстэй мөрийг өмнө нь оруулсан бол тэр мөрөө хасах хэрэгтэй.
-Output:
-Country Name (2 letter code) [XX]: MN
+==== postgresql дахин ачааллаж тохиргоог идэвхжүүлэх ====
-State or Province Name (full name) []: Ulaanbaatar
-Locality Name (eg, city) [Default City]: Ulaanbaatar
-Organization Name (eg, company) [Default Company Ltd]: Компанийнхаа нэрийг
-Organizational Unit Name (eg, section) []: Тасаг хэлтсийн нэрийг
-Common Name (eg, your name or your server's hostname) []: domain.tld
-Email Address []: email@domain.tld
-. For self-signed certificates, use the server certificate as the trusted root certificate:
+    systemctl restart postgresql
-    cp server.crt root.crt
+OR
+эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:
+    systemctl restart postgresql@10-main.service
-==== Edit posgresql config files ====
-Prepare PostgreSQL standalone for SSL authentication:
+==== Test the Connection ====
-To prepare the Postgres server for SSL authentication, run the following steps:
+Ингээд доорх байдлаар холбогдоод үзэхэд SSL connection гэж харагдаж байвал болсон гэсэн үг. X.X.X.X, dbName, dbUsername тохирох утгуудыг оруулна:
-* Edit the postgresql.conf file to activate SSL:
+<pre>
+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.
-    vim postgresql.conf
+postgres=#
+</pre>
-Uncomment and change the following parameters:
+Type \Q to exit the PostgreSQL client.
 <pre>
-ssl = on
+postgres=# \q
-ssl_cert_file = 'server.crt'
+root@~#
-ssl_key_file = 'server.key'
-ssl_prefer_server_ciphers = on
 </pre>
-===== Enforcing SSL/TLS =====
+(Харин pgAdmin 4-с холбогдохдоо SSL tab дээр sslmode=required тохируулж өгч болно.)
+==== 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
-pg_hba.conf файлд доорх мөрүүдийг нэмнэ. 10.10.1.0-255 IP-аас хандах боломжтой тохируулах үед доорх байдлаар:
+Ингээд root.crt, client.crt, client.key файлуудыг хуулж авна. Эдгээр файлуудыг дараа нь холбогдохдоо ашиглана. Эдгээр файлуудаа мэдээж маш сайн нууцлах шаардлагатай ба ил задгай дамжуулж болохгүй.
-<pre>
+X.X.X.0-255 IP-аас хандах боломжтой тохируулах үед доорх байдлаар өөрчилнө:
-vim pg_hba.conf:
-<pre>
 <pre>
-hostssl    database     user    10.10.1.0/24   md5
+hostssl    dbName     dbUsername    X.X.X.0/24   md5 clientcert=1
-hostnossl all            all             10.10.1.0/24     reject
+hostnossl all            all             X.X.X.0/24     reject
 </pre>
-OR зөвхөн 1 IP-аас хандахаар тохируулах үед
+OR
+эсвэл зөвхөн 1 X.X.X.X IP-аас хандахаар тохируулах үед доорх байдлаар өөрчилнө:
 <pre>
-hostssl    database     user    10.10.1.115/32   md5
+hostssl    dbName     dbUsername    X.X.X.X/32   md5 clientcert=1
-hostnossl all            all             10.10.1.0/24    reject
+hostnossl all            all             X.X.X.0/24    reject
 </pre>
+'''clientcert=1''' нэмэгдсэнийг анхаарна уу.
@@ 178-р мөр: / 244-р мөр: @@
      systemctl restart postgresql
+OR
+эсвэл олон хувилбар суусан бол тохируулсан хувилбарын сервисийг:
+    systemctl restart postgresql@10-main.service
-==== Test the Connection ====
-Connect to the database from another machine with the PostgreSQL client installed. Replace DatabaseName, 10.10.1.55, databaseUsername values with yours.
+==== 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 -d "dbname=DatabaseName sslmode=require" -h 10.10.1.55 -U databaseUsername
+     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 postgres:
+     Password for user dbUsername:
-     psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
+     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=#
@@ 199-р мөр: / 268-р мөр: @@
      postgres=# \q
+    root@~#
+Харин pgAdmin 4-с холбогдохдоо SSL tab дээр sslmode=verify-ca ба сертификатын 3 файлаа тохируулж өгнө.
 [[Ангилал:Сисадмин]]