PostgreSQLでユーザー作成して権限の変更をするには？

# コマンドをついつい忘れてしまう。
GRANTを使うと作成したユーザーに権限を付与したり制限をすることができます。ローカル環境で作成するときは、全部つけると思いますが...

https://www.postgresql.jp/docs/9.4/sql-grant.html

なぜ記事を書こうと思ったのか？

業務で、Active Recordを使うことがあったのですが、KubernetesのPodで定義されたマニフェストファイルの通り作成されているが、コマンドが実行する権限がないエラーがでた？

>権限を作成してユーザーに付与すれば解決します😅

>そこで詰まったので練習用に、Dockerを使用してローカル環境を汚さないコンテナを作成して、練習してみました。

もしDockerなくてもローカルに、PostgreSQLがあればそちら使っていただいても同じことができると思います。

`postgre.md`を作成して以下の解説を記載

# PostgreSQL References
DockerでPostgreSQLを使う。

[- itオプションについて](https://zenn.dev/swata_dev/articles/2f85a3f4b3022c)

exec -it オプションの説明
docker exec -it コマンドの -it は2つのオプションが組み合わさっています：

-i (interactive): コンテナの標準入力（STDIN）をオープンに保ちます。これにより、ユーザーからの入力をコンテナに送信できます。
-t (tty): 疑似TTY（ターミナル）を割り当てます。これによりプロンプトが正しく表示され、コマンド実行時のフォーマットが整います。
この2つを組み合わせることで、ターミナルでコンテナと対話的にやり取りできるようになります。

使用例
PostgreSQLコンテナに入るなら例えば：

-it なしで実行すると、単にコマンドを実行してその結果を返すだけで、対話的な操作ができません。対話的なシェルやDBクライアントを使う場合は -it が必要です。

コンテナ内部に入る
```shell
# コンテナ名を調べる
docker ps
# Postgre内部に入る
# NAMESの場合rails8-docker-exec-db-1
docker exec -it rails8-docker-exec-db-1 bash
```

# PostgreSQL ユーザー管理コマンド
PostgreSQLコンテナ内に入った後、以下のコマンドでユーザー管理ができます。

## PostgreSQLの対話モードに入る
まず、PostgreSQLの対話モードに入ります：

```shell
psql -U postgres
```

## ユーザー作成コマンド
### 基本的なユーザー作成
```sql
CREATE USER username WITH PASSWORD 'password';
```

### 権限付きユーザー作成
```sql
CREATE USER username WITH PASSWORD 'password' CREATEDB;
```

### 一般ユーザー作成 (General User)
```sql
-- 基本的な一般ユーザーの作成
CREATE USER general_user WITH PASSWORD 'password';

-- 特定のデータベースへの接続権限を付与
GRANT CONNECT ON DATABASE database_name TO general_user;

-- テーブルへの読み取り権限のみを付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO general_user;

-- または特定のテーブルへの読み書き権限を付与
GRANT SELECT, INSERT, UPDATE ON table_name TO general_user;
```

### ユーザーに全ての権限を付与
```sql
-- スーパーユーザー権限を付与する方法
ALTER USER general_user WITH SUPERUSER;

-- または、データベースごとに全ての権限を付与
GRANT ALL PRIVILEGES ON DATABASE database_name TO general_user;

-- スキーマ内のすべてのテーブルに対して全ての権限を付与
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO general_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO general_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO general_user;

-- 新規作成されるオブジェクトに対しても自動的に権限を付与
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO general_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO general_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO general_user;
```

## ユーザー確認コマンド
```sql
\du
```

![スクリーンショット 2025-05-09 7.22.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2303722/e303c245-a095-4ee5-9dd4-a3cf3924decc.png)


### 特定のユーザーの権限を確認
```sql
SELECT usename, usecreatedb, usesuper FROM pg_user WHERE usename = 'username';
```

## PostgreSQL対話モードを終了
```sql
\q
```

これらのコマンドはすべてPostgreSQLの対話モード内で実行します。

```sql
-- データベース新規作成
CREATE DATABASE dev_db;
-- 作成されているデータベースを確認
\l
```

## データベース変更コマンド
PostgreSQLの対話モード内で、データベースを変更するには以下のコマンドを使用します：

```sql
\c database_name
-- dev_dbに変更
\c dev_db
```

## テーブル作成コマンド
`food_reservation` テーブルを作成するには以下のクエリを使用します：

```sql
CREATE TABLE food_reservation (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    reservation_date DATE NOT NULL,
    number_of_guests INT NOT NULL,
    special_requests TEXT
);
```

## テーブル一覧表示コマンド
現在のデータベース内のテーブル一覧を表示するには以下のコマンドを使用します：

```sql
\dt
```

## ダミーデータ挿入コマンド
`food_reservation` テーブルにダミーの予約情報を追加するには以下のクエリを使用します：

```sql
INSERT INTO food_reservation (customer_name, reservation_date, number_of_guests, special_requests)
VALUES 
    ('山田 太郎', '2023-11-01', 4, '誕生日ケーキあり'),
    ('佐藤 花子', '2023-11-02', 2, '誕生日ケーキなし'),
    ('鈴木 一郎', '2023-11-03', 3, '誕生日ケーキあり');
```

これにより、3件のダミー予約情報がテーブルに追加されます。

追加されたデータの確認
```sql
-- もし仮にデータが１００件あって取得すると良くないので３件と指定
SELECT * FROM food_reservation LIMIT 3;
```

![スクリーンショット 2025-05-09 7.41.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2303722/540a0429-80b8-41e9-a56c-dfe6ed4295b3.png)

# 最後に
普段はモバイルアプリのお仕事が専門なのですが、横断的な業務とやらをすることが多いです。SQLなんて使わずに、PrismaなどのORMを使うことが最近は多かったのですが、プログラミングスクールで学んでいた昔からある知識は意外と多く使われていたので、枯れた技術を学んでおいてよかったなと思いました。

変な話、NoSQLしかわからない人は死にますね😇

[昔学習で使っていた本](https://www.amazon.co.jp/%E3%81%93%E3%82%8C%E3%81%8B%E3%82%89%E3%81%AF%E3%81%98%E3%82%81%E3%82%8B-PostgreSQL%E5%85%A5%E9%96%80-%E9%AB%98%E5%A1%9A-%E9%81%99-ebook/dp/B07FS97ZSW/ref=sr_1_6?adgrpid=68564814919&dib=eyJ2IjoiMSJ9.QE2Z_f472cuFkh0n25-6KcE8GFnn8Vn1hOiUr6vStUxx-gQ6XxMxOJnOfEfHsI0S0TUarf0NpoHXmDspPSdKNGZSsV8DF1tYPmn9DYOZs86f4I795-CpgvqSFoSI-VPWtAv1Xo02L7y9flZaoeBTIAjvgEjpHIj3KVBVul4M_VrkjDxi6Qu4xWgNwXq0qzh8xOmq2uIcdim4nDeMYZSvynaAoruQ5u3FMAuWJpyRTApZBwjqmQJi6hh3svTCvInk.7z5nmK7kiKR4G6bsDFK2iJdKsoJpbYfvZdDGN2dvcug&dib_tag=se&hvadid=679050761702&hvdev=c&hvlocphy=1009308&hvnetw=g&hvqmt=e&hvrand=1135390527276481414&hvtargid=kwd-789819504604&hydadcr=1796_13657004&jp-ad-ap=0&keywords=postgresql+amazon&mcid=97d25d1a5b6e3707a448d8f25076bfc1&qid=1746744440&sr=8-6)