{"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":28,"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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}