Marketechlabo

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

calendar Apr 8, 2019 / · 2 分で読了 · PostgreSQLデータベース管理  ·
共有する: linkedin copy

概要

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

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

という順になる。

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

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

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

initdb
initdb データディレクトリ
initdb -E UTF8 --locale=C データディレクトリ
sql

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

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

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

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

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

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

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

必要に応じてユーザに

GRANT CONNECT ON DATABASE データベース名 TO ユーザ名;
GRANT USAGE ON SCHEMA public TO ユーザ名;
sql
createuser ユーザ名
createuser -P ユーザ名    # パスワードを指定
sql

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

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

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 ユーザ名;
text

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

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

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

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

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 ユーザ名;
text
pg_dump データベース名 > 出力ファイル名
pg_dumpall > 出力ファイル名    # 全データベース
sql
psql -f SQLファイル名 データベース名
psql -f SQLファイル名 postgres    # 全データベース
sql
vacuumdb データベース名
vacuumdb -a    # 全データベース
sql