勉強日記

チラ裏

OSSDB標準教科書7章 マルチユーザでの利用

OSSDB標準教科書

oss-db.jp

マルチユーザでの利用

  • PostgreSQLはマルチユーザのデータベース
    • 権限付与
      • 表のデータを更新できるユーザ
      • 検索のみ行えるユーザ
    • 複数ユーザがネットワーク経由で接続
    • トランザクション

ユーザの作成

  • つくる
    • 消すのはDROP USER
CREATE USER sharo;
CREATE ROLE
  • 確認
\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 sharo     |                                                            | {}
\! createuser rize
\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rize      |                                                            | {}
 sharo     |                                                            | {}

ユーザとロール

  • 複雑な使い方をしない限り、同じものと考えて差し支えない
  • ユーザとグループの両方の概念を持ち合わせたもの
    • 以前のバージョンで、ユーザという概念をロールという概念に置き換えたらしい

スーパーユーザ

  • すべての権限を持ったユーザー
  • DB初期化時に作成される
    • OSユーザ名で作成される
    • su - postgresしてからcreatedb ossdbしているので、
      スーパーユーザpostgresが作成される

接続と認証

  • 外部からネットワーク経由でのPostgreSQLへの接続
  • 接続時の認証
  • デフォルトで設定されていないので、設定が必要

接続認証の設定を確認

  • HBA: Host-Based Authentication
cat /var/lib/postgresql/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#

(略)

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all trust

接続方法(TYPE)

  • local
  • host
    • 外部からのTCP/IPを使った接続
  • hostssl
    • 外部からのSSLを使った接続

データベース(DATABASE)

  • 接続認証対象のデータベース
  • allは全部

ユーザ(USER)

  • 接続認証対象のユーザ
  • allは全員

認証方式(METHOD)

  • 種類
    • trust
      • 認証なしに接続
    • reject
      • 接続拒否
    • md5
      • MD5パスワード認証
    • password
      • 平文パスワード認証
    • gss
      • GSSAPI認証
    • sspi
    • krb5
      • Kerberos V5認証
    • ident
      • IDENT認証
    • ldap
    • radius
    • cert
      • SSLクライアント証明書認証
    • pam
      • PAM認証
  • 【TODO】認証メソッド調べる
  • デフォルト、全DB全ユーザIDENT認証らしい
    • 【疑問点】全ユーザtrustだったのはdockerイメージだから??

接続ユーザの指定

  • これまで、暗黙でOSユーザ名が指定されていた
  • 接続しているユーザの確認
ossdb=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'ossdb'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'SQL_ASCII'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/var/run/postgresql'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '11.1 (Debian 11.1-3.pgdg90+1)'
SERVER_VERSION_NUM = '110001'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 11.1 (Debian 11.1-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit'
VERSION_NAME = '11.1 (Debian 11.1-3.pgdg90+1)'
VERSION_NUM = '110001'
  • これ
USER = 'postgres'
  • 全DB全ユーザtrust認証だったので、シャロちゃんで普通に入れる
  • スーパーユーザでないので、プロンプトはossdb=>となる
psql ossdb sharo
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

ossdb=>

パスワード認証の設定

  • パスワード認証の目的
    • OSユーザとは異なるPostgreSQLユーザでの接続
    • 外部からの接続
  • pg_hba.confを編集
  # PostgreSQL Client Authentication Configuration File
  # ===================================================
  #
   
  (略)
   
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
  # "local" is for Unix domain socket connections only
- local   all             all                                     ident
+ #local   all             all                                     ident
+ local   all             all                                     md5
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            ident
  # IPv6 local connections:
  host    all             all             ::1/128                 ident
  # Allow replication connections from localhost, by a user with the
  # replication privilege.
  local   replication     all                                     ident
  host    replication     all             127.0.0.1/32            ident
  host    replication     all             ::1/128                 ident
   
  host all all all ident
  • まだ設定は反映されない
  • パスワードを設定してから設定を反映しないと、DBに接続できなくなる

ユーザ・パスワードの設定

  • 既存ユーザ: ALTER USER文
ALTER USER postgres WITH PASSWORD 'password';
  • 新規ユーザ: CREATE USER文
DROP USER sharo;
DROP USER rize;

CREATE USER sharo WITH PASSWORD 'password';
createuser --pwprompt rize
createuser -P chiya
Enter password for new role:
Enter it again:

設定反映

  • どちらか
    • 再起動
    • 設定リロード

再起動

  • rootの場合
    • 教科書ではこう書いてある
    • インストール方法やバージョンが異なるので使えない
service postgresql-9.0 restart
  • postgresの場合
    • PGDATA環境変数が設定されていれば--pgdataオプションは省略可能
/usr/lib/postgresql/11/bin/pg_ctl restart --pgdata=/var/lib/postgresql/data
waiting for server to shut down....2019-02-16 03:05:02.913 UTC [7] LOG:  received fast shutdown request
2019-02-16 03:05:02.976 UTC [7] LOG:  aborting any active transactions
2019-02-16 03:05:02.978 UTC [7] LOG:  background worker "logical replication launcher" (PID 30) exited with exit code 1
2019-02-16 03:05:02.978 UTC [25] LOG:  shutting down
2019-02-16 03:05:03.372 UTC [7] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2019-02-16 03:05:03.526 UTC [2152] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-02-16 03:05:03.526 UTC [2152] LOG:  listening on IPv6 address "::", port 5432
2019-02-16 03:05:03.651 UTC [2152] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-02-16 03:05:03.843 UTC [2153] LOG:  database system was shut down at 2019-02-16 03:05:03 UTC
2019-02-16 03:05:03.945 UTC [2152] LOG:  database system is ready to accept connections
 done
server started

設定リロード

  • SIGHUP (ハングアップ)を送信する
  • rootの場合
    • 教科書ではこう書いてある
    • インストール方法やバージョンが異なるので使えない
service postgresql-9.0 reload
  • postgresの場合
    • PGDATA環境変数が設定されていれば--pgdataオプションは省略可能
/usr/lib/postgresql/11/bin/pg_ctl reload --pgdata=/var/lib/postgresql/data
server signaled
2019-02-16 03:07:02.529 UTC [2152] LOG:  received SIGHUP, reloading configuration files

パスワード認証による接続

postgres@963ce1530561:~$ psql ossdb
Password for user postgres:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

ossdb=#
postgres@963ce1530561:~$ psql ossdb sharo
Password for user sharo:
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

ossdb=>
  • IDENT認証ではないので、OSユーザーpostgresPostgreSQLユーザーsharoにログインできる

ネットワーク経由接続

ネットワーク経由接続の設定

(略)

...

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
  • listem_addresses
    • 接続を受け付けるネットワークインターフェース
    • デフォルトでは'localhost'
      • PostgreSQLが実行されているホストのローカルループバック接続のみ
  • port
    • 接続受付のポート番号
    • デフォルト5432

psqlを使ったネットワーク経由接続

  • pg_hba.conf
    • 別ホストからのアクセスはhost all all all部分が該当
    • MD5パスワード認証にしておく
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
  # "local" is for Unix domain socket connections only
  local   all             all                                     md5
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            ident
  # IPv6 local connections:
  host    all             all             ::1/128                 ident
  # Allow replication connections from localhost, by a user with the
  # replication privilege.
  local   replication     all                                     ident
  host    replication     all             127.0.0.1/32            ident
  host    replication     all             ::1/128                 ident
   
- host all all all ident
+ host all all all md5 
/usr/lib/postgresql/11/bin/pg_ctl reload --pgdata=/var/lib/postgresql/data/
  • もう一つdockerコンテナを立ち上げて、そちらから接続してみる
  • pgsql/docker-compose.yml
    • 今までpostgresボリュームにデータを永続化していたので、
      pgsql_postgresボリュームに中身を移すなどした
version: "3"
services:
  server:
    image: postgres
    volumes:
      - postgres:/var/lib/postgresql/data

  client:
    image: postgres
    links:
      - server
    command: bash
    tty: true

volumes:
  postgres:
docker-compose up -d
docker-compose exec client bash
  • serverという名前で、PostgreSQLサーバーが動いているコンテナを名前解決できる
  • clientコンテナからserverコンテナのPostgreSQLに接続できた
root@5c7d6cd0dbf5:/# su - psql
postgres@5c7d6cd0dbf5:~$ psql --host server ossdb sharo
Password for user sharo:
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

ossdb=>

アクセス制限

  • 1つのデータベースに複数のユーザが接続できる場合、
    アクセス制限を設定することで表などに対する操作を制御できる

アクセス権限の確認・付与・取り消し

確認

ossdb=# \dp prod
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 public | prod | table |                   |                   |
(1 row)

付与

GRANT ALL ON prod TO sharo;
  • 再確認
\dp prod
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
 public | prod | table | postgres=arwdDxt/postgres+|                   |
        |      |       | sharo=arwdDxt/postgres    |                   |
(1 row)
  • arwdDxt
    • a: INSERT (Append)
    • r: SELECT (Read)
    • w: UPDATE (Write)
    • d: DELETE
    • D: TRUNCATE
    • x: REFERENCES
    • t: TRIGGER

取り消す

REVOKE ALL ON prod FROM sharo;
  • 再確認
\dp prod
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
 public | prod | table | postgres=arwdDxt/postgres |                   |
(1 row)

トランザクション

  • データベースに対する1つ以上の処理のまとまり
    • 開始
      • BEGIN
    • 終了
      • COMMITで確定
      • ROLLBACKで破棄
  • これまで: 自動コミット(AUTOCOMMIT)がデフォルト有効だった
    • 操作が成功するごとに自動でCOMMITが発行されていた
  • 自動コミット無効化
    • \set AUTOCOMMIT=off
    • BEGIN;
BEGIN;

INSERT INTO customer VALUES(4, '田中産業');
-- 未確定

SELECT * FROM customer;

ROLLBACK;

SELECT * FROM customer;
ossdb=# BEGIN;
BEGIN
ossdb=# INSERT INTO customer VALUES(4, '田中産業');
INSERT 0 1
ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
           4 | 田中産業
(4 rows)

ossdb=# ROLLBACK;
ROLLBACK
ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
(3 rows)

読み取り一貫性

ossdb=# BEGIN;
BEGIN
ossdb=#
ossdb=# INSERT INTO customer VALUES(4, '田中産業');
INSERT 0 1
ossdb=# -- 未確定
ossdb=#
ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
           4 | 田中産業
(4 rows)

ossdb=# \! psql --host server ossdb
Password for user postgres:
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

ossdb=# select * from customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
(3 rows)
  • Dirty Read
  • PostgreSQLでは、Dirty Readが発生しないよう、読み取り一貫性が維持されている

【補】ロールバックとシーケンス

  • 進んだシーケンスはrollbackしても戻らない
    • MySQLのAUTO_INCREMENTもそう
SELECT * FROM staff;

BEGIN;

INSERT INTO staff(name) VALUES ('ココア');
SELECT * FROM staff;

ROLLBACK;

BEGIN;

INSERT INTO staff(name) VALUES ('ココア');
SELECT * FROM staff;

ROLLBACK;
 id |      name       |  birthday
----+-----------------+------------
  1 | 桐間紗路    | 2000-07-15
  2 | 天々座理世 | 2000-02-14
(2 rows)


 id |      name       |  birthday
----+-----------------+------------
  1 | 桐間紗路    | 2000-07-15
  2 | 天々座理世 | 2000-02-14
  3 | ココア       |
(3 rows)


 id |      name       |  birthday
----+-----------------+------------
  1 | 桐間紗路    | 2000-07-15
  2 | 天々座理世 | 2000-02-14
  4 | ココア       |
(3 rows)

ロック機構と更新の競合

ossdb=# BEGIN;
BEGIN
ossdb=#
ossdb=# INSERT INTO customer VALUES(4, '田中産業');
INSERT 0 1
ossdb=# -- 未確定
ossdb=#
ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
           4 | 田中産業
(4 rows)

ossdb=# \! psql --host=server ossdb
Password for user postgres:
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

ossdb=# INSERT INTO customer VALUES(4, '田中産業');
  • 別ターミナルで実行したINSERTは、先に行われたトランザクションが完了するまで待たされる

デッドロック

  • 端末A
BEGIN;

UPDATE prod 
   SET price = price * 1.1 
 WHERE prod_id = 1;
  • 端末B
BEGIN;

UPDATE prod 
   SET price = price * 1.1 
 WHERE prod_id = 2;
  • 端末A
UPDATE prod 
   SET price = price * 1.1 
 WHERE prod_id = 2;
  • 端末B
UPDATE prod 
   SET price = price * 1.1 
 WHERE prod_id = 1;
ERROR:  deadlock detected
DETAIL:  Process 64 waits for ShareLock on transaction 782; blocked by process 76.
Process 76 waits for ShareLock on transaction 783; blocked by process 64.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,4) in relation "prod"