Рубрика: blog

Введение.

Продолжаем тему обеспечения безопасности пользователей БД PgSQL, начало см. PostgreSQL Linux часть 5.

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

Все статьи посвящённые PostgreSQL см.

тэг БД или поиск postgres

Подготовка PostgreSQL Linux часть 5.

• Стенд
  • Cервер ALT Linux, имя: lin-pq; IP 10.0.2.11;
    • PostgreSQL X.X
      • метод аутентификации md5
  • Simply Linux, имя: sl, IP=10.0.2.30
  • Пользователь, имя: trt

Команда createuser.

После того, как был добавлен новый пользователь trt с правами администратора, становится доступна консольная команда creatuser.

Роль trt позволяет создать нового пользователя xre с консоли сервера.

Пользователь trt имеет учётную запись на сервере, где установлен PgSQL

Теперь, эти же действия произведём с удалённой рабочей станции.

• Ключи команды createuser
  • -p порт сервера (_{если стандартный, 5432, можно не указывать})
  • -h имя\IP сервера PgSQL
  • -i подтверждение об удалении роли
  • -e вывести журнал действий

В строке CREATE ROLE указаны какие роли доступны/Не доступны при создании.

Проверяем наши действия с помощью клиента psql.

Команда dropuser.

Команда dropuser удаляет пользователя, только суперпользователь или пользователь с привилегией CREATEROLE могут выполнять данные действия.

Необходимо быть суперпользователем, чтобы удалить учётную запись другого суперпользователя

Выполним удаление пользователя xre, находясь на удалённой рабочей станции и проверим свои действия.

Находясь на консоли сервера команда dropuser может выполнятся без ключа «h».

CREATE ROLE.

Встроенная в оболочку psql команда CREATE ROLE имеет в своём арсенале более широкие возможности чем createuser.

Команда CREATE ROLE использует параметры, с помощью которых может гибко управлять ролевыми профилями пользователей.

Подключимся к серверу с помощью терминального клиента psql.

[trt@sl ~]$ psql -h 10.0.2.11 -U trt
Пароль пользователя trt: **********

Создадим роль, для которой разрешён вход.

trt=# CREATE ROLE xre LOGIN;

Сформируем роль с паролем.

trt=# CREATE USER xre WITH PASSWORD 'jwdold$%^;

Инициируем роль, которая может создавать БД и управлять др.ролями.

trt=# CREATE ROLE admbd WITH CREATEDB CREATEROLE;

DROP ROLE.

Команда DROP ROLE удаляет роль в базе данных, освобождая объекты от ролевой зависимости.

Перед удалением, необходимо освободить все принадлежащие роли объекты и удалить права этой роли на другие объекты.

Удалим существующею роль xre с используя psql.

[trt@sl ~]$ psql -h 10.0.2.11 -U trt
Пароль пользователя trt:********
trt=# DROP ROLE xre;
DROP ROLE

Можете проверить свои действия самостоятельно, с помощью клиента psql с ключом \du.

ALTER ROLE.

Команда ALTER ROLE изменяет атрибуты и название роли в PgSQL.

Для примера создадим пользователя xre и проведём с его записью несколько манипуляций.

[trt@sl ~]$ createuser -h 10.0.2.11 -i -e xre
Пароль: ********** 
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE xre NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Сделаем удалённый вход на сервер.

[trt@sl ~]$ psql -h 10.0.2.11 -U trt
Пароль пользователя trt:********* 

Меняем пароль роли.

trt=# ALTER ROLE trt WITH PASSWORD 'hu6h44e';
ALTER ROLE

Удаляем пароль у роли.

trt=# ALTER ROLE xre WITH PASSWORD NULL;
ALTER ROLE

Добавляем права роли xre для управление другими ролями и созданием новых баз и проверяем свои действия.

trt=# ALTER ROLE xre CREATEROLE CREATEDB;
ALTER ROLE
trt=# \du
                Список ролей
Имя роли |      Атрибуты             | Член ролей                                         
---------+------------------------------------------------
xre      | Создаёт роли, Создаёт БД  | {}                                              

В заключение, самостоятельно, удалим роль xre, используя команду DROP ROLE.

Добавление роли в группу.

Нам остаётся рассмотреть вопрос, участия одной роли членом другой. По аналогии с OC Windows, Linux, где есть группы, в PgSQL так же можно работать с групповыми политиками называемыми ролями для групп.

Что бы не плодить сущности и каждый раз, при создании пользователя БД, устанавливать права, проще иметь готовые роли с нужными правами и добавлять туда вновь созданных пользователей.

Для выполнения этих задач существуют команды GRANT и REVOKE

Команда GRANT.

Команда GRANT добавляет одни роли членами других и назначает права, для доступа к объектам баз данных.

Создадим пользователя xre и проконтролируем свои действия.

psql -h 192.168.100.11 -U trt
Пароль пользователя trt:*********
trt=# CREATE ROLE xre;
CREATE ROLE
trt=# \du
                     Список ролей
Имя роли |             Атрибуты                        | Член ролей 
---------+---------------------------------------------+------------
trt      | Суперпользователь, Создаёт роли, Создаёт БД |  {}
xre      | Вход запрещён                               |  {}

Добавим роль LOGIN для xre и сделаем его членом группы trt роли.

...
trt=# ALTER ROLE xre LOGIN;
ALTER ROLE
trt=# \du
                     Список ролей
Имя роли |             Атрибуты                        | Член ролей 
---------+---------------------------------------------+------------
trt      | Суперпользователь, Создаёт роли, Создаёт БД |  {}
xre      |                                             |  {}

trt=# GRANT trt TO xre;
GRANT ROLE
trt=# \du
                     Список ролей
Имя роли |             Атрибуты                        | Член ролей 
---------+---------------------------------------------+------------
trt      | Суперпользователь, Создаёт роли, Создаёт БД |  {}
xre      |                                             | {trt}

Обратите внимание на столбец «Член ролей» пользователя xre.

Команда REVOKE.

Команда REVOKE удаляет из роли права, назначенных ранее.

...
trt=# REVOKE trt FROM xre;
trt=# \du
                     Список ролей
Имя роли |             Атрибуты                        | Член ролей 
---------+---------------------------------------------+------------
trt      | Суперпользователь, Создаёт роли, Создаёт БД | {}
xre      |                                             | {}

Столбец «Член ролей» пользователя xre изменился.

Заключение.

Эта статья заканчивает курс коротких лекций для начинающих, которые хотят получить общие сведения и начальную практику по установке и управлению PostgreSQL. Позже, вероятно, добавится ещё одна статья, PostgreSQL Linux часть 6, в которой будет рассказано, как создавать базы, таблицы и SQL запросы.

Полезные ссылки.

• Сайт
• Форум
• Документация

Введение.

Изучая основы работы сервера PostgreSQL, переходим к рассмотрению вопросов обеспечения безопасности пользователей использующих БД PgSQL. В статье PostgreSQL Linux часть 4, мы будем рассматривать такие важные понятия как пользователи и роли.

Основу защиты учётных записей пользователей PgSQL формируют пользовательские роли и пароли.

Все статьи посвящённые PostgreSQL см.

тэг БД или поиск postgres

Подготовка PostgreSQL Linux часть 4.

• Стенд
  • Cервер ALT Linux, имя: lin-pq; IP 10.0.2.11;
    • PostgreSQL X.X
      • метод аутентификации md5
  • Simply Linux, имя: lin-sl, IP=10.0.2.30
  • Пользователь, имя: trt

Роль — это пользователь СУБД или, если пользоваться терминологией ОС Linux, Windows — группа. Роли не связаны с именами пользователей ОС. Роли являются общими объектами для PgSQL, могут подключаться к разным базам и быть владельцами объектов для различных БД.

Команды управления ролями PostgreSQL Linux часть 4.

• Список основных команд для управления ролями:
  • CREATE ROLE — вызывается из psql
  • DROP ROLE — вызывается из psql
  • ALTER ROLE — вызывается из psql
  • createuser — вызываются из командной строки ОС
  • dopuser — вызываются из командной строки ОС

CREATE ROLE.

• CREATE ROLE — создать роль в базе данных
  • CREATE ROLE имя [ [WITH] параметр [ …] ]
    • параметр
      • LOGIN _{вход на сервер}
      • NOLOGIN
      • SUPERUSER _{суперпользователь}
      • NOSUPERUSER
      • CREATEDB _{создавать базы данных}
      • NOCREATEDB
      • CREATEROLE _{создавать, изменять, удалять роли}
      • NOCREATEROLE
      • REPLICATION _{выполнять репликации}
      • NOREPLICATION
      • и другие…

Параметры (атрибуты) имеют два варианта, пример, CREATEDB (дает право на создание БД) и NOCREATEDB (не дает такого права), по умолчанию выбирается ограничивающий вариант.

DROP ROLE.

• DROP ROLE — удалить роль в базе данных
  • DROP ROLE [ IF EXISTS ] имя [, …]
    • _{IF EXISTS — не считать ошибкой, если роль не существует}

ALTER ROLE.

• ALTER ROLE — изменяет атрибуты роли
• ALTER ROLE имя [ [WITH] параметр [ …] ]
  • параметр
    • SUPERUSER _{суперпользователь}
    • NOSUPERUSER
    • CREATEDB _{создавать базы данных}
    • NOCREATEDB
    • CREATEROLE _{создавать, изменять и удалять другие роли}
    • NOCREATEROLE
    • INHERIT _{наследовать права ролей}
    • NOINHERIT
    • LOGIN _{вход на сервер}
    • NOLOGIN
    • REPLICATION _{режим репликации}
    • NOREPLICATION
    • BYPASSRLS — _{игнорировать все политики защиты на уровне строк}
    • NOBYPASSRLS
    • CONNECTION LIMIT _{сколько параллельных подключений установить}
    • [ ENCRYPTED ] PASSWORD ‘пароль‘ _{задаёт пароль роли}
    • PASSWORD NULL _{пароль не задан}
    • VALID UNTIL ‘дата_время‘ _{устанавливает дату и время, после которого пароль роли перестаёт действовать}

createuser.

• createuser — создать новую учётную запись PostgreSQL
  • createuser [параметр…] [имя_пользователя]
    • параметр
      • -c _{номер количество соединений}
      • -d _{может создавать базы данных}
      • -D _{не может создавать базы данных}
      • -g role _{роль, к которой будет добавлена текущая роль}
      • -i _{наследовать права ролей}
      • -I _{не наследовать права ролей}
      • -l _{может подключаться к серверу}
      • -L _{не может подключаться к серверу}
      • -r _{может создавать, изменять и удалять роли}
      • -R _{не может создавать, изменять и удалять роли}
      • -s _{имеет права суперпользователя}
      • -S _{не имеет права суперпользователя}
      • —interactive _{диалоговый режим для параметров}
      • и другие…

createuser — та же функциональность, что и команда CREATE ROLE

dropuser.

• dropuser — удалить учётную запись пользователя PostgreSQL
  • dropuser [параметр…] [имя_пользователя]
    • параметр
      • -i _{диалоговый режим}
      • -e _{журнал выполнения команд}

dopuser — та же функциональность, что и команда DROP ROLE

Управление ролями PostgreSQL Linux часть 4.

Перейдём к практическим действиям использования ролей. Для начала посмотрим; какие роли, пользователи и БД существуют после установки и первичной настройки PgSQL.

Для сокращения фрагментации кода, строки

$ sudo su - postgres -s /bin/bash
-bash-4.4$ psql

будут в дальнейшем опущены, мы внутри клиента psql

Создадим новую базу данных.

postgres=# CREATE DATABASE trest;

Введём в нашу БД новую роль (пользователя)

postgres=# CREATE ROLE trt LOGIN CREATEROLE;

Установим пароль для пользователя trt.

postgres=#  \password trt
Введите новый пароль для пользователя "trt":********** 
Повторите его:**********

Проверяем наши действия, пробуем подключить trt к базе.

$ psql -U trt
Пароль пользователя trt: 
...
trt=> 

Используя ключ du смотрим обновлённый список ролей.

Пользователь trt не обладает привилегированной ролью, мы вынужденны, для управления PostgreSQL, постоянно обращаться к записи postgres. Это неправильно, тем более, что консольные команды из OC, createuser и dropuser для postgres не доступны — у postgres нет регистрации в OC и это хорошо. Узко — целевое значение пользователя postgres, один из элементов безопасности БД. Запись Postgres владеет только теми данными, которыми управляет сервер БД.

Рекомендуется не назначать пользователя postgres владельцем исполняемых файлов PgSQL, чтобы их нельзя было подменить в случае компрометации серверного процесса.

Добавление административной роли.

Сейчас пользователь trt может только создавать роли, мы добавим ему некоторые привилегированные права, что бы разгрузить запись postgres и сделать управление PqSQL более гибким.

Теперь trt имеет привилегии администратора и может выполнять управление другими пользовательскими ролями и записями.

Заключение.

Тема управления ролями PostgreSQL широка и требует более глубокого рассмотрения.

Продолжение смотри в пятой части.

Полезные ссылки.

• Сайт
• Форум
• Документация

Введение.

Продолжая изучение PgSQL переходим к рассмотрению специальной учётной записи postgres. Ранние мы кратко упоминали о пользователе postgres, см.часть2, теперь, в PostgreSQL Linux часть3, рассмотрим этот вопросе более подробно.

Все статьи посвящённые PostgreSQL см.

тэг БД или поиск postgres

Подготовка PostgreSQL Linux часть 3.

• Стенд
  • Cервер ALT Linux, имя: lin-pq; IP 10.0.2.11;
    • PostgreSQL X.X
      • метод аутентификации md5
  • Simply Linux, имя: lin-sl, IP=10.0.2.30
  • Пользователь. имя: trt

Пользователь PostgreSQL Linux часть3.

При установке БД, создаётся пользователь, который регистрируется в операционной системе с именем — postgres. Учётная запись postgres обладает ролью суперпользователя, для кластера PqSQL и методом аутентификации, из «коробки», trust.

Пример атрибутов пользователя postgres для PostgreSQL Linux часть3.

$ id postgres
uid=46(postgres) gid=46(postgres) группы=46(postgres)

Многие программы требуют при конфигурации своих служб , устанавливать пароль для работы с БД.

• PostgreSQL предлагает несколько вариантов аутентификации:
  • trust
  • password
    • scram-sha-256
    • md5
    • password
  • GSSAPI
  • SSPI
  • ident
  • peer
  • LDAP
  • RADIUS
  • по сертификату SSL
  • RAM
  • BSD

Из списка приведённого выше, остановимся на методе аутентификации md5, как более распространённым. Для включения md5, необходимо внести изменения в файл /var/lib/pgsql/data/pg_hba.conf.

Перед выполнением создадим резервную копию файла pg_hba.conf.

Возврат старой копии файла pg_hba.conf, если требуется.

Смотрим содержание файла pg_hba.conf.

$ sudo su - postgres -s /bin/bash
-bash-3.2$ psql
postgres=# SHOW hba_file;
hba_file
---------------------------------
/var/lib/pgsql/data/pg_hba.conf 
(1 строка) 

Из листинга показанного выше видно, что файл pg_hba.conf содержит одну строку.

Создадим запрос для демонстрации содержимого файла pg_hba.conf до редактирования.

Как видно из листинга в качестве метода аутентификации, для PostgreSQL Linux часть3, стоит trust.

Установка пароля для PostgreSQL Linux часть3.

Управление паролями и ролями в PqSQL осуществляется с помощью нескольких SQL команд. Выбор зависит от задач которые необходимы для работы.

• Список команд для управления ролями и паролями PqSQL:
  • CREATE ROLE
  • ALTER ROLE
  • password — входит в сборник утилиты pqsql.

Для нашей задачи выбираем команду password.

Дополнительные сведения про роли и пароли читайте в следующих частях.

$ sudo su - postgres -s /bin/bash
-bash-3.2$ psql 
postgres=# \password postgres
Введите новый пароль: 
Повторите его: 
postgres=# \q

Вносим изменения в столбец database, БД replication нас сейчас не интересует, все базы находятся на одном сервере, реплик нет

Перегружаем сервер для перечитывания новых значений.

$ sudo su - postgres -s /bin/bash
bash-3.2$ psql
postgres=# SELECT pg_reload_conf();
pg_reload_conf 
----------------
 t
(1 строка)
\q

Проверяем наши действия на ошибки.

$ sudo su - postgres -s /bin/bash
-bash-3.2$ psql
postgres=# SELECT line_number, error FROM pg_hba_file_rules;  line_number | error 
-------------+-------
           1 | 
           2 | 
(2 строки)

Смотрим какой метод аутентификации представлен теперь.

Проверяем с помощью параметра password_encryption, алгоритм хранения пароля.

-bash-3.2$ psql
postgres=# SHOW password_encryption;
password_encryption 
---------------------
 scram-sha-256
(1 строка)
\q

Таким образом мы установили метод аутентификации md5 с алгоритмом шифрования, scram-sha-256.

Заключение.

В приведённом выше примеры было показано, как установить пароль и метод аутентификации с шифрованием для привилегированного пользователя postgres. Для этого мы использовали самый распространённый метод аутентификации md5.

В дальнейшем, при широком использовании БД PostgreSQL, возникнет потребность доступа к базам данных других пользователей, использующих табличное пространство PqSQL в своей работе. Как создавать новых пользователей и управлять пользовательскими учётными записями, читайте в следующей части цикла статей о PostgreSQL.

Продолжение смотри в четвёртой части.

Полезные ссылки.

• Сайт PostgreSQL
• Форум PostgreSQL
• Документация PostgreSQL

Введение.

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

Все статьи посвящённые PostgreSQL см.

тэг БД или поиск postgres

Подготовка PostgreSQL.

• Стенд
• Cервер ALT Linux, имя: lin-pq; IP 10.0.2.11;
  • PostgreSQL X.X
• ПК Simply Linux, имя: lin-sl, IP=10.0.2.30
• Пользователь. имя: trt

Ввод команд PqSQL.

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

Для ввода команд и управление запросами в среде PostgreSQL существует три способа:

Консоль пользователя.

Находясь в сеансе консоли, подключившись по ssh к серверу, пользователь должен иметь право для работу с БД. Под правами подразумевается присвоение роли, терминология PgSQL, для выполнение команд в БД.

Из консоли можно выполнить ограниченное число команд.

Оболочка BASH.

Из среды BASH управление осуществляет пользователь с именем postgres. Пользователь создаётся в процессе установки PgSQL, ему присваивается роль администратора всего кластера.

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

создаётся автоматически при установке PostgreSQL

Как добавить пользователя в привилегированную группу, читайте в следующих частях.

Команда для входа в BASH.

$ sudo su - postgres -s /bin/bash

• sudo — выполнить команду от имени другого пользователя
• su — добавить права суперпользователя
• s — путь к оболочке
• /bin/bash — имя оболочки

Среда BASH позволяет выполнять основные команды управления сервером БД.

Терминальный клиент psql.

Интерактивный клиент psql имеет более расширенные и гибкие возможности для работы с PqSQL, включая сложные SQL запросы.

Установленный сервер из коробки имеет метод авторизации trust. Это означает, что при подключению к PqSQL, работая локально из консоли сервера, пароль вводить не требуется.

Все доступные методы авторизации читайте в других частях.

Команда для входа с помощью psql выглядит так.

$ psql -Uuser -p[port]

• Ключи.
• -d имя базы данных
• -h имя сервера
• -p номер порта сервера, _{можно не указывать если стандартный}
• -U имя пользователя

Таким образом, основными средами управления PgSQL являются BASH и Psql.

Настройка psql клиента.

Локальное подключение к серверу по ssh для выполнения команд, о чём говорилось выше должно быть ограниченным и использоваться только для управления сервером администратором. Все остальные пользователя, имеющие учётные записи на PgSQL должны подключатся удалённо для выполнения SQL запросов с помощью клиента psql.

Осуществить удалённое подключение к PgSQL с помощью клиента psql можно, изменив параметр listen_addresses в настройках файла postgresql.conf сервера.

Указанный параметр использует протокол TCP/IP. По умолчанию в listen_addresses указанно значение localhost — только для локальных подключений к серверу через unix-domain socket. Это значение можно оставить без изменений при условии, что сервер PgSQL и приложения установленные на нём, например Zabbix, работают на одном сервере.

При этом подключение создавать дополнительных пользователей не требуется — управление осуществляет администратор подключённый по ssh к серверу

используя права пользователя postgres или создав новую роль с правами администратора

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

Смотрим значение listen_addresses из коробки.

-bash-3.2$ psql
postgres=# SHOW listen_addresses;
 listen_addresses
------------------
 localhost
(1 строка)
\q

Меняем параметр listen_addresses с localhot на  *.  Переменная * позволяет разрешить подключение к серверу PgSQL из всех сетевых диапазонов организации по протоколу TCP/IP.

Корректировка listen_addresses .

Перегружаем сервер для перечитывания новых значений.

-bash-3.2$ pg_ctl restart

Контролируем фиксацию значение.

-bash-3.2$ psql 
postgres=# SHOW listen_addresses;
 listen_addresses 
------------------
 *
(1 row)
\q

При желание параметр  *  можно сузить до нужного диапазона сети.

После изменения настроек, клиент psql готов к приёму команд для удалённого управления базами сервера PqSQL с рабочих станций.

$ psql -h lin-pq -U trt
Password for user trt: 
...
trt=# 

• -h _{имя сервера для удалённого подключения}
• lin-pq _{стендовое название сервера PqSQL}
• trt _{стендовый пользователь}

В следующих частях конспекта «Как установить PostgreSQL Linux» будут показаны практическое использование клиента psql.

Пользователи работающие на ПК Windows также могут использовать клиент psql для управления базами PqSQL.

как это сделать читайте здесь

Журнал PqSQL.

Сервер PqSQL позволяет вести журнал производительности и ошибок своей работы. При установке сервера, режим журналирования отключен. Пользователь, при желании, может включить контроль записи работы сервера с помощью журнала, отредактировав файл postgresql.conf. После наших действий в дереве каталога /var/lib/pgsq/data создаётся папка log, для хранения файлов журналов

Команда включающая режим журнала.

Перегружаем сервер для перечитывания новых значений.

-bash-3.2$ pg_ctl restart

Контролируем фиксацию значения.

-bash-3.2$ psql
postgres=# SHOW listen_addresses;
 listen_addresses 
------------------
 *
(1 строка)

Заключение.

В этой части конспекта рассмотрены базовые основы конфигурации и первичного управления сервером PgSQL. Понимание основ позволяет уверенно чувствовать себя при погружение в тему управления сервером баз данных PostgeSQL в дальнейшем.

Продолжение смотри в третьей части.

Полезные сылки.

• Сайт
• Форум
• Документация