Authentication and Authorization in Postgres

Rules can be added to the pg_hba.conf file to configure host-based authentication.

The entries all follow the same format: connection_type db user address auth_method [auth_options]

The example entry shows an entry that will:

• allow SSL connections
• to database db_example
• for members of g_example group
• on the same network as the server
• and use sha-256 password authentication.

The listen_addresses, port, and ssl parameters in the postgresql.conf file can be updated to ensure secure server configuration.

• The listen_addresses parameter controls what IP addresses are allowed to connect to the server.
• The port parameter is the port the Postgres server listens on.
• The ssl parameter determines whether or not the server will support SSL connections.

In SQL, the CREATE ROLE command can be used to help create an access control that follows the principle of least privilege.

CREATE ROLE follows the format CREATE ROLE role_name [options];.

The code example shows a command that creates a role named p_customers_read which can read the customer table.

In SQL, the GRANT command can be used to create an access control system that follows the principle of least privilege.

GRANT has two formats:

• GRANT PERMISSION ON table TO role; which grants permissions to a role
• GRANT p_example TO g_example; which assigns the permissions of the first role to the second role

The example code gives the p_customers_read role permission to SELECT items in the customers table.