Додавання нового користувача в PostgreSQL

PostgreSQL - це потужна реляційна система управління базами даних (РСУБД) з відкритим вихідним кодом, що володіє широкими можливостями для зберігання, пошуку та управління даними. У будь-якому багатокористувацькому середовищі необхідно ефективно додавати користувачів і керувати ними, щоб підтримувати безпеку, надавати відповідні привілеї та забезпечувати безперебійну роботу бази даних. У цій статті представлено докладний покроковий посібник із додавання нового користувача в PostgreSQL, розглядаються різні перспективи та практичні сценарії.

Поняття ролей у PostgreSQL

У PostgreSQL використовується система управління доступом на основі ролей. Ролі в PostgreSQL можуть діяти як користувачі або групи, залежно від того, як вони налаштовані. Кожна операція з базою даних, що виконується користувачем, виконується в рамках призначеної йому ролі. Перед створенням нового користувача необхідно розібратися з ролями, оскільки користувач - це, по суті, роль з атрибутом LOGIN.

Ролі можуть мати різні дозволи, наприклад:

    - Можливість створювати бази даних (CREATEDB).
    - Привілей на створення інших ролей (CREATEROLE).
    - Можливість доступу до сервера баз даних (LOGIN).

Під час додавання нового користувача важливо враховувати необхідні дозволи та обмеження ролі, щоб забезпечити безпечний та ефективний доступ до бази даних.

Підготовка середовища

Перед додаванням нового користувача переконайтеся, що у вас є доступ до сервера PostgreSQL з адміністративними привілеями. Адміністративні ролі, як-от postgres (суперкористувач за замовчуванням), мають необхідні повноваження для управління ролями та привілеями.

Щоб увійти в систему під користувачем postgres, виконайте таку команду:

    sudo -i -u postgres

Після входу в систему отримайте доступ до інтерфейсу командного рядка PostgreSQL (psql), виконавши команду:

    psql

Тепер ви готові до додавання нового користувача.

Додавання нового користувача в PostgreSQL

Щоб створити нового користувача, PostgreSQL надає команду CREATE ROLE, яка може використовуватися в поєднанні з певними атрибутами. Новий користувач повинен мати атрибут LOGIN, щоб отримати доступ до бази даних.

Ось базовий приклад додавання нового користувача:

    CREATE ROLE new_user WITH LOGIN PASSWORD 'secure_password';

    new_user: Замініть це на ім'я користувача, яке ви хочете створити.
    PASSWORD: Завжди вибирайте надійний пароль для забезпечення безпеки.

Як альтернативу можна використовувати скорочену команду:

    CREATE USER new_user WITH PASSWORD 'secure_password';

Команда CREATE USER - це спрощена версія CREATE ROLE, яка автоматично включає атрибут LOGIN.

Інші корисні статті в нашому Блозі:

    - Створення нового користувача та управління привілеями у MySQL

    - Детальний посібник: як знайти та оптимізувати повільні запити у MySQL

    - Як віддалено підключитися до MySQL

    - Оператор Bash "If": синтаксис, варіанти використання, команди та багато іншого!

Призначення привілеїв новому користувачеві

За замовчуванням новий користувач не має прав на жодну базу даних. Щоб дозволити користувачеві взаємодіяти з певною базою даних, необхідно явно призначити привілеї.

Надати доступ до бази даних: Призначте новому користувачеві доступ до бази даних:

    GRANT CONNECT ON DATABASE example_db TO new_user;

Надайте доступ до схеми: Якщо база даних має кілька схем, надайте доступ до певної схеми:

    GRANT USAGE ON SCHEMA public TO new_user;

Надати привілеї на рівні таблиць: Дозвольте користувачеві взаємодіяти з таблицями:

    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;

Надання майбутніх привілеїв: Щоб забезпечити користувачеві привілеї для таблиць, створених у майбутньому:

    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;

Призначення мінімально необхідних привілеїв - найважливіша практика забезпечення безпеки баз даних, що дає змогу знизити потенційні ризики.

Найкращі практики управління користувачами в PostgreSQL

Використовуйте групові ролі: Замість того щоб керувати привілеями окремих користувачів, створіть групові ролі із заздалегідь визначеними правами і призначте користувачів у ці групи. Наприклад:

    CREATE ROLE dev_team WITH LOGIN PASSWORD 'team_password';
    GRANT CONNECT, TEMPORARY ON DATABASE example_db TO dev_team;

Додайте окремих користувачів до групи:

    GRANT dev_team TO new_user;

Регулярно змінюйте паролі: Забезпечте регулярне оновлення паролів, щоб мінімізувати вразливості безпеки.

    ALTER USER new_user WITH PASSWORD 'new_secure_password';

Проводьте аудит дій користувачів: Використовуйте функції протоколювання PostgreSQL для моніторингу активності користувачів і забезпечення відповідності політикам доступу.
Скасуйте непотрібні привілеї: Періодично переглядайте і скасовуйте непотрібні привілеї, щоб запобігти «повзучому зростанню» привілеїв:

    REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM new_user;

Автоматизація створення користувачів за допомогою сценаріїв

У середовищах, де створення користувачів відбувається часто, автоматизація може заощадити час і забезпечити послідовність дій. Нижче наведено приклад простого сценарію для додавання нового користувача:

    #!/bin/bash
    USER_NAME=$1
    PASSWORD=$2
    DATABASE=$3

    psql -U postgres -c «CREATE USER $USER_NAME WITH PASSWORD “$PASSWORD”;»
    psql -U postgres -c «GRANT CONNECT ON DATABASE $DATABASE TO $USER_NAME;»

Збережіть цей скрипт під ім'ям create_user.sh і запустіть його з необхідними аргументами:

    bash create_user.sh new_user secure_password example_db

Цей підхід особливо корисний для конвеєрів DevOps і робочих процесів CI/CD.

Поширені помилки та усунення неполадок

    Помилка: role «new_user» does not exist: Переконайтеся, що користувач був створений і йому присвоєно атрибут LOGIN.
    Помилка: permission denied for database: Переконайтеся, що користувач має відповідний привілей CONNECT для бази даних.
    Помилка: could not connect to server: Переконайтеся, що PostgreSQL запущено та приймає з'єднання на зазначеному порту.

Висновок

Додавання нового користувача в PostgreSQL - це фундаментальне завдання, яке вимагає ретельного розгляду управління ролями, призначення привілеїв і найкращих практик безпеки. Розуміючи рольову архітектуру PostgreSQL і дотримуючись структурованих процесів, адміністратори можуть ефективно керувати користувачами, забезпечуючи цілісність і безпеку бази даних. Освоєння управління користувачами необхідне для ефективного адміністрування баз даних, незалежно від того, чи працюєте ви в невеликому середовищі розробки або у великій корпоративній системі.