{"id":11811,"date":"2018-10-16T14:18:19","date_gmt":"2018-10-16T12:18:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/"},"modified":"2018-10-16T14:18:19","modified_gmt":"2018-10-16T12:18:19","slug":"inheriting-super-user-privileges-over-a-role-automatically-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/","title":{"rendered":"Inheriting super user privileges over a role automatically in PostgreSQL"},"content":{"rendered":"

In a recent project at a customer where we synchronize the users and group out of Active Directory<\/a> we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you login using the other role? Will you have the super user privileges by default? Sounds confusing, I know, so lets do a test.<\/p>\n

<\/p>\n

To start with we create a simple role and make that role a super user:<\/p>\n

\npostgres=# create role my_admin;\nCREATE ROLE\npostgres=# alter role my_admin superuser;\nALTER ROLE\n<\/pre>\n
Of course you could also do that in one step:<\/p>\n
\npostgres=# create role my_admin superuser;\nCREATE ROLE\n<\/pre>\n
As a second step lets create a new user that is a member of the admin group and inherits the permissions of that role automatically:<\/p>\n
\npostgres=# create user my_dba login password 'admin' in role my_admin inherit;\nCREATE ROLE\npostgres=# du\n                                    List of roles\n Role name |                         Attributes                         | Member of  \n-----------+------------------------------------------------------------+------------\n my_admin  | Superuser, Cannot login                                    | {}\n my_dba    |                                                            | {my_admin}\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n<\/pre>\n
The questions now is: When we login using the my_dba user are we superuser automatically?<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PGDEV] psql -X -U my_dba postgres\npsql (12devel)\nType \"help\" for help.\n\npostgres=> du\n                                    List of roles\n Role name |                         Attributes                         | Member of  \n-----------+------------------------------------------------------------+------------\n my_admin  | Superuser, Cannot login                                    | {}\n my_dba    |                                                            | {my_admin}\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n\npostgres=> create database db1;\nERROR:  permission denied to create database\npostgres=> \n<\/pre>\n
… and we are not. What we can do is:<\/p>\n
\npostgres=> set role my_admin;\nSET\npostgres=# create database db1;\nCREATE DATABASE\n<\/pre>\n
The reason for that is that some privileges<\/a> are not inherited automatically and these are:  LOGIN, SUPERUSER, CREATEDB, and CREATEROLE.<\/p>\n
What you can do is put something like that into “.psqlrc”<\/a>:<\/p>\n
\nset role my_admin\n<\/pre>\n
… or do it like that:<\/p>\n
\npostgres=# alter user my_dba set role my_admin;\nALTER ROLE\n<\/pre>\n
This will explicitly set the role with each login and the super user privileges will be there. When you have a bit more complicated scenario where roles are assigned based on patterns in the username you could do something like this and add it to .psqlrc as well (or put that into a file and then execute that file in .psqlrc):<\/p>\n
\nDO $$\nDECLARE\n  lv_username pg_roles.rolname%TYPE := current_user;\nBEGIN\n  if ( substr(lv_username,1,2) = 'xx'\n       and\n       position ('yy' in lv_username) > 0\n     )\n  then\n    execute 'set role my_admin';\n  end if;\n  perform 1;\nEND $$;\n<\/pre>\n
… or whatever checks you need to identify the correct user names. Hope that helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
In a recent project at a customer where we synchronize the users and group out of Active Directory we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11811","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nInheriting super user privileges over a role automatically in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Inheriting super user privileges over a role automatically in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"In a recent project at a customer where we synchronize the users and group out of Active Directory we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-16T12:18:19+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Inheriting super user privileges over a role automatically in PostgreSQL\",\"datePublished\":\"2018-10-16T12:18:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/\"},\"wordCount\":274,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/\",\"name\":\"Inheriting super user privileges over a role automatically in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-10-16T12:18:19+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inheriting super user privileges over a role automatically in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Inheriting super user privileges over a role automatically in PostgreSQL - dbi Blog","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:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Inheriting super user privileges over a role automatically in PostgreSQL","og_description":"In a recent project at a customer where we synchronize the users and group out of Active Directory we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you […]","og_url":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2018-10-16T12:18:19+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Inheriting super user privileges over a role automatically in PostgreSQL","datePublished":"2018-10-16T12:18:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/"},"wordCount":274,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/","name":"Inheriting super user privileges over a role automatically in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-10-16T12:18:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/inheriting-super-user-privileges-over-a-role-automatically-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Inheriting super user privileges over a role automatically in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11811"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11811\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11811"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}