Postgresql create user with password

In this Postgresql tutorial, we will learn about “Postgresql create user with password”. And we are going to cover the following topics.

• Postgresql create user with password grant all privileges
• Postgresql create user with password superuser
• Postgresql create user with password read only
• Postgresql create user with password pgadmin
• Postgresql create user with password script
• Postgresql create user with password md5
• Postgresql create user with password login
• Postgresql create user with password and database
• Postgresql create user with password authentication failed

Before beginning we need to know in Postgresql, there is a command named createuser which helps in creating a new Postgresql user account.

Syntax:

createuser connection-option option username

Only superusers can create a new user in the Postgresql database or the user must have CREATEROLE privileges to add s, new user.

There are different kinds of options or command-line arguments accepted by the createuser command.

• username: It is used to specify the name of the PostgreSQL user to be created. The name of the user must be different from all existing roles in this PostgreSQL installation.
• -c number: It is used to set a maximum number of connections for the new user. by default is to set no limit.
• -d: It allows a new user to create databases.
• -D: It doesn’t allow a new user to create databases. This is the default one.
• -e: It is used to echo the commands that createuser generates and sends to the server.
• -E: It is an option that is obsolete but still accepted for backward compatibility.
• -g: It is used to indicates the role to which this role will be added immediately as a new member.
• -i: It allows the new role to automatically inherit privileges of roles it is a member of.
• -I: It doesn’t allow the new role to automatically inherit privileges of roles it is a member of.
• –interactive: It is used to prompt for the user name if none is specified on the command line, and also prompt for options -d/-D, -r/-R, -s/-S which is not specified on the command line.
• -l: It allows a new user to log in.
• -L: It doesn’t allow a new user to log in, but a user without login privilege is still able to manage the database permissions.
• -P: If specified, createuser is going to issue a prompt for the password of the new user.
• -r: It allows a new user to create new roles means this user will have CREATEROLE privilege.
• -R: It doesn’t allow a new user to create new roles. This is the default one.
• -s: It allows a new user to be a superuser.
• -S: It doesn’t allow a new user to be a superuser. This is the default one.

Postgresql create user with password grant all privileges

In Postgresql, we are going to create a new user with a password and this user will have all privileges.

• From the terminal, enter the below command to log in Postgres prompt, if it asks for a password, then provide the password.

sudo -i -u postgres

• Create a new user with a password.

createuser jhon -P

• Here -P prompt password for new user jhon.
• Create a new database using the createdb command.

createdb jhon_database

• Log into psql prompt using “psql“. Then grant all privileges of the database to the user.

GRANT ALL PRIVILEGES ON DATABASE jhon_database TO jhon;

• Check the newly created user using the below command.

\du -- lists all user

We have granted all privileges of the database to user jhon and we can also grant all privileges on schema, tables, etc.

Read: Postgresql create database

Postgresql create user with password superuser

In Postgresql, we can create a new user with a password as a superuser who bypasses all permission checks and can override all access restrictions within the database.

• To create a new user as a superuser in Postgresql using the below command.

createuser decker -s -P

• Here -s allows decker to be a superuser. Next, log in to psql prompt.

psql

• Next, view the newly created superuser decker.

\du -- to list all the user with roles attributes

From the above output, we can that the user decker has the role attribute of the superuser.

Read: How to set user password in PostgreSQL

Postgresql create user with password read only

In Postgresql, we can create a user with a password who can only read the database and can’t make any changes to any databases.

Use the below command.

sudo -i -u postgres -- To login into postgres prompt

createuser adam -P; -- To create a new user named adam

\du  -- To list all the user

From the above output, we have created a user named adam who hasn’t any permission or role attributes like to create database, role, owner, etc. It means the user has only read permission, not write.

Read: Postgresql ilike case insensitive

Postgresql create user with password pgadmin

In Postgresql, we are going to create a new user with a password using the pgAdmin application.

Follow the below instructions.

• Open the pgAdmin application on your computer.
• Go to Browser Section and expand icon > in front of Server, right-click on Login/Group Roles and select Create option then click on Login/Group Role…

• A Create-Login/Group Role dialog will appear, click on the General tab and give a new name to the user in the Name box of the General tab.

• Click on the Definition tab, enter the password and click on the Save button at the bottom right corner.

• Again in the Browser section, expand the icon > in front of Login/Group Roles.

In the above output, we can see the newly created user Lucifer under the Login/Group Roles section.

Read: Postgresql group_concat

Postgresql create user with password script

In Postgresql, we can use the script file to create a user with a password from the command-line or terminal.

Here we will use the alternate command to create a user that is given below.

CREATE USER user_name WITH PASSWORD 'new_password';

Create a new script named user.sql and save it somewhere on your computer.

For Linux:

sudo nano user.sql

Type the below command in the user.sql file.

CREATE USER sql_user WITH PASSWORD '12345'; -- To create a new user

Run the below command from your Linux terminal to create a user.

sudo su - postgres -- To login into postgres prompt

-- then run the below command in postgres prompt

psql -f /home/user_directory/user.sql -- To create a user

For Windows:

Create script user.sql file in Notepad and save it.

CREATE USER sql_user WITH PASSWORD '12345';

Open CMD and type the below command to create a user password.

psql -U postgres -f C:\Users\user_directory\Documents\user.sql

From the above output, if we see the message ‘CREATE ROLE’ which is indicated using pink color in both images, we have successfully created a new user named sql_user.

Read: How to migrate from MySQL to Postgres

Postgresql create user with password login

In Postgresql, we can create a new user with a password and allow the user to log in.

• Use the below command to allow the user to log in.

createuser dan -s -l -P

• Here -s represent superuser, -P for the password, and -l allows the new user to log in.

• Enter the psql prompt.

psql

• View the newly created user with login,

\du -- To list all the user

Read: Postgresql listen_addresses

Postgresql create user with password md5

In Postgresql, the creatuser command allows us to create a user with password encryption md5 which stands for Message Digest algorithm.

Let’s create a user with password encryption md5.

createuser user_md5 -s -e -P

Here user_md5 is a name of the user, -s for superuser, -P prompt password for a new user, and -e is used to display commands generated by createuser.

In the above output, we have encrypted the password with md5 ‘md5e5244d8892aaeef6d92ad7f795f74bb’. we have also found that behind the scene createuser command executed the CREATE ROLE command to create a new user.

So when we create a new user with a password in the Postgresql database, it by default encrypt the password with md5 encryption.

But “how can we access this md5 password?” all of this information about database authorization identifiers are stored in the system catalog named pg_authid.

Let’s view the md5 password or encryption using the below statement.

SELECT rolname,rolpassword from pg_authid;

Where rolname stores the name of the user and rolpassword stores the encrypted password of that user.

From the above output, we can see the user named user_md5 has md5 encrypted password ‘md5e5244d8892aaeef6d92ad7f795f74bb’.

Read: Postgresql current_timestamp

Postgresql create user with password and database

In Postgresql, we are going to create a new user with a password and under that user, we will create a new database.

Log in Postgres prompt.

sudo -i -u postgres

Create a new user named samuel.

createuser samuel -s -d -P

Here -s for superuser, -d allows a new user to create a database, and -P prompt for a password.

Check the current user.

SELECT current_user;

Change the current user to a new user named samuel.

SET ROLE samuel;

Again check the current user.

SELECT current_user;

In the above output, we have created a user named samuel and change the current user from postgres to samuel.

Let’s create a new database named samuel_database under a user samuel.

CREATE DATABASE samuel_database;

Check the database using the below command.

\l -- To list all the databases

In the above output, we can that newly created database samuel_database under user/role/owner samuel.

Read: Postgresql row_number

Postgresql create user with password authentication failed

In Postgresql, we get this kind of error often due to wrong syntax or syntax errors.

Let’s first see the error that we get.

createuser -P aiden

From the above output, it shows an error that password authentication failed for user ‘user_name_of_your_system’, The error is due to we didn’t mention the session user of the Postgresql database in the command, and by default, it considers system user name as session user of the database.

In reality, we didn’t create this kind of user or it doesn’t exist in the database.

So if we want to create a user from a terminal or command line, then we can create a new user with the help of any session user.

By default, postgres is the session user of the Postgresql database and we can use this user.

To resolve this problem use the below syntax.

createuser -U user_name_of_session -P new_user_name

Create a new user named aiden.

createuser -U postgres -P aiden

The above command will ask for two kinds of passwords, one for the new user password and the other for the session user named postgres.

Log into the psql prompt to verify the newly created user.

psql -U postgres --To login into psql prompt

\du   -- To list all user

Also, read the following tutorials.

• Postgresql date add
• PostgreSQL list users
• Postgresql date between two dates
• Postgresql difference between two timestamps

So, in this tutorial, we learned about “Postgresql create a user with password” and covered the following topic.

• Postgresql create user with password grant all privileges
• Postgresql create user with password superuser
• Postgresql create user with password read only
• Postgresql create user with password pgadmin
• Postgresql create user with password script
• Postgresql create user with password md5
• Postgresql create user with password login
• Postgresql create user with password and database
• Postgresql create user with password authentication failed