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 указание_роли ] [ элемент_схемы [ … ] ]

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

Создание пользователя

CREATE USER username WITH PASSWORD 'your_password';

Смена пароля

ALTER USER user WITH PASSWORD 'password';

Ввод хэшированного пароля пароля

ALTER USER User WITH ENCRYPTED PASSWORD 'md5788e7531f05a27b36bbb6187d58db2c8';

Где пароль хэшируется алгоритмом 'md5' || md5('Pass' || 'User'). Например, в Python3:

import hashlib
print('md5' + hashlib.md5("PassUser".encode()).hexdigest())

Включение пользователя в группу

GRANT group TO user;

Разрешения

подключение к БД

GRANT CONNECT ON DATABASE database_name TO role_name;

Кроме того, для удалённого подключения с паролем нужно добавить в pg_hba.conf:

# 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

использование схемы

GRANT USAGE ON SCHEMA schema_name TO role_name;

селект в таблице

GRANT SELECT ON table_name TO role_name;

селект во всех таблицах схемы

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

разрешение по-умолчанию для новых таблиц схемы

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO role_name;

Просмотр разрешений

SELECT * FROM information_schema.role_table_grants;

Примерное количество строк в таблице

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

postgresql