{"id":40,"date":"2023-09-26T15:41:44","date_gmt":"2023-09-26T14:41:44","guid":{"rendered":"https:\/\/thevadasan.com\/?p=40"},"modified":"2024-04-29T04:45:33","modified_gmt":"2024-04-29T03:45:33","slug":"how-to-create-a-new-user-in-postgres","status":"publish","type":"post","link":"https:\/\/thevadasan.com\/?p=40","title":{"rendered":"How to create a new user in Postgres"},"content":{"rendered":"\n<p>To add a new user (often referred to as a &#8220;role&#8221;) and set a password for that user in PostgreSQL, follow these steps:<\/p>\n\n\n\n<ol>\n<li><strong>Access PostgreSQL<\/strong>: First, you&#8217;ll need to access the PostgreSQL shell. If you&#8217;re on the same machine where PostgreSQL is installed, you can use the psql command.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">psql -U postgres<\/code><\/pre>\n\n\n\n<p>This command assumes you&#8217;re connecting with the superuser <code>postgres<\/code>. If you&#8217;re using a different user to connect, replace <code>postgres<\/code> with that username.<\/p>\n\n\n\n<ol start=\"2\">\n<li><strong>Create the User<\/strong>: To create a new user, use the <code>CREATE ROLE<\/code> SQL command. For example, to create a user named <code>newuser<\/code>, you&#8217;d enter:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">CREATE ROLE newuser WITH LOGIN;<\/code><\/pre>\n\n\n\n<p>The <code>WITH LOGIN<\/code> option allows the role to be used for logging in (like a user). Without it, the role would be more like a group.<\/p>\n\n\n\n<ol start=\"3\">\n<li><strong>Set the Password<\/strong>: Next, set a password for the new user:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">ALTER ROLE newuser WITH ENCRYPTED PASSWORD 'your_password_here';<\/code><\/pre>\n\n\n\n<p>Replace <code>your_password_here<\/code> with the desired password for the user.<\/p>\n\n\n\n<ol start=\"4\">\n<li><strong>Grant Privileges<\/strong>: If you want the user to have the ability to create databases, you can grant them that privilege:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">ALTER ROLE newuser CREATEDB;<\/code><\/pre>\n\n\n\n<p>If you want the user to be a superuser (which grants all privileges), use:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">ALTER ROLE newuser SUPERUSER;<\/code><\/pre>\n\n\n\n<p>Be cautious with the <code>SUPERUSER<\/code> privilege, as it gives the role the ability to do anything within PostgreSQL, including bypassing all permission checks.<\/p>\n\n\n\n<ol start=\"5\">\n<li><strong>Exit PostgreSQL Shell<\/strong>: Finally, exit the PostgreSQL shell by typing:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">\\q<\/code><\/pre>\n\n\n\n<ol start=\"6\">\n<li><strong>Test the New User<\/strong>: You can now test the new user by trying to connect with <code>psql<\/code>:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code class=\"\">psql -U newuser -W<\/code><\/pre>\n\n\n\n<p>This will prompt you for the password of <code>newuser<\/code>.<\/p>\n\n\n\n<p>Remember to follow best practices and avoid using superuser privileges unless absolutely necessary. Always consider the principle of least privilege when assigning roles and permissions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>To add a new user (often referred to as a &#8220;role&#8221;) and set a password for that user in PostgreSQL, follow these steps: This command assumes you&#8217;re connecting with the superuser postgres. If you&#8217;re using a different user to connect, replace postgres with that username. The WITH LOGIN option allows the role to be used [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":75,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[20,18],"tags":[],"_links":{"self":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/40"}],"collection":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=40"}],"version-history":[{"count":2,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/40\/revisions"}],"predecessor-version":[{"id":43,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/40\/revisions\/43"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/media\/75"}],"wp:attachment":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=40"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=40"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=40"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}