Архив метки: SQL

PostgreSQL выдача прав пользователю с учетом создаваемых в будущем объектов

Многие сталкиваются в PostgreSQL с тем, что выдав пользователю права на, например, таблицы в бд, на новые таблицы эти права не распространяются. Т.е., если мы выдаем права на update на все таблицы в схеме public следующим образом:

GRANT UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO testuser;

Потом создадим в схеме новую таблицу, то пользователь не будет иметь прав выполнять update на этой новой таблице. Та же ситуация и с другими объектами бд (функции, sequence-ы и т.д.).
Для того, чтобы решить эту проблему, нужно изменить у пользователя права доступа по умолчанию ALTER DEFAULT PRIVILEGES

Допустим, мы создали нового пользователя testuser и от нас требуется выдать ему все права на схему public в БД testdb.

GRANT CONNECT ON DATABASE testdb TO testuser; --права на подключение к бд
GRANT ALL ON SCHEMA public TO testuser; --права на CREATE и USAGE
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO testuser; --права на таблицы
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO testuser; --права на sequence-ы
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO testuser; --права на функции
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TYPES TO testuser; --права на типы

Теперь testuser может создавать таблицы и другие объекты в схеме public. А так же будет иметь права на созданные другими пользователями (например, владельцем бд) объекты.
REVOKE выполняется так же.

Важно! Если при выдаче или отзыве прав указать не конкретного пользователя а роль PUBLIC, например так:

ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT SELECT ON TABLES TO PUBLIC;

То все пользователи получат права select таблиц в этой схеме.

Так же рекомендуется для безопасности отзывать все права у роли PUBLIC на всех базах:

REVOKE ALL ON schema public FROM public;
0

Репликация в PostgreSQL при помощи repmgr (настройка, switchover, failover)

Не так давно, мне довелось разбираться с настройкой репликации PostgreSQL при помощи утилиты repmgr. В процессе работы, я столкнулся с тем, что не смог найти полного гайда по настройке и выполнению switchover и failover в различных ситуациях. В итоге, я написал для себя инструкцию, надеюсь она будет полезна тем, кто решит управлять репликацией в PostgreSQL при помощи инструмента repmgr. Читать далее

1+

PostgreSQL управление расширениями (extensions)

Расширения PostgreSQL предоставляют дополнительный функционал. Часть расширений входит в пакет contrib и доступны в
PostgreSQL сразу после его установки (пакет свой под каждую версию PG, например postgresql11-contrib).
Расширения ставятся индивидуально в каждую БД отдельно.
Так же, можно установить и использовать расширения не входящие в contrib-пакет. Для этого их необходимо скомпилировать
под конкретную мажорную версию PG и добавить необходимые файлы в директории PG.
PostgreSQL хранит файлы расширений в директориях (директория зависит от версии):

  • в /usr/pgsql-11/share/extension — sql-файлы и control-файлы
  • в /usr/pgsql-11/lib — so-файлы

Просмотр списка расширений
В бд выполнить запрос:

select * from pg_available_extensions;
\dx

Читать далее

0

PostgreSQL узнать replication lag

Для того чтобы знать replication lag и на каких этапах он возникает, на мастере выполняем следующий скрипт:

--Начиная с PG10
select
client_addr as client, usename as user, application_name as name, state,
sync_state as mode,
(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::int as pending,
(pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::int as write,
(pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::int as flush,
(pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::int as replay,
(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) / 1024)::int / 1024 as
total_lag
from pg_stat_replication;

--До PG10
select
client_addr as client, usename as user, application_name as name, state,
sync_state as mode,
(pg_xlog_location_diff(pg_current_xlog_location(),sent_location) /
1024)::int as pending,
(pg_xlog_location_diff(sent_location,write_location) / 1024)::int as write,
(pg_xlog_location_diff(write_location,flush_location) / 1024)::int as
flush,
(pg_xlog_location_diff(flush_location,replay_location) / 1024)::int as
replay,
(pg_xlog_location_diff(pg_current_xlog_location(),replay_location) /
1024)::int / 1024 as total_lag
from pg_stat_replication;
0

PostgreSQL статистика по пользовательским таблицам (чтения/изменения, autovacuum и т.д)

--all info
select * from pg_stat_user_tables where schemaname = 'public';

--autovacuum counts
select relname, last_autovacuum, autoanalyze_count, autovacuum_count,
last_autoanalyze,
seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd,
n_tup_del, n_dead_tup, n_mod_since_analyze
from pg_stat_user_tables where schemaname = 'public' order by
autovacuum_count desc;

Документация

0

PostgreSQL сбросить коннекты к бд

Смотрим список подключений:

SELECT pid, usename, client_addr, backend_start, query FROM
pg_stat_activity WHERE datname = 'dbname';

Сбрасываем все подключения к бд (кроме того из под которого работаем сейчас, если мы подключены к базе dbname):

SELECT pg_terminate_backend( pid ) FROM pg_stat_activity WHERE pid <>
pg_backend_pid( ) AND datname = 'dbname';
0

PostgreSQL узнать число открытых/разрешенных подключений

select max_conn,used,res_for_super,max_conn-used-res_for_super as
res_for_normal
from
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where
name='superuser_reserved_connections') t2,
(select setting::int max_conn from pg_settings where
name='max_connections') t3;
0

PostgreSQL посмотреть текущие запросы к базам

До версии PG 9.6:

--all info
select * from pg_stat_activity;
--connections count
select datname, count(*) as connections from pg_stat_activity group by
datname;
select datname, count(*) as connections, state, waiting from
pg_stat_activity group by datname, state, waiting order by datname;
--connections not idle (active)
select datname, count(*) as connections, state, waiting from
pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting order by datname;
--connections not idle (active) with time of longes query
select datname, count(*) as connections, state, waiting,
max(age(clock_timestamp() as age, query_start)) from pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting order by datname;
--connections not idle (active) with time of longes query and query type
select datname, count(*) as connections, state, waiting,
max(age(clock_timestamp(), query_start)) as age, lower(left(query,
position(' ' in query) - 1)) as query_type from pg_stat_activity
where state <> 'idle' and query not like '%FROM pg_stat_activity%' group by
datname, state, waiting, lower(left(query, position(' ' in query) - 1))
order by datname;
--active with query text
select pid, datname, state, waiting, age(clock_timestamp(), query_start),
query from pg_stat_activity where state <> 'idle' and query not like '%FROM
pg_stat_activity%';

Начиная с версии PG 9.6:

Читать далее

0