コマンドをついつい忘れてしまう。
GRANTを使うと作成したユーザーに権限を付与したり制限をすることができます。ローカル環境で作成するときは、全部つけると思いますが...
なぜ記事を書こうと思ったのか？
業務で、Active Recordを使うことがあったのですが、KubernetesのPodで定義されたマニフェストファイルの通り作成されているが、コマンドが実行する権限がないエラーがでた？
権限を作成してユーザーに付与すれば解決します😅
そこで詰まったので練習用に、Dockerを使用してローカル環境を汚さないコンテナを作成して、練習してみました。
もしDockerなくてもローカルに、PostgreSQLがあればそちら使っていただいても同じことができると思います。
postgre.mdを作成して以下の解説を記載
PostgreSQL References
DockerでPostgreSQLを使う。
exec -it オプションの説明
docker exec -it コマンドの -it は2つのオプションが組み合わさっています：
-i (interactive): コンテナの標準入力（STDIN）をオープンに保ちます。これにより、ユーザーからの入力をコンテナに送信できます。
-t (tty): 疑似TTY（ターミナル）を割り当てます。これによりプロンプトが正しく表示され、コマンド実行時のフォーマットが整います。
この2つを組み合わせることで、ターミナルでコンテナと対話的にやり取りできるようになります。
使用例
PostgreSQLコンテナに入るなら例えば：
-it なしで実行すると、単にコマンドを実行してその結果を返すだけで、対話的な操作ができません。対話的なシェルやDBクライアントを使う場合は -it が必要です。
コンテナ内部に入る
# コンテナ名を調べる
docker ps
# Postgre内部に入る
# NAMESの場合rails8-docker-exec-db-1
docker exec -it rails8-docker-exec-db-1 bash
PostgreSQL ユーザー管理コマンド
PostgreSQLコンテナ内に入った後、以下のコマンドでユーザー管理ができます。
PostgreSQLの対話モードに入る
まず、PostgreSQLの対話モードに入ります：
psql -U postgres
ユーザー作成コマンド
基本的なユーザー作成
CREATE USER username WITH PASSWORD 'password';
権限付きユーザー作成
CREATE USER username WITH PASSWORD 'password' CREATEDB;
一般ユーザー作成 (General User)
-- 基本的な一般ユーザーの作成
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;
ユーザーに全ての権限を付与
-- スーパーユーザー権限を付与する方法
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;
ユーザー確認コマンド
\du
特定のユーザーの権限を確認
SELECT usename, usecreatedb, usesuper FROM pg_user WHERE usename = 'username';
PostgreSQL対話モードを終了
\q
これらのコマンドはすべてPostgreSQLの対話モード内で実行します。
-- データベース新規作成
CREATE DATABASE dev_db;
-- 作成されているデータベースを確認
\l
データベース変更コマンド
PostgreSQLの対話モード内で、データベースを変更するには以下のコマンドを使用します：
\c database_name
-- dev_dbに変更
\c dev_db
テーブル作成コマンド
food_reservation テーブルを作成するには以下のクエリを使用します：
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
);
テーブル一覧表示コマンド
現在のデータベース内のテーブル一覧を表示するには以下のコマンドを使用します：
\dt
ダミーデータ挿入コマンド
food_reservation テーブルにダミーの予約情報を追加するには以下のクエリを使用します：
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件のダミー予約情報がテーブルに追加されます。
追加されたデータの確認
-- もし仮にデータが１００件あって取得すると良くないので３件と指定
SELECT * FROM food_reservation LIMIT 3;
最後に
普段はモバイルアプリのお仕事が専門なのですが、横断的な業務とやらをすることが多いです。SQLなんて使わずに、PrismaなどのORMを使うことが最近は多かったのですが、プログラミングスクールで学んでいた昔からある知識は意外と多く使われていたので、枯れた技術を学んでおいてよかったなと思いました。
変な話、NoSQLしかわからない人は死にますね😇