【PostgreSQL】PostgreSQL脚本
2022-06-27 09:54 abce 阅读(252) 评论(0) 编辑 收藏 举报#查看用户权限1
select grantor,grantee,table_catalog,table_schema,table_name,privilege_type,is_grantable,with_hierarchy from information_schema.table_privileges WHERE grantee = 'abce';
#查看用户权限2
SELECT grantee ,table_catalog ,table_schema ,table_name ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges FROM information_schema.role_table_grants WHERE grantee != 'postgres' GROUP BY grantee, table_catalog, table_schema, table_name;
#查看用户有哪些数据库的访问权限
select pgu.usename as user_name, (select string_agg(pgd.datname, ',' order by pgd.datname) from pg_database pgd where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name from pg_user pgu order by pgu.usename;
这里的connect,可以替换成
SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY or TEMP, EXECUTE, USAGE
#查看用户对函数的使用权限
SELECT routine_catalog AS fct_db, routine_schema AS fct_schema, routine_name AS fct_name, privilege_type AS fct_priv, array_agg (grantee::text ORDER BY grantee::text) AS fct_roles FROM information_schema.routine_privileges WHERE routine_schema NOT IN ('information_schema','pg_catalog') GROUP BY routine_catalog, routine_schema, routine_name, privilege_type ORDER BY routine_catalog, routine_schema, routine_name, privilege_type;
#查看autovacuum进程的活动
SELECT p.pid, p.datname, p.QUERY, p.backend_type, a.phase, a.heap_blks_scanned / a.heap_blks_total :: FLOAT * 100 AS "% scanned", a.heap_blks_vacuumed / a.heap_blks_total :: FLOAT * 100 AS "% vacuumed", pg_size_pretty ( pg_table_size ( a.relid ) ) AS "table size", pg_size_pretty ( pg_indexes_size ( a.relid ) ) AS "indexes size", pg_get_userbyid ( c.relowner ) AS OWNER FROM pg_stat_activity p JOIN pg_stat_progress_vacuum a ON a.pid = p.pid JOIN pg_class c ON c.oid = a.relid WHERE p.QUERY LIKE 'autovacuum%';