PostgreSQLとは
PostgreSQLは、SQLクエリ言語を使用するオープンソースのデータベース管理システムです。 PostgreSQLは、VPS上のアプリケーションとWebデータを管理するために使用できる強力なツールです。
このガイドでは、権限とユーザー付与権限を適切に管理する方法について説明します。 これにより、個別のデータベースに自由に影響を与えることなく、アプリケーションに必要な特権を提供できます。
Ubuntu 12.04クラウドサーバーでPostgreSQLを使用しますが、インストール以外のすべては、最新のLinuxディストリビューションでも同じように機能するはずです。
PostgreSQLの初期設定
UbuntuにPostgreSQLがまだインストールされていない場合は、次のコマンドを入力してダウンロードしてインストールします。
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
インストール中に、PostgreSQLは操作するデフォルトのユーザーを作成します。 最初のステップでは、このユーザーを使用します。 次のコマンドでログインします。
sudo su - postgres
これで環境の準備が整い、PostgreSQLがパーミッションを処理する方法について学び始めることができます。
PostgreSQLパーミッションの概念
PostgreSQL（または単に「postgres」）は、「ロール」の概念を通じて権限を管理します。
役割は、ユーザーとグループの間に区別がないという点で、従来のUnixスタイルの権限とは異なります。 役割は、これらの規則の両方に似るように操作できますが、より柔軟です。
たとえば、ロールは他のロールのメンバーになることができ、以前に定義されたロールの権限特性を引き継ぐことができます。 ロールはオブジェクトを所有し、他のロールのそれらのオブジェクトへのアクセスを制御することもできます。
PostgreSQLでロールを表示する方法
次のコマンドを使用してプロンプトインターフェイスにログインすることにより、PostgreSQLで現在定義されているロールを表示できます。
psql
ロールのリストを取得するには、次のように入力します。
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
ご覧のとおり、デフォルトの役割は1つだけで、多くの強力な特権があります。
PostgreSQLでロールを作成する方法
Postgresのロールを作成する方法はいくつかあります。 Postgres内またはコマンドラインからロールを作成することができます。
PostgreSQL内からロールを作成する方法
新しい役割を作成する最も基本的な方法は、Postgresプロンプトインターフェイス内からです。
次の構文で新しい役割を作成できます。
CREATE ROLE new_role_name;
「demo_role」という新しいロールを作成しましょう。
CREATE ROLE demo_role;
CREATE ROLE
定義されたユーザーを再度確認すると、次のようになります。
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
The only information we are given is that the new role has no login privileges. This is okay for now.
コマンドラインからロールを作成する方法
ロールを作成する別の方法は、「createuser」コマンドを使用することです。
次のように入力して、PostgreSQLコマンドプロンプトから少し離れます。
\q
次のコマンドを使用して、「test_user」というロールを作成します。
createuser test_user
Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n
新しい役割の初期権限を定義する一連の質問が表示されます。 これらのプロンプトすべてに「いいえ」と答えると、前に作成したユーザーと同様のユーザーが作成されます。
Postgresプロンプトに再度ログインし、「\ du」コマンドを再度発行して、2つの新しい役割の違いを確認できます。
psql \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
ご覧のとおり、これらのコマンドは同じ結果を生成しません。 コマンドラインから作成されたユーザーには、属性として「ログインできません」がリストされていません。
PostgreSQLでロールを削除する方法
演習として、「demo_role」に「test_user」と同じ権限を持たせるようにしてみましょう。 最初に作成中にこれを試み、後で既存の役割の権限を変更する方法を学習します。
作成時に「demo_role」のアクセス許可を定義する練習をする前に、現在のロールを破棄して再試行できるようにする必要があります。
次の構文を使用して、役割を削除できます。
DROP ROLE role_name;
次のように入力して、「demo_role」ロールを削除します。
DROP ROLE demo_role;
DROP ROLE
存在しないユーザーに対してコマンドを発行すると、次のエラーが発生します。
DROP ROLE demo_role;
ERROR: role "demo_role" does not exist
この状況を回避し、dropコマンドでユーザーが存在する場合は削除し、ユーザーが存在しない場合は静かに何もしないようにするには、次の構文を使用します。
DROP ROLE IF EXISTS role_name;
ご覧のとおり、このオプションを指定すると、役割の有効性に関係なく、コマンドは正常に完了します。
DROP ROLE IF EXISTS demo_role;
NOTICE: role "demo_role" does not exist, skipping DROP ROLE
ロール作成時に特権を定義する方法
これで、権限を変更して「demo_role」ロールを再作成する準備が整いました。 これを行うには、メインのcreate句の後に必要な権限を指定します。
CREATE ROLE role_name WITH optional_permissions;
次のように入力すると、オプションの完全なリストが表示されます。
\h CREATE ROLE
このユーザーにログイン機能を提供したいので、次のように入力します。
CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE
属性をもう一度確認すると、2人のユーザーが同じ権限を持っていることがわかります。
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
ロールを作成するたびに「login」属性を指定せずにこの状態にしたい場合は、実際には「CREATEROLE」コマンドの代わりに次のコマンドを使用できます。
CREATE USER role_name;
2つのコマンドの唯一の違いは、「CREATEUSER」が自動的にロールにログイン権限を与えることです。
PostgreSQLでロールの権限を変更する方法
作成済みのロールの属性を変更するには、「ALTERROLE」コマンドを使用します。
このコマンドを使用すると、前に示したようにユーザーを削除して再作成しなくても、特権の変更を定義できます。
基本的な構文は次のとおりです。
ALTER ROLE role_name WITH attribute_options;
たとえば、次のコマンドを発行することで、「demo_role」を以前の状態に戻すことができます。
ALTER ROLE demo_role WITH NOLOGIN;
ALTER ROLE
特権が以前の状態に戻ったことがわかります。
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
demo_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
test_user | | {}
次のコマンドで簡単に元に戻すことができます。
ALTER ROLE demo_role WITH LOGIN;
PostgreSQLで別のユーザーとしてログインする方法
デフォルトでは、システムのユーザー名がPostgreSQLのユーザー名と一致する場合にのみ、ユーザーはローカルにログインできます。
これを回避するには、ログインタイプを変更するか、PostgreSQLがループバックネットワークインターフェイスを使用するように指定します。これにより、実際にはローカル接続であっても、接続タイプがリモートに変更されます。
2番目のオプションについて説明します。 まず、認証できるように、接続するユーザーにパスワードを指定する必要があります。
次のコマンドを使用して、「test_user」にパスワードを指定します。
\password test_user
パスワードを入力して確認するように求められます。 ここで、PostgreSQLインターフェースを終了し、終了して通常のユーザーに戻ります。
\q exit
PostgreSQLは、ログイン時に、オペレーティングシステムのユーザー名と一致するユーザー名を使用し、同じ名前のデータベースにも接続することを前提としています。
これは、私たちが示している状況には当てはまらないため、使用するオプションを明示的に指定する必要があります。 次の構文を使用します。
psql -U user_name -d database_name -h 127.0.0.1 -W
「user_name」は、接続するユーザー名に置き換える必要があります。 同様に、「database_name」は、アクセスできる既存のデータベースの名前である必要があります。
「-h127.0.0.1」セクションは、ローカルマシンに接続することを指定する部分ですが、ネットワークインターフェイスを介して、システムのユーザー名が一致しなくても認証できるようにします。 「-W」フラグは、パスワードを入力することをPostgreSQLに通知します。
「test_user」でログインするには、次のコマンドを発行できます。
psql -U test_user -d postgres -h 127.0.0.1 -W
Password for user test_user:
設定したパスワードを入力する必要があります。 この例では、データベース「postgres」を使用します。 これは、インストール中に設定されるデフォルトのデータベースです。
このセッションでいくつかのアクションを実行しようとすると、多くのことを実行する能力がないことがわかります。 これは、多くのものを管理するための「test_user」権限を付与しなかったためです。
終了して、管理セッションに戻りましょう。
\q sudo su - postgres psql
PostgreSQLで権限を付与する方法
データベースまたはテーブルが作成されると、通常、それを作成したロール（スーパーユーザーステータスのロールは含まない）のみがデータベースまたはテーブルを変更する権限を持ちます。 他のロールに権限を付与することで、この動作を変更できます。
「GRANT」コマンドを使用して権限を付与できます。 一般的な構文は次のとおりです。
GRANT permission_type ON table_name TO role_name;
これらの概念を実践するための簡単な表を作成します。
CREATE TABLE demo ( name varchar(25), id serial, start_date date);
NOTICE: CREATE TABLE will create implicit sequence "demo_id_seq" for serial column "demo.id" CREATE TABLE
結果は次のように確認できます。
\d
List of relations Schema | Name | Type | Owner --------+-------------+----------+---------- public | demo | table | postgres public | demo_id_seq | sequence | postgres (2 rows)
これで、新しい「demo」テーブルに「demo_role」にいくつかの特権を付与できます。 次のコマンドを使用して、ユーザーに「UPDATE」権限を付与します。
GRANT UPDATE ON demo TO demo_role;
権限タイプを「all」という単語に置き換えることで、ユーザーに完全な権限を付与できます。
GRANT ALL ON demo TO test_user;
システム上のすべてのユーザーに権限を指定する場合は、特定のユーザーの代わりに「パブリック」という単語を使用できます。
GRANT INSERT ON demo TO PUBLIC;
付与テーブルを表示するには、次のコマンドを使用します。
\z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------+----------+----------------------------+--------------------------
public | demo | table | postgres=arwdDxt/postgres +|
| | | demo_role=w/postgres +|
| | | test_user=arwdDxt/postgres+|
| | | =a/postgres |
public | demo_id_seq | sequence | |
(2 rows)
これにより、割り当てたすべての付与権限が表示されます。
PostgreSQLで権限を削除する方法
「REVOKE」コマンドを使用して権限を削除できます。 revokeコマンドは、grantとほぼ同じ構文を使用します。
REVOKE permission_type ON table_name FROM user_name;
ここでも、同じ省略語（allとpublic）を使用して、コマンドを簡単にすることができます。
REVOKE INSERT ON demo FROM PUBLIC;
PostgreSQLでグループロールを使用する方法
ロールは、他のロールをグループ化して広範な権限制御を可能にするのに十分な柔軟性があります。
たとえば、「temporary_users」という新しいロールを作成してから、そのロールに「demo_role」と「test_user」を追加できます。
CREATE ROLE temporary_users; GRANT temporary_users TO demo_role; GRANT temporary_users TO test_user;
これで、これら2人のユーザーは、各メンバーを個別に管理する代わりに、「temporary_users」グループの役割を操作することで、アクセス許可を管理できます。
次のように入力すると、役割のメンバーシップ情報を確認できます。
\du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------+-------------------
demo_role | | {temporary_users}
postgres | Superuser, Create role, Create DB, Replication | {}
temporary_users | Cannot login | {}
test_user | | {temporary_users}
グループロールのメンバーは、「set role」コマンドを使用して、メンバーであるグループロールとして機能できます。
現在ログインしている「postgres」ユーザーにはスーパーユーザー権限があるため、そのグループのメンバーでなくても「setrole」を使用できます。
SET ROLE temporary_users;
これで、作成されたテーブルはすべて、temporary_usersロールによって所有されます。
CREATE TABLE hello ( name varchar(25), id serial, start_date date);
次のコマンドを発行することで、テーブルの所有権を確認できます。
\d
List of relations Schema | Name | Type | Owner --------+--------------+----------+----------------- public | demo | table | postgres public | demo_id_seq | sequence | postgres public | hello | table | temporary_users public | hello_id_seq | sequence | temporary_users (4 rows)
ご覧のとおり、新しいテーブル（およびシリアルデータ型に関連付けられたシーケンス）は、「temporary_users」ロールによって所有されています。
次のコマンドを使用して、元の役割のアクセス許可に戻すことができます。
RESET ROLE;
「alterrole」コマンドを使用してユーザーに「inherit」プロパティを与えると、そのユーザーは「setrole」コマンドを使用せずに所属するロールのすべての特権を自動的に取得します。
ALTER ROLE test_user INHERIT;
これで、test_userは、メンバーであるロールのすべての権限を持ちます。
「droprole」コマンドを使用して、グループロール（または任意のロール）を削除できます。
DROP ROLE temporary_users;
ERROR: role "temporary_users" cannot be dropped because some objects depend on it DETAIL: owner of table hello owner of sequence hello_id_seq
「temporary_users」が所有するテーブルを作成したため、エラーが発生します。 所有権を別の役割に譲渡することで、この問題を解決できます。
ALTER TABLE hello OWNER TO demo_role;
確認すると、「temporary_users」がテーブルを所有していないことがわかります。
\d
List of relations Schema | Name | Type | Owner --------+--------------+----------+----------- public | demo | table | postgres public | demo_id_seq | sequence | postgres public | hello | table | demo_role public | hello_id_seq | sequence | demo_role (4 rows)
これで、コマンドを再度発行することにより、「temporary_users」ロールを正常に削除できます。
DROP ROLE temporary_users;
これにより、temporary_usersロールが破棄されます。 temporary_usersの以前のメンバーは削除されません。
結論
これで、PostgreSQLデータベースの権限を管理するために必要な基本的なスキルを身に付けることができます。 他のアプリケーションで使用されるデータを中断せずに、アプリケーションが必要なデータベースにアクセスできるように、アクセス許可を管理する方法を知ることが重要です。
