kakiro-webカキローウェブ

システム開発情報とコンピューター書籍の紹介サイト

PostgreSQL データベース接続ユーザーの作成

PostgreSQLでデータベース接続ユーザーの作成に関するSQLについて、以下に示します。

※ここで説明に使用しているPostgreSQLは、当サイトのCentOS7 64bitのyumリポジトリにPostgreSQLを追加し、最新バージョンをインストールのページを元にインストールした、バージョン9.4になります。

※データベースの作成については、当サイトのPostgreSQL データベースの作成のページをご覧ください。

※テーブルの作成については、当サイトのPostgreSQL テーブルの作成のページをご覧ください。

※PostgreSQLでは、データベース接続ユーザーのことは、ロールとも言います。ロールには、ユーザーという概念と、グループという概念が含まれています。

データベース接続ユーザーの作成

データベース接続ユーザーを作成するには、以下のようなSQLを実行します。

postgres=# CREATE ROLE user01 LOGIN PASSWORD 'xxxxxxxxxx';
  • 「CREATE ROLE」に続けて、作成するユーザーの名前を指定します。

    ここでは「user01」という名前のユーザーを作成するようにしています。

  • ユーザー名に続けて、オプションの指定を行います。

    「LOGIN」を指定すると、データベースに接続可能なユーザーになります。

  • 「PASSWORD」に続けて、ユーザーのパスワードを指定します。

    「PASSWORD」の前に「ENCRYPTED」と指定すると、パスワードは暗号化されて保存されます。「UNENCRYPTED」と指定すると、暗号化されずに保存されます。

    「ENCRYPTED」、「UNENCRYPTED」を指定しなかった場合の動作は、PostgreSQLの設定ファイル「/var/lib/pgsql/9.4/data/postgresql.conf」で、「password_encryption」に設定されている値によって変わります。

    「password_encryption」が未設定または「on」と指定されている場合は、パスワードは暗号化されて保存されます。「off」と指定されている場合は、暗号化されずに保存されます。

作成したユーザーによる、PostgreSQLへの接続の認証に関する設定については、当サイトのCentOS7 64bitのyumリポジトリにPostgreSQLを追加し、最新バージョンをインストールのページをご覧ください。

データベース接続ユーザーの一覧の確認

データベース接続ユーザーの一覧を確認するには、以下のようなSQLを実行します。

postgres=# SELECT * FROM pg_authid;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit |             rolpassword             | rolvaliduntil
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------------------------------+---------------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
 user01   | f        | t          | f             | f           | f            | t           | f              |           -1 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
  • 「pg_authid」は、データベース接続ユーザーの情報を保持しているシステムカタログになります。

  • ここではデフォルトで作成されているユーザー「postgres」に、ユーザー「user01」を追加で作成した際の結果になっています。

データベース接続ユーザーの一覧は、以下のコマンドで確認することもできます。

postgres=# \du
                                         ロール一覧
 ロール名 |                                 属性                                 | メンバー
----------+----------------------------------------------------------------------+----------
 postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}
 user01   |                                                                      | {}

既存のデータベース接続ユーザーのパスワードの変更

既存のデータベース接続ユーザーのパスワードを変更するには、以下のようなSQLを実行します。

postgres=# ALTER ROLE user01 PASSWORD 'xxxxxxxxxx';
  • 「ALTER ROLE」に続けて、パスワードを変更するユーザー名を指定します。

    ここではユーザー「user01」のパスワードを変更するようにしています。

  • 「PASSWORD」に続けて、変更するパスワードを指定します。

    パスワードの暗号化に関する指定は、上記のCREATE ROLE文の説明に記載したものと同様になります。

データベース接続ユーザーに権限を付与

データベースの所有者になっているユーザーには、各種のSQLを実行する権限が自動的に付与されますが、データベースへの接続のみが許可された通常のユーザーを作成した時点では、テーブルに対しSQLを実行する権限が与えられていません。

ここでは、PostgreSQL データベースの作成のページPostgreSQL テーブルの作成のページを元に作成した、データベース「db01」のテーブル「tbl01」、「tbl02」に対し、SQLを実行する権限を付与する例を示します。

データベース接続ユーザーにテーブルに対する権限を付与

データベース接続ユーザーにテーブルに対する権限を付与するには、以下のようなSQLを実行します。

ここでは、データベース「db01」に、その所有者「user01」が接続して実行しています。

db01=> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user02;
  • 「GRANT」に続けて、付与する権限を指定します。

    「ALL PRIVILEGES」を指定すると、全ての権限が付与されるようになります。

    「SELECT, INSERT, UPDATE, DELETE」のように、付与したい権限の種類をカンマで区切って指定することもできます。

  • 「ON」に続けて、対象のテーブルを指定します。

    「ALL TABLES IN SCHEMA public」を指定すると、publicスキーマの全てテーブルが対象になります。

    「ALL TABLES IN SCHEMA public」を指定していても、後から作成されたテーブルに自動的に同じ権限が付与されることはありません。

    「tbl01, tbl02」のように、対象のテーブルをカンマで区切って指定することもできます。

  • 「TO」に続けて、権限を付与するユーザーを指定します。

    ここではユーザー「user02」に権限を付与するようにしています。

データベース接続ユーザーに付与されているテーブルに対する権限を確認

データベース接続ユーザーに付与されているテーブルに対する権限を確認するには、以下のようなSQLを実行します。

db01=> SELECT * FROM information_schema.role_table_grants WHERE grantee = 'user02';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 user01  | user02  | db01          | public       | tbl02      | INSERT         | NO           | NO
 user01  | user02  | db01          | public       | tbl02      | SELECT         | NO           | YES
 user01  | user02  | db01          | public       | tbl02      | UPDATE         | NO           | NO
 user01  | user02  | db01          | public       | tbl02      | DELETE         | NO           | NO
 user01  | user02  | db01          | public       | tbl02      | TRUNCATE       | NO           | NO
 user01  | user02  | db01          | public       | tbl02      | REFERENCES     | NO           | NO
 user01  | user02  | db01          | public       | tbl02      | TRIGGER        | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | INSERT         | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | SELECT         | NO           | YES
 user01  | user02  | db01          | public       | tbl01      | UPDATE         | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | DELETE         | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | TRUNCATE       | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | REFERENCES     | NO           | NO
 user01  | user02  | db01          | public       | tbl01      | TRIGGER        | NO           | NO
  • スキーマ「information_schema」にある「role_table_grants」は、データベース接続ユーザーに付与されているテーブルに対する権限の情報を取得するビューになります。

  • WHERE句の「grantee」で、テーブルに対して付与されている権限の情報を確認するデータベース接続ユーザーの名前を指定します。

    ここではデータベース接続ユーザー「user02」に付与されているテーブルに対する権限の情報を確認するようにしています。

データベース接続ユーザーに付与されているテーブルに対する権限は、以下のコマンドで確認することもできます。

db01=> \dp
                                     アクセス権
 スキーマ |      名前       |     型     |      アクセス権       | 列のアクセス権限
----------+-----------------+------------+-----------------------+------------------
 public   | tbl01           | テーブル   | user01=arwdDxt/user01+|
          |                 |            | user02=arwdDxt/user01 |
 public   | tbl02           | テーブル   | user01=arwdDxt/user01+|
          |                 |            | user02=arwdDxt/user01 |
 public   | tbl02_num01_seq | シーケンス |                       |
  • アクセス権の表示で、「user02=」の部分は「user02」に対する権限であることを示し、「/user01」の部分は「user01」によって権限が与えられたことを示しています。

    権限の内容の「a」は「INSERT」、「r」は「SELECT」、「w」は「UPDATE」、「d」は「DELETE」、「D」は「TRUNCATE」、「x」は「REFERENCES」、「t」は「TRIGGER」が付与されていることを示しています。

データベース接続ユーザーに付与したテーブルに対する権限を取り消し

データベース接続ユーザーに付与したテーブルに対する権限を取り消しするには、以下のようなSQLを実行します。

db01=> REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user02;
  • 「REVOKE」に続けて、取り消しする権限を指定します。

    「ALL PRIVILEGES」を指定すると、全ての権限が取り消しされるようになります。

    「SELECT, INSERT, UPDATE, DELETE」のように、取り消ししたい権限の種類をカンマで区切って指定することもできます。

  • 「ON」に続けて、対象のテーブルを指定します。

    「ALL TABLES IN SCHEMA public」を指定すると、publicスキーマの全てテーブルが対象になります。

    「tbl01, tbl02」のように、対象のテーブルをカンマで区切って指定することもできます。

  • 「TO」に続けて、権限を取り消しするユーザーを指定します。

    ここではユーザー「user02」の権限を取り消しするようにしています。

データベース接続ユーザーの削除

データベース接続ユーザーを削除するには、以下のようなSQLを実行します。

postgres=# DROP ROLE user01;
  • 「DROP ROLE」に続けて、削除するユーザー名を指定します。

    ここではユーザー「user01」を削除するようにしています。

PostgreSQL データベース接続ユーザーの作成については、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。