AWS cloud9でpostgreSQLのユーザー切り替え・ログイン
インストールは「AWS cloud9にpostgresQLをインストール！」を参照
参考にさせていただいたサイト
インストールした段階でpostgresというスーパーユーザーが作成されている。
パスワードを決めて、ユーザーの切り替えを行う。
$ sudo passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
# ユーザーの切りかえ
$ su - postgres
Password:
Last login: Thu May 24 01:04:32 UTC 2018 on pts/2
-bash-4.2$
PostgreSQLは初期設定では「Unixユーザ名」と「PostgreSQLのユーザ」が一致しないとログインできない(Peer認証)。
新しいユーザを作り、sudoしないでshellからpsqlにログイン&アプリケーションからつなげるようにしていく。
最初はpostgresユーザしかいないのでpostgresユーザで入る
create user 【ユーザ名】 createdb password '【パスワード】' login;でユーザーを作る
-bash-4.2$ psql
psql (9.6.8)
Type "help" for help.
postgres=# create user testuser createdb password 'testuser1234' login;
CREATE ROLE
postgres=# \q
-bash-4.2$ exit
logout
# パスワードは再設定できる
postgres=# \password
Enter new password:
Enter it again:
postgres=#
# アプリケーション用ユーザの作成
-- ユーザを作る
postgres=# CREATE ROLE ユーザ名 LOGIN CREATEDB PASSWORD 'ここにパスワード';
-- ↑のユーザが管理したいdbを作る
postgres=# CREATE DATABASE データベース名 OWNER ユーザ名;
「pg_hba.conf」という認証ファイルのデフォルト値が「OSのユーザーIDとpostgreのユーザーIDが一致していれば受け入れる（ident）」になっているから、ここを「パスワード認証(md5)」か「無条件で信頼(trust)」に書き換える必要があります。
$ vim /var/lib/pgsql96/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
vimを使って右端の項目を全て「trust」(無条件で信頼)に変更
変更後、再起動
$ sudo service postgresql96 restart
Stopping postgresql96 service: [ OK ]
Starting postgresql96 service: [ OK ]
これで以下でログイン可能になる
psql -U ユーザ名 -d データベース名 -h localhost;
以上の設定で
postgresql://ユーザ名:パスワード@localhost:5432/データベース名
でアクセスできる。
## おまけ
heroku postgresにローカルからアクセス
参考にさせていただいたサイト
herokuに繋ごうとするが、ターミナル再起動のたびにnodeのバージョンが下がるから最新版をセット
$ heroku pg:psql
WARNING
WARNING Node version must be >=8.0.0 to use this CLI
WARNING Current node version: 6.13.1
$ nvm use 8.0
Now using node v8.0.0 (npm v5.0.0)
heroku toolbeltがインストールされていることを前提として、以下のコマンドを実行する。
$ heroku pg:psql -a sample_app
--> Connecting to postgresql-shallow-77491
psql (9.6.8, server 10.3 (Ubuntu 10.3-1.pgdg16.04+1))
WARNING: psql major version 9.6, server major version 10.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
sample_app::DATABASE=>
データベースを作成
sample_app::DATABASE=> create table sample(
sample_app::DATABASE(> id integer primary_key,
sample_app::DATABASE(> name varchar(100) not null,
sample_app::DATABASE(> primary key (id)
sample_app::DATABASE(> );
CREATE TABLE
あとは普通にSQLが書ける
sample_app::DATABASE=> select * from sample;
id | name
----+----------
(0 row)
権限の追加
- psqlにスーパーユーザーで入る
- 権限の追加
- ERROR: permission denied for sequence cities_id_seq using Postgres
- 上記リンクを参考にGRANTを用いてロール（ユーザー）に権限を自由に付与
- サービスの再起動
$ psql -d sample_app -U postgres;
sample_app=# GRANT SELECT, UPDATE, INSERT,DELETE ON sample_app TO testuser;
GRANT
sample_app=# GRANT USAGE, SELECT, UPDATE ON sample_app_id_seq TO testuser;
stockpile=# \q
$ sudo service postgresql96 restart
Stopping postgresql96 service: [ OK ]
Starting postgresql96 service: [ OK ]
