PostgreSQL roles (users) ¶

Configuring Postgres for Use ¶

The postgres user ¶

While PostgreSQL become installed, a system user account named postgres was also created with an identical user account in postgres.

By default, the postgres user account isn’t configured with a password , so it isn’t viable to log into the server the use of the postgres user account without first creating a password for it.

This postgres account has an all-access pass on your PostgreSQL database server, permission-wise.

The postgres user account has similarities to the sa account in SQL server .

The postgres database ¶

PostgreSQL is installed with a default database postgres .

For the most part, we use the postgres database for administration functions, and create new databases on the PostgreSQL server to suit our needs.

The psql Command Line Utility ¶

PostgreSQL consists of psql, a command line application for managing your databases and server.

While a GUI-based software such as pgadmin3 is often less complicated to use in the daily task, the command line utilty psql is always handy.

psql gives total control of your postgres system from the terminal, together with the ability to execute SQL queries.

We will use psql to perform our preliminary configuration and to create an initial database super user.

For most systems, the default Postgres user is postgres and a password is not required for authentication.

Thus, to add a password, we must first login and connect as the postgres user.

$ sudo -u postgres psql

           [sudo] Mot de passe de pvergain :

          

           psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))
Saisissez « help » pour l'aide.

postgres=#

Changing the Password ¶

With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:

           postgres=# alter user postgres password 'myPassword';

          

ALTER ROLE

If successful, Postgres will output a confirmation of ALTER ROLE as seen above.

Finally, exit the psql client by using the q command:

           postgres=# \q

          

You’re all done.

The default postgres user now has a password associated with the account for use in your other applications.

Le rôle postgres de ¶

sudo su - postgres ¶

❯ sudo su - postgres

            postgres@uc045:~$ psql -p 5433
psql (15.1 (Debian 15.1-1.pgdg110+1))
Saisissez « help » pour l'aide.

postgres=#

            SELECT current_role, current_user;

           

             current_role | current_user
--------------+--------------
 postgres     | postgres
(1 ligne)

           

Create role ¶

           CREATE ROLE intranet WITH
    NOSUPERUSER
    CREATEDB
    CREATEROLE
    INHERIT
    LOGIN
    NOREPLICATION
    NOBYPASSRLS
    CONNECTION LIMIT -1;

          

Update role ¶

           export PGPASSWORD=XXXXXXXXXXXX; psql -h localhost -d db_intranet -p 5433 -U intranet -c "ALTER ROLE intranet SET search_path = intranet, public;"

          

Liste des rôles ¶

           sudo -u postgres psql

          

           postgres=# \du
                                              Liste des rôles
 Nom du rôle |                                    Attributs                                    | Membre de
-------------+---------------------------------------------------------------------------------+-----------
 postgres    | Superutilisateur, Créer un rôle, Créer une base, Réplication, Contournement RLS | {}

          