====== postgresql ======

===== Команды psql =====

''\l'' - список баз данных.

''\c'' dbname - подсоединение к БД dbname.

''\dn'' - список схем

''\dt'' - список всех таблиц.

''\d table'' - структура таблицы table.

''\du'' - список всех пользователей и их привилегий.

''\dt+'' - список всех таблиц с описанием.

''\dt *s*'' - список всех таблиц, содержащих s в имени.

''\i FILE'' - выполнить команды из файла FILE.

''\o FILE'' - сохранить результат запроса в файл FILE.

''\a'' - переключение между режимами вывода: с/без выравнивания.

''\q'' - выход

===== Схема =====

создать схему:

CREATE SCHEMA имя_схемы [ AUTHORIZATION указание_роли ] [ элемент_схемы [ ... ] ]

===== Пользователи и права =====

==== Создание пользователя ====
<code>
CREATE USER username WITH PASSWORD 'your_password';
</code>

==== Смена пароля ====
<code>
ALTER USER user WITH PASSWORD 'password';
</code>

==== Ввод хэшированного пароля пароля ====
<code>
ALTER USER User WITH ENCRYPTED PASSWORD 'md5788e7531f05a27b36bbb6187d58db2c8';
</code>
Где пароль хэшируется алгоритмом 'md5' || md5('Pass' || 'User').
Например, в Python3:
<code>
import hashlib
print('md5' + hashlib.md5("PassUser".encode()).hexdigest())
</code>

==== Включение пользователя в группу ====
<code>
GRANT group TO user;
</code>

==== Разрешения ====

=== подключение к БД ===
<code>
GRANT CONNECT ON DATABASE database_name TO role_name;
</code>

Кроме того, для удалённого подключения с паролем нужно добавить в pg_hba.conf:
<code>
# TYPE DATABASE       USER      CIDR-ADDRESS   METHOD
host   database_name  user_name 192.168.0.1/32 md5

# or for coonect all users to all databases from all ip-addresses:
host   all            all       0.0.0.0/0      md5
</code>


=== использование схемы ===
<code>
GRANT USAGE ON SCHEMA schema_name TO role_name;
</code>

=== селект в таблице ===
<code>
GRANT SELECT ON table_name TO role_name;
</code>

=== селект во всех таблицах схемы ===
<code>
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;
</code>

=== разрешение по-умолчанию для новых таблиц схемы ===
<code>
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO role_name;
</code>

==== Просмотр разрешений ====
<code>
SELECT * FROM information_schema.role_table_grants;
</code>

===== Примерное количество строк в таблице =====
<code>
select n.nspname, c.relname, c.reltuples::bigint
FROM pg_class c
left join pg_namespace n on relnamespace = n.oid
where relname like 'u%'
and n.nspname = 'app'
and relkind = 'r'
order by relname
</code>

 {{tag> postgresql }}