■スキーマについて学習してみた その２

再度、ちょっとした練習

①Ownerがtestuserでtestdb2を作成
$ createuser -d -r -l -P testuser
$ createdb -O testuser testdb2

②DBに接続
$pslq -U testuser -d testdb
testdb2=> \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 dvdrental | postgres | UTF8     | C       | C     |
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
 testdb2   | testuser | UTF8     | C       | C     |

③スキーマを作成
testdb2=> create schema testuser;

④テーブルを作成
testdb2=> create schema testuser;
CREATE SCHEMA
testdb2=> create table TripType(
testdb2(> TripTypeID                  INTEGER NOT NULL,
testdb2(> Name                        VARCHAR(15),
testdb2(> Description                 VARCHAR(50),
testdb2(> LastUpdated                 TIMESTAMP
testdb2(> );
CREATE TABLE
testdb2=> \dt+
                           List of relations
  Schema  |       Name       | Type  |  Owner   |  Size   | Description
----------+------------------+-------+----------+---------+-------------
 public   | pgbench_accounts | table | testuser | 13 MB   |
 public   | pgbench_branches | table | testuser | 64 kB   |
 public   | pgbench_history  | table | testuser | 1152 kB |
 public   | pgbench_tellers  | table | testuser | 72 kB   |
★testuser | triptype         | table | testuser | 0 bytes |
→スキーマがtestuserでtriptypeテーブルが作成された

⑤publicスキーマのテーブルを作成
testdb2=> create table public.TripType(
testdb2(> TripTypeID                  INTEGER NOT NULL,
testdb2(> Name                        VARCHAR(15),
testdb2(> Description                 VARCHAR(50),
testdb2(> LastUpdated                 TIMESTAMP
testdb2(> );
CREATE TABLE

testdb2=> \dt+
                           List of relations
  Schema  |       Name       | Type  |  Owner   |  Size   | Description
----------+------------------+-------+----------+---------+-------------
 public   | pgbench_accounts | table | testuser | 13 MB   |
 public   | pgbench_branches | table | testuser | 64 kB   |
 public   | pgbench_history  | table | testuser | 1152 kB |
 public   | pgbench_tellers  | table | testuser | 72 kB   |
 testuser | triptype         | table | testuser | 0 bytes |
(5 rows)
→publicスキーマのtriptypeテーブルは表示されない

⑥データの挿入
testdb2=> insert into TripType values( 1, 'TRNG', 'Training', NULL);
INSERT 0 1
testdb2=> select count(*) from triptype;
 count
-------
     1
(1 row)

testdb2=> select count(*) from public.triptype;
 count
-------
     0
→publicのtriptypeテーブルにはデータは格納されず。

⑦サーチパスの確認
testdb2=> show search_path;
   search_path
-----------------
 "$user", public
→自分のユーザに紐づいたスキーマを1番最初にリストするから。

⑧テーブル情報の確認
estdb2=> select * from pg_tables where tableowner='testuser';
 schemaname |    tablename     | tableowner | tablespace | hasindexes | hasrules
 | hastriggers | rowsecurity
------------+------------------+------------+------------+------------+---------
-+-------------+-------------
 public     | pgbench_tellers  | testuser   |            | t          | f
 | f           | f
 public     | pgbench_branches | testuser   |            | t          | f
 | f           | f
 public     | pgbench_accounts | testuser   |            | t          | f
 | f           | f
 public     | pgbench_history  | testuser   |            | f          | f
 | f           | f
 testuser   | triptype         | testuser   |            | f          | f
 | f           | f
 public     | triptype         | testuser   |            | f          | f
 | f           | f
(6 rows)

うーん、ややこしいね。

■スキーマについて学習してみた

postgreSQLのスキーマって名前空間とか言ってるけど、よく分らん。
調べてみると”A”というユーザがいて、そのAが所有するスキーマを複数指定でき、そのスキーマに各々のテーブルを格納できる的な感じかな。

1．testuserの作成
template1=# create user testuser password 'testuser';

２．testuserでtestdbの作成
-bash-4.1$ psql -U testuser -d template1

template1=> create database testdb;
ERROR:  データベースを作成する権限がありません

→exitして、postgresユーザでtemplate1データべースに接続し、
以下を実行。

template1=# alter role testuser createdb;
ALTER ROLE
template1=# \q
-bash-4.1$ psql -U testuser -d template1
template1=> create database testdb;
CREATE DATABASE
→データベース作成できた。

３．一覧表示
-bash-4.1$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権
-----------+----------+------------------+-------------+-------------------+-----------------------
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres          +
           |          |                  |             |                   | postgres=CTc/postgres
 testdb    | testuser | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |

→testdbのオーナーはtestuser

４．testuserでtestdbに接続し、テーブルを作成
→作成するテーブルは下記
psql -U tesuser -d testdb
create table TripType (
  TripTypeID                  INTEGER NOT NULL,
  Name                        VARCHAR(15),
  Description                 VARCHAR(50),
  LastUpdated                 TIMESTAMP
);

５．作成したテーブルの確認
testdb=> \dt+
                     リレーションの一覧
 スキーマ |   名前   |    型    |  所有者  | サイズ  | 説明
----------+----------+----------+----------+---------+------
 public   | triptype | テーブル | testuser | 0 bytes |
(1 行)

６．triptypeテーブルにデータの追加
→インサートするレコードは下記
insert into TripType values( 1, 'TRNG', 'Training', NULL);

７．結果確認
testdb=> \dt+
                      リレーションの一覧
 スキーマ |   名前   |    型    |  所有者  |   サイズ   | 説明
----------+----------+----------+----------+------------+------
 public   | triptype | テーブル | testuser | 8192 bytes |
(1 行)

→上記のように、スキーマはpublicである。

８．testuserスキーマの作成
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d template1
→postgresユーザでtemplate1に接続

template1=#
template1=# create schema testuser;
CREATE SCHEMA
→testuserスキーマができた

９．テーブル名にスキーマを追加して、テーブル作成
-bash-4.1$ psql -d testdb;
→postgresユーザでtestdbに接続
→testdb内にTripTypeテーブルを作成
create table testuser.TripType (
  TripTypeID                  INTEGER NOT NULL,
  Name                        VARCHAR(15),
  Description                 VARCHAR(50),
  LastUpdated                 TIMESTAMP
);

ERROR:  スキーマ"testuser"は存在しません
→何？testuserスキーマ作ったじゃん。
つまりtemplate1のDBにtestuserスキーマを作ってしまったのでは？

testdb=# create schema testuser;
CREATE SCHEMA
→もしや、DB単位でスキーマを用意する必要があるのでは。
→今度はうまくいった。

10．testdbにtestuserスキーマのテーブルを作成
testdb=# create table testuser.TripType (
testdb(#   TripTypeID                  INTEGER NOT NULL,
testdb(#   Name                        VARCHAR(15),
testdb(#   Description                 VARCHAR(50),
testdb(#   LastUpdated                 TIMESTAMP
testdb(# );
CREATE TABLE

testdb=# select * from triptype;
 triptypeid | name | description | lastupdated
------------+------+-------------+-------------
          1 | TRNG | Training    |
(1 行)

testdb=# select * from testuser.triptype;
 triptypeid | name | description | lastupdated
------------+------+-------------+-------------
(0 行)

testdb=> \dt triptype
            リレーションの一覧
 スキーマ |   名前   |    型    |  所有者
----------+----------+----------+----------
 public   | triptype | テーブル | testuser
(1 行)
→publicスキーマのtriptypeテーブル

testdb=> \dt testuser.triptype
            リレーションの一覧
 スキーマ |   名前   |    型    |  所有者
----------+----------+----------+----------
 testuser | triptype | テーブル | postgres
(1 行)
→testuserスキーマでテーブルできているじゃん。
testuserスキーマはpostgresユーザで作成しているので、所有者はpostgresとなっている

11．tesuserスキーマのtriptypeテーブルにレコードを追加
psql -U testuser -d testdb
insert into tesuser.TripType values( 2, 'SALES', 'Sales', NULL);
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
→testuserではtestdbに接続できてもtestuser.Triptypeテーブルにレコードの追加はできないようだ。

ERROR:  スキーマ testuser への権限がありません
行 1: insert into testuser.TripType values( 2, 'SALES', 'Sales', N...
→testuserでは、スキーマへのアクセス権限ないって。
→マニュアルには以下のように記載。
ユーザは、デフォルトでは所有していないスキーマのオブジェクトをアクセスすることはできません。
アクセスするためには、そのスキーマの所有者からスキーマのUSAGE権限を付与してもらう必要があります。

→つまり、postgresユーザでスキーマを作成したからpostgresユーザでtestuserにUSAGE権限を付与する必要あり？
testdb=> \q
→一回抜けて
-bash-4.1$ psql -d testdb;
testdb=# grant usage on schema testuser to testuser;
→postgresユーザでtestuserに対し、testuserスキーマへのusage権限付与

→テーブル作成しようとしたが、まだ権限がないってよ。
testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR:  リレーション triptype への権限がありません

testdb=# grant all on schema testuser to testuser;
GRANT
→わからんので、全権限付与。
→これでどうだ。

testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;

testdb=> insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
ERROR:  リレーション triptype への権限がありません
→まだだめだよ。

→testuserスキーマの所有者はpostgresだね。
testdb=# \dn+
                            スキーマ一覧
   名前   |  所有者  |      アクセス権      |          説明
----------+----------+----------------------+------------------------
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
 testuser | postgres | postgres=UC/postgres+|
          |          | testuser=UC/postgres |

testdb=# insert into testuser.TripType values( 2, 'SALES', 'Sales', NULL);
INSERT 0 1
→postgresユーザで接続したtestdbでは、testuser.TripTypeにインサートできるわけね。

testdb=# \q
-bash-4.1$ psql -U testuser -d testdb;
→抜けて、testuserでtestdbに接続

-bash-4.1$ psql -U testuser -d testdb;
testdb=> \dt;
            リレーションの一覧
 スキーマ |   名前   |    型    |  所有者
----------+----------+----------+----------
 testuser | triptype | テーブル | postgres
→testuserスキーマが表示。publicじゃないのかよ。

testdb=> select * from triptype;
ERROR:  リレーション triptype への権限がありません
→えっ。

testdb=> select * from public.triptype;
 triptypeid | name | description | lastupdated
------------+------+-------------+-------------
          1 | TRNG | Training    |
→publicスキーマは表示されるね。

testdb=# grant select on testuser.triptype to testuser;
GRANT
→上記のように、postgresユーザでtestuser.triptypeへの参照権限を与えたら
見えるようになった。
※少し前でgrant all ,usage権限を付与したが、
grant all on testuser.triptype…
とか
grant usage on testuser.triptype…
とかする必要があるのでは。あるいは、
grant usage on schema testuser to testuser;
↑これ、そもそも間違えてて
grant usage on testuser to testuser;
↑こうじゃん。！！

testdb=> \dt;
            リレーションの一覧
 スキーマ |   名前   |    型    |  所有者
----------+----------+----------+----------
 testuser | triptype | テーブル | postgres
(1 行)

testdb=> select * from triptype;
 triptypeid | name  | description | lastupdated
------------+-------+-------------+-------------
          2 | SALES | Sales       |
(1 行)

testdb=> select * from public.triptype;
 triptypeid | name | description | lastupdated
------------+------+-------------+-------------
          1 | TRNG | Training    |
(1 行)

→testuserユーザと同じ名前のtestuserスキーマができているので、
最初の参照は$userということからpublicのテーブルではなく、testuserスキーマの
テーブルから検索しているようだ。

おしまい。