postgreSQL その18 データベースユーザの変更　つづき

postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見

【検証6】　データベース作成権限を一般ユーザに付与

・そもそもデフォルトの一般ユーザにテーブル作成権限はあるか？

「createuser -P gad3」で作成したデフォルト状態の一般ユーザgad3でテーブルを作成してみる。

postgres=> select * from pg_roles where rolname = 'gad3';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | r
olconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+--
---------+-------
gad3 | f | t | f | f | t | f | -1 | ******** | | f |
| 16422
(1 row)

postgres=> \c postgres gad3
Password for user gad3:
You are now connected to database "postgres" as user "gad3".
postgres=> select user;
user
------
gad3
(1 row)

→一般ユーザでもテーブル作成は可能

作成されたテーブルは作成者が所有者になる。

postgres=> insert into tab4 values(1,2);
INSERT 0 1
postgres=> select * from tab4;
a | b
---+---
1 | 2
(1 row)

→テーブル所有者は当然writeもreadも可能

postgres=> drop table tab4;
DROP TABLE

・データベース作成権限がないことを確認

postgres=> create database GAD3DB;
ERROR: permission denied to create database

→「rolcreatedb」がfalseなのだから当然作成できない。

・gad3ユーザにデータベース作成権限付与

スーパーユーザにチェンジ

postgres=> \c postgres postgres
Password for user postgres:
You are now connected to database "postgres" as user "postgres".
postgres=# select user;
user
----------
postgres
(1 row)

postgres=# alter user gad3 createdb;
ALTER ROLE
postgres=# select * from pg_roles where rolname = 'gad3';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | r
olconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+--
---------+-------
gad3 | f | t | f | t | t | f | -1 | ******** | | f |
| 16422
(1 row)

→「rolcreatedb」がtrueになった。

※ここからリモートクライアントから実行

せ6> psql -h 192.168.2.7 -U gad3 -d postgres
ユーザ gad3 のパスワード:
psql (8.4.20, サーバ 10.2)
注意： psql バージョン 8.4, サーババージョン 10.2.
psql の機能の中で、動作しないものがあるかもしれません。
"help" でヘルプを表示します.
postgres=> select user;
user
------
gad3
(1 行)

postgres=> create database GAD3DB;
CREATE DATABASE

→データベースが作成できた。所有者がgad3

・gad3ユーザから「rolcreatedb」権限を削除

※ここからローカルクライアントから実施

postgres=# alter user gad3 nocreatedb;
ALTER ROLE
postgres=# select * from pg_roles where rolname = 'gad3';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | r
olconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+--
---------+-------
gad3 | f | t | f | f | t | f | -1 | ******** | | f |
| 16422
(1 row)

→「rolcreatedb」がfalseになった。

→GAD3DBデータベースの所有者は依然gad3ユーザ

※ここからリモートクライアントから実行（セッションはずっとつなぎっぱなし）

postgres=> select user;
user
------
gad3
(1 行)

→所有者ならデータベース作成権限がなくてもデータベースを削除できた！

postgres=> drop database testdb;
ERROR: must be owner of database testdb

→所有者じゃないのでデータベースを削除できない。

逆に言えば所有者ならデータベース作成権限がなくてもデータベースを削除できる。

【検証7】　ユーザ作成権限を一般ユーザに付与

postgres=# select user;
user
----------
postgres
(1 row)

postgres=# alter user gad2 createrole;
ALTER ROLE
postgres=# select * from pg_roles where rolname = 'gad2';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | r
olconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+--
---------+-------
gad2 | f | t | t | f | t | f | -1 | ******** | | f |
| 16421
(1 row)

→「rolcreaterole」がtrueになった。

postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gad2 | Create role | {}
gad3 | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
punisuke | | {}

・gad2ユーザにスイッチ

postgres=# \c postgres gad2
Password for user gad2:
You are now connected to database "postgres" as user "gad2".
postgres=> select user;
user
------
gad2
(1 row)

postgres=> create role gad4 password 'gad4';
CREATE ROLE

→ユーザの新規作成成功

postgres=> alter user gad3 createdb;
ALTER ROLE
postgres=> alter user gad4 createrole;
ALTER ROLE
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gad2 | Create role | {}
gad3 | Create DB | {}
gad4 | Create role, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
punisuke | | {}

→create userとalter userは自由にできる

※一般ユーザが新規作成したユーザはデフォルトでnologin

・他人(postgresユーザ)が作成したgad3ユーザをgad2で削除する

その前にgad3ユーザが所有するテーブルとデータベースを一個ずつ作る

postgres=> create database gad3db;
CREATE DATABASE
postgres=> create table gad3tb(a int,b int);
CREATE TABLE

・gad2はgad3を削除できるか？

postgres=> select user;
user
------
gad2
(1 row)

postgres=> drop role gad3;
ERROR: role "gad3" cannot be dropped because some objects depend on it
DETAIL: owner of table gad3tb
owner of database gad3db

・スーパーユーザpostgresでgad3が作成したデータベースとテーブルを削除する

postgres=> \c postgres postgres
ユーザ postgres のパスワード:
psql (8.4.20, サーバ 10.2)
注意： psql バージョン 8.4, サーババージョン 10.2.
psql の機能の中で、動作しないものがあるかもしれません。
データベース "postgres" に接続しました。ユーザ名："postgres".
postgres=# select user;
user
----------
postgres
(1 行)

postgres=# drop database gad3db;
DROP DATABASE
postgres=# drop table gad3tb;
DROP TABLE

→スーパーユーザな何でも作れて何でも削除できる

・もう一度gad2でgad3を削除する

postgres=> select user;
user
------
gad2
(1 row)

postgres=> drop role gad3;
DROP ROLE

postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gad2 | Create role | {}
gad4 | Create role, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
punisuke | | {}

→postgresユーザが作成したgad3ユーザをgad2が削除出来た。

postgreSQL その18 データベースユーザの変更 つづき

【検証6】 データベース作成権限を一般ユーザに付与

・そもそもデフォルトの一般ユーザにテーブル作成権限はあるか？

【検証7】 ユーザ作成権限を一般ユーザに付与

postgreSQL その18 データベースユーザの変更　つづき

【検証6】　データベース作成権限を一般ユーザに付与

【検証7】　ユーザ作成権限を一般ユーザに付与