{"id":275,"date":"2023-10-03T21:45:32","date_gmt":"2023-10-03T21:45:32","guid":{"rendered":"http:\/\/77interactive.com\/?p=275"},"modified":"2023-10-03T21:47:58","modified_gmt":"2023-10-03T21:47:58","slug":"postgresql-permissions","status":"publish","type":"post","link":"http:\/\/77interactive.com\/?p=275","title":{"rendered":"PostgreSQL Permissions"},"content":{"rendered":"\n<p>The following scripts create some tables that store PostgreSQL permissions. The permissions come from the system tables pg_roles, pg_auth_members, and others. The scripts assume there is a database named DBA, where this information is stored. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Permissions Tables<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Connect to the DBA database\n\\c DBA\n\n-- Drop the old tables if exist\nDROP TABLE IF EXISTS object_permissions;\nDROP TABLE IF EXISTS role_permissions;\n\n-- Create table for object permissions\nCREATE TABLE object_permissions (\n    oid serial PRIMARY KEY,\n    servername text,\n    databasename text,\n    schemaname text,\n    objectname text,\n    objecttype text,\n    privilege_type text,\n    grantor text,\n    grantee text,\n    is_grantable text\n);\n\n-- Create table for role permissions\nCREATE TABLE role_permissions (\n    oid serial PRIMARY KEY,\n    servername text,\n    databasename text, -- Roles are server-wide but it might be helpful to know where the query was executed\n    role_name text,\n    member_name text,\n    grantor text,\n    admin_option boolean\n);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Permissions Queries<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Object Permissions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Connect to the target database\n\\c target_database_name\n\nINSERT INTO DBA.object_permissions (servername, databasename, schemaname, objectname, objecttype, privilege_type, grantor, grantee, is_grantable)\nSELECT \n    'ServerName' AS servername, -- Replace with the actual server name or hostname function based on your environment\n    current_database() AS databasename,\n    n.nspname AS schemaname,\n    c.relname AS objectname,\n    CASE c.relkind\n        WHEN 'r' THEN 'TABLE'\n        WHEN 'v' THEN 'VIEW'\n        WHEN 'm' THEN 'MATERIALIZED VIEW'\n        WHEN 'S' THEN 'SEQUENCE'\n        WHEN 'f' THEN 'FUNCTION'\n        WHEN 'p' THEN 'PROCEDURE'\n        WHEN 'E' THEN 'FOREIGN TABLE'\n        ELSE 'UNKNOWN'\n    END AS objecttype,\n    a.privilege_type,\n    a.grantor::regrole::text,\n    a.grantee::regrole::text,\n    a.is_grantable\nFROM \n    pg_class c \nJOIN pg_namespace n ON n.oid = c.relnamespace\nLEFT JOIN (\n    SELECT \n        table_schema, table_name, privilege_type, grantor, grantee, is_grantable \n    FROM information_schema.table_privileges\n    UNION ALL\n    SELECT \n        routine_schema, routine_name, privilege_type, grantor, grantee, is_grantable\n    FROM information_schema.routine_privileges\n) AS a ON a.table_schema = n.nspname AND a.table_name = c.relname\nWHERE n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema';\n\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Role Permissions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Roles are server-wide, so you don't need to switch databases for this\nINSERT INTO DBA.role_permissions (servername, databasename, role_name, member_name, grantor, admin_option)\nSELECT \n    current_setting('my.server.name') AS servername, -- Replace with the actual server name or hostname function based on your environment\n    current_database() AS databasename,\n    r.rolname AS role_name,\n    u.rolname AS member_name,\n    g.rolname AS grantor,\n    m.admin_option\nFROM \n    pg_auth_members m\nJOIN pg_roles r ON r.oid = m.roleid\nJOIN pg_roles u ON u.oid = m.member\nJOIN pg_roles g ON g.oid = m.grantor;\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The following scripts create some tables that store PostgreSQL permissions. The permissions come from the system tables pg_roles, pg_auth_members, and others. The scripts assume there is a database named DBA, where this information is stored. Permissions Tables Permissions Queries Object Permissions Role Permissions<\/p>\n","protected":false},"author":1,"featured_media":277,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[17,18],"class_list":["post-275","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgres","tag-postgres","tag-postgresql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL Permissions - 77 Interactive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/77interactive.com\/?p=275\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL Permissions - 77 Interactive\" \/>\n<meta property=\"og:description\" content=\"The following scripts create some tables that store PostgreSQL permissions. The permissions come from the system tables pg_roles, pg_auth_members, and others. The scripts assume there is a database named DBA, where this information is stored. Permissions Tables Permissions Queries Object Permissions Role Permissions\" \/>\n<meta property=\"og:url\" content=\"http:\/\/77interactive.com\/?p=275\" \/>\n<meta property=\"og:site_name\" content=\"77 Interactive\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-03T21:45:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-03T21:47:58+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Rudy\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Rudy\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/77interactive.com\/?p=275#article\",\"isPartOf\":{\"@id\":\"http:\/\/77interactive.com\/?p=275\"},\"author\":{\"name\":\"Rudy\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\"},\"headline\":\"PostgreSQL Permissions\",\"datePublished\":\"2023-10-03T21:45:32+00:00\",\"dateModified\":\"2023-10-03T21:47:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/77interactive.com\/?p=275\"},\"wordCount\":48,\"image\":{\"@id\":\"http:\/\/77interactive.com\/?p=275#primaryimage\"},\"thumbnailUrl\":\"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png\",\"keywords\":[\"postgres\",\"postgresql\"],\"articleSection\":[\"postgres\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/77interactive.com\/?p=275\",\"url\":\"http:\/\/77interactive.com\/?p=275\",\"name\":\"PostgreSQL Permissions - 77 Interactive\",\"isPartOf\":{\"@id\":\"http:\/\/77interactive.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/77interactive.com\/?p=275#primaryimage\"},\"image\":{\"@id\":\"http:\/\/77interactive.com\/?p=275#primaryimage\"},\"thumbnailUrl\":\"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png\",\"datePublished\":\"2023-10-03T21:45:32+00:00\",\"dateModified\":\"2023-10-03T21:47:58+00:00\",\"author\":{\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\"},\"breadcrumb\":{\"@id\":\"http:\/\/77interactive.com\/?p=275#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/77interactive.com\/?p=275\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/77interactive.com\/?p=275#primaryimage\",\"url\":\"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png\",\"contentUrl\":\"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png\",\"width\":512,\"height\":512},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/77interactive.com\/?p=275#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/77interactive.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Permissions\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/77interactive.com\/#website\",\"url\":\"http:\/\/77interactive.com\/\",\"name\":\"77 Interactive\",\"description\":\"Rudy&#039;s Code snippets\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/77interactive.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9\",\"name\":\"Rudy\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/77interactive.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g\",\"caption\":\"Rudy\"},\"url\":\"http:\/\/77interactive.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL Permissions - 77 Interactive","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":"http:\/\/77interactive.com\/?p=275","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL Permissions - 77 Interactive","og_description":"The following scripts create some tables that store PostgreSQL permissions. The permissions come from the system tables pg_roles, pg_auth_members, and others. The scripts assume there is a database named DBA, where this information is stored. Permissions Tables Permissions Queries Object Permissions Role Permissions","og_url":"http:\/\/77interactive.com\/?p=275","og_site_name":"77 Interactive","article_published_time":"2023-10-03T21:45:32+00:00","article_modified_time":"2023-10-03T21:47:58+00:00","og_image":[{"width":512,"height":512,"url":"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png","type":"image\/png"}],"author":"Rudy","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Rudy","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/77interactive.com\/?p=275#article","isPartOf":{"@id":"http:\/\/77interactive.com\/?p=275"},"author":{"name":"Rudy","@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9"},"headline":"PostgreSQL Permissions","datePublished":"2023-10-03T21:45:32+00:00","dateModified":"2023-10-03T21:47:58+00:00","mainEntityOfPage":{"@id":"http:\/\/77interactive.com\/?p=275"},"wordCount":48,"image":{"@id":"http:\/\/77interactive.com\/?p=275#primaryimage"},"thumbnailUrl":"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png","keywords":["postgres","postgresql"],"articleSection":["postgres"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"http:\/\/77interactive.com\/?p=275","url":"http:\/\/77interactive.com\/?p=275","name":"PostgreSQL Permissions - 77 Interactive","isPartOf":{"@id":"http:\/\/77interactive.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/77interactive.com\/?p=275#primaryimage"},"image":{"@id":"http:\/\/77interactive.com\/?p=275#primaryimage"},"thumbnailUrl":"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png","datePublished":"2023-10-03T21:45:32+00:00","dateModified":"2023-10-03T21:47:58+00:00","author":{"@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9"},"breadcrumb":{"@id":"http:\/\/77interactive.com\/?p=275#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/77interactive.com\/?p=275"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/77interactive.com\/?p=275#primaryimage","url":"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png","contentUrl":"http:\/\/77interactive.com\/wp-content\/uploads\/2023\/10\/elephant-keys.png","width":512,"height":512},{"@type":"BreadcrumbList","@id":"http:\/\/77interactive.com\/?p=275#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/77interactive.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Permissions"}]},{"@type":"WebSite","@id":"http:\/\/77interactive.com\/#website","url":"http:\/\/77interactive.com\/","name":"77 Interactive","description":"Rudy&#039;s Code snippets","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/77interactive.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/77interactive.com\/#\/schema\/person\/0e61d2a984b8304618026b207e6121e9","name":"Rudy","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/77interactive.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e336b9aecd39b40691ff8ccfcd68506415072dbe8caffc0485b94a1bc22b774d?s=96&d=mm&r=g","caption":"Rudy"},"url":"http:\/\/77interactive.com\/?author=1"}]}},"_links":{"self":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts\/275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=275"}],"version-history":[{"count":0,"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/posts\/275\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=\/wp\/v2\/media\/277"}],"wp:attachment":[{"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/77interactive.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}