How to create a new user in Postgres

To add a new user (often referred to as a “role”) and set a password for that user in PostgreSQL, follow these steps:

1. Access PostgreSQL: First, you’ll need to access the PostgreSQL shell. If you’re on the same machine where PostgreSQL is installed, you can use the psql command.

psql -U postgres

This command assumes you’re connecting with the superuser postgres. If you’re using a different user to connect, replace postgres with that username.

2. Create the User: To create a new user, use the CREATE ROLE SQL command. For example, to create a user named newuser, you’d enter:

CREATE ROLE newuser WITH LOGIN;

The WITH LOGIN option allows the role to be used for logging in (like a user). Without it, the role would be more like a group.

3. Set the Password: Next, set a password for the new user:

ALTER ROLE newuser WITH ENCRYPTED PASSWORD 'your_password_here';

Replace your_password_here with the desired password for the user.

4. Grant Privileges: If you want the user to have the ability to create databases, you can grant them that privilege:

ALTER ROLE newuser CREATEDB;

If you want the user to be a superuser (which grants all privileges), use:

ALTER ROLE newuser SUPERUSER;

Be cautious with the SUPERUSER privilege, as it gives the role the ability to do anything within PostgreSQL, including bypassing all permission checks.

5. Exit PostgreSQL Shell: Finally, exit the PostgreSQL shell by typing:

\q

6. Test the New User: You can now test the new user by trying to connect with psql:

psql -U newuser -W

This will prompt you for the password of newuser.

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.