OSSDB標準教科書7章 マルチユーザでの利用
OSSDB標準教科書
マルチユーザでの利用
- 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
- PostgreSQLが実行されているホストと同じホストからの接続
- host
- 外部からのTCP/IPを使った接続
- hostssl
- 外部からのSSLを使った接続
データベース(DATABASE)
- 接続認証対象のデータベース
- allは全部
ユーザ(USER)
- 接続認証対象のユーザ
- allは全員
認証方式(METHOD)
- 種類
- 【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オプションは省略可能
- 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オプションは省略可能
- 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ユーザー
postgresでPostgreSQLユーザーsharoにログインできる
ネットワーク経由接続
ネットワーク経由接続の設定
- /var/lib/postgresql/data/postgresql.conf
(略)
...
#------------------------------------------------------------------------------
# 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=offBEGIN;
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"
