Как работать с пользователями в PostgreSQL
Обновлено: 20.12.2023 Опубликовано: 20.07.2016
Часть нижеописанных операций нужно выполнять в командной оболочке 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:
— для версии postgresql 14 и выше:
database1=# GRANT pg_read_all_data TO dmosk;
database1=# GRANT pg_write_all_data TO dmosk;
— для версии postgresql 13 и ниже:
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;
* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.
Посмотреть список всех схем можно командой:
б) Если мы хотим дать максимальные права на базу данных, то можно сделать пользователя ее владельцем:
ALTER DATABASE database1 OWNER TO dmosk;
в) Также можно дать доступ к базе для определенных таблиц:
database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;
* в данном примере мы даем права на таблицу table1.
3. Настройка файла pg_hba.conf
Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.
Для начала смотрим путь до него:
В ответ мы получим, что-то на подобие:
Выходим из оболочки sql:
Возвращается в командную строку суперпользователя:
Добавляем права на подключение нашему созданному пользователю:
.
# 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.
В файле уже может быть строка на подобие:
host all all 127.0.0.1/32 md5
Это значит, что у нас будут права на подключение с локального хоста под всеми пользователями с использованием пароля.
После перезапускаем службу:
systemctl restart postgresql-9.6
* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.
4. Проверка
Для теста пробуем подключиться к PostgreSQL с помощью созданного пользователя:
psql -U dmosk -d database1 -h 127.0.0.1
* в данном примере мы подключается к локальному серверу 127.0.0.1 под пользователем dmosk и базе database1, на которую мы дали ему права.
Настройка прав доступа к базе с помощью групп
Сначала создадим групповую роль:
=# 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. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.
Как войти под пользователем?
Я установил postgres9. далее работаю с ним через командную строку. Моя ОС linux mint 18.
После установки postgres, в ОС создался пользователь postgres, о чем говорит запись в /etc/passwd:
postgres:x:123:132:PostgreSQL administrator. /var/lib/postgresql:/bin/bashТеперь я хотел бы создать пользователя postgres и работать под ним, а не под суперпользователем. Для этого я зашел в postgres под суперпользователем:
sudo -u postgres psql
и создал нового пользователя, новую БД, дал права пользователю на эту БД:
postgres=# CREATE USER user1 WITH PASSWORD 'qwerty1'; CREATE ROLEpostgres=# CREATE DATABASE database1; CREATE DATABASEpostgres=# GRANT ALL PRIVILEGES ON DATABASE database1 to user1; GRANTpostgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- database1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | user1=CTc/postgresДалее я выхожу из аккаунта суперпользователя и пытаюсь залогиниться под пользователем user1, но не получается:
postgres=# \q (ven) md@md ~/.MINT18/code/python/project0/ven/bin $ sudo -u user1 psql sudo: неизвестный пользователь: user1 sudo: не удаётся инициализировать модуль политикиПодскажите пожалуйста в чём проблема и можно ли это исправить?
- Вопрос задан более трёх лет назад
- 13204 просмотра
1 комментарий
Простой 1 комментарий
zlodiak @zlodiak Автор вопроса
Bacon, а разве в 12 процедура входа под определённым пользователем сильно изменилась?
Решения вопроса 1
PostgreSQL DBA
sudo — запуск команды от имени пользователя ОС.
Пользователь ОС не имеет совсем ничего общего с пользователем в БД. Пользователя ОС вы не создали, ответ sudo закономерен.
Для использования psql sudo не нужен. Нужны настройки pg_hba.conf и сам psql. Например, psql -U user1 database1
Ответ написан более трёх лет назад
Нравится 1 5 комментариев
zlodiak @zlodiak Автор вопроса
Не получается. В соответствии с документацией я добавил в файл
/etc/postgresql/9.5/main/pg_hba.conf
строку
local database1 user1 trust
и снова попробовал зайти:
(ven) md@md ~/.MINT18/code/python/project0/ven/bin $ psql -U user1 database1 psql: FATAL: Peer authentication failed for user "user1" (ven) md@md ~/.MINT18/code/python/project0/ven/bin $reload базе скомандовали?
Vitsliputsli @Vitsliputsli
Пользователь ОС не имеет совсем ничего общего с пользователем в БД.
Не совсем так, судя по ошибке «Peer authentication failed», у автора включена как раз аутентификация через пользователя ОС, т.е. PostgreSQL ищет и проверяет пароль пользователя в ОС.
Хотя он вроде пытался изменить это, значит либо ошибка в pg_hba.conf, либо не было reload.
ищет и проверяет пароль пользователя в ОС
Это неправда.
Метод peer вовсе не проверяет какой-либо пароль и даже не получает его по fe протоколу от клиента. Это лишь проверка соответствия символьного имени unix пользователя от имени которого выполняется подключение по unixsock на пользователя в базе.
https://github.com/postgres/postgres/blob/REL_11_S.
Что не противоречит утверждению
Пользователь ОС не имеет совсем ничего общего с пользователем в БД.
Это разные сущности, peer лишь проверяет совпадение имени. Пароли могут разные. пользователя может не быть на какой-то из сторон и т.д.
hba же необходимо смотреть целиком, от позиции строки в списке правил результат может меняться. Правила проверяются строго по порядку и применяется только первое совпадающее. Так если сначала сделать local all all peer а затем local all all trust — второе правило никогда не сработает.
Как сменить пользователя в postgresql
ALTER DATABASE — изменить атрибуты базы данных
Синтаксис
ALTER DATABASEимя[ [ WITH ]параметр[ . ] ] Здесьпараметр: ALLOW_CONNECTIONSразр_подключенияCONNECTION LIMITпредел_подключенийIS_TEMPLATEэто_шаблонALTER DATABASEимяRENAME TOновое_имяALTER DATABASEимяOWNER TO <новый_владелец| CURRENT_USER | SESSION_USER > ALTER DATABASEимяSET TABLESPACEновое_табл_пространствоALTER DATABASEимяSETпараметр_конфигурации< TO | = ><значение| DEFAULT > ALTER DATABASEимяSETпараметр_конфигурацииFROM CURRENT ALTER DATABASEимяRESETпараметр_конфигурацииALTER DATABASEимяRESET ALL
Описание
ALTER DATABASE изменяет атрибуты базы данных.
Первая форма оператора меняет параметры на уровне базы данных. (Подробнее описано ниже.) Изменять эти параметры может только владелец базы данных или суперпользователь.
Вторая форма меняет имя базы данных. Переименовать базу данных может только владелец БД или суперпользователь (не суперпользователю требуется также право CREATEDB ). Переименовать текущую базу данных нельзя. (Если вам нужно сделать это, сначала подключитесь к другой базе.)
Третья форма меняет владельца базы данных. Чтобы сменить владельца базы, необходимо быть её владельцем и также непосредственным или опосредованным членом новой роли-владельца, и кроме того, иметь право CREATEDB . (Заметьте, что суперпользователи наделяются всеми этими правами автоматически.)
Четвёртая форма меняет табличное пространство по умолчанию для базы данных. Произвести это изменение может только её владелец или суперпользователь; кроме того, необходимо иметь право для создания нового табличного пространства. Эта команда физически переносит все таблицы или индексы из прежнего основного табличного пространства БД в новое. Новое табличное пространство должно быть пустым для этой базы данных и к ней никто не должен быть подключён. Таблицы и индексы, находящиеся не в основном табличном пространстве, при этом не затрагиваются.
Остальные формы меняют значение по умолчанию конфигурационных переменных времени выполнения для базы данных PostgreSQL . Когда устанавливается следующий сеанс работы с указанной базой данных, заданное этой командой значение становится значением по умолчанию. Значения переменных, заданные для базы данных, переопределяют значения, определённые в postgresql.conf или полученные через командную строку postgres . Менять сеансовые значения переменных для базы данных может только её владелец или суперпользователь. Некоторые параметры изменить таким образом нельзя, а некоторые может изменить только суперпользователь.
Параметры
Имя базы данных, атрибуты которой изменяются. разр_подключения
Если false, никто не сможет подключаться к этой базе данных. предел_подключений
Число разрешённых одновременно подключений к этой базе данных (-1 снимает ограничение). это_шаблон
Если true, базу данных сможет клонировать любой пользователь с правами CREATEDB ; в противном случае клонировать эту базу смогут только суперпользователи и её владелец. новое_имя
Новое имя базы данных. новый_владелец
Новый владелец базы данных. новое_табл_пространство
Новое основное табличное пространство базы данных. параметр_конфигурации
значение
Устанавливает сеансовое значение по умолчанию для указанного параметра конфигурации. Если указывается значение DEFAULT или равнозначный вариант, RESET , определение параметра на уровне базы данных удаляется, так что в новых сеансах будет действовать значение по умолчанию, определённое на уровне системы. Для очистки всех значений параметров на уровне базы данных выполните RESET ALL . SET FROM CURRENT устанавливает значение параметра на уровне базы данных из текущего значения в активном сеансе.
За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 19.
Замечания
Также возможно связать параметры сеанса не с базой данных, а с определённой ролью; см. ALTER ROLE . В случае конфликта параметры на уровне роли переопределяют параметры на уровне базы данных.
Примеры
Отключение сканирования индекса по умолчанию в базе данных test :
ALTER DATABASE test SET enable_indexscan TO off;
Совместимость
Оператор ALTER DATABASE является расширением PostgreSQL .
Как сменить пользователя в postgresql
ALTER ROLE — изменить роль в базе данных
Синтаксис
ALTER ROLEуказание_роли[ WITH ]параметр[ . ] Здесьпараметр: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMITпредел_подключений| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'пароль' | VALID UNTIL 'дата_время' ALTER ROLEимяRENAME TOновое_имяALTER ROLE <указание_роли| ALL > [ IN DATABASEимя_бд] SETпараметр_конфигурации< TO | = ><значение| DEFAULT > ALTER ROLE <указание_роли| ALL > [ IN DATABASEимя_бд] SETпараметр_конфигурацииFROM CURRENT ALTER ROLE <указание_роли| ALL > [ IN DATABASEимя_бд] RESETпараметр_конфигурацииALTER ROLE <указание_роли| ALL > [ IN DATABASEимя_бд] RESET ALL Здесьуказание_роли:имя_роли| CURRENT_USER | SESSION_USER
Описание
ALTER ROLE изменяет атрибуты роли PostgreSQL .
Первая форма команды в этой справке может изменить многие атрибуты роли, которые можно указать в CREATE ROLE . (Покрываются все возможные атрибуты, отсутствуют только возможности добавления/удаления членов роли; для этого нужно использовать GRANT и REVOKE .) Атрибуты, не упомянутые в команде, сохраняют свои предыдущие значения. Суперпользователи базы данных могут изменить любые параметры любой роли, а пользователи с правом CREATEROLE могут также менять любые параметры (за исключением параметров SUPERUSER , REPLICATION и BYPASSRLS ), но только не ролей суперпользователей и репликации. Обычные пользователи (роли) могут менять только свой пароль.
Вторя форма меняет имя роли. Суперпользователи базы данных могут переименовать любую роль, а пользователи с правом CREATEROLE могут переименовывать роли не суперпользователей. Также нельзя переименовать роль текущего пользователя в активном сеансе. (Если вам нужно сделать это, подключитесь другим пользователем.) Так как в паролях с MD5 -шифрованием имя роли используется в качестве криптосоли, при переименовании роли её пароль очищается, если он был зашифрован MD5 .
Оставшиеся формы меняют значение по умолчанию конфигурационной переменной, которое будет распространяться на сеансы роли во всех базах данных, либо, если добавлено предложение IN DATABASE , только на сеансы роли в заданной базе. Если вместо имени роли указано ALL , это значение переменной распространяется на все роли. Использование ALL с IN DATABASE по сути равносильно использованию команды ALTER DATABASE . SET . .
Когда эта роль впоследствии установит новое подключение, указанное значение станет значением по умолчанию в сеансе, переопределяя значение, заданное в postgresql.conf или полученное из командной строки postgres . Это происходит только в момент входа; при выполнении SET ROLE или SET SESSION AUTHORIZATION новые значения не применятся. Набор параметров для всех баз данных переопределяется параметрами уровня БД, установленными для роли. Параметры для конкретной базы данных или конкретной роли переопределяют параметры для всех ролей.
Суперпользователи могут менять значения переменных по умолчанию для любых ролей, а пользователи с правом CREATEROLE могут менять их только для ролей не суперпользователей. Обычные пользователи могут определять переменные только для себя. Некоторые переменные конфигурации нельзя задать таким способом, а некоторые может настроить только суперпользователь. Параметры всех ролей во всех базах данных могут настраивать только суперпользователи.
Параметры
Имя роли, атрибуты которой изменяются. CURRENT_USER
Выбирает для изменения текущего пользователя, а не явно задаваемую роль. SESSION_USER
Выбирает для изменения текущего пользователя сеанса, а не явно задаваемую роль. SUPERUSER
NOSUPERUSER
CREATEDB
NOCREATEDB
CREATEROLE
NOCREATEROLE
INHERIT
NOINHERIT
LOGIN
NOLOGIN
REPLICATION
NOREPLICATION
BYPASSRLS
NOBYPASSRLS
CONNECTION LIMIT предел_подключений
PASSWORD пароль
ENCRYPTED
UNENCRYPTED
VALID UNTIL ‘ дата_время ‘
Эти предложения меняют атрибуты, изначально установленные командой CREATE ROLE . За дополнительными сведениями обратитесь к странице справки CREATE ROLE . новое_имя
Новое имя роли. имя_бд
Имя базы данных, в которой устанавливается конфигурационная переменная. параметр_конфигурации
значение
Указанный параметр конфигурации принимает заданное значение по умолчанию в сеансах роли. Если значение задано как DEFAULT или, что то же самое, применяется операция RESET , переопределение этого параметра для роли удаляется и роль будет получать в новых сеансах системное значение параметра. Для очистки значений всех параметров, связанных с ролью, применяется RESET ALL . SET FROM CURRENT сохраняет текущее значение параметра в активном сеансе в качестве значения для данной роли. Если указано IN DATABASE , параметр конфигурации настраивается или удаляется только для данной роли и указанной базы данных.
Определения переменных для роли применяются только в начале сеанса; команды SET ROLE и SET SESSION AUTHORIZATION эти определения не обрабатывают.
За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 19.
Замечания
Для добавления новых ролей используйте команду CREATE ROLE , а для удаления роли — DROP ROLE .
ALTER ROLE не может управлять членством роли, для этого применяется GRANT и REVOKE .
Указывая в этой команде незашифрованный пароль, следует проявлять осторожность. Пароль будет передаваться на сервер открытым текстом и может также записаться в историю команд клиента или в протокол работы сервера. В psql есть команда \password , с помощью которой можно сменить пароль роли, не рискуя рассекретить пароль.
Также возможно связать сеансовые значения по умолчанию с определённой базой данных, а не с ролью (см. ALTER DATABASE ). В случае конфликта параметры для базы данных и роли переопределяют параметры только для роли, которые, в свою очередь, переопределяют параметры для базы данных.
Примеры
Изменение пароля роли:
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
Удаление пароля роли:
ALTER ROLE davide WITH PASSWORD NULL;
Изменение срока действия пароля (в частности, определяется, что пароль должен перестать действовать в полдень 4 мая 2015 г. в часовом поясе UTC +1):
ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
Установка бесконечного срока действия пароля:
ALTER ROLE fred VALID UNTIL 'infinity';
Наделение роли правами на создание других ролей и новых баз данных:
ALTER ROLE miriam CREATEROLE CREATEDB;
Определение нестандартного значения параметра maintenance_work_mem для роли:
ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
Определение нестандартного значения параметра client_min_messages для роли и заданной базы:
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
Совместимость
Оператор ALTER ROLE является расширением PostgreSQL .