PostgreSQL Usage¶
PostgreSQLの使い方メモです。
psql（コマンドライン）¶
接続¶
コマンドラインツールでPostgreSQLサーバーに接続します。デフォルトでは次の制約があります。
- サーバーと同じホスト上（localhost）からの接続のみ受け付け
- ローカルホストからのアクセスでは、コマンドを実行するOS上のユーザーとPostgreSQLのユーザーが同一であること（peer認証）
最初の設定は、/etc/postgresql/16/main/postgresql.confの次の記述によるものです。#listen_addresses = 'localhost' # what IP address(es) to listen on;
- コメントアウトを解除し、
'localhost'を'*'に修正すると、外部からの接続が可能になります。
2番目の設定は、/etc/postgresql/16/main/pg_hda.confの次の記述によるものです。local all all peer
- peerをmd5に変更するとpeer認証を無効にし、パスワード認証が使用されます。
- peerをtrustに変更すると、ローカルホストからの接続はすべて信頼できると判断され、ユーザー・パスワード情報なしに接続できます。
~ % psql -p 5432 -d postgres
- ローカルホストのPostgreSQLにポート番号5432を指定し、使用するデータベース名 postgres を指定して接続
ユーザー管理¶
PostgreSQLでは、ユーザーは「ロール」という概念に含まれます。ログイン可能なロール（LOGIN権限を持つロール）がユーザーです。
ユーザー一覧¶
- \du
ユーザーの作成¶
- ユーザーとなるロールを作成（パスワードは未設定）
CREATE USER <ユーザー名>;
これは、次と等価CREATE ROLE <ユーザー名> LOGIN;
* パスワードを指定してユーザーとなるロールを作成CREATE USER <ユーザー名> WITH PASSWORD 'パスワード'; - superuse権限を持つユーザーとなるロールをパスワードを指定して作成
CREATE USER <ユーザー名> WITH SUPERUSER PASSWORD 'パスワード';
ユーザーの削除¶
- DROP USER <ユーザー名>;
パスワード変更¶
- ALTER USER <ユーザー名> WITH PASSWORD '<パスワード>';
ロールの属性¶
- LOGIN
データベースに接続できるロール属性 - SUPERUSER
ログイン以外のすべての権限検査が行われない特権（危険） - CREATEDB
データベースの作成権限 - CREATEROLE
ロール作成権限。ロールの変更や削除も可能 - REPLICATION
ストリーミングレプリケーションの新規接続権限 - PASSWORD
データベースに接続する際のパスワード - INHERIT
- NOINHERIT
- BYPASSRLS
- CONNECTION LIMIT
データベース管理¶
データベース一覧¶
- \l
データベース作成¶
- CREATE DATABASE <データベース名>;
データベース削除¶
- DROP DATABASE <データベース名>;
データベースの所有者変更¶
- ALTER DATABASE <データベース名> OWNER TO <ユーザー名>;
スキーマ¶
PostgreSQLは、データベースの中に複数のスキーマが設定できます。テーブルはいずれかのスキーマに属します。通常はデフォルトで生成されるスキーマの一つpublicが使われます。
テーブル¶
テーブル一覧¶
- \dt
テーブル作成¶
- CREATE TABLE <テーブル名> (<カラム名> <型>, ...);
テーブル削除¶
- DROP TABLE <テーブル名>;
テーブルを空にする¶
- TRUNCATE <テーブル名>;
カラム一覧¶
- \d <テーブル名>