Database Technologies

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link: http://www.postgresql.org/docs/9.1/static/functions-info.html Functions 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. 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. 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. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION. Following are functions which can use to get the privileges of a particular user: 1. Function for table privileges: C...

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information: 1. datid: database OID 2. datname: database name 3. procpid: process ID 4. usesysid: user OID 5. usename: user name 6. application_name: application name 7. client_addr: client's address 8. client_hostname: host name (if available) 9. client_port: Clients port number 10. backend_start: time at which the server process started 11. xact_start: time at which current transaction started 12: query_start: time at which current query began execution started 13: waiting: process's waiting status 14. current_query: text of the current query. Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function. To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the p...