Книга "Администрирование PostgreSQL 9. Книга рецептов"

Клиент psql

Переменные окружения:

  • PGHOST или PGHOSTADDR - адрес сервера PostgreSQL для подключения,
  • PGPORT - TCP-порт сервера PostgreSQL для подключения (по умолчанию используется порт 5432),
  • PGDATABASE - база данных на сервере PostgreSQL (по умолчанию используется база данных postgres),
  • PGUSER - имя пользователя на сервере, от имени которого нужно установить подключения,
  • PGPASSWORD - пароль пользователя на сервере (использовать эту переменную окружения не рекомендуется из соображений безопасности),
  • PGPASSFILE - местоположение файла с настройками подключения к базам данных (по умолчанию используется файл с именем ~/.pgpass).

Опции командной строки:

  • -h <сервер> - адрес сервера PostgreSQL для подключения,
  • -p <TCP-порт> - TCP-порт сервера PostgreSQL для подключения (по умолчанию используется порт 5432),
  • -d <база данных> - база данных на сервере PostgreSQL (по умолчанию используется база данных postgres),
  • -U <пользователь> - имя пользователя на сервере, от имени которого нужно установить подключения,
  • -W - опция указывает на необходимость спросить пароль пользователя в диалоговом режиме (в противном случае будет предпринята попытка подключения без аутентификации, с использованием переменной окружения или настроек из файла ~/.pgpass),
  • -c <SQL-запрос> - выполнить указанный SQL-запрос, вывести результаты и завершить работу,
  • -f <SQL-скрипт> - выполнить SQL-запросы из файла с указанным именем, вывести результаты выполнения каждого запроса и завершить работу.

Команды:

  • help - вывести справку по использованию psql,
  • \quit или \q - завершить работу psql в диалоговом режиме,
  • \h - вывести справку о мета-командах psql (о командах, начинающихся с обратной косой черты),
  • \? - вывести справку по SQL-запросам. Через пробел после мета-команды можно указать конкретную команду, по которой нужно вывести справку: CREATE DATABASE, ALTER TABLE, DELETE и т.п.,
  • \password - мета-команда для смены пароля текущего пользователя. Для смены пароля нужно будет дважды ввести текущий пароль, после чего ввести новый пароль.

Файл ~/.pgpass состоит из одной или нескольких строчек, в каждой из которых задаётся шаблон настроек подключения и пароль для использования с этим шаблоном. В каждой строчке есть следующие колонки, разделённые двоеточиями:

  1. сервер,
  2. порт сервера,
  3. имя базы данных,
  4. имя пользователя,
  5. пароль пользователя.

В любом из полей, кроме поля пароля, вместо конкретного значения допускается указывать звёздочку. Это значение будет указывать на совпадение с любым значением, указанным с помощью опций командной строки, в том числе со значениями, которые используются по умолчанию.

Права доступа к файлу должны разрешать его чтение только владельцу, в противном случае psql откажется использовать этот файл. Выставить подходящие права доступа можно с помощью следующей команды:

$ chmod 0600 ~/.pgpass

Сервер PostgreSQL

Файл pg_hba.conf

HBA расшифровывается как Host-based authentication, то есть аутентификация на основе узлов. Файл определяет, какой метод аутентификации нужно использовать для определённых сочетаний базы данных, имени пользователя и адреса клиента. Правила задаются строками, в каждой из которых есть следующие колонки, разделённые пробельными символами:

  • TYPE - тип узла. Значение host соответствует удалённому клиенту, подключающемуся через TCP-порт,
  • DATABASE - имя базы данных, для которой действует правило. Особое значение all обозначает любую базу данных, Список баз данных можно указать через запятую. Если колонка начинается с символа @, то список баз данных находится в файле с указанным именем. Допустимо значение sameuser - имя базы данных должно совпадать с именем пользователя.
  • USER - имя пользователя, для которого действует правило. Особое значение all обозначает любое имя пользователя, Список пользователей можно указать через запятую. Если колонка начинается с символа +, то правило соответствует указанной группе пользователей. Если колонка начинается с символа @, то список пользовтаелей находится в файле с указанным именем.
  • CIDR-ADDRESS - адрес подсети в виде A.B.C.D/M, определяющий IP-адреса клиентов, соответствующих этому правилу. Допустимы значения samenet и samehost (?).
  • METHOD - метод аутентификации. Значение reject запрещает доступ. Значение trust обозначает разрешение доступа без проверки пароля.

Файл postgresql.conf

Опции:

  • listen_address = '*' - опция позволяет настроить приём подключений через все имеющиеся в системе сетевые интерфейсы.

Полезные запросы

  • SELECT inet_server_addr(); - запрос позволяет узнать IP-адрес сервера, к которому было установлено текущее подключение,
  • SELECT inet_server_port(); - запрос позволяет узнать номер TCP-порта, прослушиваемого сервером,
  • SELECT current_database(); - запрос позволяет узнать имя базы данных, подключение к которой установлено в настоящее время,
  • SELECT current_user; - запрос позволяет узнать имя пользователя, под которым было установлено подключение,
  • SELECT version(); - запрос позволяет узнать версию СУБД PostgreSQL,
  • ALTER USER user PASSWORD 'secret'; - запрос для установки нового пароля пользователя, где user - имя пользователя, а secret - его новый пароль. Пароль можно отправлять и в хэшированном виде.