![]() ![]() Has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFRENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')Įxample: edb=# select * from table_privs('test_user') ![]() (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 [ Function for table privileges: CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text) These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION.įollowing are functions which can use to get the privileges of a particular user:ġ. Therefore, I thought about making some functions, which can be used to list users privileges, based on what is available in PostgreSQL/PPAS 9.1. Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn’t have a view, which DBA can use to list users privileges on objects for a particular database. Those functions is available in following link: įunctions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false. PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Author Database Technologies & Security Posted on FebruFebruCategories Hot Standby, Monitor Hot Standby, Postgres Plus Advanced Server, PostgreSQL, PostgreSQL DBA SQLs Tags EnterpriseDB, pg_xlog_location_diff, Postgres Plus Advanced Server, PostgreSQL, PostgreSQL 9.0, PostgreSQL 9.1, PostgreSQL 9.2, postgresql admin functions, PostgreSQL Monitoring, PPAS 2 Comments on pg_xlog_location_diff function for PostgreSQL/PPAS List user privileges in PostgreSQL/PPAS 9.1 I hope this will help PostgreSQL/PPAS 9.0/9.1 users. Worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578') * Return the value in numeric by explicit casting */ So convert into decimal and then calculate the difference */ Since, hexadecimal calculation is cumbersome * Prepare SQL query for calculation based on following formula * Extract the Offset and xlog from input in Where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)īased on above formula, following is plpgsql function which can be use to get the difference:ĬREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text) ![]() To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal: So, whenerver user sees information for xlog location, he gets xlog information in following format: D1012B80 is hexadecimal offset inside the logical xlogfile. In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. Worktest=# select pg_current_xlog_location() Let’s consider user has used function pg_current_xlog_location() function and he gets following information: So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1.īefore using formula and developing function, lets understand what is xlog and offset. However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. Detail of this function is given in following link: In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. ![]()
0 Comments
Leave a Reply. |