Sakiのプログラミング学習ブログ

プログラミングについて学んだことや、学習の振返りを書いています。

【Debian】PostgreSQLのインストール・設定・外部接続の手順

設定環境

Debianにインストール

macのターミナルを使っている場合は、Debianにリモート接続しておく。

Debianのバージョンを確認。

$ cat /etc/debian_version
#=>10.3

リポジトリのパスを記載するためのファイルを作成し、実行権限を追加。

$ sudo touch /etc/apt/sources.list.d/pgdg.list
$ sudo chmod u+x /etc/apt/sources.list.d/pgdg.list 

リポジトリのパスを記載。

$ su
$ echo  deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main  > /etc/apt/sources.list.d/pgdg.list 

リポジトリのパスが記載されたか確認。

$ cat /etc/apt/sources.list.d/pgdg.list
#=>deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main

CA証明書をインストール。

$ sudo apt install wget ca-certificates

PostgreSQL公開鍵(リポジトリ署名キー)を追加。

$ su
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
$ exit

パッケージリストの更新

$ sudo apt-get update 

インストール。 この時バージョンを指定しなければ、最新バージョンをインストールする。

# 最新バージョンのインストール
$ sudo apt-get -y install postgresql
# 特定のバージョンのインストール
$ sudo apt-get install postgresql-<バージョン番号>

インストールできたか確認。

$ psql --version 
# => psql (PostgreSQL) 13.0 (Debian 13.0-1.pgdg100+1)

インストールはこれで完了。

PostgreSQLにログインして設定を行う

PostgreSQLをインストールすると、自動的にpostgresという、データベースの管理ユーザーが作成されるので、このユーザーでログインする。

$ sudo su - postgres

新しくスーパーユーザーを追加する。

$ createuser --pwprompt --interactive <ユーザー名> 

上記を入力すると、設定したいパスワードと、スーパーユーザーにするかを聞かれる。 パスワードを2回入力し、yを押してスーパーユーザーとして設定する。

Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n)

今作ったユーザーでログインする。

psql -U <ユーザー名> -d postgres -h localhost

プロンプトが以下に変われば成功

postgres=#

ちなみに、ユーザー一覧は\duで見ることができる。

なぜ新しくスーパーユーザーを追加するのか?

postgresユーザーをそのまま使わず、新しくスーパーユーザーを作成する理由が分からなかったので、調べた。
公式ドキュメントの21.2. ロールの属性によれば、

スーパーユーザー以外にCREATEDB権限とCREATEROLE権限を持つロールを作成することを勧めます。 そして、このロールを使用して、データベースとロールを管理するためのすべての処理を行ってください。 この方法によって、実際には不要な処理をスーパーユーザーとして行う危険性を避けることができます。 とある。

※ロール=ユーザーのこと。

つまり、postgresユーザーは権限の範囲が広い=何でもできてしまい、普段ログインして使うには危ないため、新しくスーパーユーザー権限を与えたユーザーを作成して使う必要がある。

外部接続の設定

外部接続とは、自分のmacから、PostgreSQLを使ってサーバーのデータベースに接続すること。 以下の手順はすべてDebianにリモート接続した状態で行っている。

PostgreSQLの設定が記述されている、postgresql.confを編集する。 ここで、バージョン番号を間違えると、「そのようなファイルはありません」とエラーが出るので注意。

$ sudo vim /etc/postgresql/<バージョン番号>/main/postgresql.conf 

ファイルの60行目あたりの、# - Connection Settings -に、

listen_addresses = 'localhost' 

とあるので、これを

listen_addresses = '*'  

ここで、#を消さないと設定が反映されないので注意。

自分のIPアドレスを確認。 $ hostname -I

次に、 pg_hba.confファイルを編集する。

$ sudo vim etc/postgresql/13/main/pg_hba.conf

認証を受け付けるIPの範囲を追記する。 変更ではなく、追記な点に注意。

# IPv4 local connections:
host    all             all             <自分のmacのIPアドレス>/32         md5

再起動。

$ sudo service postgresql start

これで外部接続の設定は完了したので、できるか以下のコマンドで試す。

$ psql -U <postgresqlのスーパーユーザ名> -d postgres -h <ホスト名>`

postgres=#と出たら成功。

参考記事