【PostgreSQL】PostgreSQL脚本

#查看用户权限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%';