![]() SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [ ![]() To get privilege of a particular with view name, following function can be used:ĬREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text) ![]() For schema_privilege in PostgreSQL, users can try the following function:įROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE') Ĩ. Note: Above function can be used in Advanced Server. (CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)įROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE') AND c.nspparent=0 ( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END), SELECT $1, c.nspname, array(select privs from unnest(ARRAY[ To find schema privileges of a user, the following can be used:ĬREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privileges text) SELECT $1, l.lanname, ARRAY FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE') ħ. To find language privileges, following function can be used:ĬREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text) (CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s WHERE has_server_privilege ($1,srvname,'USAGE') Ħ. To find foreign server privileges, the following functions can be used:ĬREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text) (CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') Įdb=# select * from fdw_wrapper_privs('test_user') ĥ. (CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE') Įdb=# select * from tablespace_privs('test_user') ĬREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text) Test_user | test_id | ĬREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text) (CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and c.relkind='r' andĮdb=# select * from table_privs('test_user') For table_privs in PostgreSQL, users can try the following function: Note: Above function can be used in Advanced Server. Has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE') (CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and nspparent=0 and c.relkind='r' and (CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END), (CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END), (CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END), (CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END), (CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END), ( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END), SELECT $1,c.oid::regclass, array(select privs from unnest(ARRAY [ Following are functions which can be used to designate the privileges of a particular user:ĬREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text) These are basic functions and can be expanded, as needed, to show more privileges like WITH GRANT OPTION. Therefore, I thought about making some functions, which can be used to list users' privileges, based on what is available in PostgreSQL/PPAS 11. Currently PostgreSQL doesn’t have a view that a DBA can use to list users' privileges on objects for a particular database. ![]() Download Now.ĭBAs/Users are interested in listing objects and privileges of a Database User. Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |