|
Exploring relationship between Greenplum Instance, database, schema, group, users and schema objects
Create Table DDL function
Note: distributed by and partitions are not implemented. Next version will include distributed by, partitions and index if any on that table. Usage: select crt_table_ddl('table_name'); Example: sachi=# select crt_table_ddl('bloattest'); crt_table_ddl ------------------------------------------ CREATE TABLE public.bloattest ( id integer NULL, int_1 integer NULL, int_2 integer NULL, int_3 integer NULL, ts_1 timestamp with time zone NULL, ts_2 timestamp with time zone NULL, ts_3 timestamp with time zone NULL, text_1 text NULL, text_2 text NULL, text_3 text NULL); (1 row) sachi=# CREATE OR REPLACE FUNCTION crt_table_ddl(p_table_name varchar) RETURNS text AS $BODY$ DECLARE v_table_ddl text; column_record record; BEGIN FOR column_record IN SELECT b.nspname as schema_name, b.relname as table_name, a.attname as column_name, pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type, CASE WHEN (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN 'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) ELSE '' END as column_default_value, CASE WHEN a.attnotnull = true THEN 'NOT NULL' ELSE 'NULL' END as column_not_null, a.attnum as attnum, e.max_attnum as max_attnum FROM pg_catalog.pg_attribute a INNER JOIN (SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ ('^('||p_table_name||')$') AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3) b ON a.attrelid = b.oid INNER JOIN (SELECT a.attrelid, max(a.attnum) as max_attnum FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped GROUP BY a.attrelid) e ON a.attrelid=e.attrelid WHERE a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum LOOP IF column_record.attnum = 1 THEN v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ('; ELSE v_table_ddl:=v_table_ddl||','; END IF; IF column_record.attnum <= column_record.max_attnum THEN v_table_ddl:=v_table_ddl||chr(10)|| ' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null; END IF; END LOOP; v_table_ddl:=v_table_ddl||');'; RETURN v_table_ddl; END; $BODY$ LANGUAGE 'plpgsql' SECURITY INVOKER; |
Finding relationship between username, schemaname, owner, objectname and acl on that object
SELECT use.usename as username, nsp.nspname as schemaname, c.relname as objectname, c.relkind as type, use2.usename as owner, c.relacl FROM pg_user use cross join pg_class c left join pg_namespace nsp on (c.relnamespace = nsp.oid) left join pg_user use2 on (c.relowner = use2.usesysid) WHERE c.relowner = use.usesysid and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ORDER BY 1,2,3 |
Find if a user belongs to any groups
sachi=# select usesysid, usename, COALESCE(groname,'default') from pg_user u left join pg_group g on ','||array_to_string(grolist,',')||',' like '%,'||cast(usesysid as varchar(10))||',%' where usename='sachi' order by 2,1; usesysid | usename | coalesce ----------+---------+---------- 16994 | sachi | admin (1 row) sachi=# |
1-4 of 4
