Удалить роль postgresql 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 linux

Так как роли могут владеть объектами баз данных и иметь права доступа к объектам других, удаление роли не сводится к немедленному действию DROP ROLE . Сначала должны быть удалены и переданы другим владельцами все объекты, принадлежащие роли; также должны быть отозваны все права, данные роли.

Владение объектами можно передавать в индивидуальном порядке, применяя команду ALTER , например:

Кроме того, для переназначения какой-либо другой роли владения сразу всеми объектами, принадлежащих удаляемой роли, можно применить команду REASSIGN OWNED . Так как REASSIGN OWNED не может обращаться к объектам в других базах данных, её необходимо выполнить в каждой базе, которая содержит объекты, принадлежащие этой роли. (Заметьте, что первая такая команда REASSIGN OWNED изменит владельца для всех разделяемых между базами объектов, то есть для баз данных или табличных пространств, принадлежащих удаляемой роли.)

После того как все ценные объекты будут переданы новым владельцам, все оставшиеся объекты, принадлежащие удаляемой роли, могут быть удалены с помощью команды DROP OWNED . И эта команда не может обращаться к объектам в других базах данных, так что её нужно запускать в каждой базе, которая содержит объекты, принадлежащие роли. Также заметьте, что DROP OWNED не удаляет табличные пространства или базы данных целиком, так что это необходимо сделать вручную, если роли принадлежат базы или табличные пространства, не переданные новым владельцам.

DROP OWNED также удаляет все права, которые даны целевой роли для объектов, не принадлежащих ей. Так как REASSIGN OWNED такие объекты не затрагивает, обычно необходимо запустить и REASSIGN OWNED , и DROP OWNED (в этом порядке!), чтобы полностью ликвидировать зависимости удаляемой роли.

С учётом этого, общий рецепт удаления роли, которая владела объектами, вкратце таков:

Когда не все объекты нужно передать одному новому владельцу, лучше сначала вручную отработать исключения, а в завершение выполнить показанные выше действия.

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

Источник

Как работать с пользователями в 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 linux

Так как роли могут владеть объектами баз данных и иметь права доступа к объектам других, удаление роли не сводится к немедленному действию DROP ROLE . Сначала должны быть удалены и переданы другим владельцами все объекты, принадлежащие роли; также должны быть отозваны все права, данные роли.

Владение объектами можно передавать в индивидуальном порядке, применяя команду ALTER , например:

Кроме того, для переназначения какой-либо другой роли владения сразу всеми объектами, принадлежащими удаляемой роли, можно применить команду REASSIGN OWNED . Так как REASSIGN OWNED не может обращаться к объектам в других базах данных, её необходимо выполнить в каждой базе, которая содержит объекты, принадлежащие этой роли. (Заметьте, что первая такая команда REASSIGN OWNED изменит владельца для всех разделяемых между базами объектов, то есть для баз данных или табличных пространств, принадлежащих удаляемой роли.)

После того как все ценные объекты будут переданы новым владельцам, все оставшиеся объекты, принадлежащие удаляемой роли, могут быть удалены с помощью команды DROP OWNED . И эта команда не может обращаться к объектам в других базах данных, так что её нужно запускать в каждой базе, которая содержит объекты, принадлежащие роли. Также заметьте, что DROP OWNED не удаляет табличные пространства или базы данных целиком, так что это необходимо сделать вручную, если роли принадлежат базы или табличные пространства, не переданные новым владельцам.

DROP OWNED также удаляет все права, которые даны целевой роли для объектов, не принадлежащих ей. Так как REASSIGN OWNED такие объекты не затрагивает, обычно необходимо запустить и REASSIGN OWNED , и DROP OWNED (в этом порядке!), чтобы полностью ликвидировать зависимости удаляемой роли.

С учётом этого, общий рецепт удаления роли, которая владела объектами, вкратце таков:

Когда не все объекты нужно передать одному новому владельцу, лучше сначала вручную отработать исключения, а в завершение выполнить показанные выше действия.

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

Источник

Использование ролей и управление доступом в PostgreSQL

PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL. PostgreSQL – очень производительный инструмент, предназначенный для систематизации и хранения данных приложения.

Данное руководство научит вас управлять правами доступа PostgreSQL.

Примечание: Руководство выполнено на облачном сервере Ubuntu 12.04, однако все инструкции, кроме раздела по установке, можно применить и в других современных дистрибутивах Linux.

Установка PostgreSQL

Если СУБД PostgreSQL не была установлена ранее, установите её сейчас. Для этого используйте команды:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

sudo su — postgres

Права доступа PostgreSQL

PostgreSQL управляет доступом при помощи так называемых ролей.

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

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

Просмотр ролей

Чтобы просмотреть существующие роли PostgreSQL, нужно открыть командную строку СУБД:

а затем ввести команду:

\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
postgres | Superuser, Create role, Create DB, Replication | <>

Как видите, после установки в PostgreSQL существует всего одна роль, которая обладает широкими правами доступа.

Создание ролей PostgreSQL

Существует два базовых способа создания ролей: в командной строке PostgreSQL и в командной строке системы.

Создание роли в PostgreSQL

Проще всего создавать новые роли в командной строке PostgreSQL.

Для этого используется следующий синтаксис:

CREATE ROLE new_role_name;

Попробуйте создать новую роль (в руководстве она условно называется demo_role):

CREATE ROLE demo_role;
CREATE ROLE

Проверьте список существующих ролей:

\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>

Как видите, в списке появилась новая роль. Обратите внимание: на данный момент у неё нет привилегий входа.

Создание роли в командной строке системы

Также можно создать роль при помощи команды createuser.

Закройте командную строку PostgreSQL:

Чтобы создать роль в командной строке системы, введите следующую команду (в руководстве эта роль будет условно называться test_user):

createuser test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

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

Снова откройте командную строку Postgres и запросите список существующих ролей:

psql
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>

Как видите, роли, созданные разными методами, не идентичны. Роль, созданная в командной строке системы, имеет привилегии входа.

Удаление ролей PostgreSQL

Теперь попробуйте уровнять привилегии ролей demo_role и test_user. Это можно сделать во время создания роли (то есть нужно удалить и заново создать demo_role). Также можно просто отредактировать привилегии существующей роли.

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

Для этого используется следующий синтаксис:

DROP ROLE role_name;
Delete the «demo_role» role by typing:
DROP ROLE demo_role;
DROP ROLE

Если заданной в команде роли не существует, команда вернёт ошибку:

DROP ROLE demo_role;
ERROR: role «demo_role» does not exist

Оператор IF EXISTS позволяет избежать этой ошибки; команда с таким оператором удалит роль, если она существует. Если указанной роли нет, команда не вернёт ошибку.

DROP ROLE IF EXISTS role_name;

То есть, в любом случае команда будет выполнена успешно и не вернёт ошибку.

DROP ROLE IF EXISTS demo_role;
NOTICE: role «demo_role» does not exist, skipping
DROP ROLE

Определение привилегий во время создания роли

Теперь попробуйте снова создать роль demo_role, заранее установив её права доступа. Права роли можно указать сразу после главного оператора create.

Синтаксис выглядит так:

CREATE ROLE role_name WITH optional_permissions;

Полный список опций доступа можно просмотреть при помощи команды:

Чтобы у пользователя, связанного с этой ролью, были привилегии входа, введите:

CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE

Проверьте список существующих ролей и обратите внимание на то, что теперь обе роли имеют одинаковые привилегии:

\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>

Чтобы роль имела права входа без аргумента login, используйте вместо CREATE ROLE такую команду:

CREATE USER role_name;

Команда CREATE USER отличается только тем, что автоматически даёт роли привилегии входа.

Управление правами роли PostgreSQL

Чтобы изменить права доступа уже существующей роли, используйте команду ALTER ROLE.

Её базовый синтаксис:

ALTER ROLE role_name WITH attribute_options;

Для примера попробуйте вернуть роли demo_role её исходные привилегии:

ALTER ROLE demo_role WITH NOLOGIN;
ALTER ROLE

Просмотрите список ролей:

\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>

Теперь у роли demo_role нет привилегий входа.

Вернуть привилегии входа можно при помощи команды:

ALTER ROLE demo_role WITH LOGIN;

Смена пользователя PostgreSQL

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

Чтобы изменить это поведение, можно изменить тип входа или настроить PostgreSQL для прослушивания локального интерфейса (это изменит тип подключения на удалённый).

Рассмотрим второй вариант.

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

Установите пароль для test_user:

Команда предложит ввести и подтвердить пароль. Затем закройте интерфейс PostgreSQL и вернитесь в сессию системного пользователя.

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

Но в данном случае это не так, потому нужно явно указать опции. Для этого используйте синтаксис:

psql -U user_name -d database_name -h 127.0.0.1 -W

Примечание: Вместо user_name укажите имя пользователя, при помощи которого нужно установить соединение; вместо database_name укажите имя БД, к которой нужно подключиться.

Оператор -h 127.0.0.1 указывает, что нужно подключиться к локальной машине по сетевому интерфейсу. Это позволит проходить аутентификацию, даже если имя пользователя и имя роли не совпадают. Флаг –W значит, что при входе в PostgreSQL нужно ввести пароль.

Чтобы открыть сессию пользователя test_user, введите:

psql -U test_user -d postgres -h 127.0.0.1 -W
Password for user test_user:

Программа запросит установленный ранее пароль.

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

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

Вернитесь в сессию администратора:

\q
sudo su — postgres
psql

Управление привилегиями PostgreSQL

Как передать привилегии

Как правило, при создании БД или таблицы права доступа к ней есть только у создавшей её роли. Но такое поведение можно изменить.

Передавать права доступа другим ролям можно при помощи команды GRANT; её базовый синтаксис:

GRANT permission_type ON table_name TO role_name;

Для примера создайте таблицу:

CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
NOTICE: CREATE TABLE will create implicit sequence «demo_id_seq» for serial column «demo.id»
CREATE TABLE

\d
List of relations
Schema | Name | Type | Owner
———+————-+———-+———-
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)

Теперь попробуйте передать некоторые права доступа к таблице demo роли demo_role (пусть это будет право на обновление, UPDATE).

GRANT UPDATE ON demo TO demo_role;

Чтобы передать полные права на таблицу, используйте оператор ALL:

GRANT ALL ON demo TO test_user;

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

GRANT INSERT ON demo TO PUBLIC;

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

\z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
———+————-+———-+—————————-+—————————
public | demo | table | postgres=arwdDxt/postgres +|
| | | demo_role=w/postgres +|
| | | test_user=arwdDxt/postgres+|
| | | =a/postgres |
public | demo_id_seq | sequence | |
(2 rows)

Как отнять привилегии

Команда REVOKE отнимает привилегии.

REVOKE permission_type ON table_name FROM user_name;

Данная команда тоже может использовать операторы all и public.

REVOKE INSERT ON demo FROM PUBLIC;

Групповые роли PostgreSQL

PostgreSQL позволяет группировать роли, благодаря чему роли могут наследовать заранее установленные права доступа.

Для примера можно создать роль temporary_users и добавить в неё роли demo_role и test_user:

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

Теперь групповая роль temporary_users управлят привилегиями ролей demo_role и test_user.

Чтобы просмотреть сведения о принадлежности ролей можно с помощью команды:

\du
List of roles
Role name | Attributes | Member of
——————+————————————————+——————-
demo_role | |
postgres | Superuser, Create role, Create DB, Replication | <>
temporary_users | Cannot login | <>
test_user | |

Команда set role позволяет выбрать групповую роль, права которой нужно использовать.

Например, текущий пользователь postgres имеет права суперпользователя. Даже несмотря на то, что этот пользователь не является членом роли temporary_users, он может использовать её права:

SET ROLE temporary_users;

Теперь любая созданная таблица будет принадлежать групповой роли temporary_users.

CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);

Просмотреть владельцев таблиц можно с помощью команды:

\d
List of relations
Schema | Name | Type | Owner
———+—————+———-+——————
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | temporary_users
public | hello_id_seq | sequence | temporary_users
(4 rows)

Как видите, новая таблица принадлежит роли temporary_users.

Чтобы вернуть оригинальные права текущей роли, введите:

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

ALTER ROLE test_user INHERIT;

Чтобы удалить групповую роль (или любую роль), используйте:

DROP ROLE temporary_users;
ERROR: role «temporary_users» cannot be dropped because some objects depend on it
DETAIL: owner of table hello
owner of sequence hello_id_seq

Эта команда вернёт ошибку, потому что роли temporary_users принадлежит таблица. Сначала нужно передать права на таблицу другой роли:

ALTER TABLE hello OWNER TO demo_role;

Теперь роль таблица принадлежит роли demo_role.

\d
List of relations
Schema | Name | Type | Owner
———+—————+———-+————
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | demo_role
public | hello_id_seq | sequence | demo_role
(4 rows)

После этого роль temporary_users можно удалить:

DROP ROLE temporary_users;

Это удалит роль temporary_users; члены этой групповой роли не будут удалены.

Заключение

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

Источник