Codecademy Logo

Authentication and Authorization in Postgres

Print Cheatsheet

pg_hba.conf File

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.
# entry format
connection_type db user address auth_method [auth_options]
# example entry
hostssl db_example +g_example samenet scram-sha-256

Server Configuration: postgresql.conf File

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.
listen_addresses = 'localhost'
port = 5432
ssl = off

CREATE ROLE and Least Privilege

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.

CREATE ROLE p_customers_read;

GRANT and Least Privilege

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.

GRANT SELECT ON customers TO p_customers_read;