Postgresql подключение к удаленной бд. Создание нового пользователя PostgreSQL

System Administration

Этот пост - краткая инструкция для начинающих, для тех кто впервые установил PostgreSQL. Здесь вся необходимая информация для того, чтобы начать работу с PostgreSQL.

Подключение к СУБД

Первое, что нужно сделать - получить доступ к PostgreSQL, доступ в качестве суперпользователя.
Настройки аутентификации находятся в файле pg_hba.conf.

1. local all postgres peer

Эта строка говорит о том, что пользователь postgres может подключаться к любой базе данных локальной СУБД PostgreSQL через сокет. Пароль при этом вводить не надо, операционная система передаст имя пользователя, и оно будет использовано для аутентификации.
Подключаемся:

1. $ sudo -u postgres psql postgres postgres

Чтобы иметь возможность подключаться по сети, надо в pg_hdba.conf добавить строку:

1. # TYPE DATABASE USER ADDRESS METHOD
2. hostssl all all 0.0.0.0/0 md5

Метод аутентификации md5 означает, что для подключения придется ввести пароль. Это не очень удобно, если вы часто пользуетесь консолью psql. Если вы хотите автоматизировать какие-то действия, то плохая новость в том, что psql не принимает пароль в качестве аргумента. Есть два пути решения этих проблем: установка соответствующей переменной окружения и хранение пароля в специальном файле.pgpass .

Установка переменной окружения PGPASSWORD

Сразу скажу, что лучше этот способ не использовать, потому что некоторые операционные системы позволяют просматривать обычным пользователям переменные окружение с помощью ps. Но если хочется, то надо написать в терминале:

1. export PGPASSWORD=mypasswd

Переменная будет доступна в текущей сессии. Если нужно задать переменную для всех сессий, то надо добавить строку из примера в файл.bashrc или.bash_profile

Хранение пароля в файле.pgpass

Если мы говорим о Linux, то файл должен находится в $HOME (/home/username). Права на запись и чтение должны быть только у владельца (0600). В файл нужно записывать строки вида:

1. hostname:port:database:username:password

В первые четыре поля можно записать "*", что будет означать отсутствие фильтрации (полную выборку).

Получение справочной информации

\? - выдаст все доступные команды вместе с их кратким описанием,
\h - выдаст список всех доступных запросов,
\h CREATE - выдаст справку по конкретному запросу.

Управление пользователями СУБД

Как получить список пользователей PostgreSQL? Или можно сделать запрос к таблице pg_user.

1. SELECT * FROM pg_user ;

Создание нового пользователя PostgreSQL

Из командной оболочки psql это можно сделать с помощью команды CREATE.

1. CREATE USER username WITH password "password" ;

Или можно воспользоваться терминалом.

1. createuser -S -D -R -P username

Ввод пароля будет запрошен.

Изменение пароля пользователя

1. ALTER USER username WITH PASSWORD "password" ;

Изменение ролей пользователя

Чтобы пользователь имел право создавать базы данных, выполните запрос:

1. ALTER ROLE username WITH CREATEDB ;

Управление базами данных

Вывод списка баз данных в терминале psql: Тоже самое из терминала Linux:

1. psql -l

Создание базы данных из psql (PostgreSQL Terminal)

1. CREATE DATABASE dbname OWNER dbadmin ;

Создание новой базы данных при помощи терминала:

1. createdb -O username dbname;

Настройка прав доступа к базе данных

Если пользователь является владельцем (owner) базы данных, то у него есть все права. Но если вы хотите дать доступ другому пользователю, то сделать это можно с помощью команды GRANT. Запрос ниже позволит пользователю подключаться к базе данных. Но не забывайте о конфигурационном файле pg_hba.conf, в нем тоже должны быть соответствующие разрешения на подключение.

1. GRANT CONNECT ON DATABASE dbname TO dbadmin ;

PostgreSQL — это кроссплатформенная объектно-реляционная СУБД с открытым исходным кодом. Из этой статьи вы узнаете, как установить PostgreSQL в Ubuntu Linux, подключиться к нему и выполнить пару простых SQL-запросов, а также о том, как настроить резервное копирование.

Чтобы установить PostgreSQL 9.2 в Ubuntu 12.10, выполните следующие команды:

sudo apt-add-repository ppa:pitti/ postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Попробуем поработать с СУБД через оболочку:

sudo -u postgres psql

Создадим тестовую базу данных и тестового пользователя:

CREATE DATABASE test_database;
CREATE USER test_user WITH password "qwerty" ;
GRANT ALL ON DATABASE test_database TO test_user;

Для выхода из оболочки введите команду \q .

Теперь попробуем поработать с созданной базой данных от имени test_user:

psql -h localhost test_database test_user

Создадим новую таблицу:

CREATE SEQUENCE user_ids;
CREATE TABLE users (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL ("user_ids" ) ,
login CHAR (64 ) ,
password CHAR (64 ) ) ;

Обратите внимание, что в отличие от некоторых других СУБД, в PostgreSQL нет столбцов со свойством auto_increment. Вместо этого в постгресе используются последовательности (sequences). На данный момент достаточно знать, что с помощью функции nextval мы можем получать уникальные числа для заданной последовательности:

SELECT NEXTVAL ("user_ids" ) ;

Прописав в качестве значения по умолчанию для поля id таблицы users значение NEXTVAL ("user_ids" ) , мы добились того же эффекта, что дает auto_increment. При добавлении новых записей в таблицу мы можем не указывать id, потому что уникальный id будет сгенерирован автоматически. Несколько таблиц могут использовать одну и ту же последовательность. Таким образом мы сможем гарантировать, что значения некоторых полей у этих таблиц не пересекаются. В этом смысле последовательности более гибки, чем auto_increment.

Точно такую же таблицу можно создать и при помощи всего лишь одной команды:

CREATE TABLE users2 (
id SERIAL PRIMARY KEY ,
login CHAR (64 ) ,
password CHAR (64 ) ) ;

В этом случае последовательность для поля id создается автоматически.

Теперь с помощью команды \d можно ознакомиться со списком всех доступных таблиц, а с помощью \d users — увидеть описание таблицы users. Если вы не получили интересующую вас информацию, попробуйте \d+ вместо \d . Список баз данных можно получить командой \l , а переключиться на конкретную БД — командой \c dbname . Для отображения справки по командам скажите \? .

Важно отметить, что в PostgreSQL по умолчанию имена таблиц и столбцов приводятся к нижнему регистру. Если это поведение нежелательно, можно воспользоваться двойными кавычками:

CREATE TABLE "anotherTable" ("someValue" VARCHAR (64 ) ) ;

Еще одна особенность PostgreSQL, с которой могут возникнуть сложности в начале работы с этой СУБД — так называемые «схемы». Схема представляет собой что-то вроде пространства имен для таблиц, как бы каталог с таблицами внутри базы данных.

Создание схемы:

CREATE SCHEMA bookings;

Переключение на схему:

SET search_path TO bookings;

Просмотреть список существующих схем можно командой \dn . По умолчанию используется схема с именем public. В принципе, можно успешно использовать PostgreSQL, и не зная про существование схем. Но при работе с унаследованным кодом, а также в некоторых граничных случаях, знание о схемах может очень пригодиться.

В остальном работа с PostgreSQL мало чем отличается от работы с любой другой реляционной СУБД:

INSERT INTO users (login, password)
VALUES ("afiskon" , "123456" ) ;
SELECT * FROM users;

Если сейчас вы попытаетесь подключиться к постгресу с другой машины, то потерпите неудачу:

psql -h 192.168.0.1 test_database test_user

Psql: could not connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting
TCP/IP connections on port 5432?

Чтобы исправить это, добавьте строку:

listen_addresses = "localhost,192.168.0.1"

… в файл /etc/postgresql/9.2/main/postgresql.conf, а также.

0

Я в тупике. У нас есть база данных postgres 9.2, которую мы используем в течение некоторого времени. Теперь нам нужно иметь возможность подключаться к нему из-за пределов нашей сети.

Итак, мы настраиваем внешний IP-адрес и тестируем, что мы можем подключиться к порту 5432 с удаленного хоста через telnet. Таким образом, это доказывает, что сетевое соединение работает, брандмауэры и т. Д. Все хорошо.

Когда я пытаюсь подключиться с помощью:
PSQL -h db.host.com -d имя_бд -p 5432 -U пользователь

я вернусь Psql: сервер закрыл соединение неожиданно.

Я проверил, что
listen_addresses = «*» устанавливается в postgresql.conf

И в pg_hba.conf мы имеем линию, которая читает (только для тестирования)
хозяина все все 0.0.0.0/0 md5

И я перезагрузил базу данных, чтобы проверить ее выбранные изменения.
Таким образом, это должно разрешать соединения из любого источника.

Версия psql на сервере БД составляет 9.2.14, а на клиенте - 9.2.13.

Любые предложения или идеи?

• 1 ответ

Сортировка:

Активность

1

Я хотел бы попробовать несколько вещей, чтобы получить больше информации:

хвост Postgres войти на сервер, чтобы увидеть, что там происходит при подключении.

Запустить psql --version , чтобы убедиться, что оно более или менее соответствует версии сервера. (Я вижу, вы уже это сделали, но я оставлю его здесь для потомков.)

Запустить strace psql .... , чтобы увидеть, как далеко доходят до отказа.

Это по-прежнему звучит как проблема с сетью для меня. Что такое команда telnet, с которой вы работаете? «Внешний IP» звучит так же, как вы на AWS. Настройка удаленного доступа к VPC занимает много шагов. Есть ли у вас какие-либо другие услуги, открытые для снаружи, которые работают?

Вы также можете устранить неполадки, отключив сервер Postgres и используя nc , чтобы прослушать 5432 . Затем подключитесь к telnet и посмотрите, можете ли вы отправлять данные взад и вперед.

1

Пол, спасибо за предложения. Я сделал стрейч и проверял версии. И вы были правы, пытался подключиться от AWS. Оказывается, это был ярус, который я использовал в балансировщике нагрузки, чтобы разрешить доступ только к диапазонам IP-2 ИС, которые публикует AWS. Когда я отключил irule, я мог немедленно подключиться. Таким образом, это должна быть ирреальная логика, так как я проверил публичный IP-адрес моего экземпляра EC2, и он действительно был указан в irule через уведомление CIDR. -