Create PostgreSQL users

You can create PostgreSQL users with specific permissions to manage database access and control user privileges for secure database administration.

Before you begin

  • The PostgreSQL server is installed.
  • The database cluster is initialized.
  • The password_encryption parameter in the /var/lib/pgsql/data/postgresql.conf file is set to scram-sha-256.
  • Entries in the /var/lib/pgsql/data/pg_hba.conf file use the scram-sha-256 hashing algorithm as authentication method.

About this task

PostgreSQL users are of the following types:

  • The postgres Linux system user: Use it 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 /var/lib/pgsql/data/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 by 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

Perform 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.

Procedure

  1. Log in as the postgres system user, or switch to this user: 
    # su - postgres
  2. Start the PostgreSQL interactive shell prompt: 
    $ 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 shell prompt by using the \q meta command: 
    postgres=# \q

Results

  1. Log in to PostgreSQL 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=>