During one of the sessions from the last Swiss PGDay there was a question which could not be answered during the talk: Is it possible to modify pg_hba.conf from inside PostgreSQL without having access to the operating system? What everybody agreed on is, that there currently is no build-in function for doing this.
When you are on a recent version of PostgreSQL there is a view you can use to display the rules in pg_hba.conf:
postgres=# select * from pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
84 | local | {all} | {all} | | | trust | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
91 | local | {replication} | {all} | | | trust | |
92 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
94 | host | {all} | {mydb} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
(7 rows)
But there is nothing which allows you to directly modify that. When you are lucky and you have enough permissions there is a way to do it, though. First, lets check where pg_hba.conf is located:
postgres=# select setting from pg_settings where name like '%hba%';
setting
-----------------------------
/u02/pgdata/DEV/pg_hba.conf
Having that information we can load that file to a table:
postgres=# create table hba ( lines text ); CREATE TABLE postgres=# copy hba from '/u02/pgdata/DEV/pg_hba.conf'; COPY 93
Once it is loaded we have the whole content in our table (skipping the comments and empty lines here):
postgres=# select * from hba where lines !~ '^#' and lines !~ '^$';
lines
-----------------------------------------------------------------------
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
(6 rows)
As this is a normal table we can of course add a row:
postgres=# insert into hba (lines) values ('host all mydb ::1/128 trust');
INSERT 0 1
postgres=# select * from hba where lines !~ '^#' and lines !~ '^$';
lines
-----------------------------------------------------------------------
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all mydb ::1/128 trust
(7 rows)
And now we can write it back:
postgres=# copy hba to '/u02/pgdata/DEV/pg_hba.conf'; COPY 94
Reading the whole file confirms that our new rule is there:
postgres=# select pg_read_file('pg_hba.conf');
pg_read_file
--------------------------------------------------------------------------
# PostgreSQL Client Authentication Configuration File +
# =================================================== +
# +
# Refer to the "Client Authentication" section in the PostgreSQL +
# documentation for a complete description of this file. A short +
# synopsis follows. +
# +
# This file controls: which hosts are allowed to connect, how clients +
# are authenticated, which PostgreSQL user names they can use, which +
# databases they can access. Records take one of these forms: +
# +
# local DATABASE USER METHOD [OPTIONS] +
# host DATABASE USER ADDRESS METHOD [OPTIONS] +
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS] +
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] +
# +
# (The uppercase items must be replaced by actual values.) +
# +
# The first field is the connection type: "local" is a Unix-domain +
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket, +
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a +
# plain TCP/IP socket. +
# +
# DATABASE can be "all", "sameuser", "samerole", "replication", a +
# database name, or a comma-separated list thereof. The "all" +
# keyword does not match "replication". Access to replication +
# must be enabled in a separate record (see example below). +
# +
# USER can be "all", a user name, a group name prefixed with "+", or a +
# comma-separated list thereof. In both the DATABASE and USER fields +
# you can also write a file name prefixed with "@" to include names +
# from a separate file. +
# +
# ADDRESS specifies the set of hosts the record matches. It can be a +
# host name, or it is made up of an IP address and a CIDR mask that is +
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that +
# specifies the number of significant bits in the mask. A host name +
# that starts with a dot (.) matches a suffix of the actual host name. +
# Alternatively, you can write an IP address and netmask in separate +
# columns to specify the set of hosts. Instead of a CIDR-address, you +
# can write "samehost" to match any of the server's own IP addresses, +
# or "samenet" to match any address in any subnet that the server is +
# directly connected to. +
# +
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256", +
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert". +
# Note that "password" sends passwords in clear text; "md5" or +
# "scram-sha-256" are preferred since they send encrypted passwords. +
# +
# OPTIONS are a set of options for the authentication in the format +
# NAME=VALUE. The available options depend on the different +
# authentication methods -- refer to the "Client Authentication" +
# section in the documentation for a list of which options are +
# available for which authentication methods. +
# +
# Database and user names containing spaces, commas, quotes and other +
# special characters must be quoted. Quoting one of the keywords +
# "all", "sameuser", "samerole" or "replication" makes the name lose +
# its special character, and just match a database or username with +
# that name. +
# +
# This file is read on server startup and when the server receives a +
# SIGHUP signal. If you edit the file on a running system, you have to +
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",+
# or execute "SELECT pg_reload_conf()". +
# +
# Put your actual configuration here +
# ---------------------------------- +
# +
# If you want to allow non-local connections, you need to add more +
# "host" records. In that case you will also need to make PostgreSQL +
# listen on a non-local interface via the listen_addresses +
# configuration parameter, or via the -i or -h command line switches. +
+
# CAUTION: Configuring the system for local "trust" authentication +
# allows any local user to connect as any PostgreSQL user, including +
# the database superuser. If you do not trust all your local users, +
# use another authentication method. +
+
+
# TYPE DATABASE USER ADDRESS METHOD +
+
# "local" is for Unix domain socket connections only +
local all all trust +
# IPv4 local connections: +
host all all 127.0.0.1/32 trust +
# IPv6 local connections: +
host all all ::1/128 trust +
# Allow replication connections from localhost, by a user with the +
# replication privilege. +
local replication all trust +
host replication all 127.0.0.1/32 trust +
host replication all ::1/128 trust +
host all mydb ::1/128 trust +
(1 row)
All you need to do from now on is to reload the configuration and you’re done:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
Of course: Use with caution!
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/STH_web-min-scaled.jpg)
Impressed
03.08.2024Daniel - thank you for this wizardry, amazing, so useful in a hosted postgres setup where we do not control the underlying vm!
Daniel Westermann
09.08.2024You're welcome. Happy that it helps ...