Authentication on PostgreSQL

PostgreSQL Roles and Authentication Methods

PostgreSQL handles database access permissions using the concept of roles. Depending on how you set up the role, it can represent a database user or a group of database users.

PostgreSQL supports several authentication methods . The most frequently used methods are:

• Trust - A role can connect without a password, as long as the criteria defined in the pg_hba.conf are met

• Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256 md5 and password (clear-text)

• Ident - Only supported for TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.

• Peer - Same as Ident, but it is only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. For local connections, PostgreSQL is set to use the peer authentication method.

The “postgres” user is automatically created when PostgreSQL is installed. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as “postgres”, switch to the user postgres and access a PostgreSQL prompt using the psql utility:

sudo su - postgres
psql

To Exit:

\q

You can use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql

The postgres user is typically used only from the localhost