普通にPOSTGRESQLをUbuntuにインストール
いままで使ってきた主要なオープンソフトのRDBっていうとMARADBがほとんどでたまにPOSTGRESQLを使うという感じでした。環境はUbuntu 24.04です。(2025/05/25). PCのフォルダーの片隅に忘備録手順残すよりここに書いたほうが場所わかりやすいので書くことにしました（類似情報はすでに検索すればあるたくさんあるかと思います）。
MARIADBがアプリで普通にIDとパスワードでTCP接続するのがmysqlデータベースのテーブルでコントロールしているのに対して、POSTGRESQLは設定ファイルでコントロールされているようです。DJANGOとか他のアプリで接続だとやはりTCPでホスト名(またはIP)、データベース名、ユーザ名、パスワード、ポート番号が一番使われているやりかたなのではないかと思い、その設定方法を書いていきます。
- まずインストールとアクセス確認
# apt install postgresql postgresql-contrib
するとpostgresというユーザーも作成されます。MARIADBとちがい、デフォルト状態では、rootでそのままDBにはいろうとしても入れないです。
# cat /etc/passwd | grep postgres
postgres:x:111:114:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
# sudo -u postgres psql
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.9 (Ubuntu 16.9-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
とりあえずはいれました。あと補足としてパスワードはデフォルトではないので気になる方は設定しておきます。
postgres=# ALTER ROLE postgres WITH password 'yourpass123';
- IPでLISTENする
POSTGRESの接続権限や設定は以下の場所にあります。
/etc/postgresql/16/main (/etc/postgresql/[POSTGRESQLのバージョン番号]/main)
16はPOSTGRESQLのバージョンです。
postgresql.confの60行あたりに記述があります。デフォルトではlocalhostのみ、外部からの直接接続が必要であれば'*'としろとあります。
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
今回、私はアプリ自体も同じサーバーに入れるため不要なのでlocalhostのままにします。
アプリなどが別サーバーにある場合はここを外部からも認識できるインターフェースのIPにしましょう。
（NATなどでプライベートアドレスの場合はそのサーバーに割り当てられたプライベートIPアドレスかもしくは＊を設定します。）
次にアプリで使うユーザーを1つ作り、そのアプリにアクセスさせるデータベースを１つ作成します。そしてそのユーザーに対してselect, update, delete insertだけ許可します。
- ユーザ作成
postgres=# CREATE ROLE supportuser LOGIN ENCRYPTED PASSWORD 'mypass';
CREATE ROLE
- DB作成
postgres=# CREATE DATABASE supportdb OWNER postgres ENCODING 'UTF8';
CREATE DATABASE
- 接続許可
postgres=# GRANT CONNECT ON DATABASE supportdb TO supportuser;
GRANT
*publicスキーマを使う権限許可
postgres=# GRANT USAGE ON SCHEMA public TO supportuser;
GRANT
*Select,Insert,Update,Delteのみ許可
postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO supportuser;
GRANT
*あとややこしいですがauto_incrementなどのテーブルがある場合にSERIAL 列や IDENTITY 列に INSERT する場合は、シーケンスにも権限が必要です。必要なら次を追加してください。
postgres=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO supportuser;
GRANT
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO supportuser;
ALTER DEFAULT PRIVILEGES
これで権限的なところは終わりました。
ここでアクセスをしてみます。
# psql supportdb -U supportuser -h localhost
Password for user supportuser:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: database "supportuser" does not exist
エラーになりました！ pg_hba.confに接続方法を定義する必要があるからです。
pg_hba.confに以下を追加します。IPv4だけであれば最初の１行だけでOKです。
# TYPE DATABASE USER ADDRESS METHOD
host supportdb supportuser 127.0.0.1/32 scram-sha-256
host supportdb supportuser ::1/128 scram-sha-256 # IPv6
一応POSTGRESQLを再起動
# systemctl restart postgresql
再度アクセスしてみます
# psql supportdb -U supportuser -h localhost
Password for user supportuser:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
supportdb=>
以前はデフォルトでhostタイプの接続がダメだったような気がしましたが現在では以下の１行で
host all all 127.0.0.1/32 scram-sha-256
完全のオープンになっているようです。デフォルトでは、全てのデータベースに対して、全てのユーザーがLOCALHOSTからであればアクセス可能になっているため、それを絞ることにします。
#host all all 127.0.0.1/32 scram-sha-256
host supportdb supportuser 127.0.0.1/32 scram-sha-256
とDBユーザーを明示しました。
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
#host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
#host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication all peer
#host replication all 127.0.0.1/32 scram-sha-256
#host replication all ::1/128 scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
host supportdb supportuser 127.0.0.1/32 scram-sha-256
host supportdb supportuser ::1/128 scram-sha-256 # IPv6
最近はSSH TUNNELでアクセスできるツールもあるようで、リモートコンピュータから管理のためにわざわざデータベースポートを開放する必要もなくなってきました。
私はいかのA5M2という日本人の方が開発されたツールを使い始めてます。いろいろなDBに対応していてSSHトンネルもでき（つまりデータベース管理目的の接続のためDB側でlocalhost以外にPORTを外部開放する必要がない）セキュリティ的にも管理の容易さからも重宝してます。
時間があればmariadbで比較のため、全く同じ設定での設定をしてみます。
Discussion