Exploring relationship between Greenplum Instance, database, schema, group, users and schema objects

  1. Greenplum database system -> Greenplum database ->Schema -> Schema Objects
  2. Group Role/User Role
  3. Resource Queues

Create Table DDL function

posted Nov 1, 2014, 8:30 AM by Sachchida Ojha   [ updated Nov 1, 2014, 8:35 AM ]

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

posted Nov 1, 2014, 8:06 AM by Sachchida Ojha

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

List schema and user grants

posted Nov 1, 2014, 7:59 AM by Sachchida Ojha   [ updated Nov 7, 2014, 6:29 AM ]

The gadget spec URL could not be found
sachi=# select * from pg_namespace;
nspname | nspowner | nspacl
--------------------+----------+----------------------------------
gp_toolkit | 10 | {gpadmin=UC/gpadmin,=U/gpadmin}
pg_toast | 10 |
pg_bitmapindex | 10 |
pg_aoseg | 10 |
pg_catalog | 10 | {gpadmin=UC/gpadmin,=U/gpadmin}
public | 10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
information_schema | 10 | {gpadmin=UC/gpadmin,=U/gpadmin}
123abc | 10 |
(8 rows)


The gadget spec URL could not be found

-- query to see if user have any schema grants

sachi=#

select * from pg_namespace where nspowner > 1 and array_to_string(nspacl,',') like '%sachi%';

The gadget spec URL could not be found

Find if a user belongs to any groups

posted Nov 1, 2014, 7:51 AM by Sachchida Ojha

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