PostgreSQLの設定

カテゴリ:データベース

CentOS/Ubuntu 両対応

PostgreSQL の初期設定

PostgreSQLをインストールしたら、CentOSの場合のみ、初回のみ以下のコマンドを実行し、初期化します。

# postgresql-setup initdb

PostgreSQLを起動

# systemctl start postgresql

データベースに接続

初期設定ではログイン方式はident認証が設定されており、OSのログインユーザーとPostgreSQLのユーザーがマップされています。また初期設定ではPostgreSQLのスーパーユーザー「postgres」が作成されているため、ユーザーをpostgresに切り替えident認証でPostgreSQLに接続します。
※パスワード認証に変更する方法は後述します。

# su - postgres

psqlコマンドで接続します。

$ psql
psql (10.17)
Type "help" for help.

postgres=#

※上記のように「postgres=#」が表示されれば接続成功です。

ユーザーの作成

「hoge」ユーザーをパスワード付きで作成する場合は以下のコマンドを実行します。

postgres=# CREATE USER hoge WITH PASSWORD 'pass';
CREATE ROLE

ユーザーのパスワードを変更

「postgres」ユーザーにパスワードを設定しておきます。
パスワードを変更する場合は、以下のコマンドを実行します。

postgres=# ALTER USER postgres WITH PASSWORD 'pass';
ALTER ROLE

データベースの作成

以下のコマンドで「hoge」ユーザーが所有者の「hoge_db」データベースを作成します。

postgres=# CREATE DATABASE hoge_db OWNER hoge;
CREATE DATABASE

データベースの一覧を表示

以下のコマンドでデータベースの一覧が表示されます。
作成した「hoge_db」が作成されていることが確認できます。

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 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
(3 rows)

データベースから切断するには \q を入力します。

アクセス制御

ちなみに、CentOSのみ、初期設定ではパスワード認証での接続は無効になっています。

# psql -U hoge -h 127.0.0.1 -d hoge_db
psql: FATAL:  Ident authentication failed for user "hoge"

これは、/var/lib/pgsql/data/pg_hba.conf ファイルで 127.0.0.1/32 からのident認証(OSのログインユーザーとPostgreSQLのユーザーをマップさせる)のみが許可されているためです。

# tail /var/lib/pgsql/data/pg_hba.conf
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

METHODについて、pg_hba.confファイルでは以下のように説明されています。

# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.

パスワード認証を許可するためには、以下のようにMETHODをmd5に変更します。

# tail /var/lib/pgsql/data/pg_hba.conf
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

参考Ubuntuのpg_hba.confファイル(/etc/postgresql/xx/main/pg_hba.conf)では、初めからMETHODはmd5に設定されています。(xxはPostgreSQLのバージョン)

設定を反映するためにPostgreSQLを再起動します。

# systemctl restart postgresql

パスワード認証で接続できることを確認します。

# psql -U hoge -h 127.0.0.1 -d hoge_db
Password for user hoge:
psql (10.17)
Type "help" for help.

hoge_db=>

もしスーパーユーザー権限で操作したい場合は、postgresユーザーに切り替えてpsqlコマンドを実行しなくても、以下のようにパスワード認証でログインする事ができます。
※先にpostgresユーザーのパスワードを設定しておく必要があります。

# psql -U postgres -h 127.0.0.1
ユーザ postgres のパスワード:
psql (8.4.20)
"help" でヘルプを表示します.

postgres=#

※JavaやPHPなどのWebアプリケーションから127.0.0.1のデータベースにパスワード認証で接続する場合は、この設定は必須になります。

PostgreSQLを自動起動に設定

OS起動時にPostgreSQLが自動起動するように設定します。

# systemctl enable postgresql

公開日時:2014年08月09日 15:26:15
最終更新日時:2021年08月21日 08:44:21

なお、VPS選びで迷ったら、こちらの記事で主要VPSのメモリ容量ごとの月額、年額料金を比較していますので、是非参考にしてみてください。

データベースに戻る

「データベース」に関するTips

このページのトップに戻る