Создать пользователя postgres linux

Работаем с PostgreSQL через командную строку в Linux

Установка PostgreSQL на Linux (Mint)

Для подключения к базе данных PostgreSQL понадобится установленный PostgreSQL клиент:

Для установки PostgreSQL сервера:

Проверим, можем ли мы подключиться к базе данных PostgreSQL:

Вывод команды должен быть примерно таким:

PostgreSQL Подключение, Пользователи (Роли) и Базы Данных

Логин в только что установленный postgreSQL сервер нужно производить под именем пользователя postgres:

Для подключения к базе данных PostgreSQL можно использовать команду:

Если такая команда не просит ввести пароль пользователя, то можно еще добавить опцию -W.

После ввода пароля и успешного подключения к базе данных PostgreSQL, можно посылать SQL-запросы и psql-команды.

PostgreSQL создание новой роли и базы данных

Создать новую роль c именем admin (указывайте нужное имя):

Создание новой базы данных:

Дать права роли на базу данных:

Включить удаленный PostgreSQL доступ для пользователей

Нам нужно отредактировать файл /etc/postgresql/ /main/pg_hba.conf, задав опцию md5 вместо peer.

может быть 10, 11, 12 и т.д.

После этого сделать restart PostgreSQL:

Полезные команды PostgreSQL

Выйти из клиента PostgreSQL:

\q

Показать список баз данных PostgreSQL:

\l

Показать список таблиц:

\dt

Показать список пользователей (ролей):

\du

Показать структуру таблицы:

Переименовать базу данных:

Удалить базу данных:

Изменить текущую базу данных в PostgreSQL (вы не сможете переименовать или удалить текущую базу данных):

\connect db_name или более короткий alias: \c db_name

Удалить роль (пользователя):

Роль не будет удалена, если у нее есть привелегии — возникнет ошибка ERROR: role cannot be dropped because some objects depend on it .

Нужно удалить привелегии у роли, например если нужно удалить роль admin2, нужно выполнить последовательность комманд с Drop Owned:

Дать права пользователю/роли на логин ( role is not permitted to log in ):

Выбор shema psql в консоли:

Посмотреть список всех схем:

Подключиться к конкретной схеме:

Sequences

Получить имена всех созданных sequences:

Получить последнее значение sequence, которые будет присвоено новой вставляемой в таблицу записи:

Источник

Как работать с пользователями в PostgreSQL

Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена от пользователя postgres — чтобы войти в систему от данного пользователя, вводим:

* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.

Теперь запускаем командную оболочку PostgreSQL:

$ psql -Upostgres template1

* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.

Для просмотра всех пользователей СУБД:

=# select * from pg_user;

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

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

1. Создание пользователя

а) Добавление новой роли (пользователя) из оболочки SQL:

=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;

* в примере создана роль dmosk с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

createuser -P dmosk

2. Назначение прав на использование базы данных

Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;

Теперь подключаемся к базе, к которой хотим дать доступ:

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;

* в данном примере мы даем права на таблицу table1.

Выходим из SQL-оболочки:

3. Настройка файла pg_hba.conf

Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь расположения данных для PostgreSQL:

В ответ мы получим, что-то на подобие:

* в данном примере /var/lib/pgsql/9.6/data/ — путь расположения конфигурационных файлов.

Добавляем права на подключение нашему созданному пользователю:

.
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
.

* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию
host all all 127.0.0.1/32 ident.

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

4. Проверка

Для теста пробуем подключиться к Postgre с помощью созданного пользователя:

psql -Udmosk template1 -h127.0.0.1

Настройка прав доступа к базе с помощью групп

Сначала создадим групповую роль:

=# CREATE ROLE «myRole» NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

* данной командой создана группа myRole с минимальными правами.

Теперь добавим ранее созданного пользователя dmosk в эту группу:

=# GRANT «myRole» TO dmosk;

Подключимся к базе данных, для которой хотим настроить права

и предоставим все права для группы myRole всем таблицам базы database1

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP «myRole»;

Редактирование пользователя

1. Смена пароля

Рассмотрим несколько примеров смены пароля пользователя.

=# ALTER USER postgres PASSWORD ‘password’

* в данном примере мы зададим пароль password для пользователя postgres.

С запросов ввода пароля:

* после ввода данной команды система потребует дважды ввести пароль для пользователя (в нашем примере, postgres).

Из командной строки Linux:

sudo -u postgres psql -U postgres -d postgres -c «ALTER USER postgres PASSWORD ‘password'»

* по сути, мы выполняем также запрос в оболочке sql.

Удаление пользователей и групп

Удаление пользователя выполняется следующей командой:

=# DROP USER dmosk;

database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM «dmosk»;

* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO «dmosk» пишем FROM «dmosk»;

Назначение особых прав пользователям PostgreSQL

Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:

=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO «dmosk»;

* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя dmosk.

Назначение прав для определенной таблицы:

database1=# GRANT ALL PRIVILEGES ON table_users TO «dmosk»;

* в данном примере мы предоставим все права на таблицу table_users в базе данных database1;

Учетная запись для резервного копирования

Для выполнения резервного копирования лучше всего подключаться к базе с минимальными привилегиями.

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

=# CREATE USER bkpuser WITH PASSWORD ‘bkppasswd’;

* мы создадим учетную запись bkpuser с паролем bkppasswd.

Предоставляем права на подключения к базе

=# GRANT CONNECT ON DATABASE database TO bkpuser;

* в данном примере к базе database.

Подключаемся к базе (в нашем примере database):

Даем права на все последовательности в схеме:

=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

* мы дали права для схемы public. Это схема является схемой по умолчанию, но в вашем случае она может быть другой. В таком случае, подставляем свое значение.

Графический интерфейс

Иногда проще воспользоваться программой для выставления прав и работы с PostgreSQL. Могу посоветовать приложение pgAdmin. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.

Источник

Установка и настройка PostgreSQL 10 на Linux Ubuntu Server

Привет! Материал сегодня будет посвящен рассмотрению процесса установки СУБД PostgreSQL 10 на серверную операционную систему Linux Ubuntu Server, а также первоначальной настройки PostgreSQL 10, для того чтобы можно было ее использовать, например, в сети своей организации.

Другими словами, сейчас мы с Вами реализуем сервер баз данных на базе Linux Ubuntu Server и PostgreSQL 10, который будет иметь базовую настройку. Если Вас интересует реализация подобного сервера только на базе операционной системы CentOS 7.1, то можете ознакомиться с материалом «Установка PostgreSQL 9.4 на CentOS 7.1», в нем мы как раз рассматривали данный процесс.

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

Примечание! 10 версия PostgreSQL была актуальна на момент написания статьи, на текущий момент доступны новые версии.

Что нового в PostgreSQL 10?

Начиная с PostgreSQL 10, меняется схема нумерации версий, это вызвано тем, что раньше выходило множество минорных версий (например, 9.x), многие из которых на самом деле вносили значительные изменения не соответствующие минорным, теперь мажорные версии будут нумероваться 10, 11, 12, а минорные 10.1, 10.2, 11.1 и так далее.

Основные нововведения:

• Логическая репликация с использованием публикации и подписки — теперь возможно осуществлять репликацию отдельных таблиц на другие базы, это реализовывается с помощью команд CREATE PUBLICATION и CREATE SUBSCRIPTION;
• Декларативное партиционирование таблиц – в PostgreSQL 10 добавился специальный синтаксис для партиционирования, который позволяет легко создавать и поддерживать таблицы с интервальной или списочной схемой партиционирования;
• Улучшенный параллелизм запросов – другими словами, появилась дополнительная оптимизация запроса, для того чтобы пользователь получал данные быстрей;
• Аутентификация пароля на основе SCRAM-SHA-256 – добавился новый метод аутентификации, который является более безопасным, чем метод с использованием MD5;
• Quorum Commit для синхронной репликации – теперь администратор может указать что, если какое-либо количество реплик подтвердило, что внесено изменение в базу данных, данное изменение можно считать надёжно зафиксированным;
• Значительные общие улучшения производительности;
• Улучшенный мониторинг и контроль.

Более детально обо всех нововведениях можете почитать на официальном сайте – PostgreSQL 10.

Установка PostgreSQL 10 на Linux Ubuntu Server 16.04

Как Вы уже, наверное, поняли рассматривать процесс установки и соответственно настройки PostgreSQL 10 мы будем на примере версии Ubuntu Server 16.04, так как эта версия имеет долгосрочную поддержку и на текущий момент является актуальной среди LTS версий.

Шаг 1

Установку и настройку PostgreSQL необходимо осуществлять с правами суперпользователя, поэтому давайте сразу переключимся на пользователя root. Для этого вводим sudo -i (или sudo su) и жмем Enter.

Шаг 2

Затем первое, что нам нужно сделать, это проверить есть ли в репозиториях версия PostgreSQL 10. Это можно сделать путем ввода следующей команды.

Как видим, в Ubuntu Server 16.04 10 версии PostgreSQL нет, поэтому нам нужно подключить необходимый репозиторий, в котором присутствует PostgreSQL 10. Если у Вас более новая версия Ubuntu Server и в стандартных репозиториях есть 10 версия PostgreSQL, то дополнительный репозиторий Вам подключать не нужно, т.е. данный шаг Вы пропускаете.

Для подключения репозитория нам необходимо создать специальный файл с адресом нужного репозитория. Адреса для каждой версии Ubuntu разные, поэтому если у Вас версия Ubuntu не 16.04, то уточнить адрес Вы можете на официальном сайте PostgreSQL на странице загрузке – вот она.

После перехода на страницу выбираете версию Ubuntu, после чего у Вас отобразится адрес нужного репозитория.

Для упрощения процедуры создания файла давайте напишем скрипт с выводом адреса репозитория, а вывод перенаправим в файл. Для Ubuntu Server 16.04 подключение нужного репозитория будет выглядеть следующим образом.

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

Далее обновляем список пакетов.

И еще раз проверяем наличие пакета с PostgreSQL 10.

Теперь нужный пакет у нас есть, и мы можем переходить к установке PostgreSQL 10.

Шаг 3

Для установки PostgreSQL 10 пишем следующую команду.

По окончанию процесса установки проверяем, запущен ли сервер PostgreSQL.

Как видим, PostgreSQL 10 установился и работает.

Базовая настройка PostgreSQL 10 в Linux Ubuntu Server

После установки нам необходимо выполнить базовую настройку PostgreSQL 10, например: создать пользователя, указать какие сетевые интерфейсы будет прослушивать сервер, а также разрешить подключение по сети. Начнем мы с создания пользователя и базы данных.

Создание пользователя и базы данных в PostgreSQL

После установки, к серверу PostgreSQL мы можем подключиться только с помощью системного пользователя postgres, причем без пароля. Для этого переключаемся на пользователя postgres (учетная запись в Ubuntu создана автоматически во время установки PostgreSQL).

Запускаем psql — это консоль управления PostgreSQL.

Сначала зададим пароль для пользователя postgres.

Затем создаем нового пользователя на сервере PostgreSQL, так как работать от имени postgres крайне не рекомендуется.

где, testuser – это имя пользователя, ‘123456’ – это его пароль.

Далее давайте создадим базу данных.

где, testdb – это имя новой базы данных.

Теперь давайте дадим права на управление БД нашему новому пользователю.

Все готово, выходим из консоли.

Для проверки, давайте подключимся к PostgreSQL от имени нового пользователя, на предложение о вводе пароля вводим пароль от новой учетной записи.

Работает. Для выхода снова набираем \q.

Для переключения обратно на root вводим exit.

Разрешаем подключение к PostgreSQL по сети

По умолчанию PostgreSQL прослушивает только адрес localhost, поэтому для того чтобы мы могли подключаться по сети, нам нужно указать какие сетевые интерфейсы будет просушивать PostgreSQL. Я для примера укажу, что прослушивать нужно все доступные интерфейсы. Если у Вас несколько сетевых интерфейсов, и Вы хотите, чтобы PostgreSQL использовал только один конкретный, то Вы его можете указать именно здесь.

Для этого открываем файл postgresql.conf, например редактором nano.

Находим следующую строку.

и заменяем на (вместо звездочки Вы в случае необходимости указываете IP адрес нужного интерфейса).

Сохраняем изменения сочетанием клавиш CTRL+O и подтверждаем нажатием Enter, затем просто закрываем редактор nano сочетанием клавиш CTRL+X.

Теперь давайте разрешим подключение из сети 10.0.2.0/24 с методом аутентификации md5. Для этого открываем файл pg_hba.conf

Ищем вот такие строки.

И вносим следующие изменения (если IPv6 Вы не будете использовать, то можете закомментировать соответствующие строки знаком #).

Где, 10.0.2.0/24 адрес сети, из которой будет происходить подключение к текущему серверу PostgreSQL. Сохраняем изменения сочетанием клавиш CTRL+O, подтверждаем нажатием Enter и закрываем редактор nano сочетанием клавиш CTRL+X.

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

Источник