{"id":411,"date":"2021-12-07T12:25:31","date_gmt":"2021-12-07T05:25:31","guid":{"rendered":"https:\/\/dani.work\/blog\/?p=411"},"modified":"2023-02-25T06:21:54","modified_gmt":"2023-02-24T23:21:54","slug":"how-to-create-user-and-give-access-to-database-in-postgresql","status":"publish","type":"post","link":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/","title":{"rendered":"How to create user and give access to database in postgresql"},"content":{"rendered":"\n<p>PostgreSQL is a powerful and popular open-source relational database management system that provides numerous features and options for managing databases. If you&#8217;re new to PostgreSQL, one of the first things you&#8217;ll need to do is create a user and give them access to your database. In this article, we&#8217;ll go over the steps for creating a user and granting them access to a PostgreSQL database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Connect to PostgreSQL<\/h2>\n\n\n\n<p>Before you can create a user and grant them access to a database, you need to connect to your PostgreSQL server. You can do this using the psql command-line tool. Open a terminal or command prompt and enter the following command<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">$ sudo -u postgres \n$ psql -U postgres<\/code><\/pre>\n\n\n\n<p>This command will connect you to the default PostgreSQL database with the superuser account named &#8220;postgres.&#8221;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Create a user<\/h2>\n\n\n\n<p>To create a new user, use the CREATE USER command followed by the username and password. Here is an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';<\/code><\/pre>\n\n\n\n<p>Replace <code>\"myuser\"<\/code> and <code>\"mypassword\"<\/code> with the username and password you want to use for your new user. The semicolon at the end of the line indicates the end of the command.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Create a database<\/h2>\n\n\n\n<p>If you haven&#8217;t already created a database, you can use the CREATE DATABASE command to create one. Here is an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE DATABASE mydatabase;<\/code><\/pre>\n\n\n\n<p>Replace <code>\"mydatabase\"<\/code> with the name you want to use for your new database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Grant access to the database<\/h2>\n\n\n\n<p>To give your new user access to the database, you need to grant them permission. Use the <code>GRANT<\/code> command followed by the privileges you want to give and the name of the user and database. Here is an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;<\/code><\/pre>\n\n\n\n<p>This command grants all privileges to the user <code>\"myuser\"<\/code> on the database <code>\"mydatabase.\"<\/code> You can replace <code>\"ALL PRIVILEGES\"<\/code> with specific privileges such as <code>SELECT, INSERT, UPDATE, DELETE<\/code>, etc. depending on your requirements.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Verify access<\/h2>\n\n\n\n<p>To verify that your user has access to the database, you can try connecting to the database using the new user&#8217;s credentials. You can do this using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">$ psql -U myuser -d mydatabase<\/code><\/pre>\n\n\n\n<p>This command will connect you to the <code>\"mydatabase\"<\/code> database using the <code>\"myuser\"<\/code> user account.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>Creating a user and giving them access to a PostgreSQL database is a simple process that requires just a few commands. With the steps outlined in this article, you should be able to create a new user, create a new database, and grant your new user access to the database. By doing so, you can better manage your PostgreSQL databases and ensure that your users have the necessary permissions to perform their tasks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Bonus<\/h2>\n\n\n\n<p>if you have an error like <code>Insufficient privilege: 7 ERROR: permission denied for schema public<\/code> or something like that, then you need to do this :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">$ sudo -u postgres psql\n\npostgres=&gt; \\c mydb postgres\nmydb=# GRANT ALL ON SCHEMA public TO myuser;\nGRANT<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Create Superuser in Postgresql from command line<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">$ sudo -u postgres createuser -s -i -d -r -l -w &lt;&lt;username&gt;&gt;\n$ sudo -u postgres psql -c \"ALTER ROLE &lt;&lt;username&gt;&gt; WITH PASSWORD '&lt;&lt;password&gt;&gt;';\"<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p> In this article, we&#8217;ll go over the steps for creating a user and granting them access to a PostgreSQL database<\/p>\n","protected":false},"author":1,"featured_media":994,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[26,58],"tags":[40,41],"class_list":["post-411","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-sql","category-postgresql","tag-database","tag-postgresql"],"blocksy_meta":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to create user and give access to database in postgresql - Inside of Code<\/title>\n<meta name=\"description\" content=\"In this article, we&#039;ll go over the steps for creating a user and granting them access to a PostgreSQL database\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to create user and give access to database in postgresql - Inside of Code\" \/>\n<meta property=\"og:description\" content=\"In this article, we&#039;ll go over the steps for creating a user and granting them access to a PostgreSQL database\" \/>\n<meta property=\"og:url\" content=\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Inside of Code\" \/>\n<meta property=\"article:published_time\" content=\"2021-12-07T05:25:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-24T23:21:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"300\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Dani\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dani\" \/>\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:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\"},\"author\":{\"name\":\"Dani\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/1c6cafc6659067aa7ac1fd1a5ced218d\"},\"headline\":\"How to create user and give access to database in postgresql\",\"datePublished\":\"2021-12-07T05:25:31+00:00\",\"dateModified\":\"2023-02-24T23:21:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\"},\"wordCount\":443,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp\",\"keywords\":[\"database\",\"postgresql\"],\"articleSection\":[\"Database (SQL)\",\"Postgresql\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\",\"url\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\",\"name\":\"How to create user and give access to database in postgresql - Inside of Code\",\"isPartOf\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp\",\"datePublished\":\"2021-12-07T05:25:31+00:00\",\"dateModified\":\"2023-02-24T23:21:54+00:00\",\"description\":\"In this article, we'll go over the steps for creating a user and granting them access to a PostgreSQL database\",\"breadcrumb\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp\",\"contentUrl\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp\",\"width\":500,\"height\":300,\"caption\":\"How to create user and give access to database in postgresql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/insideofcode.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to create user and give access to database in postgresql\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#website\",\"url\":\"https:\/\/insideofcode.com\/blog\/\",\"name\":\"Inside of Code\",\"description\":\"A software engineer who wants to share what he has learned, and document his journey.\",\"publisher\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/insideofcode.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#organization\",\"name\":\"Inside of Code\",\"url\":\"https:\/\/insideofcode.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2022\/10\/logo-ioc.png\",\"contentUrl\":\"https:\/\/insideofcode.com\/wp-content\/uploads\/2022\/10\/logo-ioc.png\",\"width\":324,\"height\":324,\"caption\":\"Inside of Code\"},\"image\":{\"@id\":\"https:\/\/insideofcode.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/1c6cafc6659067aa7ac1fd1a5ced218d\",\"name\":\"Dani\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b1e4251720747aef0418e09fe0f6e7786ff89358b57f76822f1c52c286552b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b1e4251720747aef0418e09fe0f6e7786ff89358b57f76822f1c52c286552b?s=96&d=mm&r=g\",\"caption\":\"Dani\"},\"sameAs\":[\"https:\/\/insideofcode.com\/blog\"],\"url\":\"https:\/\/insideofcode.com\/blog\/author\/daniwork\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to create user and give access to database in postgresql - Inside of Code","description":"In this article, we'll go over the steps for creating a user and granting them access to a PostgreSQL database","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:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How to create user and give access to database in postgresql - Inside of Code","og_description":"In this article, we'll go over the steps for creating a user and granting them access to a PostgreSQL database","og_url":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/","og_site_name":"Inside of Code","article_published_time":"2021-12-07T05:25:31+00:00","article_modified_time":"2023-02-24T23:21:54+00:00","og_image":[{"width":500,"height":300,"url":"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp","type":"image\/webp"}],"author":"Dani","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Dani","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/"},"author":{"name":"Dani","@id":"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/1c6cafc6659067aa7ac1fd1a5ced218d"},"headline":"How to create user and give access to database in postgresql","datePublished":"2021-12-07T05:25:31+00:00","dateModified":"2023-02-24T23:21:54+00:00","mainEntityOfPage":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/"},"wordCount":443,"commentCount":0,"publisher":{"@id":"https:\/\/insideofcode.com\/blog\/#organization"},"image":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp","keywords":["database","postgresql"],"articleSection":["Database (SQL)","Postgresql"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/","url":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/","name":"How to create user and give access to database in postgresql - Inside of Code","isPartOf":{"@id":"https:\/\/insideofcode.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp","datePublished":"2021-12-07T05:25:31+00:00","dateModified":"2023-02-24T23:21:54+00:00","description":"In this article, we'll go over the steps for creating a user and granting them access to a PostgreSQL database","breadcrumb":{"@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#primaryimage","url":"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp","contentUrl":"https:\/\/insideofcode.com\/wp-content\/uploads\/2021\/12\/postgresql.webp","width":500,"height":300,"caption":"How to create user and give access to database in postgresql"},{"@type":"BreadcrumbList","@id":"https:\/\/insideofcode.com\/blog\/how-to-create-user-and-give-access-to-database-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/insideofcode.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to create user and give access to database in postgresql"}]},{"@type":"WebSite","@id":"https:\/\/insideofcode.com\/blog\/#website","url":"https:\/\/insideofcode.com\/blog\/","name":"Inside of Code","description":"A software engineer who wants to share what he has learned, and document his journey.","publisher":{"@id":"https:\/\/insideofcode.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/insideofcode.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/insideofcode.com\/blog\/#organization","name":"Inside of Code","url":"https:\/\/insideofcode.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/insideofcode.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/insideofcode.com\/wp-content\/uploads\/2022\/10\/logo-ioc.png","contentUrl":"https:\/\/insideofcode.com\/wp-content\/uploads\/2022\/10\/logo-ioc.png","width":324,"height":324,"caption":"Inside of Code"},"image":{"@id":"https:\/\/insideofcode.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/1c6cafc6659067aa7ac1fd1a5ced218d","name":"Dani","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/insideofcode.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b1e4251720747aef0418e09fe0f6e7786ff89358b57f76822f1c52c286552b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b1e4251720747aef0418e09fe0f6e7786ff89358b57f76822f1c52c286552b?s=96&d=mm&r=g","caption":"Dani"},"sameAs":["https:\/\/insideofcode.com\/blog"],"url":"https:\/\/insideofcode.com\/blog\/author\/daniwork\/"}]}},"_links":{"self":[{"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/posts\/411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/comments?post=411"}],"version-history":[{"count":0,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/posts\/411\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/media\/994"}],"wp:attachment":[{"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/media?parent=411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/categories?post=411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/insideofcode.com\/blog\/wp-json\/wp\/v2\/tags?post=411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}