Как мне найти путь к pg_hba.conf из оболочки?
Я хотел бы получить путь к pg_hba.conf из оболочки. Путь варьируется между версиями PostgreSQL. Например, для 8.4 и 9.1:
Я пробовал pg_config команду, но она, кажется, не включает эту информацию.
Это сделано для того, чтобы я мог использовать единую команду для открытия pg_hba.conf и другие файлы конфигурации PostgreSQL для редактирования.
Я использую Bash.
pg_config для информации о комплиментах, чтобы помочь расширениям и клиентским программам компилировать и связывать с PostgreSQL. Он ничего не знает об активных экземплярах PostgreSQL на машине, только двоичные файлы.
pg_hba.conf может появиться во многих других местах в зависимости от того, как был установлен Pg. Стандартное расположение в pg_hba.conf пределах data_directory базы данных (который может быть в /home , /var/lib/pgsql , /var/lib/postgresql/[version]/ , /opt/postgres/ и т.д. и т.д. и т.д.) , но пользователи и упаковщиков может поставить его там , где им нравится. К несчастью.
Единственный верный способ найти pg_hba.conf — это спросить работающий экземпляр PostgreSQL, где он pg_hba.conf находится, или спросить системного администратора, где он находится. Вы даже не можете полагаться на вопрос, где находится datadir, и на синтаксический анализ, postgresql.conf потому что сценарий инициализации может передавать параметр, как -c hba_file=/some/other/path при запуске Pg.
Что вы хотите сделать, это спросить PostgreSQL:
Эта команда должна выполняться в сеансе суперпользователя, поэтому для сценариев оболочки вы можете написать что-то вроде:
и установить переменные окружения PGUSER , PGDATABASE и т.д. , чтобы убедиться , что соединение является правильным.
Да, это в некоторой степени проблема курицы и яйца: если пользователь не может подключиться (скажем, испортил редактирование pg_hba.conf ), вы не можете найти его pg_hba.conf , чтобы исправить.
Другой вариант — посмотреть на ps вывод команды и посмотреть, видим ли там аргумент каталога данных postmaster -D , например
так как pg_hba.conf будет в каталоге данных (если вы не используете Debian / Ubuntu или какую-либо производную и используете их пакеты).
Если вы нацелены конкретно на системы Ubuntu с PostgreSQL, установленным из пакетов Debian / Ubuntu, это станет немного проще. Вам не нужно иметь дело с Pg, скомпилированным вручную из исходного кода, для которого чей-то initdb da dadadir находится в его домашнем каталоге, или с Pg EnterpriseDB, установленным в / opt, и т. Д. Вы можете спросить pg_wrapper , многоверсионный Pg Debian / Ubuntu менеджер, где PostgreSQL использует в pg_lsclusters команду с pg_wrapper .
PostgreSQL (Русский)
PostgreSQL — это поддерживаемая сообществом система управления базами данных с открытым исходным кодом.
Contents
Установка
Установите пакет postgresql . Он также создаст системного пользователя postgres.
Для переключения в пользователя postgres можно использовать sudo:
Смотрите также документацию sudo(8) или su(1) .
Начальная настройка
В первую очередь необходимо инициализировать кластер баз данных:
Где опция -D указывает на стандартное расположение данных кластера (если вы хотите использовать другой каталог, смотрите #Изменение стандартного каталога данных).
По умолчанию локаль и кодировка наследуются из вашего текущего окружения (используется значение $LANG). [1] Если вас это не устраивает, вы можете прописать нужные параметры вручную с помощью опций:
- —locale=локаль , где локаль должна быть одной из доступных системных локалей;
- -E кодировка для выбора кодировки (должна соответствовать выбранной локали).
Пример для русской локали:
После инициализации на экране появится много строчек, некоторых из которых оканчиваются на . ок :
Если вы видите подобное, значит инициализация прошла успешно. Можно вернуться в обычного пользователя, выполнив команду exit в сеансе пользователя postgres.
- Если база данных располагается на файловой системе Btrfs, стоит отключить Copy-on-Write для каталога перед созданием любых баз данных.
- Если база данных располагается на файловой системе ZFS, прочтите ZFS#Databases перед созданием любых баз данных.
Наконец, запустите и включите службу postgresql.service .
Создание Вашей первой базы данных
Становимся пользователем postgres. Добавляем нового пользователя базы данных с помощью команды createuser:
Создаём новую базу данных от имени пользователя, имеющего доступ на чтение-запись, с помощью команды createdb (выполните эту команду в вашей обычной оболочке, если имя будущего владельца базы данных совпадает с вашим именем пользователя в Linux, в ином случае добавьте опцию -O имя-пользователя )
Знакомство с PostgreSQL
Доступ к оболочке базы данных
Становимся postgres пользователем. Запускаем основную оболочку базы данных, в которой мы сможем создавать, удалять базы данных/таблицы, задавать права и запускать команды SQL. Используйте опцию -d , чтобы указать название базы данных, которую вы создали (если опцию не указать, то psql попытается подключиться к базе, имя которой совпадает с именем пользователя).
- Список всех возможных команд (например, CREATE TABLE ) для запросов
- Подробное описание команды
- Подключаем определённую базу данных
- Список всех пользователей и их уровни доступа
- Краткая информация о всех таблицах в текущей базе данных
- Меняем пароль
- Показать все используемые настройки
- Выйти из psql
Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу. Для просмотра всех мета-команд введите:
Дополнительные настройки
Файл настроек сервера баз данных PostgreSQL postgresql.conf . Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data . В этой же папке находятся основные файлы настроек включая и pg_hba.conf .
Ограничение доступа к суперпользователю по умолчанию
По умолчанию pg_hba.conf разрешает подключение любого локального пользователя к любому пользователю базы данных, в том числе суперпользователю. Скорее всего это не то, что вам нужно, поэтому, чтобы разрешить подключение только пользователю postgres, измените эту строку:
Можно добавить дополнительные строки в зависимости от ваших потребностей.
Требование пароля при входе
Измените /var/lib/postgres/data/pg_hba.conf , прописав метод аутентификации для каждого пользователя (или «all» для всех пользователей) на scram-sha-256 (предпочтительно) или md5 (менее безопасно; по возможности стоит его избегать):
Если вы выбрали scram-sha-256 , также нужно изменить /var/lib/postgres/data/postgresql.conf :
Перезапустите службу postgresql.service и заново пропишите пароли для пользователей с помощью SQL-запроса ALTER USER пользователь WITH ENCRYPTED PASSWORD ‘пароль‘; .
Доступ только через Unix-сокет
В разделе сonnections and authentications пропишите:
Это полностью отключит доступ через сеть. Не забудьте перезапустить службу postgresql.service для применения изменений.
Доступ с удалённых хостов
В разделе connections and authentications раскомментируйте или исправьте строку listen_addresses по вашему желанию на
и внимательно просмотрите другие строки.
Далее добавляем следующую строку в основной файл настройки проверки подлинности /var/lib/postgres/data/pg_hba.conf . (если вы планируете подключатся только со своего компьютера, то пропустите данный шаг) Этот файл определяет, каким хостам разрешено подключаться, так что будьте осторожны.
где your_desired_ip_address — IP-адрес клиента.
После этого необходимо перезапустить демон, чтобы изменения вступили в силу
Смотрите также документацию по pg_hba.conf.
Если возникли проблемы взгляните на лог-файл сервера
Настройка аутентификации через PAM
PostgreSQL предлагает несколько методов аутентификации. Если вы хотите разрешить пользователям аутентифицироваться с их системным паролем, необходимы дополнительные шаги. Сначала вам нужно включить PAM для соединения.
Например, та же конфигурация, что и выше, но с включенным PAM:
Однако сервер PostgreSQL работает без прав root и не сможет получить доступ к файлу /etc/shadow . Мы можем обойти это, разрешив группе postgres доступ к этому файлу:
Изменение стандартного каталога данных
По умолчанию PostgreSQL настроен на использование каталога /var/lib/postgres/data для хранения всех баз данных. Для его изменения выполните следующие шаги:
Создайте новый каталог и сделайте пользователя postgres его владельцем:
Войдите в пользователя postgres и выполните инициализацию кластера:
Отредактируйте службу postgresql.service , создав drop-in файл и переопределив настройки Environment и PIDFile . Например:
Если вы хотите использовать каталог в /home , добавьте ещё одну строку:
Изменение кодировки новых баз данных на UTF-8
Когда создаётся новая база данных (например, createdb blog ) PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ваниль, и template1 используемый по умолчанию. Один из вариантов изменения кодировки новой базы данных, заключается в изменении шаблона template1. Для этого, заходим в оболочку PostgresSQL (psql) и делаем вот что:
1. Первое, мы должны сбросить template1. Шаблоны не могут быть сброшены, так что мы сначала изменим его, как обычную базу данных:
2. Сейчас уже можно сбросить её:
3. Создаём новую базу данных, с новой кодировкой по умолчанию из template0:
4. Теперь снова сделаем template1 шаблоном:
5. (Рекомендация) Документация по PostgreSQL advises рекомендует «замораживать» изменения шаблона функцией VACUUM FREEZE:
6. (По желанию) Если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE:
Теперь вы можете создать базу данных используя стандартные команды в терминале:
Если снова войти в PSQL и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:
Графические инструменты
- phpPgAdmin — Веб-интерфейс для администрирования PostgreSQL.
- pgAdmin — Комплексный графический интерфейс для управления PostgreSQL.
- pgModeler — Инструмент для моделирования баз данных PostgreSQL.
Список инструментов, поддерживающих несколько разных СУБД, можно посмотреть в статье List of applications/Documents#Database tools.
Обновление PostgreSQL
alt=»Tango-view-fullscreen.png» width=»48″ height=»48″ />This article or section needs expansion. alt=»Tango-view-fullscreen.png» width=»48″ height=»48″ />
Для обновления до новой мажорной версии PostgreSQL необходима специальная процедура.
- Следуйте официальной документации по обновлению.
- Начиная с версии 10.0 , PostgreSQL изменил схему версионирования. Раньше мажорными были обновления с 9.x до 9.y . Теперь обновления с 10.x до 10.y считаются минорными, а мажорным является обновление с 10.x до 11.y .
Посмотреть текущую версию можно так:
Чтобы случайно не обновиться до несовместимой версии, рекомендуется запретить обновления пакетов PostgreSQL:
Минорные обновления вполне безопасны. Однако если вы случайно обновитесь до другой мажорной версии, то не сможете получить доступ к данным. Всегда проверяйте домашнюю страницу PostgreSQL, чтобы знать, какие шаги требуются для каждого обновления. Чтобы узнать, почему это так, смотрите политику управления версиями.
Есть два основных способа обновить базу данных PostgreSQL. Подробности читайте в официальной документации.
pg_upgrade
Для тех, кто хочет использовать pg_upgrade , доступен пакет postgresql-old-upgrade , который всегда отстаёт на одну мажорную версию от основного пакета PostgreSQL. Его можно установить параллельно с новой версией PostgreSQL. Для обновления более старых версий PostgreSQL доступны пакеты AUR: postgresql-96-upgrade AUR , postgresql-95-upgrade AUR , postgresql-94-upgrade AUR , postgresql-93-upgrade AUR , postgresql-92-upgrade AUR . Прочтите справочную страницу pg_upgrade(1) , чтобы понять, какие действия он выполняет.
Обратите внимание, что каталог кластера баз данных не меняется от версии к версии, поэтому перед запуском pg_upgrade необходимо переименовать существующий каталог данных и перейти в новый каталог. Новый кластер баз данных необходимо инициализировать, как описано в разделе #Установка.
Пока база данных все еще доступна, можно воспользоваться возможностью проверить используемый язык и кодировку, а также контрольные суммы данных:
Когда вы будете готовы к обновлению, выполните следующие шаги:
Остановите службу postgresql.service . Проверьте статус службы, чтобы убедиться, что PostgreSQL завершился корректно, иначе pg_upgrade не сможет отработать корректно.
Переименуйте каталог с кластером и создайте новый каталог:
Не забудьте прописать опции —locale , -E и/или —data-checksums по необходимости.
Обновите кластер, выполнив эту команду (замените PG_VERSION на номер старой версии, например 12 ):
Командой pg_upgrade будут созданы скрипты analyze_new_cluster.sh и delete_old_cluster.sh в каталоге /var/lib/postgres/tmp/ и выведены инструкции по их использованию.
- analyze_new_cluster.sh генерирует статистику оптимизатора для нового кластера и должен запускаться от имени пользователя postgres . Для его работы postgresql.service должен быть запущен.
- delete_old_cluster.sh просто удаляет каталог /var/lib/postgres/olddata и должен запускаться от имени пользователя, имеющего права записи в /var/lib/postgres (например, от имени root).
Когда обновление будет полностью завершено, каталог /var/lib/postgres/tmp можно будет удалить.
Выгрузка и загрузка вручную
Ещё можно сделать что-то вроде такого (после обновления и установки postgresql-old-upgrade ):
- В примере показано обновление с PostgreSQL 12; посмотрите в /opt/ установленную у вас версию postgresql-old-upgrade и исправьте команды по необходимости.
- Если вы меняли файл pg_hba.conf , вам может понадобиться временно разрешить полный доступ к старому кластеру с локальной системы. После обновления не забудьте прописать нужные вам настройки в новом кластере и перезапустить службу postgresql.service .
Решение проблем
Ускорение мелких транзакций
Если вы используете PostgreSQL на своей локальной машине для разработки и он медленный, то можете попробовать отключить synchronous_commit в конфигурации. Однако, не забывайте про его особенности.
Запретить запись на диск во время бездействия
PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать (и изнашивает) жёсткому диску, заставляя его шуршать. Однако можно легко и безопасно поменять локацию файла внутрь ФС (/run) расположенной в ОЗУ с помощью такой настройки:
Configure a PostgreSQL Server
This tutorial will explain how to configure a PostgreSQL server and troubleshoot the various issues that may arise during the configuration process. While it was developed for UNIX-based systems, like MacOS and Linux, PostgreSQL is highly portable so it will also run on other operating systems like Windows, Solaris, Tru64 Unix and FreeBSD. Because of the ways various operating system work, configuring a PostgreSQL server can create various issues on different systems. While there are sometimes problems when trying to configure a PostgreSQL server, there are fairly simple fixes for most of these issues.
Prerequisites
A currently supported version of PostgreSQL, 9 through 12 as of this writing, must be properly installed on the dev machine or server. Installing a 64-bit version of Postgres is recommended.
At least 2GB of memory and 256MB of free of hard-drive space is required.
Pseudo elevated privileges during terminal access must be granted in order to configure a PostgreSQL server.
Possess a basic familiarity with the UNIX shell-command line.
PostgreSQL setup
Note that errors may occur while attempting to connect to the psql command-line interface or when starting the Postgres server. The most common causes for these errors will be covered in this tutorial.
First, confirm that PostgreSQL was successfully installed by calling up its version number with the following command:
If working properly, the system should return a response that resembles the following:
Now execute the following command for Postgres:
If something like a could not identify current directory or role not found error occurs, it is not an issue for concern as most these errors can be cleared up once PostgreSQL is properly configured.
If a postgres: command not found error occurs, it is typically the result of the Postgres PATH not being properly exported or that Postgres was installed using a non-standard repository.
Configure PostgreSQL
Unlike MySQL, Postgres is pretty much designed to work as is, without much configuration. However, it is generally a good idea to make some modifications in both the pg_hba.conf and postgresql.conf files.
Locate the postgresql.conf file
The locations of the .conf files will depend on the specific OS being used and the installed version of PostgreSQL.
Execute the following find command, in a UNIX terminal, to locate the postgresql.conf file:
The results should resemble the following list of files:
Alternatively, the files may also be located with this command:
Following is a third find method that involves using the -wholename option to search for Postgres by its complete file name:
pg_hba.conf location
The find command can also be used to obtain the location of the pg_hba.conf file, as follows:
If using MacOS, the file will probably be in one of the following locations:
If using Postgres on a Linux server, the file will mostly likely be in the /etc/postgresql directory. Try locating it by executing the following command:
Next, configure the default settings for PostgreSQL.
Postgres configuration
Use an IDE like Sublime or a terminal-based text editor, like gedit, vi, or nano, to modify the postgresql.conf file. The following bash command uses nano to edit the file for PostgreSQL v11:
NOTE: It is always wise to create a backup of the configuration files before modifying them, in case the system ever needs to be returned to the default settings. A backup can easily be created with the copy command by executing cp postgresql.conf postgresql.conf.bak command in the same directory where the file is located.
Change the Postgres listen addresses
By default, PostgreSQL will only “listen” to the localhost (or 127.0.0.1 address), but can be modified to listen to all IP addresses by replacing localhost with an asterisk ( * ) as follows:
This command is especially useful for permitting remote connections to the PostgreSQL server over different domains. It is also helpful when wanting to run Postgres on a local network and allow all devices connected to the network to access the system. The following screenshot provides and example:
NOTE: Settings in a .conf file can be commented out by placing a hashtag ( # ) at the beginning of the line.
Other parameters, such as the default Postgres port of 5432 , can also be modified in the postgresql.conf file. However, the PostgreSQL server must be restarted when finished for the changes take effect.
pg_hba.conf postgresql
The pg_hba.conf , or host-based authentication, file is used to permit Postgres connections for users and roles. Execute the following command to modify this file with nano on an Ubuntu installation of PostgreSQL v11:
NOTE: In MacOS, depending on how Postgres was installed, the file will typically be located at /usr/local/var/postgres . Execute the following shell command to open the file using the Sublime IDE: sudo subl /usr/local/var/postgres/pg_hba.conf .
Depending on the PostgreSQL installation, the default settings should resemble the following:
Now scroll to the bottom of the file, just below the line that states # Database administrative login by Unix domain socket . As shown in the following image, the Postgres roles that are authorized for the server should be visible:
Now, to enable the postgres admin superuser role and instruct Postgres to “trust” the role to modify the database, append the following line to the file:
Postgres md5 vs peer
The peer setting enables Postgres to always trust the user, without the need for a password. Conversely, the following md5 settings will always prompt for a password and process it using md5 hash encryption:
NOTE: The 0.0.0.0 IP address acts as a “wildcard” for all IPv4 addresses. Be certain to replace this if Postgres is running on a server with a static IP address. If the host all permission is already set, and not commented out, then just change peer to md5 to enable password protection.
Peer authentication failed error
A FATAL: Peer authentication failed for user "postgres" error, means either permissions haven’t been given to the user in the pg_hba.conf file or the server hasn’t been restarted after making changes.
Start the Postgres server
After the Postgres database has been initialized, start the server on a MacOS Homebrew installation with the following command:
Restart Postgres in Ubuntu
Execute the following command to restart the server if running Postgres on an Ubuntu distro of Linux:
Executing the following enable command will ensure the server will start whenever Linux is rebooted:
Execute the following systemctl status command to obtain the status of the Postgres server:
Restart Postgres on a Mac
For a Homebrew installation of PostgreSQL on MacOS, execute the following command to start the service:
To run Postgres as a temporary background service, execute the following pg_ctl command:
NOTE: Since Homebrew puts its packages in the /usr/local directory by default, the above command will only work for a Homebrew installation of Postgres on a MacOS.
Execute the following command to obtain more information on the Homebrew Postgres server:
If the Postgres server is already running, and there is access to the psql command-line interface, execute the following SELECT SQL statement to reload the configuration settings:
Troubleshooting PostgreSQL
This section will cover some of the more common issues that can arise when trying to connect to the psql CLI for Postgres.
FATAL error: postmaster.pid already exists
An error may occur while executing a pg_ctl command that resembling the following:
Here it is not recommended to try to remove the postmaster.pid file with the rm command. Instead, attempt to “kill” the process by sending it a number 15 sigterm. Once the process has been terminated, the PID (process ID for the Postgres service) must be obtained by using the following lsof command to find all of the processes running on port 5432 :
Once the PID has bee located, use the sudo kill command to safely shutdown the Postgres server by passing its PID number to the command as follows:
NOTE: The default sigterm is 15 when no numerical flag has been passed to the kill command. To immediately force the process to quit, without giving it time to shutdown, use the sudo kill -9
Add Postgres to ‘PATH’
A command-not-found error for pg_ctl or postgres is usually an indication that the path for Postgres hasn’t been exported yet.
Following is an example of the basic syntax needed to export the bin path for a Postgres installation in a UNIX terminal:
Execute the above export command to append the bin path for the Postgres installation to the system’s PATH variable. Typing echo $PATH will verify the exportation was successfully.
Note that the full path for the Postgres installation should be located in /opt/PostgreSQL/ , /usr/local/pgsql/bin , /usr/local/var/postgres , /var/lib/postgresql/ or /usr/local/Cellar/postgresql/12.1/bin for a Homebrew installation. The path may vary depending on the specific machine or the server’s OS, hardware architecture or on the installed version of PostgreSQL.
psql command not found
If a psql: command not found error occurs, try exporting the path in MacOS with the following command:
Note that the path for executable files in Linux is usually located in /usr/bin .
Export the path for PostgreSQL
The PATH line to the Bourne shell for the OS, such as the
/.zprofile file, can be append to the Bourne file for a MacOS installation of Postgres as shown in the following example:
When finished, be certain to save the changes and have the new settings take effect by executing the following source command, followed by the file name, in a terminal prompt:
Show the Postgres data directory
Use the -c option in a UNIX terminal to execute the following psql command to return the Postgres data directory:
The psql CLI can also be accessed directly using a superuser, like postgres , and then executing the following Postgres command:
In a UNIX-based OS, such as Linux or MacOS, the command should return /var/lib/postgresql/ . It may include another subdirectory representing the Postgres version number, as shown here:
Connect to Postgres
After the configuration process is complete, the final test is to see if a successfully connection to the psql CLI for Postgres can be established.
If a user hasn’t been created, execute the following command to pass an username to the createuser file:
The following command can be executed in Linux to create a user, provider the user has been given permissions in the pg_hba.conf file:
A database can then be created for the user with the following command:
Connect to psql from command line
If a password has already been set for the postgres user, access can be obtained with the following su command:
Connection to the postgres user may also be attempted by invoking sudo with the following command:
Use psql to connect to a database
Connection to psql may also be obtained by specifying a username and database with the -U and -d flags as shown here:
If a user or a database has not been created, use the following postgres user and the default template1 database command to connect to psql:
Set the postgres user password
Once inside of psql, set a password for the postgres user using the following psql command:
Create a new Postgres user and database
The following SQL commands will also create a user with a password and grant the user permissions to modify the new database: