Zenn
🐘

Ubuntu 22.04にPostgreSQL15をインストールしてセットアップする

2022/12/03に公開
PostgreSQL
tech

環境

  • Ubuntu 22.04
  • PostgreSQL 15.1

インストール

公式サイトの手順に従ってaptの設定を行う。

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

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

sudo apt update
sudo apt -y install postgresql-15

apt updateの際に「N: リポジトリ 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' がアーキテクチャ 'i386' をサポートしないため設定ファイル 'main/binary-i386/Packages' の取得をスキップ」というエラーが表示される場合、エラーを回避するためにaptのリポジトリ設定ファイルに[arch=amd64]を追加する。

- deb http://apt.postgresql.org/pub/repos/apt jammy-pgdg main
+ deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt jammy-pgdg main

起動

systemdで起動する。

sudo systemctl enable postgresql
sudo systemctl start postgresql

普段使いユーザの作成

postgresロールでデータベース接続

インストールした時点ではデータベースが作られておらず、またデータベースのユーザも作られていないのでアクセスができない。
PostgreSQLをインストールしたときに作られるLinuxユーザpostgresを使うことでデータベースにアクセスすることができるので、普段遣い用のユーザとデータベースを作成する。

初回データベースアクセスは以下のコマンドで行う。

sudo -u postgres psql

パスワード認証形式の変更

PostgreSQLはローカルホストからの接続でPeer認証を使用しているので、データベースユーザとしてアクセスするには、OSのユーザ名がそのデータベースユーザと一致していなければ接続できない。
スーパーユーザーであるpostgresユーザはPostgreSQLをインストールすると自動で生成されるが、このままだとデータベースユーザを作ってもアクセスできない(OSでも同名のユーザを作ってログインしてシェル立ち上げてpsqlコマンド実行して...という手順を踏まないといけない)。

流石に面倒なので、ローカルホストからのアクセスにはパスワード認証を使うように設定する。
認証方式の設定は/etc/postgresql/15/main/pg_hba.confで設定されているので、これを編集する。

- local   all             all                                     peer
+ local   all             all                                     password

パスワード認証の方式には、暗号化したパスワードを用いるmd5と、平文のパスワードを使用するpasswordがある。

設定変更後はPostgreSQLを再起動する必要がある。

sudo systemctl restart postgresql

Optional: ロール作成

単に使うだけなら不要な手順だけど、読み取り専用・読み書き専用ロールを作っておく。
このようなロールを作っておくことで、ユーザ作成時にこれらのロールをアタッチするだけで権限を管理できるようになる。

PostgreSQL14以降は参照・更新専用のロールが事前に定義されるようになり、簡単に権限を管理できるようになった(らしい)ので、それを用いる。

とりあえず以下の2つのロールを作成する。

  1. readonly: データの参照のみを行えるロール
  2. readwrite: データの参照・更新のみを行えるロール

readonlyロールの作成

psqlでデータベースコンソールにアクセスした後、以下のSQLを実行する。

CREATE ROLE readonly;
GRANT pg_read_all_data TO readonly;

readwriteロールの作成

同じく以下のSQLを実行する。

CREATE ROLE readwrite;
GRANT pg_read_all_data, pg_write_all_data TO readwrite;

普段使いユーザ作成

開発用のユーザを作成する。データベース作成権限をつけておく。
postgresユーザでデータベースにアクセスし、以下のSQL文を実行する。

CREATE USER fjnkt98 WITH CREATEDB ENCRYPTED PASSWORD 'fjnkt98';

念の為、パスワードは暗号化して保存するように設定した。(ENCRYPTED句を付けることで暗号化して保存できる)

データベース作成

先程作成した普段使い用のユーザでデータベースにアクセスし、データベースを作成する。
psqlコマンドは接続先データベースを指定しなかったとき、ユーザ名と同じ名前のデータベースにアクセスしようとするが、今はまだないのでデフォルトのデータベースであるpostgresを指定しておく。

psql -U fjnkt98 -d postgres

データベースにアクセスしたら、データベースを作成する。名前はsampleにしておく。

CREATE DATABASE sample;

データベースを作成したら一旦ログアウトし、作成したデータベースに改めてアクセスする。

\q

psql -U fjnkt98 -d sample

あとはテーブルを作成したり、データを挿入したりして好きに開発できる。

Optional: 読み取り専用ユーザの作成

せっかくなので先程作ったreadonlyロールを使って読み取り専用ユーザを作ってみる。
readerという名前の読み取り専用ユーザを作る。

ロールを新規作成するので、postgresユーザでデータベースにアクセスし、以下のSQL文を実行する。

CREATE USER reader WITH ENCRYPTED PASSWORD 'reader';
GRANT readonly TO reader;

これで、読み取り専用ユーザreaderを作成し、読み取り専用の権限を付与することができた。

readerでデータベースにアクセスし、テーブルのデータを読み取ることができる。

psql -U reader -d sample

SELECT * FROM hoge;

しかしデータの挿入や削除はできない。

参考にしたサイト

GitHubで編集を提案

Discussion