3.3. Creating PostgreSQL users

Procedure

1. Log in as the postgres system user, or switch to this user:

   # su - postgres

2. Start the PostgreSQL interactive terminal:

   $ psql
   psql (16.4)
   Type "help" for help.
   
   postgres=#

3. 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".

4. Create a user named mydbuser, set a password for it, and assign the CREATEROLE and CREATEDB permissions to the user:

   postgres=# CREATE USER mydbuser WITH PASSWORD '<password>' CREATEROLE CREATEDB;
   CREATE ROLE

   The mydbuser user now can perform routine database management operations: create databases and manage user indexes.

5. Log out of the interactive terminal by using the \q meta command:

   postgres=# \q

Verification

1. 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 (16.4)
   Type "help" for help.
   
   postgres=>

2. Create a database:

   postgres=> CREATE DATABASE <db_name>;

3. Log out of the session:

   postgres=# \q

4. Connect to new database as mydbuser:

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