List schema and user grants

Post date: Nov 01, 2014 2:59:37 PM

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)

-- query to see if user have any schema grants

sachi=#

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