Introduction to Postgres add user
The default user that is present in PostgreSQL after installing it is a Postgres user. Besides that in real-time many users need to be created so that access privileges can be assigned accordingly to maintain the security of the database. In this article, we will learn how we can create new users in PostgreSQL.There are two methods to do so. The first method involves using the CREATE query statement to create a new user and the second method is to use the meta-command to create the user in PostgreSQL’s psql utility. We will study the syntax and options provided in both the methods and also see an example of how we can create a new user in PostgreSQL using each of the above-mentioned methods.
Syntax:
Format 1 –
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SYSID uid
CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'newPassword'
| VALID UNTIL 'expirytime'
Using the above create command the new user can be created and along with that other options can also be assigned.
- Name – It is the name of the user or role whose properties or password you want to change.
- Option – We can change multiple parameters and privileges associated with the user using this format. Some of the option properties are mentioned in the following section.
- SYSID uid – It is the user id that is assigned to the user when the user is created to identify the user uniquely in the database server. It is not necessary to mention. If not mentioned then the system assigns the value that is equal to maximum user-id plus one to the current user id that is being created.
- CREATEDB – This can be specified if you want to give the privilege to the user to create a new database
- NOCREATEDB – This can be mentioned if you want to restrict the user from creating any new database.
- CREATEUSER – This property can be specified to allow the user to create new users.
- NOCREATEUSER – When this property is mentioned in the query in the above format the user won’t be able to create new users.
- ENCRYPTED – This property determines whether the password stored in the pg_catalog’s pg_shadow table is stored in the form of an MD5 encrypted format.
- UNENCRYPTED – The password is not stored in encrypted format in pg_catalog. If neither ENCRYPTED or UNENCRYPTED property is specified and neither this is done while user creation then the default password storing mechanism is decided based on password_encryption configuration variable.
- PASSWORD – The new Password is the string that you want to set as the password for the user. If this field is not specified and the user doesn’t have any previously set password to it then no authentication will be done for the user and the user can log in to the system without mentioning the password. But in case if you switch to a password authentication system then the user won’t be able to log in.
- VALID UNTIL expirytime – This field can be used if you want to allow the set password up to some specific period. This field if the timestamp up to which you want to permit the assigned password to work.
Example of Postgres add user
We will firstly login to the system by Postgres default user. Here we have my password assigned to the Postgres role already. So, we will enter the password.
sudo su - postgres
Further, Let us check all the users which are present in the database server by firing the command using psql promp –
select usename from pg_catalog.pg_user;
Alternatively, you can use meta-command \du to retrieve the user details that give the same results.
Now, we will create a new user named payal in the current database server using the CREATE USER format mentioned above with the help of the following query –
CREATE USER payal WITH ENCRYPTED PASSWORD '[email protected]';
that gives the following output –
As the output is CREATE ROLE. The user has been created successfully.
Let us check by using the \du metacommand that results in the following output –
\du
Format 2 –
createuser [options...] [nameofuser]
This command can only be used by the superusers to create the new user in PostgreSQL.
- Nameofuser – It is the name of the user that you wish to create.
- Options – There are many options available such as a, A, e, E, d, D, i, q, N, P, etc that are prepended by – sign while using in create the user.
While creating the users remotely, some of the connection related options that can support in the operation are as mentioned below –
-h nameOfHost or –host nameOfHost
It helps us to specify the name of the host on which the server is running.
-p nameOfPort or –port nameOfPort
It helps us to specify the port of the TCP or socket of the local UNIX domain on which connections are being listened to on the server.
-U nameOfUser or –username nameOfUser
It helps us to specify the name of the user with which you want to connect as.
-W or –password
It helps us to specify whether to ask the password prompt for new users.
For example –
Let us create a new user named educba by using the above syntax –
createuser educba;
that gives the following output and password is being asked to enter for the educba user-
Note that this command cannot be run on psql command prompt but only on postgres shell.
Let us check whether educba user is created by logging into psql command prompt and firing the \du meta command as follows –
Psql
firing the \du commands gives the following output containing the educba user that we created.
\du
SELECT usename FROM pg_catalog.pg_user;
gives the following output –
Conclusion
We can create multiple new users by either using the CREATE query on the psql terminal or createuser command that can be used on the psql shell. Both these methods provide flexibility to specify user-related properties and other details required while user creation in PostgreSQL.
Recommended Articles
This is a guide to Postgres add user. Here we discuss the introduction, syntax, command with examples, and its code implementation. You may also have a look at the following articles to learn more –
