{"id":760,"date":"2018-07-14T20:38:18","date_gmt":"2018-07-14T20:38:18","guid":{"rendered":"http:\/\/dbtut.com\/?p=760"},"modified":"2020-10-02T08:01:29","modified_gmt":"2020-10-02T08:01:29","slug":"how-to-list-postgresql-users-and-permission","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/","title":{"rendered":"How to List PostgreSQL Users and Permission"},"content":{"rendered":"

In this article I will share a few scripts in postgres databases that I think will help you manage your users.<\/p>\n

The easiest way to list users is to run the following command.<\/p>\n

But it will not give you detailed information.<\/p>\n

\\du<\/pre>\n
With the help of the following script you can see which databases the users have pemission to connect to.<\/p>\n
select pgu.usename as user_name,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (select string_agg(pgd.datname, ',' order by pgd.datname) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from pg_database pgd \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as database_name\nfrom pg_user pgu\norder by pgu.usename;<\/pre>\n
With the help of the following script, you can also list by database basis.<\/p>\n
select pgd.datname as database_name,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (select string_agg(pgu.usename, ',' order by pgu.usename) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from pg_user pgu \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where has_database_privilege(pgu.usename, pgd.datname, 'CONNECT')) as user_name\nfrom pg_database pgd\norder by pgd.datname;<\/pre>\n
In the above script, you can also query other permissions by typing one of the following permission types instead of connect.<\/p>\n
SELECT,\nINSERT,\nUPDATE,\nDELETE,\nTRUNCATE,\nREFERENCES,\nTRIGGER,\nCREATE,\nTEMPORARY or TEMP,\nEXECUTE,\nUSAGE<\/pre>\n
You can see the details of the user permissions in the database with the help of the following script.<\/p>\n\n
SELECT grantee\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,table_catalog\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,table_schema\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,table_name\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges\nFROM information_schema.role_table_grants\nWHERE grantee != 'postgres'\nGROUP BY grantee, table_catalog, table_schema, table_name;<\/pre>\n
And if you want, you can revoke all the privileges from a user with the command below.<\/p>\n
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name;<\/pre>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In this article I will share a few scripts in postgres databases that I think will help you manage your users. The easiest way to list users is to run the following command. But it will not give you detailed information. \\du With the help of the following script you can see which databases the …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[866,862,864,869,865,863,815,868,867],"class_list":["post-760","post","type-post","status-publish","format-standard","","category-postgres","tag-has_database_privilege","tag-information_schema","tag-list-privileges-on-postgres","tag-list-user-permissions-on-postgres","tag-list-users-and-privileges-on-postgres","tag-privileges-on-postgres","tag-revoke-all-privileges","tag-role_table_grants","tag-users-permission-on-postgres"],"aioseo_notices":[],"yoast_head":"\nHow to List PostgreSQL Users and Permission - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How to List PostgreSQL Users and Permission\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to List PostgreSQL Users and Permission - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How to List PostgreSQL Users and Permission\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-14T20:38:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-02T08:01:29+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How to List PostgreSQL Users and Permission\",\"datePublished\":\"2018-07-14T20:38:18+00:00\",\"dateModified\":\"2020-10-02T08:01:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\"},\"wordCount\":141,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"has_database_privilege\",\"information_schema\",\"list privileges on postgres\",\"list user permissions on postgres\",\"list users and privileges on postgres\",\"privileges on postgres\",\"REVOKE ALL PRIVILEGES\",\"role_table_grants\",\"users permission on postgres\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\",\"name\":\"How to List PostgreSQL Users and Permission - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-07-14T20:38:18+00:00\",\"dateModified\":\"2020-10-02T08:01:29+00:00\",\"description\":\"How to List PostgreSQL Users and Permission\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to List PostgreSQL Users and Permission\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to List PostgreSQL Users and Permission - Database Tutorials","description":"How to List PostgreSQL Users and Permission","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/","og_locale":"en_US","og_type":"article","og_title":"How to List PostgreSQL Users and Permission - Database Tutorials","og_description":"How to List PostgreSQL Users and Permission","og_url":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/","og_site_name":"Database Tutorials","article_published_time":"2018-07-14T20:38:18+00:00","article_modified_time":"2020-10-02T08:01:29+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How to List PostgreSQL Users and Permission","datePublished":"2018-07-14T20:38:18+00:00","dateModified":"2020-10-02T08:01:29+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/"},"wordCount":141,"commentCount":1,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["has_database_privilege","information_schema","list privileges on postgres","list user permissions on postgres","list users and privileges on postgres","privileges on postgres","REVOKE ALL PRIVILEGES","role_table_grants","users permission on postgres"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/","url":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/","name":"How to List PostgreSQL Users and Permission - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-07-14T20:38:18+00:00","dateModified":"2020-10-02T08:01:29+00:00","description":"How to List PostgreSQL Users and Permission","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/14\/how-to-list-postgresql-users-and-permission\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How to List PostgreSQL Users and Permission"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/760","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=760"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/760\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}