【データベース】Postgreでのデータベースサーバー構築メモ　~初期設定＆ユーザー設定~

# 目的
Postgreでのデータベースサーバー構築を行えるようになる

・Postgreインストール
・ユーザー作成


### Postgreのインストール
⭐AmazonLinuxだとPostgreのリポジトリが用意されているのでインストールコマンドだけでおｋ

:::note warn
RHELだとリポジトリ追加が必要
```bash
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
```
:::

Postgreインストール
```bash
yum install -y postgresql15-server
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/37ae581b-4c3d-4a80-9d02-17181a49625c.png)
↑成功するとComplete!と出る

## 初期化

インストール後は初期化が必要なので下記コマンドで初期化
```bash
postgresql-setup initdb
```

### パスワード変更

Postgreをインストールする過程でDB管理用ユーザー『postgres』が自動で生成される

```bash
cat /etc/passwd | grep postgres
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/e8515cd1-8b20-412e-a729-5d7320ae4c99.png)
↑作成されている

このユーザーはOS側のパスワードが設定されていないため、手動設定する
```bash
passwd postgres

Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
```

### postgresqlのサービス起動

```bash
systemctl start postgresql
systemctl enable postgresql
```

## ユーザー関連設定

postgresに変更
```bash
sudo su - postgres

### postgresにログイン
```bash
psql
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/ec24c2a9-1c3b-496c-88bb-5bfc89604f2e.png)
↑ターミナルがpostgre表示になる

### ユーザー一覧表示
```bash
\du
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/7ce09a2b-c2eb-407a-bf34-4fb76d775af6.png)
↑現状、postgresユーザーしか存在しない

### ユーザー作成
create userコマンドでユーザー作成
```bash
CREATE USER myuser WITH PASSWORD 'securepass123';
\du
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/0d005d7a-5feb-4b24-b4e3-5c62aee9f5b9.png)
↑できている

### ロール操作
既存のユーザーへのロール操作は```alter role```コマンド

```bash
ALTER ROLE myuser WITH CREATEDB CREATEROLE;
\du
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/cc29d7c0-0c87-4301-854e-d27b58f88c63.png)
↑作成された

:::note
特定ユーザーのログインを禁止する時
ALTER ROLE myuser NOLOGIN;
:::

### 認証方式変更

:::note
peer認証およびident認証ではOSユーザーとDBユーザー名が一致していなければ認証は成功しない。
:::

```pg_hba.conf```にpostgresqlの認証設定が記載
```pg_hba.conf```の場所は下記コマンドで確認可能

```bash
SHOW hba_file;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/316416a9-8ffb-4858-b4a8-c0bc1eb726dc.png)
↑パスが表示

```bash
cat /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# 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                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
```
peer認証とident認証をscram-sha-256認証に書き換えてサービス再起動

```bash
sed -i.backup -e 's/peer/scram-sha-256/g' -e 's/ident/scram-sha-256/g' /var/lib/pgsql/data/pg_hba.conf
```

また、postgresユーザーだけはpeer認証でログインできるように固定する
```bash
vim pg_hba.conf
local   all             postgres                             peer        
```


### パスワード方式変更

postgreではpg_authidテーブルにユーザーのパスワード情報が保持されている
```bash
SELECT rolname, rolpassword FROM pg_authid;
          rolname          |                                                              rolpassword
---------------------------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres                  |
 pg_database_owner         |
 pg_read_all_data          |
 pg_write_all_data         |
 pg_monitor                |
 pg_read_all_settings      |
 pg_read_all_stats         |
 pg_stat_scan_tables       |
 pg_read_server_files      |
 pg_write_server_files     |
 pg_execute_server_program |
 pg_signal_backend         |
 pg_checkpoint             |
 myuser                    | SCRAM-SHA-256$4096:gZyJL33MaohBR9YswtTf1g==$IovFYtPfJUcP/ZK+rM3hSJUcVDmYAnyC7JJc5mQFOHs=:99QoQdqx9ONmlzmvGtkVIEpTDUCaAz8v8x0IofFRI6o=
(14 rows)
```

全体でのパスワード方式をscram-sha-256に変更
```bash
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
```

パスワード変更
```
ALTER USER myuser WITH PASSWORD 'newpassword';
```

### ログイン
作成したmyuserでpostgresqlにログインする
```bash
\q #ログアウト
psql -U myuser -d postgres
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801383/5896ce34-c671-481b-8dc7-19a5ce9683b8.png)
↑ログイン完了

:::note
スーパーユーザーだとプロンプト名に『DB名=#』
通常ユーザーだと『DB名=>』
:::