PostgreSQLの権限と権限設定コマンド

【権限には大きく２つに分類される】

• DBユーザ単位の権限
• テーブル単位の権限（SQLコマンドのみ設定可）

【権限を設定できるコマンドは大きく２種類ある】

• PostgreSQL標準のコマンド（接続後処理コマンド）
• SQLコマンド（何かしらのDBに接続して実行する必要がある）

※両コマンドは、何かしらのDBに接続して実行する必要がある。
また、コマンドを実行するDBユーザは適切な権限を持っている必要がある。

【取り扱うコマンド】

権限設定コマンド	具体例で用いるコマンド
createuser, CREATE USER, CREATE ROLE	dropuser, DROP USER, DROP ROLE
ALTER USER, ALTER ROLE	createdb, CREATE DATABASE
GRANT, REVOKE	dropdb, DROP DATABASE
	CREATE TABLE, ALTER TABLE, DROP TABLE

※PostgreSQL標準コマンドは小文字、SQLコマンドは大文字で記載
※ALTER USER, ALTER ROLEは後日、追加予定

【記事の要点】

• ユーザ単位の権限：DBユーザ（アカウント）の基本設定
• テーブル単位の権限：各DBユーザに対するテーブル内のデータ操作権限の設定
• createuser, CREATE USER, CREATE ROLE：
  • DBに接続（ログイン）するためのDBユーザ（アカウント）を作成する
  • アカウントの権限を設定する
• GRANT, REVOKEを使う意味
  • SUPERUSERまたは、DBのオーナー以外でもデータを操作できるようにするため、テーブル単位の権限を設定する（DB内のデータを操作できるのは、SUPERUSER、またはDBを作成したオーナーに限られるため）
  • \dp, \zで結果を確認できる
• \dp, \zメタコマンドの結果の見方
• CREATE TABLE：
  • 権限関係なく全てのDBユーザで実行することができる（CREATEDB権限がないDBユーザでも実行可）
  • 実行後、空のテーブルが作られる
  • 実行したDBユーザがテーブルのオーナーになる
  • テーブル単位の権限を設定したDBユーザは削除することができない（REVOKEすることで削除可能）

1. 権限

1.1 DBユーザ単位の権限

DBユーザには、主に以下の権限を付与することができる

ユーザ単位の権限	説明
SUPERUSER	スーパーユーザ権限
CREATEROLE	DBユーザ作成、削除権限
CREATEDB	DB作成権限
LOGIN	DB接続権限

【要点】

• createuser, CREATE USER(CREATE ROLE), ALTER USER(ALTER ROLE)コマンド：CREATEROLE権限、またはSUPERUSER権限を持つDBユーザのみ実行可
• createdb, CREATE DATABASEコマンド：CREATEDB権限、またはSUPERUSER権限を持つDBユーザのみ実行可
• dropuser, DROP USERコマンド：CREATEROLE権限、またはSUPERUSER権限を持つDBユーザのみ実行可
• dropdb, DROP DATABASEコマンド：DBのオーナー、またはSUPERUSER権限を持つDBユーザのみ実行可
• GRANTコマンド：テーブルのオーナー、またはSUPERUSER権限を持つDBユーザのみ実行可。
  • オプションとしてWITH GRANT OPTONを指定して権限を付与した場合、ロールは付与された権限を別のロールに追加できるようになる(ただし PUBLIC を指定した場合は付けることができません)。
• REVOKEコマンド：　　　〃

1.2 テーブル単位の権限

テーブルには、主に以下の権限付与することができる

テーブル単位の権限	説明
SELECT	SELECT, COPY TOの操作を許可
INSERT	INSERT, COPY FROMの操作を許可
UPDATE	UPDATEの操作を許可
DELETE	DELETEの操作を許可
TRUNCATE	TRUNCATEの操作（テーブル内の全てのデータを削除）を許可
REFERENCES	外部キー制約の作成を許可
TRIGGER	トリガーの作成を許可
ALL	全ての操作を許可

• GRANT, REVOKEコマンドで設定することができる。
• どのDBユーザでも操作可能なテーブルにしたり、特定ユーザのみSELECTのみの操作を許可するなど、DBユーザに対してテーブル単位の権限を設定する。

2. 権限設定コマンド

2.1 PostgreSQL標準コマンド（権限関連コマンド）

①DBユーザ単位の権限

権限付与コマンド

• createuser

オプション	説明
-P(--pwprompt)	パスワードを対話的に設定
-s(--superuser)	スーパーユーザ権限を付与
-r(--createrole)	CREATEROLE権限を付与（ユーザ作成、削除権限）
-d(--createdb)	CREATEDB権限を付与（DB作成権限）
-l(--login)	DBにログイン権限を付与（デフォルト）
-S(--no-superuser)	スーパーユーザ権限を付与しない（デフォルト）
-R(--no-createrole)	CREATEROLE権限を付与しない（デフォルト）
-D(--no-createdb)	CREATEDB権限を付与しない（デフォルト）
-L(--no-login)	DBにログイン権限を付与しない
-e(--echo)	createuserが実行したSQLを出力
--interactive	対話的に権限を付与する場合に指定

既存のpostgresユーザ（初期DBユーザ）で、CREATEROLE, CREATEDB権限を付与したuser1（DBユーザ）を作成。

$ createuser -U postgres user1 -r -d

• DBユーザ（アカウント）を作成するコマンド
• DBに接続する際に、適切な権限を持つDBユーザが必須になる
• DBユーザは、1つのDBクラスタでは一意である必要がある。つまり、同名のDBユーザを1つのDBクラスタ内に複数作ることができない
• DBユーザにはパスワードを設定することができる
• パスワードは、DB接続時のパスワード認証に使われる
• DBユーザに付与できる権限（ユーザ単位の権限）とGRANTで付与できる権限（テーブル単位の権限）は別物
• DBクラスタには、PostgreSQL管理ユーザと同名のDBユーザ（初期DBユーザ）がpg_ctl initdbの実行によって作られる
  • しかし、pg_ctl initdb -o '-U'のように-Uを指定した場合は、名前を別にすることもできる
  • 初期DBユーザは、スーパーユーザ
  • 最初にDBユーザを作成する際は、初期DBユーザを使って作成する

※以下のコマンドで作成したDBユーザ一覧を確認できる

$ createdb -U postgres db1
$ psql -U postgres db1
db1=# \du

2.2 SQLコマンド（権限関連コマンド）

①DBユーザ単位の権限

オプション	説明
SUPERUSER / NOSUPERUSER	スーパーユーザ権限の有無
CREATEROLE / NOCREATEROLE	ユーザ作成、削除権限の有無
CREATEDB / NOCREATEDB	DB作成権限の有無
LOGIN / NOLOGIN	ログイン権限の有無
PASSWORD ‘パスワード’	パスワードを設定、また変更
VALID UNTIL ‘日付’	パスワードの有効期限の指定
RENAME TO ‘ユーザ名’	ユーザ名を変更

※以下の権限を付与、変更するSQLコマンドのオプションは全て共通する

権限付与コマンド

• CREATE ROLE
• CREATE USER
  • 両コマンドの違い：CREATE USERの場合は、デフォルトでLOGIN権限を付与する。CREATE ROLEは付与しない。

$ psql -U postgres db1
db1=# SELECT user: # どのDBユーザでdb1に接続しているのかを確認
db1=# CREATE USER user2 CREATEROLE CREATEDB; # postgresユーザでuser2作成
db1=# \du #作成したDBユーザとユーザ権限を表示

※createdb -U postgres db1を実行済みである前提

権限変更コマンド

• ALTER ROLE
• ALTER USER

$ psql -U postgres db1
db1=# ALTER USER user2 SUPERUSER; # user2にSUPSERUSER権限を追加
db1=# \du　# 付与されたか確認
db1=# ALTER USER user2 NOSUPERUSER NOCREATEDB;　# user2のSUPERUSER, CREATEDB権限を削除
db1=# \du #削除されたか確認

※createdb -U postgres db1を実行済みである前提

②テーブル単位の権限

権限付与コマンド

• GRANT
  • 用途：SUPERUSERまたは、DBのオーナー以外でもデータを操作できるようにするため、テーブル単位の権限を設定する（DB内のデータを操作できるのは、SUPERUSER、またはDBを作成したオーナーに限られるため）
  • 書式：GRANT 権限 ON 対象 TO 誰に
    • 権限：以下の通り
    • 対象：テーブル、テーブルのカラム、テーブルのレコード、データベース、スキーマなどを指定
    • 誰に：権限を付与するDBユーザを指定。全てのDBユーザを対象にする場合は、PUBLICを指定

権限	説明
SELETCT	SELECT, COPY TOの使用を許可
INSERT	INSERT, COPY FROMの使用を許可
UPDATE	UPDATEの使用を許可
DELETE	DELETEの使用を許可
TRUNCATE	TRUNCATEの使用を許可
REFERENCES	外部キー制約の作成を許可
TRIGGER	トリガーの作成を許可
ALL	全ての操作を許可

※SELTCT権限：UPDATEやDELETEを使用するためにも必要

db1=# GRANT SELECT ON test_table TO user2;
db1=# \dp # テーブル単位の権限を表示する（\zでも可）

• \dp(\z)によって、テーブル、ビュー、シーケンスの一覧とそれらに設定されているテーブル単位の権限を表示することができる。
  • DBでは、全DBユーザを共有しているため権限の確認が可能

※createdb -U postgres db1を実行済みである前提
※db1=# CREATE TABLE test_table(id INTEGER);によって、テーブル作成済みである前提

権限を他のロールに追加できるのは、対象のオブジェクトの所有者とスーパーユーザーです。また GRANT コマンドを実行するときとに WITH GRANT OPTION を付けた場合、権限を与えられたロールは同じ権限を別のロールに追加出来るようになります。(ただし PUBLIC を指定した場合は付けることができません)。

権限削除コマンド

• REVOKE
  • 用途：GRANTで設定したテーブル単位の権限をDBユーザから削除する
  • 書式：REVOKE 権限 ON 対象 FROM 誰から
    • 権限：GRANTと同じ
    • 対象：テーブル、テーブルのカラム、テーブルのレコード、データベース、スキーマなどを指定
    • 誰から：権限を削除するDBユーザを指定。全てのDBユーザを対象にする場合は、PUBLICを指定

権限を他のロールから削除できるのは、対象のオブジェクトの所有者とスーパーユーザーです。それ以外のロールの場合は GRANT コマンドの WITH GRANT OPTION で他のロールに権限を追加する権限を追加されている場合に、他のロールに権限を追加していた場合にはその権限だけを削除することができます。

\dp(\z)メタコマンドの見方

• テーブル、ビュー、シーケンスの一覧とそれらに設定されているテーブル単位の権限を表示するメタコマンド。
  • 何かしらのDBに接続して実行することができる
  • 複数行での実行はできない
  • コマンドの最後に;をつけない

Access privilegesに表示される文字	説明
ユーザ名=xxxx	DBユーザに設定された権限
=xxxx	PUBLICに設定された権限
/ユーザ名	権限を設定したDBユーザ
a	INSERT(append)
r	SELECT(read)
w	UPDATE(write)
d	DELETE
D	TRUNCATE
x	REFERENCES
t	TRIGGER
arwdDxt	全ての権限（テーブルのみ。他のオブジェクトでは異なる）

postgres=# \dp
                                 Access privileges
 Schema |  Name  | Type  |     Access privileges     | Column privileges | Policies
--------+--------+-------+---------------------------+-------------------+----------
 public | table1 | table | postgres=arwdDxt/postgres+|                   |
        |        |       | user1=r/postgres       |                   |

具体例

権限を付与したDBユーザでできることを確認していく。最初に、PostgreSQLを起動しておく必要がある。

• SELECT user;（SELECT current_user;）：DBに接続している現在のDBユーザを表示する
• \dp：テーブル、ビュー、シーケンスの一覧とそれらに設定されているテーブル単位の権限を表示

DBユーザ作成からテーブル作成まで

①SQLコマンドを中心に使用した場合

• 上から順に説明：
  • DB一覧を表示
  • postgres（DBユーザ）でpostgres（DB）に接続
  • postgres（DB）に接続しているDBユーザを表示
  • postgresql（DBユーザ）で、CREATEROLE, CREATEDB権限を付与したuser1（DBユーザ）を作成
  • DBユーザの一覧と、そのユーザが所持しているテーブル単位の権限を表示
  • PostgreSQLサーバへの接続を切る（≒psqlプロンプトを終了しOSシェルに戻る）
  • 作成したuser1（DBユーザ）でpostgres（DB）に接続
  • user1（DBユーザ）でdb1（DB）を作成
  • DB一覧を表示
  • 作成したdb1（DB）に接続を切り替える
  • db1（DB）内に、teble1（テーブル）をuser1（DBユーザ）で作成する
  • db1（DB）内のテーブル一覧を表示（user1がオーナーであることも確認できる）

$ psql -U postgres -l
$ psql -U postgres postgres
postgres=# SELECT user;
postgres=# CREATE USER user1 CREATEROLE CREATEDB; 
postgres=# \du
postgres=# \q
$ psql -U user1 postgres
postgres=> CREATE DATABASE db1;
postgres=>\l
postgres=> \c db1
db1=> CREATE TABLE teble1(id INTEGER);
db1=> \dt

②PostgreSQL標準コマンドを中心に使用した場合

• 上から順に説明：
  • DB一覧を表示
  • postgresql（DBユーザ）で、CREATEROLE, CREATEDB権限を付与したuser2（DBユーザ）を作成
  • 作成したuser2（DBユーザ）でdb2（DB）を作成
  • user2（DBユーザ）でサーバーに接続し、DB一覧を表示
  • user2（DBユーザ）で作成したdb2（DB）に接続
  • db2（DB）内に、teble2（テーブル）をuser2（DBユーザ）で作成する
  • db2（DB）内のテーブル一覧を表示（user2がオーナーであることも確認できる）

$ psql -U postgres -l
$ createuser -U postgres user2 -r -d
$ createdb -U user2 db2
$ psql -U user2 -l
$ psql -U user2 db2
db2=> CREATE TABLE table2(id INTEGER);
db2=> \dt

テーブル内のデータ操作

テーブルのオーナー、SUPERUSER、またはテーブル単位の権限を持つDBユーザで実行する必要がある

①テーブルのオーナーの場合

GRANTでテーブル単位の設定をしなくてもテーブルのオーナー、またはSUPERUSERであれば、データ操作が可能

$ psql -U user1 db1
db1=> \dt
db1=> INSERT INTO teble1 VALUES(1);
db1=> SELECT * FROM teble1;

②テーブルのオーナー以外の場合

テーブルのオーナーではない、user2（DBユーザ）でデータ操作を行う場合、GRANTでテーブル単位の設定をしないとエラーになる。つまり、GRANTの設定をしないとデータ操作ができない。

$ psql -U user2 db1;
db1=> \dp
db1=> SELECT * FROM teble1; # エラー

③テーブルのオーナーであるがDBのオーナーではない場合

DBのオーナーでなくともテーブルのオーナーであれば、GRANTの設定は不要でデータ操作が可能。
DBオーナーの役割：DBのオーナー、またはSUPERUSERのみDBを削除できる。データ操作に関係ない。

$ createuser -U user1 user3
$ psql -U user3 db1
db1=> SELECT * FROM teble1; # エラー
db1=> CREATE TABEL teble3(id INTEGER);
db1=> \dt
db1=> SELECT * FROM teble3;

DBユーザの削除

CREATEROLE権限、またはSUPERUSER権限を持つDBユーザで実行する必要がある

①CREATEROLE権限をもつDBユーザの場合

$ createuser -U user1 user4
$ createuser -U user1 user5
$ psql -U user1 db1 # \duを実行するために何かしらのDBに接続
db1=> SELECT USER; # 現在のDBユーザを確認
db1=> \du # DBユーザが追加されたこと、現在のDBユーザにCREATEROLE権限があることを確認
db1=> DROP USER user4; # user4を削除(user1で)
db1=> \du
db1=> \q

②CREATEROLE権限を持たないDBユーザの場合

user3（DBユーザ）は、CREATEROLE権限、またはSUPERUSER権限を持たないためDBユーザを削除できない

$ dropuser -U user3 user5 # エラー

③オブジェクト（DBやテーブルなど）のオーナーであるDBユーザの場合

• CREATEROLE権限を持つuser1（DBユーザ）であっても削除できない
• つまり、user3はdb1内のteble3（テーブル）のオーナーであるため（オブジェクトを所持しているため）削除できない
• SUPERUSER権限を持つpostgresユーザでも同様にエラー

$ dropuser -U user1 user3 # エラー
DETAIL:  1 object in database db1

$ dropuser -U postgres user3 # エラー
DETAIL:  1 object in database db1

DBの削除

DBのオーナー、またはSUPERUSER権限を持つDBユーザで実行する必要がある

①DBのオーナーの場合

オーナー、またはSUPERUSER権限を持つDBユーザの場合は、削除に成功する

$ psql -U user1 db1 # メタコマンドを実行するために何かしらのDBに接続
db1=> \l # DBの一覧を確認
db1=> DROP DATABASE db3;
db1=> \l

※$ createdb -U user1 db3を実行している前提

②DBのオーナーではない場合

オーナー、またはSUPERUSER権限を持っていないとエラー

$ dropdb -U user3 db2 # エラー
dropdb: error: database removal failed: ERROR:  must be owner of database db2

※SUPERUSERであれば削除可能
$ dropdb -U postgres db2

③CREATEDB権限を持つDBユーザの場合

オーナー、またはSUPERUSER権限を持っていないとエラー

$ dropdb -U user1 db2 # エラー
dropdb: error: database removal failed: ERROR:  must be owner of database db2

④接続しているDBを削除する場合

DBのオーナーであっても、接続中のDBを削除することはできない

$ psql -U user2 db2
db2=> DROP DATABASE db2; # エラー

テーブルの削除

• DROP TABLEで削除できるが、SQLコマンドであるため、削除対象となるテーブルを含むDBに接続して、実行する必要がある

①テーブルのオーナーの場合

オーナー、またはSUPERUSER権限を持つDBユーザの場合は、削除に成功する

$ psql -U user1 db1 # メタコマンドを実行するために何かしらのDBに接続
db1=> \dt # テーブルの一覧を確認
db1=> SELECT user; # DROP TABLEを実行するDBユーザを確認
db1=> DROP TABLE teble1
db1=> \dt

※$ createdb -U user1 db3を実行している前提

②テーブルのオーナーではない場合

オーナー、またはSUPERUSER権限を持っていないとエラー

db1=> DROP TABLE teble3; # エラー
ERROR:  must be owner of table teble3

※SUPERUSERであれば削除可能
$ psql -U postgres db1
db1=# DROP TABLE teble3;