Atikh's DBA blog
  • Home
  • Oracle
  • MySQL
  • MongoDB
  • PostgreSQL
  • Snowflake
  • About Me
  • Contact Us

PostgreSQL : pg_hba. conf configuration file

 Atikh Shaikh     PostgreSQL     No comments   

In PostgreSQL, there are a number of configuration files, some of the files needs to be managed by postgres DBA, out of which pg_hba.conf is one of those. This file mainly includes connection details, based on this it is decided who will be able to connect to the database, we will discuss it in detail

The pg_hba.conf i.e. host based authentication file is used to control client authentication and the file stored in the data directory

  • The pg_hba.conf file consist of a set of records and contains one record per line, blanks lines are ignored and text after # character are comments.
  • Record is made up of a number of fields that are separated by spaces and or tabs
  • Each record specifies connection type, a client IP address range, a database name, a username and an authentication method to be used for connection matching these parameters.
  • It will be access denied if a matching record is not found in pg_hba.conf file while connecting.

A record in a file can have one of the seven formats :

local     database user auth-method  [auth-options]

host      database user address  auth-method [auth-options]

hostssl   database user address auth-method  [auth-options]

hostnossl database user address auth-method  [auth-options]

host     database user IP-Address IP-Mask auth-method [auth-options]

hostssl  database user IP-Address IP-Mask auth-method [auth-options]

hostnossl database user IP-Address IP-Mask auth-method[auth-options]

 

The meaning of each entity is described below

local

  • This record matches connection attempts using UNIX domain sockets, without a record of this type UNIX domain socket connections are disallowed

host

  • This record matches connection attempts made using TCP/IP, host records match either SSL or non_SSL connection attempts

hostssl

  • This record matches connection attempt made using TCP/IP, but only when the connection is made with an SSL encryption

hostnossl

  • This behaves just opposite to hostssl

Database

  • Matches with database name mentioned, options are described below
    • all- matches all databases
    • sameuser- matches if requested database has same name requested user
    • samerole- specifies requested user must be member of role with same name as requested database
    • replication- matches if a physical replication connection is requested
  • or one can give specific database name, multiple database name can be specified by separating comma
  • File name with database name list can be specified in this field using @

User

  • Specifies database user matching record
  • all- specifies it matches all users or name of specific user or a group preceded by +
  • File name with user name list can be specified in this field using @

Address

  • Specifies client machine address(es) that matches this record
  • This can contain either a hostname, an IP address range, or one of the special keywords as described below
  • IP address range is specified using standard numeric notation for the range starting address, the slash (/), and a CIDR mask length
  • This mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right side should be zero in the given IP address, must not be while spaces between IP address, the slash (/) and CIDR mask length


postgres pg_hba file


For example

IPV4 address specified this way are 

  • 172.20.143.89/32 for a single host or
  • 172.20.143.0/24 for a small network or
  • 10.6.0.0/16 for larger one

IPV6-

  •  : : 1/128 for single host
  • Feo8:7a31:ciff:0000:0000/96 for small network

To specify single host use mask length 32 for IPV4 and 128 for IPV6

all- can be used to match all IP address

samehost- match any of the servers own IP address

samenet- any address in any subnet

 

IP-address, IP-Mask

  • These can be an alternative to the IP address/mask length notation. Instead of specifying the mask length, the actual mask is specified in a separate column
  • Example- 

                             255.0.0.0 ->            IPV4 mask length 8
                 255.255.255.255 -> IPV4 mask length 32

 

auth-method

  • Specifies the authentication method to use when connection matches this record

trust

Allows connection unconditionally

reject

Reject connections unconditionally

scram-sha-256

Perform SCRAM-SHA-256 authentication

md5

Perform SCRAM-SHA-256 or MD5 authentication

password

Requires to supply password

gss

Uses GSSAPI to authenticate user

sspi

Uses SSPI to authenticate user

ident

Use of ident server

peer

Local connection only

ldap

Authenticate using LDAP server

radius

Authenticate using RADIUS server

cert

Authenticate using SSL client certificate

pam

Pluggable Authentication   Modules(PAM)

bsd

BSD authentication service provided by OS

 

auth-options

 

After the auth-method field, there can be fields of the forma name=value that specify options for authentication method

 

Below is example of pg_hba.conf 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.

 

# TYPE  DATABASE        USER            ADDRESS                 METHOD

 

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

 

  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit
Email ThisBlogThis!Share to XShare to Facebook

Related Posts:

  • Database Creation in PostgreSQL Creating the database in PostgreSQL is a very simple task, it's like creating a directory structure. We will discuss the steps to create a new databa… Read More
  • Error: Unable to find a match: postgresql14-server While installing PostgreSQL 14.10 on Linux system, I was getting the below error, so here is the solution [oracle@localhost Desktop]$ sudo yum i… Read More
  • To Know details about Linux OS :Oracle, PostgreSQL, MySQL and MongoDBIn this small article, we are going to see different commands to check the OS version in Linux and other details about the operating system Here are … Read More
  • Postgres spooling file and other command line optionsI wanted to drop all the tables in a particular postgres database, there were around 90 tables, dropping individual tables one by one is not a good id… Read More
  • PostgreSQL : How to check parameter values in postgresqlAs part of some basic administration in PostgreSQL databases, we will learn how to check and modify parameters  there are a few ways to do it u… Read More
Newer Post Older Post Home

0 comments:

Post a Comment

Author

Atikh Shaikh
View my complete profile

Categories

  • MongoDB (18)
  • Oracle 12c (30)
  • Oracle12cR2 New Feature (3)
  • PostgreSQL (20)
  • RMAN (10)
  • Snowflake (8)
  • mysql (23)
  • oracle (74)

Blog Archive

  • ►  2018 (38)
    • ►  November (25)
    • ►  December (13)
  • ▼  2019 (33)
    • ►  January (15)
    • ▼  February (6)
      • PostgreSQL : pg_hba. conf configuration file
      • MongoDB : Creating a Large Document
      • MongoDB : Indexing and explain Plan
      • MongoDB : Basic Administration
      • Oracle : Restore points
      • Configure and Use of Flash Recovery Area
    • ►  March (2)
    • ►  April (5)
    • ►  May (5)
  • ►  2020 (5)
    • ►  April (1)
    • ►  May (2)
    • ►  July (2)
  • ►  2021 (8)
    • ►  June (3)
    • ►  July (3)
    • ►  August (1)
    • ►  December (1)
  • ►  2022 (33)
    • ►  May (3)
    • ►  June (10)
    • ►  July (3)
    • ►  August (4)
    • ►  September (8)
    • ►  October (3)
    • ►  November (2)
  • ►  2023 (14)
    • ►  February (1)
    • ►  April (5)
    • ►  May (2)
    • ►  June (1)
    • ►  September (1)
    • ►  October (1)
    • ►  December (3)
  • ►  2024 (5)
    • ►  January (2)
    • ►  March (3)
  • ►  2025 (6)
    • ►  March (1)
    • ►  April (3)
    • ►  May (2)

Popular Posts

  • ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]
    I was trying to export the schema in my windows PC, it got stuck with below error    C:\Users\shaik\Videos\technodba exp>expdp userid...
  • PostgreSQL : How to get data directory location for PostgreSQL instance
    Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know th...
  • Oracle 23ai : Use of NOVALIDATE Constraints in IMPDP
    While performing impdp operations in the Oracle database, Oracle performs validation checks for every constraint on the imported table, that...
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    In previous articles, we have learned about user creation and grants  in MySQL in detail, but there are a few privileges called global priv...
  • Oracle Dataguard Broker Configuration (DGMGRL)
    Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch...

Labels

oracle Oracle 12c mysql PostgreSQL MongoDB oracle 19c Oracle23c oracle19c Orale PDB-CDB oracle12c python AWS Oracle ASM Virtualbox pluggable database storage engine

Pages

  • Disclaimer
  • Privacy Policy

Follow TechnoDBA

Copyright © 2025 Atikh's DBA blog | Powered by Blogger