psqlを使ってPostgreSQLに接続し、新しくデータベース・ロール（ユーザー）・テーブルを作成します。また、権限がなく作成したデータベースに接続できない場合は、所有者を変更します。
もくじ
postgresロールで接続する（psql）
sudo -u postgres psql
postgresロール以外をパスワード認証に変更
一般ロールがログインしやすいように、ローカルでの接続を、peer認証からパスワード認証に変更します。
~$ psql -U role_name -d newdb
psql: FATAL: ユーザ "role_name" で対向(peer)認証に失敗しました
パスワード認証に変更しないと、こんな感じで接続に失敗します。
pg_hba.confファイルの設定
まずは、pg_hba.confがどこにあるか調べます。
postgres=# show hba_file;
hba_file
-------------------------------------
/etc/postgresql/11/main/pg_hba.conf
(1 行)
postgres=# \q
sudo nano /etc/postgresql/11/main/pg_hba.conf
peerをmd5に変更します。
# "local" is for Unix domain socket connections only
local all all peer
↓
# "local" is for Unix domain socket connections only
local all all md5
設定を反映させるために、PostgreSQLを再起動します。
sudo systemctl restart postgresql
新しくデータベースを作成する
sudo -u postgres psql
postgres=# create database newdb;
作成したデータベースを確認
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
newdb | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
新しくロール（ユーザー）を作成する
postgres=# create role role_name with login password 'login_pass';
作成したロールを確認
postgres=# \du
ロール一覧
ロール名 | 属性 | 所属グループ
-----------+----------------------------------------------------------------------------+--------------
postgres | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
role_name | | {}
作成したロールで接続
psql -U role_name -d newdb
新しくテーブルを作成する
newdb=> create table new_table (id serial, name varchar(255), primary key(id));
作成したテーブルを確認
newdb=> \dt
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+-----------+----------+-----------
public | new_table | テーブル | role_name
newdb=> \d
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+------------------+------------+-----------
public | new_table | テーブル | role_name
public | new_table_id_seq | シーケンス | role_name
newdb=> \d new_table
テーブル "public.new_table"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+------------------------+----------+---------------+---------------------------------------
id | integer | | not null | nextval('new_table_id_seq'::regclass)
name | character varying(255) | | |
インデックス:
"new_table_pkey" PRIMARY KEY, btree (id)
所有者を変更
データベース
postgres=# alter database newdb owner to role_name;
所有者の変更を確認
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
newdb | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres=# alter database newdb owner to role_name;
ALTER DATABASE
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+-----------+------------------+-------------+-------------------+-----------------------
newdb | role_name | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
テーブル
newdb=# alter table new_table owner to postgres;
所有者の変更を確認
postgres=# \c newdb
データベース "newdb" にユーザ "postgres" として接続しました。
newdb=# \dt
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+-----------+----------+-----------
public | new_table | テーブル | role_name
(1 行)
newdb=# alter table new_table owner to postgres;
ALTER TABLE
newdb=# \dt
リレーション一覧
スキーマ | 名前 | 型 | 所有者
----------+-----------+----------+----------
public | new_table | テーブル | postgres
(1 行)
