{"id":12363,"date":"2019-04-25T09:07:29","date_gmt":"2019-04-25T07:07:29","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/"},"modified":"2019-04-25T09:07:29","modified_gmt":"2019-04-25T07:07:29","slug":"creating-postgresql-users-with-a-pl-pgsql-function","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/","title":{"rendered":"Creating PostgreSQL users with a PL\/pgSQL function"},"content":{"rendered":"

Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in PostgreSQL that either execute under the permission of the user who created the function or they run under the permissions of the user who executes the function. Lets see how that works.<\/p>\n

<\/p>\n

Here is a little PL\/pgSQL<\/a> function that creates a user with a given password, does some checks on the input parameters and tests if the user already exists:<\/p>\n

\ncreate or replace function f_create_user ( pv_username name\n                                         , pv_password text\n                                         ) returns boolean\nas $$\ndeclare\n  lb_return boolean := true;\n  ln_count integer;\nbegin\n  if ( pv_username is null )\n  then\n     raise warning 'Username must not be null';\n     lb_return := false;\n  end if;\n  if ( pv_password is null )\n  then\n     raise warning 'Password must not be null';\n     lb_return := false;\n  end if;\n  -- test if the user already exists\n  begin\n      select count(*)\n        into ln_count\n        from pg_user\n       where usename = pv_username;\n  exception\n      when no_data_found then\n          -- ok, no user with this name is defined\n          null;\n      when too_many_rows then\n          -- this should really never happen\n          raise exception 'You have a huge issue in your catalog';\n  end;\n  if ( ln_count > 0 )\n  then\n     raise warning 'The user \"%\" already exist', pv_username;\n     lb_return := false;\n  else\n      execute 'create user '||pv_username||' with password '||''''||'pv_password'||'''';\n  end if;\n  return lb_return;\nend;\n$$ language plpgsql;\n<\/pre>\n
Once that function is created:<\/p>\n
\npostgres=# df\n                                   List of functions\n Schema |     Name      | Result data type |        Argument data types         | Type \n--------+---------------+------------------+------------------------------------+------\n public | f_create_user | boolean          | pv_username name, pv_password text | func\n(1 row)\n<\/pre>\n
… users can be created by calling this function when connected as a user with permissions to do so:<\/p>\n
\npostgres=# select current_user;\n current_user \n--------------\n postgres\n(1 row)\n\npostgres=# select f_create_user('test','test');\n f_create_user \n---------------\n t\n(1 row)\n\npostgres=# du\n                                   List of roles\n Role name |                         Attributes                         | Member of \n-----------+------------------------------------------------------------+-----------\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n test      |                                                            | {}\n<\/pre>\n
Trying to execute this function with a user that does not have permissions to create other users will fail:<\/p>\n
\npostgres=# create user a with password 'a';\nCREATE ROLE\npostgres=# grant EXECUTE on function f_create_user(name,text) to a;\nGRANT\npostgres=# c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> select f_create_user('test2','test2');\nERROR:  permission denied to create role\nCONTEXT:  SQL statement \"create user test2 with password 'pv_password'\"\nPL\/pgSQL function f_create_user(name,text) line 35 at EXECUTE\n<\/pre>\n
You can make that work by saying that the function should run with the permissions of the user who created the function:<\/p>\n
\ncreate or replace function f_create_user ( pv_username name\n                                         , pv_password text\n                                         ) returns boolean\nas $$\ndeclare\n  lb_return boolean := true;\n  ln_count integer;\nbegin\n...\nend;\n$$ language plpgsql security definer;\n<\/pre>\n
From now on our user “a” is allowed to create other users:<\/p>\n
\npostgres=> select current_user;\n current_user \n--------------\n a\n(1 row)\n\npostgres=> select f_create_user('test2','test2');\n f_create_user \n---------------\n t\n(1 row)\n\npostgres=> du\n                                   List of roles\n Role name |                         Attributes                         | Member of \n-----------+------------------------------------------------------------+-----------\n a         |                                                            | {}\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n test      |                                                            | {}\n test2     |                                                            | {}\n<\/pre>\n
Before implementing something like this consider the “Writing SECURITY DEFINER Functions Safely” section in the documentation<\/a>, there are some points to consider such as this:<\/p>\n
\npostgres=# revoke all on function f_create_user(name,text) from public;\nREVOKE\n<\/pre>\n
… and correctly setting the search_path<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in […]<\/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-12363","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCreating PostgreSQL users with a PL\/pgSQL function - 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\/creating-postgresql-users-with-a-pl-pgsql-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating PostgreSQL users with a PL\/pgSQL function\" \/>\n<meta property=\"og:description\" content=\"Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-25T07:07:29+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=\"3 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\/creating-postgresql-users-with-a-pl-pgsql-function\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Creating PostgreSQL users with a PL\/pgSQL function\",\"datePublished\":\"2019-04-25T07:07:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\"},\"wordCount\":231,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\",\"name\":\"Creating PostgreSQL users with a PL\/pgSQL function - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-04-25T07:07:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating PostgreSQL users with a PL\/pgSQL function\"}]},{\"@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":"Creating PostgreSQL users with a PL\/pgSQL function - 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\/creating-postgresql-users-with-a-pl-pgsql-function\/","og_locale":"en_US","og_type":"article","og_title":"Creating PostgreSQL users with a PL\/pgSQL function","og_description":"Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in […]","og_url":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/","og_site_name":"dbi Blog","article_published_time":"2019-04-25T07:07:29+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Creating PostgreSQL users with a PL\/pgSQL function","datePublished":"2019-04-25T07:07:29+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/"},"wordCount":231,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/","url":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/","name":"Creating PostgreSQL users with a PL\/pgSQL function - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-04-25T07:07:29+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/creating-postgresql-users-with-a-pl-pgsql-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Creating PostgreSQL users with a PL\/pgSQL function"}]},{"@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\/12363","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=12363"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12363\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12363"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}