Различия

Показаны различия между двумя версиями страницы.

--- databases:postgres [2022/02/08 16:01]
admin
+++ databases:postgres [2025/03/01 14:08] (текущий)
admin [Роли PostgreSQL и методы аутентификации]
@@ Строка 1: / Строка 1: @@
 ====== 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файле.
@@ Строка 20: / Строка 15: @@
 Пользователь "**postgres**" создается автоматически, **в системе!!!** и является **суперпользователем** для СУБД.\\
-<code bash>$ sudo su - postgres
+<code bash>
-$ psql </code>
+$ sudo su - postgres
+$ psql
+  # PG Activity
+su postgres
+bash-4.4$ pg_activity
+</code>
@@ Строка 27: / Строка 29: @@
 $ sudo su - postgres -c "createdb kylodb"
 psql$ GRANT ALL PRIVILEGES ON DATABASE kylodb TO kylo;</code>
 По умолчанию сервер PostgreSQL слушает только локальный интерфейс 127.0.0.1.\\
@@ Строка 34: / Строка 35: @@
+<code bash>
-<code bash>$ sudo nano /etc/postgresql/11/main/pg_hba.conf
+$ 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>
-# The user jane will be able to access only the janedb from all locations using an md5 password
-host    janedb          jane            0.0.0.0/0                md5
-# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
+<code sql>
-host    all             jane            192.168.1.134            trust </code>
+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>
-<code sql>create database test_db;
-create user test_user with encrypted password 'dbpassword';
-grant all privileges on database test_db to test_user; </code>
-<code sql>CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd'; </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 <роль>;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO my_user;
-==== User Managment ====
+# Удаление прав
+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;
-<code sql>CREATE ROLE name;
+createuser name
-DROP ROLE name;</code>
+dropuser name
-<code sql>createuser name
+SELECT rolname FROM pg_roles;
-dropuser name</code>
+</code>
-<code sql>SELECT rolname FROM pg_roles;</code>
 ==== Role Attributes ====
 Роль базы данных может иметь ряд атрибутов, которые определяют ее привилегии и взаимодействуют с системой аутентификации клиента\\
   * **login privilege** - Только роли, имеющие атрибут LOGIN, могут использоваться в качестве начального имени роли для соединения с базой данных. Роль с атрибутом LOGIN можно рассматривать как «пользователя базы данных».
   * **superuser status** - Суперпользователь базы данных обходит все проверки разрешений. Это опасная привилегия, и ею не следует пользоваться небрежно
@@ Строка 87: / Строка 121: @@
 REVOKE ALL ON accounts FROM PUBLIC;</code>
-<code sql>ALTER ROLE super WITH PASSWORD 'secret123';</code>
+<code sql>
+ALTER ROLE super WITH PASSWORD 'secret123';</code>
 <code bash>
@@ Строка 94: / Строка 128: @@
 \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>
+==== Liqubase ====
+<details>
+<summary>:!: Образец скрипта для проливки (актуальнее)</summary>
+<code bash>
+#!/bin/bash
+java -cp liquibase-core-4.9.0.jar:picocli-4.7.6.jar:postgresql-42.7.4.jar \
+  liquibase.integration.commandline.LiquibaseCommandLine update \
+  -Ddefaults-file=liquibase.properties \
+  --url=${LIQUI_URL} \
+  --username=${LIQUI_UNAME} \
+  --password=${LIQUI_PASSWD}
+</code>
+Файл .properties\\
+Аргументы переопределяют проперти\\
+<code yaml>
+changeLogFile: changelog.xml
+driver: org.postgresql.Driver
+url:
+username:
+password:
+verbose: true
+#parameter.my_var: "my data"
+</code>
+В changelog используется так
+<code xml>
+(...)
+${my_var} // либо в кавычках
+(...)
+</code>
+</details>
+<details>
+<summary>:!: Образец скрипта для проливки</summary>
+<code bash>
+#!/bin/bash
+export LIQUIBASE_HOME=`pwd`
+java -jar internal/lib/liquibase-core.jar update \
+  --changelog-file=0001_changelog.xml \
+  --url=jdbc:postgresql://99.99.99.99:5432/my_db?currentSchema=my_schema \
+  --username=postgres \
+  --password=postgres
+</code>
+В подпапке "internal/lib/" располагаются еще целый ряд джарок для liqubase\\
+</details>
-Извлекаем таблицу (-l), убираем пробелы (-A) и седом меняем разделители на табы
-<code bash>sudo -u postgres psql -lA | sed -e "s/|/\t/g" </code>
+==== Docker ====
+[[linux:containers#postgresql|Compose]]
-<code bash> </code>
-<code bash> </code>

zxcx

Инструменты пользователя

Инструменты сайта

Различия

Инструменты страницы