PostgreSQL-ის დისტანციური კავშირი: psql, URI და SSL რეჟიმები

PostgreSQL-თან დისტანციური კავშირისთვის ხუთი რამ უნდა ემთხვეოდეს ერთმანეთს: host და პორტი (5432, თუ ვინმემ არ შეცვალა), ბაზის სახელი, როლი პაროლით, სერვერი, რომელიც უსმენს მისამართზე, რომელსაც შეგიძლია მიწვდე, და ხაზი pg_hba.conf-ში, რომელიც შენს მისამართს უშვებს. ხუთივე სწორია და connection string ერთი ხაზია. ერთი არასწორია და მიიღებ შეცდომას, რომელიც არასწორ დამნაშავეს ასახელებს, ამიტომ ეს პოსტი იმდენ დროს უთმობს წარუმატებლობებს, რამდენსაც მოწყობას.

PostgreSQL-ის ნაგულისხმევი ინსტალაცია დისტანციურ კავშირებს განზრახ უარყოფს. ის მხოლოდ localhost-ზე უსმენს და მისი host-based ავტორიზაციის ფაილი ლოკალური წესების გარდა ცარიელია. ეს გონივრული ნაგულისხმევია ბაზისთვის შენს ლეპტოპზე და უსარგებლოა ბაზისთვის, რომელსაც აპლიკაცია უნდა მიწვდეს, ამიტომ ვინმემ უნდა შეცვალოს - ან შენ, შენს მანქანაზე, ან ჰოსტმა, სანამ მონაცემებს გადმოგცემს.

რა სჭირდება დისტანციურ კავშირს რეალურად#

როცა რამე არ უკავშირდება, გაიარე ეს რიგით. თითოეული განსხვავებულ შეცდომას იძლევა, და რიგითობა მნიშვნელოვანია, რადგან ადრეული წარუმატებლობა შემდგომ შემოწმებებს აზრს უკარგავს.

1. DNS და მარშრუტიზაცია. host-ის სახელი გარკვეულია და პაკეტები მანქანამდე აღწევს. ping არც ერთს ერთპირადად არ ამტკიცებს, რადგან ICMP ხშირად დაბლოკილია, TCP კი კარგად მუშაობს.
2. მოსმენადი socket. listen_addresses ფაილში postgresql.conf მოიცავს მისამართს, რომელზეც პაკეტები მოდის. ნაგულისხმევია localhost; '*' ნიშნავს ყველა ინტერფეისს. შეცვლას restart სჭირდება და არა reload.
3. გზა firewall-ში. პორტი 5432/tcp შემომავალი, სასურველია მხოლოდ იმ მისამართებიდან, რომლებმაც უნდა გამოიყენონ. firewall წესები, რომლებიც მნიშვნელოვანია გონივრული წესების ნაკრების ფორმას განიხილავს.
4. შესაბამისი ხაზი `pg_hba.conf`-ში. Host-based ავტორიზაცია მოწმდება თითო კავშირზე წყაროს მისამართის, ბაზის, როლისა და კავშირის ტიპის მიხედვით. პირველი დამთხვევა იმარჯვებს, და დაუმთხვევლობა უარია და არა გადასვლა შემდეგზე.
5. მონაცემები, რომელთა წარდგენაც როლს შეუძლია. პაროლი scram-sha-256-ით, კლიენტის სერტიფიკატი cert-ით, ან არაფერი trust-ით, რომელიც არასდროს უნდა გამოჩნდეს ინტერნეტიდან მისაწვდომ არაფერზე.

Connection string ნაწილ-ნაწილ#

libpq-ზე აგებული PostgreSQL კლიენტები ორ ფორმატს იღებენ, და ენების უმეტესი driver-ი მინიმუმ პირველს.

postgresql://appuser:s3cret@db.example.com:5432/shop?sslmode=requirehost=db.example.com port=5432 dbname=shop user=appuser sslmode=require

URI ფორმა იღებს postgresql://-ს ან უფრო მოკლე postgres://-ს; ორივე ერთი და იგივე scheme-ია. ?-ის შემდეგ ყველაფერი libpq პარამეტრია, და სასარგებლოები სახელით ღირს ცოდნად:

ორი დეტალი ხალხს აბნევს. პირველი არის percent-encoding: პაროლი, რომელიც შეიცავს @, :, /, ?, # ან %-ს, URI-ს ამტვრევს, თუ ეს სიმბოლოები არ არის escape-ირებული (@ ხდება %40). გენერირებულ პაროლებს სწორედ ეს სიმბოლოები უყვართ. თუ string სწორად გამოიყურება და კლიენტი უცნაურ host-ის სახელს იუწყება, მიზეზი escape-ის გარეშე დარჩენილი @-ია. keyword ფორმას ასეთი პრობლემა არ აქვს, რაც მისი არჩევის მიზეზია shell სკრიპტებში.

მეორე ისაა, რომ connection string-ის ყველა ნაწილს გარემოს ცვლადის ექვივალენტი აქვს, და გამოტოვებული ნაწილი მათზე ეშვება: PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD, PGSSLMODE, PGAPPNAME. psql არგუმენტების გარეშე ცდილობს დაუკავშირდეს ბაზას, რომელსაც შენი ოპერაციული სისტემის მომხმარებლის სახელი ჰქვია, ლოკალურ socket-ზე, სწორედ ამიტომ ჩავარდება შიშველი psql ახალ მანქანაზე დამაბნეველი შეტყობინებით socket-ის შესახებ /var/run/postgresql-ში.

პაროლის PGPASSWORD-ში შენახვა მას პროცესის გარემოში ათავსებს, სადაც მანქანაზე ნებისმიერს შეუძლია წაიკითხოს. უკეთესი ჩვევა არის password ფაილი:

db.example.com:5432:shop:appuser:s3cretdb.example.com:5432:*:readonly:other-secret

ის უნდა იყოს chmod 0600, თორემ libpq მას ჩუმად უგულებელყოფს. Windows-ზე იგივე ფაილი მდებარეობს %APPDATA%\postgresql\pgpass.conf-ზე და უფლებები არ მოწმდება. wildcard-ები დაშვებულია პირველ ოთხ ველში. აპლიკაციის საიდუმლოებებისთვის გარემო ისევ ჩვეულებრივი პასუხია - იხილე გარემოს ცვლადები და საიდუმლოებები, როგორ დაიცვა ისინი შენი რეპოზიტორიის გარეთ.

psql და ბრძანებები, რომლებიც ღირს ცოდნად#

psql საცნობარო კლიენტია და ის, რომელიც ყველა შეცდომის შეტყობინებას ეგულება. მის მისაღებად PostgreSQL სერვერი არ გჭირდება: Debian-სა და Ubuntu-ზე ეს არის postgresql-client, Fedora-ზე postgresql, macOS-ზე brew install libpq (და მის bin დირექტორიას PATH-ში დაამატებ), ხოლო Windows-ზე ოფიციალური installer გაძლევს სერვერის მოხსნისა და command-line ხელსაწყოების დატოვების საშუალებას.

ახალი psql ძველ სერვერთან სრულყოფილად საუბრობს. პირიქით მუშაობს ჩვეულებრივი მოთხოვნებისთვის, მაგრამ არა pg_dump-ისთვის, რომელიც უარს ამბობს თავისზე უფრო ახალი სერვერიდან dump-ზე. შეარჩიე კლიენტი უახლეს სერვერზე, რომელსაც ეხები.

$ psql "postgresql://appuser@db.example.com:5432/shop?sslmode=require"$ psql -h db.example.com -U appuser -d shop -c "select version();"$ psql -h db.example.com -U appuser -d shop -f schema.sql

შიგნით მოხვედრის შემდეგ სამუშაოს უმეტესობას backslash ბრძანებები აკეთებენ:

\copy ის არის, რასაც ხალხი ვერ ამჩნევს. COPY orders TO '/tmp/orders.csv' სრულდება სერვერზე, წერს სერვერის დისკზე და პრივილეგირებულ როლს საჭიროებს. \copy orders TO 'orders.csv' CSV HEADER იმავე მოთხოვნას ასრულებს და ფაილს შენს მანქანაზე წერს, რაც თითქმის ყოველთვის ის არის, რაც გინდოდა.

SSL რეჟიმები: რას იცავს თითოეული#

sslmode არის სპექტრი "არ შეწუხდე"-დან "დამიმტკიცე, რომ ის სერვერი ხარ, რომელსაც ვთხოვე"-მდე, და მისი შუა ნაწილი უფრო სუსტია, ვიდრე ჩანს.

prefer არის libpq-ს ნაგულისხმევი და სწორედ ის არის საშიში: კავშირი, რომელიც დაშიფრული უნდა იყოს, ჩუმად წავა ღია ტექსტით, თუ სერვერზე TLS-თან რამე არასწორად მოხდება. require შიფრავს, მაგრამ იღებს ნებისმიერ სერტიფიკატს, მათ შორის იმას, რომელსაც შენსა და ბაზას შორის მყოფი წარადგენს. მხოლოდ verify-ca და verify-full ართულებენ ჩარევას, და მხოლოდ verify-full იჭერს სერტიფიკატს, რომელიც ვალიდურია, მაგრამ არასწორი მანქანისთვის.

მათ გამოსაყენებლად CA ფაილი გჭირდება. PostgreSQL 16 და უფრო ახალი იღებს sslrootcert=system-ს, რაც ოპერაციული სისტემის ნდობის საცავს ნიშნავს - სწორი პასუხი, როცა ბაზა საჯარო CA-ს სერტიფიკატს წარადგენს. სხვა შემთხვევაში მიუთითე sslrootcert ფაილზე, რომელსაც შენი ჰოსტი გაძლევს, რაც self-signed მოწყობისას სერვერის საკუთარი სერტიფიკატია.

postgresql://appuser@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=system

არა-libpq driver-ებს საკუთარი ნაგულისხმევები აქვთ და ისინი ერთნაირი არ არის. JDBC driver-ი და რამდენიმე Go და .NET driver-ი TLS-ს არ აწარმოებს, სანამ არ მოთხოვ. რეჟიმი ყოველთვის ცალსახად დააყენე და არა ნაგულისხმევს ენდო, რომელიც არ წაგიკითხავს.

სერვერის მხარე: მოსმენა, pg_hba.conf და firewall#

თუ სერვერს თვითონ ადმინისტრირებ, ვინ შემოვა ორი ფაილით კონტროლდება. postgresql.conf წყვეტს, სად უსმენს სერვერი:

listen_addresses = '*'port = 5432password_encryption = scram-sha-256

pg_hba.conf წყვეტს, ვინ არის დაშვებული, როცა მოვა. ის იკითხება ზემოდან ქვემოთ და პირველი ხაზი, რომლის კავშირის ტიპი, ბაზა, მომხმარებელი და წყაროს მისამართი ყველა ემთხვევა, არის გამოყენებული - მაშინაც კი, თუ ის გიარებს და შემდგომი ხაზი გაგიშვებდა.

# TYPE   DATABASE  USER      ADDRESS            METHODlocal    all       postgres                     peerhostssl  shop      appuser   203.0.113.10/32    scram-sha-256hostssl  shop      readonly  203.0.113.0/24     scram-sha-256host     all       all       0.0.0.0/0          reject

hostssl ემთხვევა მხოლოდ TLS კავშირებს, ასე ხდი დაშიფვრას სავალდებულოს სერვერის მხარეს იმის იმედის ნაცვლად, რომ კლიენტმა მოითხოვა. host ორივეს ემთხვევა. hostnossl ემთხვევა მხოლოდ დაუშიფრავებს და უმეტესად მათ უარსაყოფად არსებობს. ამ ფაილის ცვლილებას reload სჭირდება და არა restart:

SELECT pg_reload_conf();

listen_addresses და port განსხვავებულია - ისინი იკითხება მხოლოდ გაშვებისას, ამიტომ მათ შეცვლას restart სჭირდება. მართულ ბაზაზე ამ ყველაფრიდან არაფერი შეიძლება გქონდეს გასაკეთებელი, რადგან სერვერი უკვე უსმენს და წესები უკვე დაწერილია; ჰოსტებს შორის განსხვავება არის, კონფიგურაციის რამდენის შეხების უფლებას გაძლევენ. შეამოწმე, სანამ ცვლილებას დაგეგმავ.

RE:NODE-ზე ბაზების ხაზებია PostgreSQL და MongoDB, თითოეულ გეგმას ერთი განაწილება მოყვება, და უკავშირდები ამ host-სა და პორტზე superuser პაროლით, რომელიც პანელმა ამ სერვერისთვის შექმნა და არა ნაგულისხმევით, რომელიც ერთი image-ის ყველა ინსტალაციას საერთო აქვს. რადგან ბაზას აპლიკაცია აღწევს პორტზე და არა ბრაუზერი, ამ გეგმებს reverse-proxy სლოტი არ აქვს - 5432-ის წინ დასაყენებელი სასარგებლო არაფერია.

GUI კლიენტები და tunnel-ები#

ყველა desktop კლიენტს იგივე ხუთი ველი უნდა, პლუს ადგილი SSL რეჟიმისთვის. pgAdmin 4 ოფიციალურია და ყველაზე მძიმე; DBeaver ჩვეულებრივი cross-platform არჩევანია; TablePlus და DataGrip ფასიანებია, რომლებთანაც ხალხი რჩება. ყველა მათგანი სიამოვნებით დაუკავშირდება SSL-ის გამორთვით, ამიტომ კავშირის შექმნის შემდეგ შეამოწმე ეს პარამეტრი და ნუ ივარაუდებ.

სადაც კლიენტი სასარგებლოა და ტერმინალი არა: უცნობი სქემის დათვალიერება, განიერი შედეგების ნახვა და მნიშვნელობის ხელით შეცვლა ინციდენტის დროს. სადაც უარესია: ყველაფერი, რის გამეორებასაც მოისურვებ, რადგან .sql ფაილში შენახული და psql -f-ით გაშვებული მოთხოვნა გადასახედია, ხოლო კლიკი - არა.

თუ მანქანა shell წვდომას გაძლევს, SSH tunnel ბაზის პორტს საჯარო ქსელს მთლიანად აშორებს:

$ ssh -N -L 5433:127.0.0.1:5432 you@vds.example.com$ psql -h 127.0.0.1 -p 5433 -U appuser -d shop

მაშინ ბაზა მხოლოდ localhost-ზე უსმენს და ერთადერთი გამოჩენილი პორტი SSH-ია. ეს სწორი შაბლონია VDS-ზე, რომელსაც თავად ადმინისტრირებ - იხილე SSH გასაღებები და გამაგრება - მაგრამ მას სჭირდება SSH ანგარიში მანქანაზე, რაც მართული ბაზის გეგმას აუცილებლად არ მოყვება. პანელზე დაფუძნებულ ჰოსტზე ექვივალენტური დაცვა არის წყაროს მისამართების შეზღუდვა და verify-full-ის გამოყენება.

აპლიკაციის კოდიდან დაკავშირება#

ყველა driver ერთი და იგივე პარამეტრების თხელი ჩარჩოა. მთელი string ერთ გარემოს ცვლადში შეინახე, ჩვეულებრივ DATABASE_URL-ში, და driver-ს დაუტოვე მისი დამუშავება.

import { Pool } from "pg";const pool = new Pool({  connectionString: process.env.DATABASE_URL,  max: 10,  idleTimeoutMillis: 30_000,  connectionTimeoutMillis: 5_000,});const { rows } = await pool.query("select id, total from orders where id = $1", [id]);

import osimport psycopgwith psycopg.connect(os.environ["DATABASE_URL"], connect_timeout=5) as conn:    with conn.cursor() as cur:        cur.execute("select id, total from orders where id = %s", (order_id,))        row = cur.fetchone()

SQLAlchemy   postgresql+psycopg://appuser:s3cret@db.example.com:5432/shopDjango       DATABASES["default"] = dj_database_url.config()  # reads DATABASE_URLGo (pgx)     pgxpool.New(ctx, os.Getenv("DATABASE_URL"))JDBC         jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full

ორი წესი ენის მიუხედავად მოქმედებს. გამოიყენე placeholder-ები ($1, %s, ?) და არასდროს სტრიქონების შეერთება - სწორედ ეს ხდის SQL injection-ს შეუძლებელს და არა ნაკლებ სავარაუდოს. და pool გახსენი ერთხელ გაშვებისას და არა კავშირი თითო მოთხოვნაზე: PostgreSQL-ის ყოველი კავშირი backend პროცესია საკუთარი მეხსიერებით, და ასი მათგანი ავნებს გაცილებით ადრე, ვიდრე დაგეხმარება. Connection pool-ები და ლიმიტები შეიცავს ზომის არითმეტიკას, და ეს იგივე არითმეტიკაა, რომელიც max_connections-ს განსაზღვრავს PostgreSQL-ის დაზუსტება პატარა სერვერებისთვის-ში.

შეცდომები, რომლებსაც რეალურად ნახავ#

`connection refused` - ამ მისამართსა და პორტზე არავინ უსმენს, ან firewall-მა პაკეტი reset-ით გადააგდო. შეამოწმე listen_addresses, შეამოწმე მუშაობს თუ არა სერვისი, შეამოწმე პორტი. ეს შეცდომა ავტორიზაციიდან არასდროს მოდის.

`timeout expired` ან შეჩერება - პაკეტები სიცარიელეში მიდის. firewall, რომელიც აგდებს და არ უარყოფს, არასწორი host-ის სახელი ან მისამართი, რომელზეც მანქანას მარშრუტი არ აქვს. "refused"-გან განსხვავებული მიზეზი, მიუხედავად იმისა, რომ ერთნაირად ჩანს.

`no pg_hba.conf entry for host "203.0.113.10", user "appuser", database "shop", no encryption` - სერვერს მიაღწიე და მან თავისი წესები წაიკითხა. ბოლო ფრაზა სასარგებლო ნაწილია: no encryption ნიშნავს, რომ დაუკავშირდი ღია ტექსტით და დაემთხვა მხოლოდ hostssl ხაზები. დააყენე sslmode=require და სცადე თავიდან, სანამ რამეს დაარედაქტირებ.

`password authentication failed for user "appuser"` - როლი არსებობს და პაროლი არასწორია, ან როლი საერთოდ არ არსებობს. PostgreSQL განზრახ არ არჩევს. შეამოწმე ბოლოში დარჩენილი ჰარი და პაროლი, რომელიც percent-decode-დ განსხვავებულად აღიქვა, ვიდრე ელოდი.

`database "shop" does not exist` - სერვერს დაუკავშირდი. გაუშვი psql -l, რომ ნახო, რა არის იქ; სახელი რეგისტრმგრძნობიარეა, თუ ბრჭყალებით შეიქმნა.

`FATAL: sorry, too many clients already` - max_connections სავსეა. თითქმის ყოველთვის აპლიკაცია, რომელიც კავშირებს ხსნის და არ აბრუნებს, და არა ნამდვილი დატვირთვა.

`server closed the connection unexpectedly` - backend მოკვდა ან შუაში რაღაცამ დანებდა. შეხედე სერვერის ლოგს out-of-memory გაჩერებისთვის და ნებისმიერ load balancer-ს ან NAT მოწყობილობას idle timeout-ისთვის, რომელიც შენი pool-ის timeout-ზე მოკლეა.

`SSL error: certificate verify failed` - verify-ca ან verify-full CA ფაილით, რომელიც სერვერის სერტიფიკატს არ აწერს ხელს, ან host-ის სახელით, რომელიც არ ემთხვევა. შეადარე სახელი, რომელსაც უკავშირდები, სერტიფიკატის subject-ს.

ორი ჩვევა ამ ყველაფერს ამოკლებს. შეამოწმე psql-ით, სანამ აპლიკაციას დაადანაშაულებ: ის იგივე ბიბლიოთეკას იყენებს და რეალურ შეცდომას აცხადებს, ORM კი მას სამ ფენაში შეახვევს. და შეცვალე ერთი რამ ერთდროულად, მუშა ლოკალური კავშირიდან გარეთ დაწყებული.

FAQ#

რომელ პორტს იყენებს PostgreSQL?

ნაგულისხმევად 5432/tcp. ეს მხოლოდ კონვენციაა, რომელიც port პარამეტრით დგინდება, და მისი გადატანა მსუბუქი ბუნდოვნებაა და არა უსაფრთხოება - სკანერები ბაზას ნებისმიერ პორტზე წუთებში პოულობენ. რეალურად წყაროს მისამართების შეზღუდვა გეხმარება.

მჭირდება PostgreSQL ლოკალურად დაყენებული psql-ის გამოსაყენებლად?

არა. დააყენე მხოლოდ კლიენტის პაკეტი: postgresql-client Debian-სა და Ubuntu-ზე, libpq Homebrew-დან macOS-ზე, ან ოფიციალური Windows installer სერვერის კომპონენტის მოხსნით. ახალი კლიენტი ძველ სერვერს პრობლემის გარეშე უკავშირდება.

რატომ მუშაობს კავშირი ჩემი ლეპტოპიდან და არა აპლიკაციის სერვერიდან?

იმიტომ, რომ pg_hba.conf წყაროს მისამართს ემთხვევა. შენი ლეპტოპის მისამართი დაშვებულია და აპლიკაციის სერვერისა არა, ან აპლიკაციის სერვერი სხვა საჯარო მისამართიდან გადის, ვიდრე ფიქრობ. შეამოწმე ზუსტი მისამართი შეცდომის შეტყობინებაში - სერვერი ბეჭდავს იმას, რომელიც დაინახა.

საკმარისია sslmode=require?

ის ტრაფიკს შიფრავს, რაც პასიურ დაფიქსირებას აჩერებს, მაგრამ იღებს ნებისმიერ სერტიფიკატს, ამიტომ არ აჩერებს აქტიურ თავდამსხმელს, რომელსაც შენი ტრაფიკის გადამისამართება შეუძლია. გამოიყენე verify-full root სერტიფიკატით, როცა კავშირი ქსელს კვეთს, რომელსაც არ აკონტროლებ.

რამდენი კავშირი უნდა გახსნას ჩემმა აპლიკაციამ?

ნაკლები, ვიდრე ფიქრობ. ათიდან ოცამდე pool აპლიკაციის პროცესზე უმეტესი დატვირთვისთვის სავსებით საკმარისია, და ჯამი ყველა პროცესსა და ფონურ worker-ზე max_connections-ში უნდა ეტეოდეს ადმინისტრატორისთვის დატოვებული ადგილით. 1-2 GB ბაზის სერვერზე ეს ჯამი დაბალ ათეულებში უნდა იყოს.

შემიძლია დავუკავშირდე ტელეფონიდან ან ლეპტოპიდან მობილური ინტერნეტით?

შეგიძლია, მაგრამ წყაროს მისამართი მუდმივად იცვლება, რაც ნიშნავს ან ფართო pg_hba.conf წესს, ან tunnel-ს. ამჯობინე tunnel ან აპლიკაციის endpoint ბაზის წინ და არა 5432-ის მთელი მსოფლიოსთვის გახსნა.