データ周辺の技術

PostgreSQLの管理系コマンドまとめ

DBの管理系コマンドは導入専門にやっていれば忘れることはないのだが、
分析目的でちょっとインストールした場合など、そんな頻繁に設定しない場合は忘れてしまう。
そんなPostgreSQLの管理コマンド、ユーザ管理など意外と落とし穴があるので復習しておく。

導入手順は

  1. インストール
  2. データベースディレクトリの作成（initdbコマンド）
  3. 起動（pg_ctlコマンド）
  4. ユーザの登録（createuserコマンド）
  5. データベースの作成（createdbコマンド）
  6. データベースの権限管理

という順になる。

pg_ctlコマンド

起動、停止などのコマンド。
postgresユーザでないと実行できない。

pg_ctl start -D データディレクトリ
pg_ctl stop -D データディレクトリ
pg_ctl restart -D データディレクトリ

その他管理コマンド

initdb

データディレクトリの生成。これがないと起動もできないし、データベースも作成できない。
新しく作るデータベースのデフォルトエンコーディング、ロケール（通常不要）を指定できる

initdb
initdb データディレクトリ
initdb -E SQL_ASCII --no-locale データディレクトリ

データディレクトリを指定しない場合、環境変数PGDATA=/var/lib/postgres/dataの値が採用される。

createdb

データベースを作成。デフォルトでinitdbのエンコーディングが採用される。
デフォルトでない文字コードを採用する場合は文字コード、ロケールに加えて「-T template0」も必要。

createdb データベース名
createdb -E SQL_ASCII -l C -T template0 データベース名

dropdb

dropdb データベース名

バックスラッシュコマンド

  • \d: テーブル一覧
  • \l: データベース一覧
  • \z: 権限一覧
  • \c データベース名: 接続するデータベースの変更
  • \du: ユーザ一覧
  • \i SQLファイル名: SQLファイルの実行

権限

最初にやるべきこと

デフォルトで作成したすべてのユーザは

  • すべてのデータベースに接続できる
  • スキーマpublicではすべての権限が与えられている

ということで、管理者が意図していない権限のテーブルにアクセスできてしまう。
そこでこれらの権限を削除する必要がある。

REVOKE CONNECT ON DATABASE データベース名 FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

必要に応じてユーザに

GRANT CONNECT ON DATABASE データベース名 TO ユーザ名;
GRANT USAGE ON SCHEMA public TO ユーザ名;

ユーザの追加

createuser ユーザ名
createuser -P ユーザ名    # パスワードを指定

パスワードの変更

データベースtemplate1に接続して

ALTER USER ユーザ名 WITH PASSWORD 'パスワード';

ユーザの削除

dropuser ユーザ名

権限を付与

GRANT文の主な使い方

-- 特定のデータベースに対して（権限は{ CONNECT＝読み専用の場合でも最低限必要 | TEMPORARY＝一時テーブル作成 | CREATE | ALL }）
GRANT 権限名 ON DATABASE データベース名 TO ユーザ名;

-- 特定のテーブルに対して（主な権限は{ CRUDの4つ | TRUNCATE | ALL }）
GRANT 権限名 ON テーブル名 TO ユーザ名
GRANT 権限名 ON スキーマ名.テーブル名 TO ユーザ名

-- public以外のスキーマ（他のユーザが所有しているスキーマ）の場合はさらに以下が必要（権限は{ USAGE＝読み込み | ALL＝全権限 }）
GRANT 権限名 ON SCHEMA スキーマ名 TO ユーザ名;

「最初にやるべきこと」で権限を削除している場合、以下の手順になる。

-- 1. まずすべての権限を削除しているので、データベースへの接続権限を付ける必要がある。
GRANT CONNECT ON DATABASE データベース名 TO ユーザ名;

-- 2. スキーマに対してアクセス権限を付ける。
GRANT USAGE ON SCHEMA スキーマ名 TO ユーザ名;

-- 3. 特定のテーブルに対して（主な権限は{ CRUDの4つ | TRUNCATE | ALL }）
GRANT 権限名 ON テーブル名 TO ユーザ名

権限を削除

GRANT→REVOKE, FROM→TO

-- 特定のデータベースに対して（権限は{ CONNECT＝読み専用の場合でも最低限必要 | TEMPORARY＝一時テーブル作成 | CREATE | ALL }）
REVOKE 権限名 ON DATABASE データベース名 FROM ユーザ名;

-- 特定のテーブルに対して（主な権限は{ CRUDの4つ | TRUNCATE | ALL }）
REVOKE 権限名 ON テーブル名 FROM ユーザ名
REVOKE 権限名 ON スキーマ名.テーブル名 FROM ユーザ名

-- public以外のスキーマ（他のユーザが所有しているスキーマ）の場合はさらに以下が必要（権限は{ USAGE＝読み込み | ALL＝全権限 }）
REVOKE 権限名 ON SCHEMA スキーマ名 FROM ユーザ名;

バックアップとリストア

バックアップ

pg_dump データベース名 > 出力ファイル名
pg_dumpall > 出力ファイル名    # 全データベース

リストア

psql -f SQLファイル名 データベース名
psql -f SQLファイル名 postgres    # 全データベース

クリーンアップ

vacuumdb データベース名
vacuumdb -a    # 全データベース

データ周辺の技術 の記事一覧

,