ติดตั้ง PostgreSQL และสร้างผู้ใช้บน Ubuntu

PostgreSQL เป็นฐานข้อมูลเชิงสัมพันธ์แบบโอเพนซอร์สที่นิยมใช้กับแอปพลิเคชันยุคใหม่ เช่น Django, Rails, Odoo และระบบที่ต้องการฟีเจอร์ขั้นสูงอย่าง JSONB หรือ Full-text Search บทความนี้อธิบายการติดตั้ง PostgreSQL บน Ubuntu 22.04 และ 24.04 ทั้งจาก Repository ของ Ubuntu และจาก Repository ทางการของ PostgreSQL จากนั้นสร้างผู้ใช้และฐานข้อมูลสำหรับแอปพลิเคชัน พร้อมตัวอย่างผู้ใช้แบบอ่านอย่างเดียว

สิ่งที่ผู้ที่เคยใช้ MySQL มักสับสนคือ PostgreSQL แยกระหว่าง "ผู้ใช้ของระบบปฏิบัติการ" กับ "Role ของฐานข้อมูล" และการยืนยันตัวตนผ่าน Socket ภายในเครื่องจะใช้ชื่อผู้ใช้ Linux เป็นหลัก บทความนี้จะอธิบายจุดนี้ไว้ด้วย

สิ่งที่ต้องเตรียม

• Cloud Server ที่ติดตั้ง Ubuntu 22.04 หรือ 24.04 และเข้าผ่าน SSH ได้
• ผู้ใช้ที่มีสิทธิ์ sudo ดู สร้างผู้ใช้ใหม่และให้สิทธิ์ sudo บน Linux
• RAM อย่างน้อย 1 GB สำหรับการใช้งานทั่วไป ถ้า RAM น้อยควรเพิ่ม Swap ตาม เพิ่ม Swap File บน Linux Server

ขั้นตอนที่ 1: เลือกเวอร์ชันที่จะติดตั้ง

ถ้าแอปพลิเคชันไม่ได้กำหนดเวอร์ชัน การใช้แพ็กเกจของ Ubuntu เป็นทางเลือกที่ดูแลง่ายที่สุด

ขั้นตอนที่ 2: ติดตั้ง PostgreSQL

แบบที่ 1: จาก Repository ของ Ubuntu

sudo apt update
sudo apt install postgresql postgresql-contrib

แพ็กเกจ postgresql-contrib มีส่วนขยายที่ใช้บ่อย เช่น pg_stat_statements และ pgcrypto (บน Ubuntu รุ่นใหม่ส่วนขยายเหล่านี้รวมมากับแพ็กเกจหลักแล้ว การสั่งติดตั้งซ้ำไม่มีผลเสีย)

แบบที่ 2: จาก Repository ทางการของ PostgreSQL

ใช้สคริปต์ของแพ็กเกจ postgresql-common ตามคู่มือทางการที่ postgresql.org/download/linux/ubuntu เพื่อเพิ่ม Repository แล้วติดตั้งเวอร์ชันที่ต้องการ (ตัวอย่างนี้ใช้ 17)

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install postgresql-17

ไม่ว่าจะติดตั้งแบบใด ระบบจะสร้าง Cluster ชื่อ main สร้างผู้ใช้ Linux ชื่อ postgres และเริ่ม Service ให้อัตโนมัติ

ขั้นตอนที่ 3: ตรวจสอบว่า Service ทำงาน

pg_lsclusters

ผลลัพธ์ควรมีลักษณะดังนี้ (เลขเวอร์ชันขึ้นกับที่ติดตั้ง)

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

บน Ubuntu Service ชื่อ postgresql เป็นเพียงตัวครอบ ส่วน Cluster ที่ทำงานจริงคือ postgresql@16-main ถ้าต้องการดูสถานะหรือ Log ให้ระบุชื่อเต็ม

sudo systemctl status postgresql@16-main
sudo systemctl enable postgresql

ไฟล์ตั้งค่าอยู่ที่ /etc/postgresql/<เวอร์ชัน>/main/ ได้แก่ postgresql.conf และ pg_hba.conf ส่วนข้อมูลอยู่ที่ /var/lib/postgresql/<เวอร์ชัน>/main ค่าเริ่มต้นรับการเชื่อมต่อจาก localhost เท่านั้น ซึ่งปลอดภัยที่สุด

ขั้นตอนที่ 4: เข้าสู่ psql ในฐานะผู้ดูแล

ค่าเริ่มต้นของ Ubuntu ใช้วิธีตรวจสอบแบบ peer สำหรับการเชื่อมต่อผ่าน Socket ภายในเครื่อง หมายความว่าผู้ใช้ Linux ชื่อ postgres จะเข้าเป็น Role postgres ได้โดยไม่ต้องใช้รหัสผ่าน

sudo -u postgres psql

จะได้ Prompt postgres=# คำสั่งพื้นฐานของ psql ที่ใช้บ่อย

ขั้นตอนที่ 5: สร้างผู้ใช้และฐานข้อมูลสำหรับแอปพลิเคชัน

ภายใน psql ให้สร้าง Role ที่ล็อกอินได้พร้อมรหัสผ่าน แล้วสร้างฐานข้อมูลโดยให้ Role นี้เป็นเจ้าของ (เปลี่ยนรหัสผ่านตัวอย่างเป็นรหัสที่คาดเดายาก)

CREATE ROLE appuser WITH LOGIN PASSWORD 'เปลี่ยนเป็นรหัสผ่านที่ปลอดภัย';
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8';

ผลลัพธ์ที่ถูกต้องคือข้อความ CREATE ROLE และ CREATE DATABASE

การให้ Role เป็นเจ้าของฐานข้อมูลสำคัญมากตั้งแต่ PostgreSQL 15 เป็นต้นไป เพราะผู้ใช้ทั่วไปไม่มีสิทธิ์สร้างตารางใน Schema public อีกต่อไป แต่เจ้าของฐานข้อมูลยังสร้างได้ตามปกติ ถ้าสร้างฐานข้อมูลโดยให้ postgres เป็นเจ้าของ แอปพลิเคชันจะเจอ Error permission denied for schema public

ถ้าต้องการสร้างจาก Shell โดยไม่เข้า psql ใช้คำสั่งที่มากับ PostgreSQL ได้เช่นกัน

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb

ขั้นตอนที่ 6: สร้างผู้ใช้แบบอ่านอย่างเดียว (ทางเลือก)

เหมาะสำหรับเครื่องมือรายงานหรือทีมที่ต้องการดูข้อมูลแต่ไม่ควรแก้ไขได้ ทำในฐานะ postgres หลังจากแอปพลิเคชันสร้างตารางแล้ว

CREATE ROLE reporter WITH LOGIN PASSWORD 'รหัสผ่านอีกชุดหนึ่ง';
GRANT CONNECT ON DATABASE appdb TO reporter;
\c appdb
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
ALTER DEFAULT PRIVILEGES FOR ROLE appuser IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

คำสั่ง GRANT SELECT ON ALL TABLES ให้สิทธิ์กับตารางที่มีอยู่ตอนนี้เท่านั้น ส่วน ALTER DEFAULT PRIVILEGES ทำให้ตารางที่ appuser สร้างในอนาคตอ่านได้ด้วย

ตรวจสอบผลลัพธ์

ทดสอบเข้าสู่ระบบด้วยผู้ใช้ใหม่ผ่าน TCP ภายในเครื่อง ซึ่งค่าเริ่มต้นใช้รหัสผ่านแบบ scram-sha-256

psql -h 127.0.0.1 -U appuser -d appdb

เมื่อใส่รหัสผ่านถูกจะได้ Prompt appdb=> ลองสร้างและลบตารางทดสอบ

CREATE TABLE ping (id serial PRIMARY KEY, note text);
INSERT INTO ping (note) VALUES ('ทดสอบภาษาไทย');
SELECT * FROM ping;
DROP TABLE ping;

ถ้าทุกคำสั่งสำเร็จ แปลว่าผู้ใช้มีสิทธิ์ครบและ Encoding รองรับภาษาไทย ตรวจเวอร์ชันได้ด้วย SELECT version();

ปัญหาที่พบบ่อย

FATAL: Peer authentication failed for user "appuser"

เกิดเมื่อสั่ง psql -U appuser โดยไม่ใส่ -h ทำให้เชื่อมต่อผ่าน Socket ซึ่งใช้วิธี peer และชื่อผู้ใช้ Linux ไม่ตรงกับ Role แก้โดยเพิ่ม -h 127.0.0.1 ให้เชื่อมต่อผ่าน TCP ซึ่งใช้รหัสผ่าน ไม่แนะนำให้เปลี่ยน peer เป็น trust เพราะใครก็ตามที่อยู่ในเครื่องจะเข้าฐานข้อมูลได้โดยไม่ต้องใช้รหัสผ่าน

ERROR: permission denied for schema public

ฐานข้อมูลไม่ได้เป็นของ Role ที่แอปพลิเคชันใช้ แก้ด้วยคำสั่งต่อไปนี้ในฐานะ postgres

ALTER DATABASE appdb OWNER TO appuser;
\c appdb
ALTER SCHEMA public OWNER TO appuser;

แอปพลิเคชันจากเครื่องอื่นเชื่อมต่อไม่ได้

เป็นพฤติกรรมปกติ เพราะ PostgreSQL ฟังเฉพาะ localhost หากต้องเปิดให้เชื่อมต่อจากภายนอก ทำตาม เปิดให้เชื่อมต่อ PostgreSQL จากภายนอกด้วย pg_hba.conf หรือถ้าเป็นเพียงผู้ดูแลที่ต้องการเปิดดูข้อมูล การใช้ SSH Tunnel ด้วย DBeaver ปลอดภัยกว่าโดยไม่ต้องเปิดพอร์ต 5432

เมื่อระบบพร้อมใช้งานแล้ว อย่าลืมตั้งการสำรองข้อมูลตาม สำรองและกู้คืน PostgreSQL ด้วย pg_dump และ pg_restore หากติดปัญหาระหว่างติดตั้ง ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/