WSL への PostgreSQL のインストール

SQL WSL データ解析

WSL への PostgreSQL のインストール手順と初期設定をメモとして残しておきます。

環境

インストール

# リポジトリ追加
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-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

# PostgreSQL 10 のインストール
sudo apt install postgresql-10

# 確認
which psql
>>>> /user/bin/psql

psql --version
>>>> psql (PostgreSQL) 10.6 (Ubuntu 10.6-2.pgdg16.04+1)

初期設定

PostgreSQL の daemon を起動。

# 起動
sudo service postgresql start

# 停止
sudo service postgresql stop

PostgreSQL をインストールすると postgres という Ubuntu user が作成されています。この `postgres' は PostgreSQL の全ての権限を持った管理ユーザです。。

インストールしただけでは、他のユーザでは以下のエラーが出てデータベースに接続できません。

psql: FATAL: role "username" does not exist

そのため、はじめは postgres ユーザとして PostgreSQL に接続する必要があります。

# postgres として psql コマンドを実行
sudo -u postgres psql

# postgres ユーザに変更してから psql コマンドを実行
sudo su postgres
psql

ロールの作成

このまま毎回 postgres ユーザとしてデータベースにログインするのは、セキュリティ上問題がある上に手間もかかります。そこで、権限を制御した新たなユーザを作成します。 PostgreSQL では、このユーザをロールと呼びます。

まず、postgres ユーザでデータベースに接続してから、新たなロールを作成します。

-- ロールの作成（パスワードなし）
CREATE ROLE role_name WITH LOGIN

-- ロールの作成（パスワードあり）
CREATE ROLE role_name WITH LOGIN PASSWORD

この新たに作成したロールはデータベースに接続することはできますが、それ以外には何もできないため、必要に応じて権限を追加します。

-- role_name に SELECT, INSERT, UPDATE, DELETE を tablename 上で行えるようにする
GRANT SELECT, INSERT, UPDATE, DELETE
  ON TABLE tablename
    TO role_name;

-- role_name に全ての権限を dbname　データベース上で与える
GRANT ALL
  ON DATABASE dbname
    TO role_name;

-- 既存のテーブル全てに対する権限を与える
GRANT ALL 
  ON ALL TABLES IN SCHEMA public 
    TO role_name;

-- 権限の削除
REVOKE SELECT
  ON TABLE table_name
    FROM role_name;

参考