{"id":53,"date":"2024-01-04T12:26:29","date_gmt":"2024-01-04T12:26:29","guid":{"rendered":"https:\/\/thevadasan.com\/?p=53"},"modified":"2024-01-03T13:01:31","modified_gmt":"2024-01-03T13:01:31","slug":"how-to-create-a-new-user-in-postgres-2","status":"publish","type":"post","link":"https:\/\/thevadasan.com\/?p=53","title":{"rendered":"How to create a new user in Postgres"},"content":{"rendered":"\n

Creating a user (also known as a role) in PostgreSQL and assigning a database to this user involves several steps. Here’s a comprehensive guide to achieve this:<\/p>\n\n\n\n

1. Accessing PostgreSQL<\/h3>\n\n\n\n

First, you need to access your PostgreSQL server. Typically, this is done through the terminal using the psql<\/code> command-line tool. If you’re running PostgreSQL on a local machine, access the PostgreSQL command line as the default postgres<\/code> user:<\/p>\n\n\n\n

sudo -u postgres psql\n<\/code><\/pre>\n\n\n\n
2. Creating a New User<\/h3>\n\n\n\n
In the psql<\/code> command-line interface, create a new user using the CREATE ROLE<\/code> command. For example, to create a user named newuser<\/code>, you would run:<\/p>\n\n\n\n
CREATE ROLE newuser WITH LOGIN PASSWORD 'password';\n<\/code><\/pre>\n\n\n\n
Replace 'password'<\/code> with a secure password for the new user.<\/p>\n\n\n\n
Note<\/strong>: If you want the user to be able to create databases, you should add the CREATEDB<\/code> option.<\/p>\n\n\n\n
3. Creating a Database<\/h3>\n\n\n\n
Next, create a new database that the user will have access to. For instance, to create a database named newdatabase<\/code>, use the CREATE DATABASE<\/code> command:<\/p>\n\n\n\n
CREATE DATABASE newdatabase;\n<\/code><\/pre>\n\n\n\n
4. Assigning the Database to the User<\/h3>\n\n\n\n
To give your new user access to the database, you need to alter the database’s privileges. First, connect to the newly created database:<\/p>\n\n\n\n
\\c newdatabase\n<\/code><\/pre>\n\n\n\n
Then assign the necessary privileges to the user. For example, to grant all privileges on all tables in newdatabase<\/code> to newuser<\/code>, use:<\/p>\n\n\n\n
GRANT ALL PRIVILEGES ON DATABASE newdatabase TO newuser;\n<\/code><\/pre>\n\n\n\n
5. Verify and Exit<\/h3>\n\n\n\n
After assigning the privileges, you can verify the current permissions by running:<\/p>\n\n\n\n
\\l\n<\/code><\/pre>\n\n\n\n
This command lists all databases and their associated access privileges. Look for your new database and ensure that the new user has the correct privileges.<\/p>\n\n\n\n
Finally, you can exit the PostgreSQL prompt by typing:<\/p>\n\n\n\n
\\q\n<\/code><\/pre>\n\n\n\n
Additional Tips<\/h3>\n\n\n\n
    \n
  • Managing Roles<\/strong>: PostgreSQL roles can be users or groups. The command CREATE ROLE<\/code> creates a role that can be used as either, depending on how you set it up.<\/li>\n\n\n\n
  • Security Best Practices<\/strong>: Always use strong, unique passwords for database roles.<\/li>\n\n\n\n
  • Access Control<\/strong>: If your PostgreSQL server is accessible over a network, ensure proper network-level security measures are in place to prevent unauthorized access.<\/li>\n\n\n\n
  • psql Utility<\/strong>: If you’re accessing PostgreSQL on a remote server, you might use psql<\/code> with additional connection parameters, like hostname, port, and username.<\/li>\n<\/ul>\n\n\n\n
    Creating users and databases in PostgreSQL is a fundamental task, and understanding these basics will help you effectively manage your PostgreSQL instance.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Creating a user (also known as a role) in PostgreSQL and assigning a database to this user involves several steps. Here’s a comprehensive guide to achieve this: 1. Accessing PostgreSQL First, you need to access your PostgreSQL server. Typically, this is done through the terminal using the psql command-line tool. If you’re running PostgreSQL on […]<\/p>\n","protected":false},"author":1,"featured_media":56,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[20,18],"tags":[21,22],"_links":{"self":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/53"}],"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=53"}],"version-history":[{"count":1,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/53\/revisions"}],"predecessor-version":[{"id":54,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/posts\/53\/revisions\/54"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=\/wp\/v2\/media\/56"}],"wp:attachment":[{"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thevadasan.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}