Kohei Nozaki's blog 

Entries tagged [postgres]

ロール、DB、権限、テーブルを定義してみる


Posted on Tuesday Jan 21, 2014 at 07:42AM in Technology


環境

  • PostgreSQL 9.2.6
  • CentOS 6.5
  • ここに書いた作業をした後

何をするの?

  • ロールの定義
  • データベースの作成
  • 権限の割当
  • テーブルの作成

ロールの定義

どんなロールが必要か?

こんなものだろうか

  1. 何でもできるスーパーユーザ
  2. データベースを作ったりは出来ないが、テーブルの読み書きとDDLも流せる人力メンテ用のユーザ
  3. テーブルの読み書きできるけどDDLは流せないアプリ用のユーザ

スーパーユーザでログインする

別に作らなくてもインストールしただけでpostgresというユーザが用意されている。こうするとログインできる

sudo -i -u postgres psql

createuserコマンドのオプション

-P
作成と同時にパスワードを設定する
-e
作成時に実行されるSQL文を表示する
-S
非スーパーユーザにする(デフォルト)。スーパーユーザにする場合は -s

人力メンテ用のユーザを作る

sudo -i -u postgres createuser -P -e -S kyle

アプリ用のユーザを作る

sudo -i -u postgres createuser -P -e -S wildfly

ユーザ一覧を見る

$ sudo -i -u postgres sh -c "psql -c 'select * from pg_shadow'"
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t         | t       |                                     |          | 
 kyle     |    16384 | f           | f        | f         | f       | ***                                 |          | 
 wildfly  |    16385 | f           | f        | f         | f       | ***                                 |          | 
(3 行)

$ 

テーブル単位とか細かい権限制御は別のコマンドを使うのだろう。

データベースの作成

オーナーを指定して作成する

ここでは人力メンテ用のユーザをオーナーにしてデータベースを作ってみましょう。

echo create database articles owner kyle encoding \'UTF8\' TEMPLATE template0 | sudo -i -u postgres psql 

データベース一覧を見る

sudo -i -u postgres sh -c "psql -c '\l'"

出来てますね。

$ sudo -i -u postgres sh -c "psql -c '\l'"
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権       
-----------+----------+------------------+----------+-------------------+-----------------------
 articles  | kyle     | UTF8             | C        | C                 | 
 postgres  | postgres | SQL_ASCII        | C        | C                 | 
 template0 | postgres | SQL_ASCII        | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII        | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
(4 行)

$ 

データベースを削除する

sudo -i -u postgres sh -c "psql -c 'drop database articles'"

権限の割当

いったんpublicスキーマの権限を全て剥奪する

sudo -i -u postgres sh -c "psql articles -c 'REVOKE ALL ON schema public FROM public'"

kyleはDDLを流せるようにする

sudo -i -u postgres sh -c "psql articles -c 'GRANT ALL ON schema public TO kyle'"

wildflyはCRUDのみ許可する

sudo -i -u postgres sh -c "psql articles -c 'GRANT USAGE ON schema public TO wildfly'"
sudo -i -u postgres sh -c "psql articles -c 'grant select, insert, update, delete on all tables in schema public to wildfly'"
sudo -i -u postgres sh -c "psql articles -c 'GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO wildfly'"

テーブルの作成

psqlコマンドでログインしてみる

psql articles

テーブル一覧を見る

\d

テーブルを作る

CREATE TABLE test_table (
    id          bigint                                 NOT NULL,
    description character varying(128)                 NOT NULL,
    name        character varying(128)          UNIQUE NOT NULL,
    seq         integer                         UNIQUE NOT NULL,
    created     timestamp without time zone            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated     timestamp without time zone            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

テーブル定義を見る

\d test_table

その他

DDLのダンプ

sudo -i -u postgres sh -c "pg_dump --schema-only articles" > dumped.sql

アプリ用ユーザからログインしてみる

sudo -i -u wildfly psql articles

psql実行中にデータベースを切り替える

\c hogedb
\c hogedb hogeuser

ユーザをスーパーユーザにする

ALTER ROLE kyle WITH CREATEROLE SUPERUSER

やってみる

kyle-no-MacBook:~ kyle$ psql -U postgres
Password for user postgres: 
psql (9.2.4)
Type "help" for help.

postgres=# ALTER ROLE kyle WITH CREATEROLE SUPERUSER;
ALTER ROLE
kyle-no-MacBook:~ kyle$ psql
Password: 
psql (9.2.4)
Type "help" for help.

kyle=# 

プロンプトが変わった。

ユーザを非スーパーユーザにする

kyle-no-MacBook:~ kyle$ psql
Password: 
psql (9.2.4)
Type "help" for help.

kyle=# ALTER ROLE kyle WITH CREATEROLE NOSUPERUSER;
ALTER ROLE
kyle=# \q
kyle-no-MacBook:~ kyle$ psql
Password: 
psql (9.2.4)
Type "help" for help.

kyle=> 

プロンプトが元に戻った。

参考文献