Kohei Nozaki's blog 

環境

• 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=> 

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

参考文献

• database - How to configure postgresql for the first time? - Stack Overflow
• createuser
• データベースの作成 - データベースの作成 - PostgreSQLの使い方
• psqlプログラムサンプル集
• データベース作成 postgres編｜たは。（∩∀`＊)
• STUDIO WING .lib: PostgreSQL：テーブルを作成　CREATE TABLE
• インデックスの作成 - インデックスの作成 - PostgreSQLの使い方
• 文字列型の使い分け — Let's Postgres
• UNIXコマンド - su
• 権限の追加 - ロール(ユーザー)の作成 - PostgreSQLの使い方
• postgresql - Why is a new user allowed to create a table? - Database Administrators Stack Exchange
• PostgreSQLスキルアップノート
• PostgreSQLでの「¥c」コマンドによる接続先データベースの変更方法 - r_nobuホームページ
• sql - GRANT SELECT privilege to ALL sequences using one statement - Stack Overflow