4.3. Creating PostgreSQL users

PostgreSQL users are of the following types:

The postgres UNIX system user - should be used only to run the PostgreSQL server and client applications, such as pg_dump. Do not use the postgres system user for any interactive work on PostgreSQL administration, such as database creation and user management.
A database superuser - the default postgres PostgreSQL superuser is not related to the postgres system user. You can limit access of the postgres superuser in the pg_hba.conf file, otherwise no other permission limitations exist. You can also create other database superusers.
A role with specific database access permissions:
- A database user - has a permission to log in by default
- A group of users - enables managing permissions for the group as a whole

Roles can own database objects (for example, tables and functions) and can assign object privileges to other roles using SQL commands.

Standard database management privileges include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

Role attributes are special privileges, such as LOGIN, SUPERUSER, CREATEDB, and CREATEROLE.

Important

Red Hat recommends performing most tasks as a role that is not a superuser. A common practice is to create a role that has the CREATEDB and CREATEROLE privileges and use this role for all routine management of databases and roles.

Conditions préalables

The PostgreSQL server is installed.
The database cluster is initialized.

Procédure

To create a user, set a password for the user, and assign the user the CREATEROLE and CREATEDB permissions:
```
postgres=# CREATE USER mydbuser WITH PASSWORD 'mypasswd' CREATEROLE CREATEDB;
```
Replace mydbuser with the username and mypasswd with the user’s password.

Ressources supplémentaires

Exemple 4.1. Initializing, creating, and connecting to a PostgreSQL database

This example demonstrates how to initialize a PostgreSQL database, create a database user with routine database management privileges, and how to create a database that is accessible from any system account through the database user with management privileges.

Install the PosgreSQL server:
```
# dnf install postgresql-server
```

Initialize the database cluster:

# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Set the password hashing algorithm to scram-sha-256.

In the /var/lib/pgsql/data/postgresql.conf file, change the following line:

#password_encryption = md5              # md5 or scram-sha-256

à :

password_encryption = scram-sha-256

In the /var/lib/pgsql/data/pg_hba.conf file, change the following line for the IPv4 local connections:

host    all             all             127.0.0.1/32            ident

à :

host    all             all             127.0.0.1/32            scram-sha-256

Start the postgresql service:
```
# systemctl start postgresql.service
```
Log in as the system user named postgres:
```
# su - postgres
```

Start the PostgreSQL interactive terminal:

$ psql
psql (13.7)
Type "help" for help.

postgres=#

Optional: Obtain information about the current database connection:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Create a user named mydbuser, set a password for mydbuser, and assign mydbuser the CREATEROLE and CREATEDB permissions:
```
postgres=# CREATE USER mydbuser WITH PASSWORD 'mypasswd' CREATEROLE CREATEDB;
CREATE ROLE
```
The mydbuser user now can perform routine database management operations: create databases and manage user indexes.
Log out of the interactive terminal by using the \q meta command:
```
postgres=# \q
```
Log out of the postgres user session:
```
$ logout
```
Log in to the PostgreSQL terminal as mydbuser, specify the hostname, and connect to the default postgres database, which was created during initialization:
```
# psql -U mydbuser -h 127.0.0.1 -d postgres
Password for user mydbuser:
Type the password.
psql (13.7)
Type "help" for help.

postgres=>
```

Create a database named mydatabase:

postgres=> CREATE DATABASE mydatabase;
CREATE DATABASE
postgres=>

Log out of the session:
```
postgres=# \q
```

Connect to mydatabase as mydbuser:

# psql -U mydbuser -h 127.0.0.1 -d mydatabase
Password for user mydbuser:
psql (13.7)
Type "help" for help.
mydatabase=>

Optional: Obtain information about the current database connection:

mydatabase=> \conninfo
You are connected to database "mydatabase" as user "mydbuser" on host "127.0.0.1" at port "5432".

4.3. Creating PostgreSQL users

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Rendre l’open source plus inclusif

À propos de Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links