Postgres

# apt install postgresql postgresql-contrib

# -u postgres psql -c "SELECT version();" 

PostgreSQL поддерживает несколько методов аутентификации . Наиболее часто используемые методы:

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

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

$ sudo su - postgres
$ psql 
 
  # PG Activity
su postgres
bash-4.4$ pg_activity

$ sudo su - postgres -c "createuser kylo" 
$ sudo su - postgres -c "createdb kylodb"
psql$ GRANT ALL PRIVILEGES ON DATABASE kylodb TO kylo;

По умолчанию сервер PostgreSQL слушает только локальный интерфейс 127.0.0.1.

$ sudo nano /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*'     # what IP address(es) to listen on; 

$ 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

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';

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

Еще примеры

# Создаем нового пользователя, с разрешением логинится
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;

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

• 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

ALTER ROLE myname SET enable_indexscan TO off; 

GRANT UPDATE ON accounts TO joe;
REVOKE ALL ON accounts FROM PUBLIC;

ALTER ROLE super WITH PASSWORD 'secret123';

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

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

  # Узнать размер базы
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;

Еще примеры

Еще настройка

echo export PATH=/usr/pgsql-15/bin:$PATH > /var/lib/pgsql/.pgsql_profile
export PGPORT=27953

Экспорт/Импорт
описание

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

Работа с кластером

pg_lsclusters
 
pg_ctlcluster
 
pg_ctlcluster 9.4 secondary reload/start/stop