postgresql基本操作 + DBに他のユーザ(Superuser以外)からアクセスできないようにする

デフォルトでは、psql postgresでログインできる

User関係

1. User作成
   test_userというUnixUserがいるとして、

   create user test_user with password 'password';
   

   これによって、test_userというroleもできる

2. roleの確認

   \du
   

   結果

                                        List of roles
     Role name       |                         Attributes                         | Member of
   

----------------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | Superuser, Create role, Create DB | {}

```

1. CurrentUserをチェックする

   select current_user;
   

2. SuperUserにする

   ALTER USER myuser WITH SUPERUSER;
   

3. test_userをmd5認証にする

   Postgresでは、デフォルトでUnixのUser名と同じでないといけない（peer）という認証方法なので、test_userというUnixユーザ名以外の人は、このUserを使えない。そこで、/etc/postgresql/9.5/main/pg_hba.confを編集して、unixユーザ名が異なるユーザでもtest_userを使ってログインできるようにする(md5)

   2行目を追加

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             test_user                               md5
   # "local" is for Unix domain socket connections only
   local   all             all                                     peer
   

    これで、権限のあるDBには、以下のコマンドでログインできるようになる。（今の時点では、DBの権限が一つもないので、入れない）

    ```
    psql -d <db_name> -u test_user -W
    ```

1. あるDBの権限をUserに与える（psql postgresからログインして権限を与える）

    権限を与える
    ```
    GRANT connect on database test_db to test_user;
    ```

    権限がちゃんと与えられたかチェック (dbリストを表示して　Access PrivAccess Privilegesにtest_userがいるのを確認)

    ```
    \l
    ```

    これで、上記のtest_userは以下のコマンドでログインできるようになる。

    ```
    psql -u test_user -d test_db
    ```

1. DBにはつなげるがテーブルの中身が見れない

    以下のコマンドで`test_table`にアクセスできるようにする。
    ```
    GRANT ALL PRIVILEGES ON TABLE test_table TO test_user;
    ```
　　（全部のテーブルを可能にするやり方は調べてない…）

# DB関係の操作

1. DB作成/削除

    ```
    create database test_database;
    drop database test_database;
    ```


1. PublicからのAccessをなくす

    ```
    REVOKE ALL ON DATABASE test_database FROM PUBLIC;
    ```
    RoleがSuperUserのUserは、Accessができる。

    SuperUser以外では、以下のようにアクセス出来ないようになる
    ```
    select * from users;
    ERROR:  permission denied for relation users
    ```


1. あるDBへ移行する(mysql でいう`use <database name>;`)

    ```
    \c test_database
    You are now connected to database "test_database" as user "test_user".
    ```