Как работать с пользователями в PostgreSQL
Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена от пользователя postgres — чтобы войти в систему от данного пользователя, вводим:
* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.
Теперь запускаем командную оболочку PostgreSQL:
* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.
Для просмотра всех пользователей СУБД:
=# select * from pg_user;
Создание нового пользователя
Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.
1. Создание пользователя
Добавление новой роли (пользователя):
=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;
* в примере создана роль dmosk с паролем myPassword.
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 с помощью созданного пользователя:
Настройка прав доступа к базе с помощью групп
Сначала создадим групповую роль:
=# 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).
Удаление пользователей и групп
Удаление пользователя выполняется следующей командой:
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 как узнать пользователя
PostgreSQL — полезные команды
Внутренние команды (\команда) Помощь по внутренним командам
Просмотр существующих баз данных
Просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)
Подключение к базе данных databasename
Просмотр существующих таблиц в текущей базе данных
Просмотр списка всех таблиц с описанием в текущей базе данных
Просмотр структуры, индексов и прочих элемнтов таблицы
Просмотр списка всех пользователей и их привилегий
Просмотр списка доступных функций
Выполнить команды из файла FILE
Сохранить результат запроса в файл FILE
Включить отображение времени выполнения запроса
Timing is on. После чего все запросы станут отображаться в консольной утилите со временем выполнения (Отключение тайминга аналогично включению)
Timing is off. Помощь по SQL-командам
Например,просмотр синтаксиса создания базы данных
Полезные SQL команды Создание пользователя myuser с паролем ‘123’
Создание базы данных mytestdb с владельцем myuser
Предоставление пользователю myuser всех прав доступа к базе mytestdb
Удаление базы данных mytestdb
Удаление пользователя myuser
Просмотр активных/текущих процессов
Обнуление/усечение таблицы
Просмотр размера базы данных
Просмотр размера таблицы
Просмотр размера таблицы без индексов
Просмотр размера самых больших таблиц
Просмотр размера самой большой таблицы
Результатом будет самая большая таблица (в примере site_categories) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 18 MB)
Просмотр самых больших объектов в базе данных
Просмотр запущенной версии PostgreSQL
Полезные PSQL-команды Просмотр всех переменных и их значения в PostgreSQL
Вывод результата запроса в файл
Бекап и восстановление с бекапа баз данных PostgreSQL
Бекап баз данных
Бекап кокретной базы данных
Создание бекапа базы mydatabase, в сжатом виде
Создание бекапа базы mydatabase, в виде обычного текстового файла, включая команду для создания БД
Создание бекапа базы mydatabase с сжатием в gz
Создание дампа c удаленного сервера
Создание дампа c удаленного сервера и заливка на локальный сервер
Бекап всех баз даннях
Список наиболее часто используемых опций:
-h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
-p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
-u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
-a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
-b — включать в дамп большие объекты (blog’и).
-s, —schema-only — дамп только схемы.
-C, —create — добавляет команду для создания БД.
-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
-O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
-F, —format
-t, —table=TABLE — указываем определенную таблицу для дампа.
-v, —verbose — вывод подробной информации.
-D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.
Восстановление баз данных
В PostgreSQL есть две утилиты для восстановления базы из бекапа.
psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);
Восстановлени кокретной базы данных
PostgreSQL
Обучение PostgreSQL. Полный курс по работе с базой данных PostgreSQL!
По умолчанию PostgreSQL настроен так, что каждый локальный пользователь может подсоединиться к базе совпадающей по названию с регистрационным именем клиента, при условии что такая база данных уже создана.
Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.
pg_hba.conf идентификация пользователей
В этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к SQL серверу PostgreSQL, а также методы идентификации клиентов. Этот файл может содержать два вида записей:
Примеры записей pg_hba.conf:
Кодировка БД PostgreSQL и locale
Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):
Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.
Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.
Клиенты администрирования PostgreSQL
В директории /usr/share/doc/postgresql* можно найти дополнительную информацию по запуску.
Посмотреть и удалить активные запросы
SELECT запросы можно снимать из ОС командой kill
Транзакции в PostgreSQL
PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.
Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с >
Выполним транзакцию для test777:
Мониторинг, логи, размер БД PostgreSQL
Лог файлы
Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.
Мониторинг
Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту Команда watch с практическим примерами:
Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам, запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:
«пользователь», «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:
Views сборщик статистики
Представления (Views) сборщика статистики.
Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.
Информация собранная «статистическим сборником» может оказаться полезной для оценки эффективности базы данных и запросов. Из этих представлений можно узнать, например
Стандартные Statistics Views. Вывести все представления каталога
Количество модификаций, произошедших в таблице. Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.
Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.
Уровни блокировок таблиц
Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.
Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.
Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.
Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.
Automatic Vacuuming
Автоматическая сборка мусора (Automatic Vacuuming).
Синтаксис VACUUM:
Синтаксис ANALYZE:
Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов. То есть, если не хочется настраивать автоматическую сборку мусора, то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине, что иначе не получится заново использовать дисковое пространство, которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в PostgreSql postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.
Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:
При наличии необязательного ключевого слова ANALYZE PostgreSQL анализирует структуру данных во всех полях всех таблиц (или только заданной таблицы, если она указана), после чего эта информация используется оптимизатором запросов для более эффективного планирования. Ключевое слово ANALYZE также позволяет ограничить анализ отдельными полями.
Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.
Получение id добавленной записи в PostgeSQL
Системные таблицы pg_
Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.
| № | Имя таблицы | Назначение таблицы | |||
|---|---|---|---|---|---|
| 1 | pg_aggregate | aggregate functions | |||
| 2 | pg_am | index access methods | |||
| 3 | pg_amop | access method operators | |||
| 4 | pg_amproc | access method support procedures | |||
| 5 | pg_attrdef | column default values | |||
| 6 | pg_attribute | table columns («attributes») | |||
| 7 | pg_authid | authorization identifiers (roles) | |||
| 8 | pg_auth_members | authorization identifier membership relationships | |||
| 9 | pg_cast | casts (data type conversions) | |||
| 10 | pg_class PostgreSQL System Catalogs | tables, indexes, sequences, views («relations») | |||
| 11 | pg_constraint | check constraints, unique constraints, primary key constraints, foreign key constraints | |||
| 12 | pg_conversion | encoding conversion information | |||
| 13 | pg_database | databases within this database cluster | Хранятся имена доступных баз данных | ||
| 14 | pg_depend | dependencies between database objects | |||
| 15 | pg_description | descriptions or comments on database objects | В таблице хранятся описания объектов, для которых была применена функция COMMENT (расширение PostgreSQL). Например COMMENT ON TABLE mytable IS ‘Эта строка будет сохранена в системной таблице pg_description.’; | ||
| 16 | pg_enum | enum label and value definitions | |||
| 17 | pg_foreign_data_wrapper | foreign-data wrapper definitions | |||
| 18 | pg_foreign_server | foreign server definitions | |||
| 19 | pg_index | additional index information | |||
| 20 | pg_inherits | table inheritance hierarchy | |||
| 21 | pg_language | languages for writing functions | |||
| 22 | pg_largeobject | large objects | |||
| 23 | PostgreSQL pg_listener | asynchronous notification support | Используется механизмом LISTEN/NOTIFY. pg_listener существует в версиях PostgreSQL меньше 9. | ||
| 24 | pg_namespace | schemas | |||
| 25 | pg_opclass | access method operator classes | |||
| 26 | pg_operator | operators | |||
| 27 | pg_opfamily | access method operator families | |||
| 28 | pg_pltemplate | template data for procedural languages | |||
| 29 | pg_proc | functions and procedures | |||
| 30 | pg_rewrite | query rewrite rules | |||
| 31 | pg_shdepend | dependencies on shared objects | |||
| 32 | pg_shdescription | comments on shared objects | |||
| 33 | pg_statistic | planner statistics | |||
| 34 | pg_tablespace | tablespaces within this database cluster | |||
| 35 | pg_trigger | triggers | Триггеры хранятся в системной таблице pg_trigger, что позволяет получить информацию о существующих триггерах на программном уровне. | ||
| 36 | pg_ts_config | text search configurations | |||
| 37 | pg_ts_config_map | text search configurations’ token mappings | |||
| 38 | pg_ts_dict | text search dictionaries | |||
| 39 | pg_ts_parser | text search parsers | |||
| 40 | pg_ts_template | text search templates | |||
| 41 | pg_type | data types | |||
| 42 | pg_user_mapping | mappings of users to foreign servers | |||
| Представления (View) | Назначение | ||||
| 43 | pg_cursors | open cursors | |||
| 44 | pg_group | groups of database users | |||
| 45 | pg_indexes | indexes | |||
| 46 | pg_locks блокировки в PostgreSQL | currently held locks | Содержит информацию о блокировках. Уровни блокировок таблиц. | ||
| 47 | pg_prepared_statements | prepared statements | |||
| 48 | pg_prepared_xacts | prepared transactions | |||
| 49 | pg_roles | database roles | |||
| 50 | pg_rules | rules | |||
| 51 | pg_settings | parameter settings | |||
| 52 | pg_shadow | database users | Существует для обратной совместимости, она имитирует каталог, который существовал в PostgreSQL до версии 8.1. | ||
| 53 | pg_stats | planner statistics | |||
| 54 | pg_tables | tables | |||
| 55 | pg_timezone_abbrevs | time zone abbreviations | |||
| 56 | pg_timezone_names | time zone names | |||
| 57 | pg_user | database users | Информативный характер о пользователях, пароль содержится в таблице pg_shadow | ||
| 58 | pg_user_mappings | user mappings | |||
| 59 | pg_views | views | |||
Partitioning (Партицирование)
Partitioning (партицирование, секционирование).
Ссылки
Обучение PostgreSQL. Полный курс по работе с базой данных PostgreSQL!
