postgresql
Управление ролями

Синтаксис

• CREATE ROLE name [ [ WITH ] option [ ... ] ]

• CREATE USER name [ [ WITH ] option [ ... ] ]

• where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

Создайте пользователя с паролем

Как правило, вам следует избегать использования роли базы данных по умолчанию (часто postgres ) в вашем приложении. Вместо этого вы должны создать пользователя с более низким уровнем привилегий. Здесь мы делаем одно имя niceusername и даем ему пароль very-strong-password

CREATE ROLE niceusername with PASSWORD 'very-strong-password' LOGIN;

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

Чтобы этого избежать, используйте команду \password для установки пароля пользователя. Если пользователь, выдающий команду, является суперпользователем, текущий пароль не будет задан. (Должен быть суперпользователем, чтобы изменять пароли суперпользователей)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Создание роли и соответствующей базы данных

Чтобы поддерживать данное приложение, вы часто создаете новую роль и базу данных для соответствия.

Командами оболочки для запуска будут следующие:

$ createuser -P blogger
Enter password for the new role: ********
Enter it again: ********

$ createdb -O blogger blogger

Это предполагает, что pg_hba.conf настроен правильно, что, вероятно, выглядит так:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sameuser        all             localhost               md5
local   sameuser        all                                     md5

Предоставить и отменить привилегии.

Предположим, что у нас три пользователя:

1. Администратор базы данных> admin
2. Приложение с полным доступом к ее данным> read_write
3. Доступ только для чтения> read_only

--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

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

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

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

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

--ACCESS SEQUENCES
REVOKE ALL   ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and SETVAL
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and NEXTVAL
GRANT ALL    ON ALL SEQUENCES IN SCHEMA public TO admin;

Изменить поиск по умолчанию для пользователя

С помощью приведенных ниже команд пользовательский путь поиска по умолчанию может быть установлен.

1. Проверьте путь поиска до установки схемы по умолчанию.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

2. Установите search_path с alter user команды alter user чтобы добавить новую схему my_schema

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

3. Проверьте результат после выполнения.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Альтернатива:

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Предоставьте права доступа на объекты, созданные в будущем.

Предположим, что у нас three users :

1. Администратор базы данных> admin
2. Приложение с полным доступом к ее данным> read_write
3. Доступ только для чтения> read_only

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

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT                      ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL                         ON TABLES TO admin;

Или вы можете установить права доступа для объектов, созданных в будущем указанным пользователем.

ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT  ON TABLES TO read_only;

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

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;