Содержание

postgres.conf logs
ROLE
100 команд для администрирования postgres

postgres.conf logs

Для обеспечения безопасности и эффективного расследования инцидентов в PostgreSQL важно настроить параметры логирования. Вот основные параметры, которые следует учитывать:

1. Настройки логирования

logging_collector: Включите сборщик логов, установив этот параметр в on. Это позволит PostgreSQL записывать логи в файлы.
log_directory: Укажите директорию для хранения логов. Рекомендуется использовать отдельную директорию, чтобы облегчить управление логами.
log_filename: Настройте формат имени файлов логов, например, postgresql-%Y-%m-%d_%H%M%S.log.
log_rotation_age: Установите время, через которое логи будут ротироваться. Например, 1d (один день).
log_rotation_size: Определите максимальный размер файла лога до его ротации. Например, 100MB.

2. Уровень детализации логов

log_min_messages: Установите уровень сообщений, которые должны записываться. Рекомендуется warning или error для общего логирования, но можно использовать info для более детальной информации.
log_min_error_statement: Определите уровень ошибок, для которых будут записываться SQL-запросы. Рекомендуется error для безопасности.
log_statement: Настройте, какие SQL-запросы логировать. Вы можете установить ddl (только DDL-запросы) или all (все запросы) для более детального мониторинга.

3. Логирование активности

log_connections: Включите логирование успешных подключений к базе данных.
log_disconnections: Включите логирование отключений от базы данных.
log_duration: Записывайте время выполнения каждого запроса. Это может помочь в анализе производительности и выявлении аномалий.
log_lock_waits: Включите логирование ожидания блокировок для выявления проблем с производительностью и потенциальных инцидентов.

4. Логирование выполнения запросов

track_activities: Включите отслеживание активностей для мониторинга текущих запросов.
track_counts: Включите отслеживание статистики, чтобы иметь возможность анализировать частоту выполнения запросов.

5. Настройки безопасности

log_line_prefix: Настройте префикс для строк логов. Например, можно добавить метку времени, имя пользователя и IP-адрес клиента для удобства анализа.

Пример конфигурации

Вот пример конфигурации для файла postgresql.conf:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'
log_rotation_size = '100MB'
log_min_messages = warning
log_min_error_statement = error
log_statement = 'ddl'
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on
track_activities = on
track_counts = on
log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d,client=%h '

# Включаем сборщик логов
logging_collector = on

# Указываем директорию для логов
log_directory = 'pg_log'

# Формат имени файлов логов
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# Ротация логов по времени — один раз в день
log_rotation_age = '1d'

# Ротация логов по размеру — если файл достигает 100 MB
log_rotation_size = '100MB'

# Минимальный уровень сообщений для логирования — предупреждения и ошибки
log_min_messages = warning

# Логирование SQL-запросов, которые вызвали ошибки
log_min_error_statement = error

# Логировать команды, изменяющие данные (INSERT, UPDATE, DELETE)
log_statement = 'ddl'

# Логирование успешных подключений
log_connections = on

# Логирование отключений
log_disconnections = on

# Логирование продолжительности выполнения запросов
log_duration = on

# Логирование ожидания блокировок
log_lock_waits = on

# Включаем отслеживание активности пользователей
track_activities = on

# Включаем сбор статистики запросов
track_counts = on

# Префикс для строк логов с указанием времени, процесса, пользователя, базы данных и клиента
log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d,client=%h '

ROLE

В PostgreSQL атрибуты роли (roles) определяют права и возможности, предоставляемые роли. Роли в PostgreSQL могут быть либо пользователями (логинами), либо группами пользователей. Вот основные атрибуты роли:

LOGIN - роль может выполнять вход в систему. Это атрибут по умолчанию для новых пользователей.
NOLOGIN - роль не может выполнять вход в систему. Обычно используется для групп ролей.
SUPERUSER - роль является суперпользователем и имеет все права в базе данных.
NOSUPERUSER - роль не является суперпользователем.
CREATEDB - роль может создавать базы данных.
NOCREATEDB - роль не может создавать базы данных.
CREATEROLE - роль может создавать и изменять другие роли.
NOCREATEROLE - роль не может создавать и изменять другие роли.
INHERIT - роль наследует права от ролей, которые ей назначены.
NOINHERIT - роль не наследует права от ролей, которые ей назначены.
REPLICATION - роль может выполнять действия, связанные с репликацией (например, создавать слот репликации).
NOREPLICATION - роль не может выполнять действия, связанные с репликацией.
BYPASSRLS - роль может обходить политику управления доступом к строкам (Row Level Security).
NOBYPASSRLS - роль не может обходить политику управления доступом к строкам.
CONNECTION LIMIT - ограничивает количество одновременных соединений, которые может установить роль.

Примеры создания ролей с различными атрибутами:

-- Создание роли пользователя с правами суперпользователя
CREATE ROLE superuser_role WITH SUPERUSER LOGIN PASSWORD 'password';

-- Создание роли пользователя без прав суперпользователя и без возможности создавать базы данных
CREATE ROLE normal_user WITH NOSUPERUSER NOCREATEDB LOGIN PASSWORD 'password';

-- Создание группы ролей без возможности входа
CREATE ROLE readonly_group WITH NOLOGIN;

Для изменения атрибутов существующей роли используется команда ALTER ROLE:

-- Добавление прав на создание баз данных роли normal_user
ALTER ROLE normal_user CREATEDB;

-- Удаление прав суперпользователя у роли superuser_role
ALTER ROLE superuser_role NOSUPERUSER;

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

100 команд для администрирования postgres

Управление ролями и пользователями

Создание роли:
```
CREATE ROLE role_name;
```

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

CREATE USER user_name WITH PASSWORD 'password';

Удаление роли:
```
DROP ROLE role_name;
```
Удаление пользователя:
```
DROP USER user_name;
```
Изменение роли:
```
ALTER ROLE role_name WITH LOGIN;
```
Назначение роли другой роли:
```
GRANT role_name TO another_role;
```
Отзыв роли у другой роли:
```
REVOKE role_name FROM another_role;
```
Добавление прав на создание баз данных:
```
ALTER ROLE role_name CREATEDB;
```
Удаление прав на создание баз данных:
```
ALTER ROLE role_name NOCREATEDB;
```
Добавление прав суперпользователя:
```
ALTER ROLE role_name SUPERUSER;
```
Удаление прав суперпользователя:
```
ALTER ROLE role_name NOSUPERUSER;
```

Управление правами доступа

Предоставление прав на таблицу:
```
GRANT SELECT, INSERT ON table_name TO role_name;
```

Отзыв прав на таблицу:

REVOKE SELECT, INSERT ON table_name FROM role_name;

Предоставление прав на все таблицы в схеме:

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA schema_name TO role_name;

Отзыв прав на все таблицы в схеме:

REVOKE SELECT, INSERT ON ALL TABLES IN SCHEMA schema_name FROM role_name;

Предоставление прав на схему:

GRANT USAGE ON SCHEMA schema_name TO role_name;

Отзыв прав на схему:

REVOKE USAGE ON SCHEMA schema_name FROM role_name;

Предоставление прав на базу данных:
```
GRANT CONNECT ON DATABASE db_name TO role_name;
```

Отзыв прав на базу данных:

REVOKE CONNECT ON DATABASE db_name FROM role_name;

Предоставление прав на функцию:
```
GRANT EXECUTE ON FUNCTION func_name TO role_name;
```

Отзыв прав на функцию:

REVOKE EXECUTE ON FUNCTION func_name FROM role_name;

Предоставление прав на последовательность:
```
GRANT USAGE, SELECT ON SEQUENCE seq_name TO role_name;
```

Отзыв прав на последовательность:

REVOKE USAGE, SELECT ON SEQUENCE seq_name FROM role_name;

Политики безопасности строк (Row Level Security)

Включение политики RLS для таблицы:
```
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
```
Отключение политики RLS для таблицы:
```
ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;
```

Создание политики RLS:

CREATE POLICY policy_name ON table_name FOR SELECT TO role_name USING (condition);

Удаление политики RLS:
```
DROP POLICY policy_name ON table_name;
```

Управление аудитом и журналированием

Включение параметра журнала:
```
ALTER SYSTEM SET log_statement = 'all';
```
Отключение параметра журнала:
```
ALTER SYSTEM SET log_statement = 'none';
```

Включение аудита для определенной таблицы:

CREATE EXTENSION IF NOT EXISTS pgaudit;
ALTER SYSTEM SET pgaudit.log = 'read, write';

Настройка журнала сессий:

ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';

Настройка уровня журналирования ошибок:
```
ALTER SYSTEM SET log_min_error_statement = 'error';
```

Управление сертификатами и шифрованием

Настройка SSL для сервера:
```
ALTER SYSTEM SET ssl = 'on';
```

Указание пути к сертификатам:

ALTER SYSTEM SET ssl_cert_file = '/path/to/server.crt';
ALTER SYSTEM SET ssl_key_file = '/path/to/server.key';
ALTER SYSTEM SET ssl_ca_file = '/path/to/root.crt';

Включение шифрования SSL для клиентов:
```
ALTER SYSTEM SET ssl_prefer_server_ciphers = 'on';
```

Резервное копирование и восстановление

Создание резервной копии базы данных:
```
pg_dump db_name > db_name_backup.sql
```
Восстановление базы данных из резервной копии:
```
psql db_name < db_name_backup.sql
```

Мониторинг и управление производительностью

Настройка параметров журналирования запросов:
```
ALTER SYSTEM SET log_duration = 'on';
```
Просмотр текущих процессов:
```
SELECT * FROM pg_stat_activity;
```
Завершение процесса:
```
SELECT pg_terminate_backend(pid);
```

Управление подключениями

Ограничение количества подключений:
```
ALTER ROLE role_name CONNECTION LIMIT 5;
```
Отключение роли от возможности подключения:
```
ALTER ROLE role_name NOLOGIN;
```

Полезные запросы для безопасности

Проверка прав на таблицы:

SELECT grantee, table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'role_name';

Просмотр всех пользователей:
```
SELECT usename FROM pg_user;
```
Просмотр всех ролей:
```
SELECT rolname FROM pg_roles;
```

Просмотр прав пользователей на функции:

SELECT nspname AS schema, proname AS function, proowner::regrole AS owner
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid;

Примеры использования схем и контекстов безопасности

Создание схемы и назначение прав:

CREATE SCHEMA schema_name AUTHORIZATION role_name;
GRANT USAGE ON SCHEMA schema_name TO another_role;

Перемещение таблицы в другую схему:
```
ALTER TABLE table_name SET SCHEMA new_schema_name;
```

Создание контекста безопасности (security context):

CREATE EXTENSION IF NOT EXISTS sepgsql;
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Применение контекста безопасности к пользователям:
```
ALTER ROLE role_name SET sepgsql.enable = 'on';
```

Команды для управления таблицами

Создание таблицы с определенными правами:

CREATE TABLE table_name (id SERIAL PRIMARY KEY, name TEXT) WITH (OIDS=FALSE);
GRANT SELECT, INSERT ON table_name TO role_name;

Удаление таблицы:
```
DROP TABLE table_name;
```
Изменение владельца таблицы:
```
ALTER TABLE table_name OWNER TO new_owner;
```

Добавление столбца в таблицу:

ALTER TABLE table_name ADD COLUMN new_column TEXT;

Удаление столбца из таблицы:

ALTER TABLE table_name DROP COLUMN column_name;

Работа с индексами

Создание индекса:

CREATE INDEX idx_name ON table_name (column_name);

Удаление индекса:
```
DROP INDEX idx_name;
```

Создание уникального индекса:

CREATE UNIQUE INDEX unique_idx_name ON table_name (column_name);

Управление представлениями

Создание представления:

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;

Удаление представления:
```
DROP VIEW view_name;
```

Обновление представления:

CREATE OR REPLACE VIEW view_name AS SELECT column1, column2 FROM table_name;

Управление триггерами

Создание триггера:

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();

Удаление триггера:

DROP TRIGGER trigger_name ON table_name;

Создание функции триггера:

CREATE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
  -- код триггера
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Расширения безопасности

Установка расширения pgcrypto для шифрования:
```
CREATE EXTENSION IF NOT EXISTS pgcrypto;
```
Использование функции для хэширования:
```
SELECT crypt('my_password', gen_salt('bf'));
```
Использование функции для генерации UUID:
```
SELECT gen_random_uuid();
```

Резервное копирование и восстановление с помощью утилит

Резервное копирование только структуры базы данных:
```
pg_dump -s db_name > db_schema_backup.sql
```
Восстановление только данных в существующую базу данных:
```
pg_restore -d db_name db_data_backup.dump
```
Полное резервное копирование базы данных:
```
pg_basebackup -D /path/to/backup -F tar -z -P
```

Управление логами и мониторингом

Просмотр текущих настроек логирования:
```
SHOW log_statement;
```

Включение логирования длительных запросов:

ALTER SYSTEM SET log_min_duration_statement = '1000'; -- в миллисекундах

Просмотр логов в реальном времени:
```
tail -f /var/log/postgresql/postgresql.log
```

Управление точками восстановления и транзакциями

Создание точки восстановления:
```
SAVEPOINT savepoint_name;
```
Откат к точке восстановления:
```
ROLLBACK TO SAVEPOINT savepoint_name;
```
Коммит транзакции:
```
COMMIT;
```
Откат транзакции:
```
ROLLBACK;
```

Полезные функции и утилиты

Проверка размер таблицы:

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Проверка размер базы данных:

SELECT pg_size_pretty(pg_database_size('db_name'));

Проверка активных блокировок:
```
SELECT * FROM pg_locks;
```
Анализ и очистка таблицы:
```
VACUUM ANALYZE table_name;
```
VACUUM ANALYZE выполняет очистку (VACUUM), а затем анализ (ANALYZE) всех указанных таблиц. Это удобная комбинация для регулярного обслуживания БД.
Анализ всех таблиц в базе данных:
```
VACUUM ANALYZE;
```

Управление таблицами партиций

Создание таблицы с партициями:

CREATE TABLE partitioned_table (
    id SERIAL,
    data TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

Создание партиции для таблицы:

CREATE TABLE partition_jan2024 PARTITION OF partitioned_table
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Настройки безопасности на уровне сети

Ограничение доступа к базе данных по IP-адресу: В файле pg_hba.conf:
```
host    all             all             192.168.0.0/24            md5
```

Управление резервным копированием на уровне файлов

Резервное копирование конфигурационных файлов:

cp /etc/postgresql/12/main/postgresql.conf /backup/path/
cp /etc/postgresql/12/main/pg_hba.conf /backup/path/

Управление репликацией

Настройка главного сервера для репликации: В файле postgresql.conf:
```
wal_level = replica
max_wal_senders = 5
```

Создание слота репликации:

SELECT * FROM pg_create_physical_replication_slot('replica_slot');

Управление миграцией данных

Импорт данных из CSV файла:

COPY table_name FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

Экспорт данных в CSV файл:

COPY table_name TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;

Управление данными в JSON

Создание таблицы с JSON полем:

CREATE TABLE json_table (id SERIAL, data JSONB);

Добавление данных в JSON поле:

INSERT INTO json_table (data) VALUES ('{"key": "value"}');

Запрос данных из JSON поля:
```
SELECT data->>'key' FROM json_table;
```

Настройки производительности

Настройка параметров кеша:
```
ALTER SYSTEM SET shared_buffers = '1GB';
```
Настройка параметров воркера для параллельного выполнения:
```
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
```

Полезные команды для администрирования

Просмотр текущего состояния системы:
```
SELECT * FROM pg_stat_database;
```
Проверка состояния подключений:
```
SELECT * FROM pg_stat_activity;
```
Просмотр истории транзакций:
```
SELECT * FROM pg_stat_xact_all_tables;
```
Просмотр статистики выполнения запросов:
```
SELECT * FROM pg_stat_statements;
```

Удаление временных файлов:

SELECT pg_catalog.pg_stat_file('base/pgsql_tmp', 'm');

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