OSSDB標準教科書
[http://oss-db.jp/ossdbtext/text.shtml:embed:cite]
マルチユーザでの利用
-
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 | | {}
- linuxコマンドラインからも作れる
\! 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が実行されているホストと同じホストからの接続
- UNIXドメインソケット通信のみ
-
-
host
- 外部からのTCP/IPを使った接続
-
hostssl
- 外部からのSSLを使った接続
データベース(DATABASE)
- 接続認証対象のデータベース
- allは全部
ユーザ(USER)
- 接続認証対象のユーザ
- allは全員
認証方式(METHOD)
-
種類
-
trust
- 認証なしに接続
-
reject
- 接続拒否
-
md5
- MD5パスワード認証
-
password
- 平文パスワード認証
-
gss
- GSSAPI認証
-
sspi
- SSPI(Windowsのみ)
-
krb5
- Kerberos V5認証
-
ident
- IDENT認証
-
ldap
- LDAP認証
-
radius
- 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
オプションは省略可能
- 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パスワード認証にしておく
```diff
# 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:
```sh
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
```sql
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;
-
端末B側のトランザクションがロールバックされる
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"
-
デッドロックを避けるには
-
更新の順番を決める
- 一般的なプログラミング言語の資源獲得・解放と同じ
- トランザクションの時間を短くする
-