Re: view to get all role privileges

On Thu, Nov 7, 2013 at 11:10 AM, Raghu Ram <raghuchennuru(at)gmail(dot)com> wrote:

> On Thu, Nov 7, 2013 at 10:40 AM, Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>wrote:
>
>> Hello Ram,
>>
>>
>>
>> The create schema privilege is missed from the dump file.
>>
>>
>>
>
You can get Schema privileges using below function:

postgres=# CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS
table(username text, schemaname name, privieleges text[])
postgres-# AS
postgres-# $$
postgres$# SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
postgres$# ( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN
'CREATE' ELSE NULL END),
postgres$# (CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE'
ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
postgres$# FROM pg_namespace c where
has_schema_privilege($1,c.oid,'CREATE,USAGE');
postgres$# $$ language sql;
CREATE FUNCTION

postgres=# select schema_privs('postgres');
schema_privs
------------------------------------------------
(postgres,pg_toast,"{CREATE,USAGE}")
(postgres,pg_temp_1,"{CREATE,USAGE}")
(postgres,pg_toast_temp_1,"{CREATE,USAGE}")
(postgres,pg_catalog,"{CREATE,USAGE}")
(postgres,information_schema,"{CREATE,USAGE}")
(postgres,public,"{CREATE,USAGE}")
(6 rows)

Thanks & Regards
Raghu Ram