PostgreSQL 読み取り専用のユーザーを作成する
jiroshin.icon まずタイトルにユーザーと書きましたが、現状のPosgresSQLにおいて「ユーザー」と「ロール」はログイン権限の有無の違いしかありません。
検証で利用するDBはこちらです。
code: docker-compose.yml
version: '3'
services:
postgres:
image: postgres:13.1
restart: always
environment:
POSTGRES_DB: jiroshin_db
POSTGRES_USER: jiroshin
POSTGRES_PASSWORD: pass
ports:
- 5432:5432
ログインすると jiroshin_dbが作成されていることが確認できます。ついでに jiroshin_db の中に table_1を作っておきます。
code: terminal
$ psql -h localhost -U jiroshin -d postgres
Password for user jiroshin:
psql (13.1)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+------------+------------+-----------------------
jiroshin_db | jiroshin | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | jiroshin | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | jiroshin | UTF8 | en_US.utf8 | en_US.utf8 | =c/jiroshin +
| | | | | jiroshin=CTc/jiroshin
template1 | jiroshin | UTF8 | en_US.utf8 | en_US.utf8 | =c/jiroshin +
| | | | | jiroshin=CTc/jiroshin
(4 rows)
postgres=# \c jiroshin_db
You are now connected to database "jiroshin_db" as user "jiroshin".
jiroshin_db=# CREATE TABLE table_1 (ID int);
CREATE TABLE
[*** 今回はこの jiroshin_dbの読み取り権限をもつユーザー readerを作成することをGoalとします。]
まず読み取り専用のユーザー readerを作って、jiroshin_dbの全テーブルへの読み取り(SELECT)権限を付与します。
code: terminal
$ psql -h localhost -U jiroshin -d postgres
Password for user jiroshin:
psql (13.1)
Type "help" for help.
postgres=# CREATE ROLE reader WITH LOGIN PASSWORD 'pass';
CREATE ROLE
postgres=# \c jiroshin_db
You are now connected to database "jiroshin_db" as user "jiroshin".
jiroshin_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
GRANT
これで readerは既存テーブルの全テーブルを読み取れるようになりました。
code: terminal
$ psql -h localhost -U reader -d jiroshin_db
Password for user reader:
psql (13.1)
Type "help" for help.
jiroshin_db=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | table_1 | table | jiroshin
(1 row)
jiroshin_db=> select * from table_1;
id
----
(0 rows)
[*** ここでポイントなのが「 readerは既存のテーブルへの読み取り権限は持っているが、今後作成されるテーブルへの権限は持っていない」ことです。]
試しに table_2 を作ってselectするとPermission Errorとなります。
code: terminal
$ psql -h localhost -U jiroshin -d jiroshin_db
Password for user jiroshin:
psql (13.1)
Type "help" for help.
jiroshin_db=# CREATE TABLE table_2 (ID int);
CREATE TABLE
$ psql -h localhost -U reader -d jiroshin_db
Password for user reader:
psql (13.1)
Type "help" for help.
jiroshin_db=> select * from table_2;
ERROR: permission denied for table table_2
今後 jiroshinが作成したテーブルに対してreaderが今後アクセスできる権限を付与する場合は以下のようになります。
code: terminal
$ psql -h localhost -U jiroshin -d jiroshin_db
Password for user jiroshin:
psql (13.1)
Type "help" for help.
jiroshin_db=# ALTER DEFAULT PRIVILEGES FOR USER jiroshin IN SCHEMA public GRANT SELECT ON TABLES TO reader;
ALTER DEFAULT PRIVILEGES
jiroshin_db=# \dpp
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+-------+-------------------
jiroshin | public | table | reader=r/jiroshin
(1 row)
※ \ddpでスキーマごとの Default access privileges を確認できます。
試しに table_3 を作成して、reader で select してみると成功しました。\dpで table_2 の権限はないが table_3 の権限があることも確認できました。
code: terminal
$ psql -h localhost -U jiroshin -d jiroshin_db
Password for user jiroshin:
psql (13.1)
Type "help" for help.
jiroshin_db=# CREATE TABLE table_3 (ID int);
CREATE TABLE
$ psql -h localhost -U reader -d jiroshin_db
Password for user reader:
psql (13.1)
Type "help" for help.
jiroshin_db=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
public | table_1 | table | jiroshin=arwdDxt/jiroshin+| |
| | | reader=r/jiroshin | |
public | table_2 | table | | |
public | table_3 | table | jiroshin=arwdDxt/jiroshin+| |
| | | reader=r/jiroshin | |
(3 rows)
jiroshin_db=> select * from table_3;
id
----
(0 rows)
※ \dpでテーブルごとの Access privileges を確認できます。
無事 jiroshin_dbの読み取り権限をもつユーザー readerを作成することができました。
おわり。