Authentication and Authorization in Postgres
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_examplegroup - on the same network as the server
- and use sha-256 password authentication.
# entry formatconnection_type db user address auth_method [auth_options]# example entryhostssl 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
portparameter is the port the Postgres server listens on. - The
sslparameter determines whether or not the server will support SSL connections.
listen_addresses = 'localhost'port = 5432ssl = 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 roleGRANT 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;
