====== Postgres ======
===== Install =====
<code bash># apt install postgresql postgresql-contrib</code>
<code bash># -u postgres psql -c "SELECT version();" </code>


==== Роли PostgreSQL и методы аутентификации ====
**PostgreSQL** поддерживает несколько методов аутентификации . Наиболее часто используемые методы:
  * **Trust** — роль может подключаться без пароля, если соблюдены критерии, определенные в pg_hba.confфайле.
  * **Password** - Роль может подключиться, указав пароль. Пароли могут быть сохранены как scram-sha-256 md5и password(открытый текст)
  * **Ident** — поддерживается только для соединений TCP/IP. Он работает путем получения имени пользователя операционной системы клиента с необязательным отображением имени пользователя.
  * **Peer** — то же, что и Ident, но поддерживается только для локальных подключений.

Аутентификация клиента PostgreSQL определяется в файле конфигурации с именем **pg_hba.conf**.\\
Пользователь "**postgres**" создается автоматически, **в системе!!!** и является **суперпользователем** для СУБД.\\

<code bash>
$ sudo su - postgres
$ psql 

  # PG Activity
su postgres
bash-4.4$ pg_activity

</code>


<code bash>$ sudo su - postgres -c "createuser kylo" 
$ sudo su - postgres -c "createdb kylodb"
psql$ GRANT ALL PRIVILEGES ON DATABASE kylodb TO kylo;</code>

По умолчанию сервер PostgreSQL слушает только локальный интерфейс 127.0.0.1.\\
<code bash>$ sudo nano /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*'     # what IP address(es) to listen on; </code>


<code bash>
$ sudo nano /etc/postgresql/11/main/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# The user jane will be able to access all databases from all locations using an md5 password
host    all             jane            0.0.0.0/0                md5
host    all             jane            192.168.1.134            trust
</code>


<code sql>
create database test_db;
create user test_user with encrypted password 'dbpassword';
grant all privileges on database test_db to test_user;
CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';
</code>

<code sql>
  # List tables
\dt || SELECT * FROM pg_catalog.pg_tables;
  # Список пользователей
\du
  # Права пользователя
SELECT * FROM information_schema.table_privileges where grantee = 'limit_view';
  # Сменить пароль пользователю
\password <username>
</code>


<details>
<summary>:!: Еще примеры</summary>
<code sql>
# Создаем нового пользователя, с разрешением логинится
create user <логин> with login password '<пароль>';
 
# Настройку привилегий удобнее вынести в группу (роль), создаем роль с мин правами
create role <роль> nosuperuser nocreatedb nocreaterole noreplication inherit;
 
# Добавляем созданного пользователя в нее
grant <роль> to <логин>;
 
# Далее настраиваем доступ (только select) для группы, переходим в нужную базу
\c <имя-базы>
GRANT USAGE ON SCHEMA <имя-схемы> TO <роль>;
GRANT SELECT ON ALL TABLES IN SCHEMA <имя-схемы> TO <роль>;
 
# Удаление прав
revoke usage on schema <имя-схемы> from <роль>;
revoke all on all tables in schema <имя-схемы> from <роль>;
</sql>
</details>


==== User Managment ====
:!: Роли базы данных концептуально полностью отделены от пользователей операционной системы. На практике может быть удобно поддерживать переписку, но это не обязательно. Роли базы данных являются глобальными для всей установки кластера базы данных (а не для отдельной базы данных).\\

<code sql>
CREATE ROLE name;
DROP ROLE name;

createuser name
dropuser name

SELECT rolname FROM pg_roles;
</code>


==== Role Attributes ====
Роль базы данных может иметь ряд атрибутов, которые определяют ее привилегии и взаимодействуют с системой аутентификации клиента\\
  * **login privilege** - Только роли, имеющие атрибут LOGIN, могут использоваться в качестве начального имени роли для соединения с базой данных. Роль с атрибутом LOGIN можно рассматривать как «пользователя базы данных».
  * **superuser status** - Суперпользователь базы данных обходит все проверки разрешений. Это опасная привилегия, и ею не следует пользоваться небрежно
  * **database creation** - Роли должно быть явно предоставлено разрешение на создание баз данных (за исключением суперпользователей
  * **role creation** - Роли должно быть явно предоставлено разрешение на создание дополнительных ролей
  * **password** - Пароль имеет значение только в том случае, если метод аутентификации клиента требует, чтобы пользователь ввел пароль при подключении к базе данных. Методы аутентификации password, md5 и crypt используют пароли. Пароли базы данных отделены от паролей операционной системы.

:!: **CREATE USER** эквивалентен **CREATE ROLE**, за исключением того, что **CREATE USER** предполагает **LOGIN** по умолчанию, а **CREATE ROLE** — нет.\\

:!: Атрибуты роли можно изменить после создания с помощью **ALTER ROLE**\\
<code sql>ALTER ROLE myname SET enable_indexscan TO off; </code>


<code sql>GRANT UPDATE ON accounts TO joe;
REVOKE ALL ON accounts FROM PUBLIC;</code>

<code sql>
ALTER ROLE super WITH PASSWORD 'secret123';</code>

<code bash>
\l - список баз
\c <dbname> выбор базы
</code>



==== OverAll ====
=== Форматирование вывода ===
<code bash>
  # Извлекаем таблицу (-l), убираем пробелы (-A) и седом меняем разделители на табы
sudo -u postgres psql -lA | sed -e "s/|/\t/g"
sudo -i -u postgres psql -d zabbix -c "select * from functions ;"
</code>


<code bash>
  # Узнать размер базы
sudo -u postgres psql -c "SELECT pg_size_pretty( pg_database_size( 'DBName' ) );"

  # Активные подключения к базе
SELECT * from pg_stat_activity where datname = 'mgat_R1789_0-11-g33f30064_20221209_110115_1820';

  # Предотвращаем возможность новых подключений
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'database_name';

  # Закрываем текущие сессии
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'database_name' AND pid <> pg_backend_pid();

  # Удалить
DROP DATABASE database_name;

  # Переименовать базу
alter database test rename to test1;
</code>

<details>
<summary>:!: Еще примеры</summary>
**Еще настройка**
<code bash>
echo export PATH=/usr/pgsql-15/bin:$PATH > /var/lib/pgsql/.pgsql_profile
export PGPORT=27953
</code>

Экспорт/Импорт\\
[[https://selectel.ru/blog/postgresql-backup-tools/|описание]]\\
<code bash>
su - postgres -c "pg_dump -p 27953 -Fc postgres > /tmp/postgres_db.dump"
  # Базу нужно создать перед восстановлением
su - postgres -c "pg_restore --dbname=test_postgres /tmp/postgres_db.dump"

  # Полный бекап
pg_dumpall -h localhost -p 27953 -U backuper --schema-only --file /tmp/db_dump.sql
  # Восстановление
psql -U postgres -p 27953 -f /tmp/db_dump.sql
</code>
</details>


<details>
<summary>:!: Работа с кластером</summary>
<code bash>
pg_lsclusters

pg_ctlcluster

pg_ctlcluster 9.4 secondary reload/start/stop
</code>
</details>