PostgreSQL – CREATE USER

The CREATE USER PostgreSQL statement creates a database account that allows you to log into a PostgreSQL database.

Syntax for CREATE USER operator in PostgreSQL

CREATE USER user_name
   [ WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password_value‘
   | VALID UNTIL ‘expiration’ ];

Parameters or arguments

• user_name – The name of the database account you want to create.
• password_value – Password to assign user_name.
• expiration – Date / time when the password expires. If you do not want your password to expire, set it to ‘infinity’.

Example
Let’s see how to create a user in PostgreSQL using the CREATE USER operator. For example:

CREATE USER trizor_1;

In this example, the CREATE USER operator will create a new user named trizor. This new user will not have a password, but you can use ALTER USER to assign a password later.

If you want to assign a password while creating the user, you can change the CREATE USER as follows:

CREATE USER trizor_1
   WITH PASSWORD 'zubastik_1';

This will create a user named trizor with the password zubastik_1. If you want to create a trizor user with a zubastik_1 password that expires on January 1, 2020, you must use the CREATE USER operator as follows:

CREATE USER trizor_1
   WITH PASSWORD 'zubastik_1'
   VALID UNTIL 'Jan 1, 2020';

If you want the password for a trizor user never expires, you must use the CREATE USER operator as follows:

CREATE USER trizor_1
   WITH PASSWORD 'zubastik_1'
   VALID UNTIL 'infinity_1';

How to work with users in PostgreSQL

Some of the operations described below should be performed in the PostgreSQL command shell. It can be run from a postgres user – to login from this user, enter:

su - postgres_1

* if the system generates an error due to a lack of rights, we first raise the privileges with the command sudo su or su.

Now run the PostgreSQL command shell:

$ psql -Upostgres template1_1

* in this example, login is performed from the postgres account to the template1 template database.

For viewing all the DBMS users:

=# select * from pg_user_1;

New user creation

In order to be able to connect to the PostgreSQL DBMS from a new user, it is necessary to create this user, assign him/her rights and configure the pg_hba.conf_id file.

1. User creation
Adding a new role (user):

=# CREATE USER dmosk WITH PASSWORD 'myPassword_1';

* the dmosk role with myPassword password is created in this example.

2. Assignment of database usage rights
First, you need to connect to the base you want to work with:

=# \c database1

* in this example, connect to a database1.

This will add all rights to use the database1 base of the dmosk account:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "dmosk_1";

* in most cases, the default public scheme is used. But the administrator can create a new schema. This should be considered when assigning permissions.

Exit the SQL shell:

database1=# \q

3. pg_hba.conf file setup
To be able to connect to the DBMS from a created user, it is necessary to check the rights settings in the pg_hba.conf configuration file.

First let’s see the data location path for PostgreSQL:

ps aux | grep postgres | grep -- -D

In return, we’ll get something like that:

postgres 30728 0.0 1.5 359120 15676 ? Ss 14:49 0:00 /usr/pgsql-9.6/bin/postmaster_1 -D /var/lib/pgsql/9.6/data/_1

* in this example, the last /var/lib/pgsql/9.6/data/_1 is the path for the configuration files.

Open pg_hba.conf:

vi /var/lib/pgsql/9.6/data/_1pg_hba.conf

We are adding the rights to connect to our created user:

...
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
...

* in this example we have allowed dmosk user to connect to all databases on the server (all) from node 127.0.0.1 (localhost) with password requirement (md5).
* it is necessary that this line is above the line that is written by default
host all 127.0.0.1/32 ident.

After restarting the service:

systemctl restart postgresql-9.6

* in this example postgresql version 9.6 is installed, for different versions on different operating systems the commands for restarting the service may be different.

4. Check
For the test we are trying to connect to Postgre using a created user:

psql -Udmosk template1 -h127.0.0.1

Configuring access rights to the database using groups

First, let’s create a group role:

=# CREATE ROLE "myRole" NOSUPERUSER INHERIT NOCREATEDB NOREPLICATION;

* this command creates myRole group with minimal rights.

Now let’s add previously created dmosk user to this group:

=# GRANT "myRole" TO dmosk;

Connect to the database for which we want to configure the rights.

=# \c database1

and grant all rights for the myRole group to all database1 tables.

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP "myRole";

Deletion of users and groups

The following command is used to delete a user:

=# DROP USER dmosk;

Take away the rights:

database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "dmosk";

* note that this request is different from granting rights in two ways: 1) instead of GRANT we write REVOKE; 2) instead of TO “dmosk” we write FROM “dmosk”;

Assigning special rights to PostgreSQL users

In addition to ALL PRIVILEGES, you can also grant rights to special operations, for example:

=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "dmosk";

* the command will give the rights to receive data, update and add them. Other operations, e.g. deletion, will be prohibited for the dmosk user.

Assigning rights to a certain table:

database1=# GRANT ALL PRIVILEGES ON table_users TO "dmosk";

* in this example we will grant all rights to the table_users table in database1;

An account for backups

It is best to connect to the database with minimal privileges to perform backups.

First we create the role that we will use to perform the backup:

=# CREATE USER bkpuser WITH PASSWORD 'bkppasswd';

* we will create a bkpuser account with the password bkppasswd.

We give you the rights to connect to the base

=# GRANT CONNECT ON DATABASE database TO bkpuser;

* in this example to the database.

Connect to the database (in our example, database):

=# \c database

We give the rights to all sequences in the schematic:

=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

* we gave the rights to the schema public. This is the default schema, but in your case it may be different. In that case, we’ll substitute our value.

GUI

Sometimes it is easier to use the program for rights setting and working with PostgreSQL. I can recommend the pgAdmin application. It will allow not only creating and deleting users in the window mode, but also fully working with the DBMS.

How to create postgresql user and database