PostgreSQLのVPS上での役割の使用方法と権限の管理手順について

2年 ago
桜, 春樹
6 minutes

はじめに、

PostgreSQLまたはPostgresは、オープンソースのリレーショナルデータベース管理システムです。他のリレーショナルデータベースと同様に、PostgreSQLは行と列で構成されるテーブルにデータを格納します。ユーザーは、Structured Query Language（一般的にはSQLとして知られる）を使用してデータを定義、操作、制御、およびクエリすることができます。PostgreSQLは、仮想専用サーバー上でアプリケーションやウェブデータを管理するために使用できる強力なツールです。

このガイドでは、特権を適切に管理し、ユーザーに権限を付与する方法を示します。これにより、アプリケーションに必要な特権を付与することができ、別々のデータベースに影響を与えることなく実現できます。

前提条件

このチュートリアルに従うためには、以下が必要です:

  • One Ubuntu 22.04 server that has been configured by following our Initial Server Setup for Ubuntu 22.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.
  • To complete Step 1 of our How To Install and Use PostgreSQL on Ubuntu 22.04 tutorial to have Postgres installed on your server.

準備が整い、サーバー上でPostgresが稼働している状態で、Postgresが権限をどのように扱うかについて学び始めることができます。

PostgreSQLでの役割と権限の表示

Postgresは、ロールという概念を通じて権限を管理します。ロールは、従来のUnixスタイルのパーミッションとは異なる点があります。ユーザーとグループの区別がないのです。ロールは、これらの規約の両方を模倣するように操作できますが、さらに柔軟です。インストール時に、Postgresはピア認証を使用するように設定されています。つまり、Postgresのロールは、対応するUnix/Linuxシステムアカウントと関連付けられます。もしPostgres内にロールが存在する場合、同じ名前のUnix/Linuxユーザー名でそのロールとしてサインインできます。

インストール手順によって、デフォルトのPostgresロールに関連付けられた「postgres」というユーザーアカウントが作成されました。Postgresを使用するためには、そのアカウントにログインする必要があります。

最初に、systemctl startコマンドを使用してサーバーが実行されていることを確認してください。

  1. sudo systemctl start postgresql.service

 

それから、postgresアカウントに切り替えるには、次のように入力します。

  1. sudo -i -u postgres

 

「PostgreSQLのプロンプトには、すぐにアクセスすることができます。以下のように入力してください。」

  1. psql

 

Postgresインスタンス内の役割をリストするには、次のコマンドを入力してください。

  1. \du

 

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

現在、強力な特権を持つデフォルト役職は一つしかありません。

PostgreSQLにおける役割の作成

Postgresの役割を作成するための異なる方法がいくつかあります。Postgres内部またはコマンドラインからの役割を作成することが可能です。

PostgreSQL内から役割を作成する

Postgresプロンプトインターフェース内で新しいロールを作成する方法の一つは、次のような構文です。

  1. CREATE ROLE new_role_name;

 

これを示すために、”demo_role”という新しい役割を作成します。

  1. CREATE ROLE demo_role;

 

再度定義済みのユーザーを確認してください。

  1. \du

 

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

あなたの出力には2人のユーザーが表示されます。

コマンドラインから役割を作成する

役割を作成する別の方法は、コマンドラインからcreateuserコマンドを使用することです。

最初に、PostgreSQLのコマンドプロンプトから一時的に退出するために以下を入力してください。

  1. \q

 

それから、ポストグレスのアカウントにログインしてください。

  1. sudo -i -u postgres

 

createuserコマンドを使用して、コマンドラインから新しい役割を作成することができます。–interactiveフラグを使用すると、新しい役割の名前を入力するようにプロンプトが表示され、また、スーパーユーザー権限を持つかどうかも尋ねられます。

「postgres」アカウントでログインした状態で、以下のコマンドを入力することで新しいユーザーを作成することができます。

  1. createuser –interactive

 

以下の台本に基づいて、いくつかの選択肢を提示し、あなたの回答に基づいて、正しいPostgresのコマンドをあなたの指示に従って実行します。

Output

Enter name of role to add: 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

これらのプロンプトに対してすべて「いいえ」と答えると、前のユーザーに似たユーザーを作成することができます。

psqlのPostgresプロンプトに再ログインしてください。

  1. psql

 

次に、duコマンドを実行して、2つの新しい役割間の差異を明らかにします。このコマンドは\で始まるため、これはpsql独自のメタコマンドであり、PostgreSQLではなくpsql自体によって処理されます。

  1. \du

 

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test_user | | {}

コマンドラインから作成したユーザーには、「ログイン不可」という属性がリストされていないことに注意してください。

PostgreSQLにおける役割の削除

次の構文を使用して、役割を削除することができます。

  1. DROP ROLE role_name;

 

以下のように入力して、demo_roleの役割を削除してみましょう。

  1. DROP ROLE demo_role;

 

存在しないユーザーにコマンドを実行すると、エラーメッセージが表示されます。

Output

ERROR: role “demo_role” does not exist

この状況を避け、存在する場合はユーザーを削除し、存在しない場合は何もせずに静かにするためには、次の構文を使用します。

  1. DROP ROLE IF EXISTS role_name;

 

このオプションを指定すると、ロールの有効性に関係なく、コマンドは正常に完了します。上記のコマンドでdemo_roleを削除しようとすると、以下のような結果になります。

  1. DROP ROLE IF EXISTS demo_role;

 

Output

NOTICE: role “demo_role” does not exist, skipping DROP ROLE

役割は現在削除済みです。(Yakuwari wa Ima Sakujo-zumi desu.)

役割作成時の特権の定義

今、アルタードパーミッションを持つdemo_roleを再作成する準備ができました。メインの作成節の後に、希望するパーミッションを指定してこれを行うことができます。

  1. CREATE ROLE role_name WITH assigned_permissions;

 

オプションの完全なリストを見るには、入力してください。

  1. \h CREATE ROLE

 

Output

Command: CREATE ROLE Description: define a new database role Syntax: CREATE ROLE name [ [ WITH ] option [ … ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD ‘password’ | PASSWORD NULL | VALID UNTIL ‘timestamp’ | IN ROLE role_name [, …] | IN GROUP role_name [, …] | ROLE role_name [, …] | ADMIN role_name [, …] | USER role_name [, …] | SYSID uid URL: https://www.postgresql.org/docs/14/sql-createrole.html

「demo_role」ユーザーには、ログイン権限を与えることができます。タイプすることでログインできます。

  1. CREATE ROLE demo_role WITH LOGIN;

 

\duコマンドで属性を確認すると、現在、2つのユーザーの特権がまったく同じであることを確認できます。

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test_user | | {}

以下のCREATE USERコマンドを使用すると、ログイン属性を指定せずにこの状態に到達できます。このコマンドを使用することで、役割の作成時に自動的にログイン権限が与えられます。

  1. CREATE USER role_name;

 

役割は特権が自動的に与えられる形で作成されます。

PostgreSQLにおけるロールの権限変更を行う。

既に作成された役割の属性を変更するには、ALTER ROLEコマンドを使用します。このコマンドの構文は次のとおりです。

  1. ALTER ROLE role_name WITH attribute_options;

 

このコマンドを使用すると、以前に示したようにユーザーを削除して再作成することなく、特権の変更を定義することができます。たとえば、このコマンドを使用して、demo_role を以前の「ログイン不可」の状態に戻すことができます。

  1. ALTER ROLE demo_role WITH NOLOGIN;

 

「\du」コマンドで変更を確認できます。

  1. \du

 

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test_user | | {}

ログインアクセスがあるロールに戻すには、次のコマンドを使用してください。

  1. ALTER ROLE demo_role WITH LOGIN;

 

今、役割が逆転しました。

PostgreSQLで別ユーザーとしてログインする

デフォルトでは、システムのユーザー名とPostgreSQLのユーザー名が一致している場合にのみ、ユーザーはローカルでのログインが許可されます。この設定を変更するには、ログインタイプを変更するか、PostgreSQLがループバックネットワークインターフェースを使用するように指定することができます。これにより、ローカル接続であっても接続タイプをリモートに変更することができます。

最初に、接続したいユーザーのためにパスワードを作成して、認証できるようにしてください。以前に作成したtest_userに対しては、パスワードを設定することで試すことができます。

  1. \password test_user

 

パスワードを入力して確認するように求められます。それでは、PostgreSQLのインターフェースを終了し、次のコマンドを使用して通常のユーザーに戻ってください。

  1. \q

 

PostgreSQLでは、ログインする際にオペレーティングシステムのユーザー名と一致するユーザー名を使用し、同じ名前のデータベースに接続すると仮定しています。

使用するオプションを明確に指定するには、次の構文を使用してパラメータを使用します。

  1. psql -U user_name -d database_name -h 127.0.0.1 -W

 

以下は、コマンドの各項目の簡単な説明です。

  • The user_name should be replaced with the username you want to connect with.
  • The database_name should be the name of an existing database that you have access to.
  • The -h 127.0.0.1 section is the part that specifies that you will be connecting to the local machine, but through a network interface, which allows you to authenticate even though your system username does not match.
  • The -W flag tells PostgreSQL that you will be entering a password.

テストユーザーでログインするには、次のコマンドを発行してください。

  1. sudo psql -U test_user -d postgres -h 127.0.0.1 -W

 

このコマンドの後にはパスワードを入力する必要があります。

この例では、データベースpostgresを使用しています。これはインストール時にデフォルトのデータベースです。このセッションで何かの操作を試みると、多くのことができないことがわかります。これはtest_userに管理権限が付与されていないためです。

現在のセッションを終了してください。 (Genzai no sesshon o shūryō shite kudasai.)

  1. \q

 

その後、管理者のpostgresqlセッションに戻ってください。

  1. sudo u – postgres psql

 

次に、許可を与えることになります。

PostgreSQLにおける権限の付与

データベースやテーブルが作成される際、通常はスーパーユーザー権限を持たない役割を作成した役割だけが変更する権限を持っています。他の役割に権限を付与することで、この挙動を変更できます。

この一般的な構文を使って、GRANTコマンドを使用して権限を付与することができます。

  1. GRANT permission_type ON table_name TO role_name;

 

以下のコマンドを使用して、これらの概念を練習するためのテーブルを作成できます。

  1. CREATE TABLE demo (
  2. name varchar(25),
  3. id serial,
  4. start_date date);

 

作成したテーブルを表示するには、このコマンドを入力してください。

  1. \d

 

Output

List of relations Schema | Name | Type | Owner ——–+————-+———-+———- public | demo | table | postgres public | demo_id_seq | sequence | postgres (2 rows)

以下のように言い換えます（1つのオプションのみ）：
表のタイプとシーケンスのタイプがあることに注意してください。シーケンスは、表の作成時にidシリアルコマンドを使用した場合に生成されます。これにより、自動増分する整数が生成されます。

新しいデモテーブルにいくつかの特権をデモロールに付与することができます。以下のコマンドを使用して、demo_roleユーザーにUPDATE特権を与えてください。

  1. GRANT UPDATE ON demo TO demo_role;

 

以下のようにユーザーに完全な権限を付与することができます。パーミッションの種類をALLと置き換えることで、test_userにこの権限を付与します。このコマンドを使用してください。

  1. GRANT ALL ON demo TO test_user;

 

システム上のすべてのユーザーに対して特定の権限を設定したい場合は、特定のユーザー名の代わりに「PUBLIC」を使用することができます。

  1. GRANT INSERT ON demo TO PUBLIC;

 

グラントテーブルを表示するには、次のコマンドを使用してください。

  1. \z

 

Output

Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies ——–+————-+———-+—————————-+——————-+———- public | demo | table | postgres=arwdDxt/postgres +| | | | | demo_role=w/postgres +| | | | | test_user=arwdDxt/postgres+| | | | | =a/postgres | | public | demo_id_seq | sequence | | | (2 rows)

これによって、与えられた全ての許可権が明らかにされます。

ポストグレSQLでのアクセス権削除

REVOKEコマンドを使用することで、権限を削除することができます。REVOKEコマンドは、grantとほぼ同じ構文を使用します。

  1. REVOKE permission_type ON table_name FROM user_name;

 

コマンド内でも「ALL」と「PUBLIC」という同じ省略語を使用することができます。

  1. REVOKE INSERT ON demo FROM PUBLIC;

 

以前に設定した権限は、現在取り消されました。

PostgreSQLにおいて、グループロールを使用する。

役割は柔軟で、他の役割のグループ化を許容し、広範な権限制御を可能にします。例えば、temporary_usersという新しい役割を作成し、そのグループにdemo_roleとtest_userを追加することができます。

最初に、グループとして使用する新しい役割を作成してください。

  1. CREATE ROLE temporary_users;

 

その後、ユーザーを新しく作成されたtemporary_usersグループに割り当てます。

  1. GRANT temporary_users TO demo_role;

 

  1. GRANT temporary_users TO test_user;

 

これからは、個々のメンバーを個別に管理するのではなく、一時的なユーザーグループの役割を操作することで、これら2つのユーザーの権限を管理することができます。

「役割メンバーシップ情報を確認するには、入力してください。」

  1. \du

 

Output

List of roles Role name | Attributes | Member of —————–+————————————————————+——————- demo_role | | {temporary_users} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} temporary_users | Cannot login | {} test_user | | {temporary_users}

グループの役割のメンバーは、SET ROLEコマンドを使用することで、自分が所属するグループの役割としても行動することができます。現在ログインしているpostgresユーザーは、スーパーユーザー権限を持っているため、一時ユーザーグループのメンバーではなくてもSET ROLEコマンドを使用することができます。

  1. SET ROLE temporary_users;

 

今、作成されるテーブルは一時ユーザの役割によって所有されます。

  1. CREATE TABLE hello (
  2. name varchar(25),
  3. id serial,
  4. start_date date);

 

今、このコマンドを発行してテーブルの所有権を確認してください。

  1. \d

 

Output

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)

一つだけのオプションで、次の文章を日本語で表現します：
一時的なユーザーの役割である新しいテーブルと、シリアルデータ型に関連するシーケンスを所有しています。

元の役割許可に戻るために、以下のコマンドを入力してください。

  1. RESET ROLE;

 

ALTER ROLEコマンドでユーザーにINHERITプロパティを与えると、SET ROLEコマンドを使わずにそのユーザーは所属するロールのすべての権限を自動的に持つようになります。

  1. ALTER ROLE test_user INHERIT;

 

今後、test_userは、自身が所属している役割のすべての権限を持つことになります。グループの役割またはその他の役割を、DROP ROLEコマンドを使用して削除することができます。以下のコマンドを入力することで、temporary_usersグループでこれをテストすることができます。

  1. DROP ROLE temporary_users;

 

Output

ERROR: role “temporary_users” cannot be dropped because some objects depend on it DETAIL: owner of sequence hello_id_seq owner of table hello

このエラーが出力されるのは、helloテーブルがtemporary_usersによって所有されているためです。この問題を解決するには、所有権を別のロールに移すことです。

  1. ALTER TABLE hello OWNER TO demo_role;

 

次の方法で、temporary_usersがテーブルを所有していないかどうかを確認できます。

  1. \d

 

Output

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）を正常に削除できます。

  1. DROP ROLE temporary_users;

 

これにより、temporary_usersの役割が破棄されます。temporary_usersの前メンバーは削除されません。

結論

あなたは現在、PostgreSQLのデータベースの権限を管理するために必要な基本的なスキルを持っています。権限を管理する方法を知ることは重要です。これにより、他のアプリケーションが使用しているデータに影響を与えることなく、必要なデータベースにアクセスできるようにすることができます。

PostgreSQLやその使い方についてもっと学びたい場合は、以下のガイドをご覧いただくことをお勧めします。

  • SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
  • Practice running queries with SQL
コメントを残す 0

Your email address will not be published. Required fields are marked *

✖
广告
広告は10秒後に閉じます。
bannerAds