Re: view to get all role privileges
| От | Raghu Ram |
|---|---|
| Тема | Re: view to get all role privileges |
| Дата | |
| Msg-id | CALnrrJQk+bcnx-i1RixfmOdSB4Ddiyq6X9qjrhDv_J2GgCwJew@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: view to get all role privileges (Raghu Ram <raghuchennuru@gmail.com>) |
| Список | pgsql-novice |
On Thu, Nov 7, 2013 at 11:10 AM, Raghu Ram <raghuchennuru@gmail.com> wrote:
On Thu, Nov 7, 2013 at 10:40 AM, Huang, Suya <Suya.Huang@au.experian.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
В списке pgsql-novice по дате отправления: