We mentioned previously that the pg_hba.conf file enables client authentication to occur between the PostgreSQL server and the client application. Upon the application initiating a connection, the application will specify PostgreSQL user name it wants to connect to PostgreSQL with. PostgreSQL, being a client-server architecture will check the pg_hba.conf on each connection made to the server and verify that the machine hosting the application has rights to connect to the database. If the machine requesting access has the correct permissions to connect, PostgreSQL will then check the conditions that the application must meet in order to successfully authenticate. This includes connections that are initiated locally.

PostgreSQL will check the authentication method via the pg_hba.conf for each connection created. As this check is perfomed everytime a new connection is made to the PostgreSQL server, there is no need to restart PostgreSQL if you add an entry into the pg_hba.conf file. When a connection is initalized, PostgreSQL will read through the pg_hba.conf one entry at a time. As soon as PostgreSQL finds a matching record, PostgreSQL will stop searching and allow or reject the connection based on the entry. If PostgreSQL does not find a matching entry in the pg_hba.conf the connection fails.

The pg_hba.conf will be located in the $PGDATA directory (/usr/local/pgsql/data/) and is installed by default upon the execution of initlocation .

The table level permissions still apply to a database even if a user has permissions to connect to the database. If you can connect but can not select data from a table, you may want to verify that a user has permissions to that table. If you are using the psql application you can check the permissions of the tables within a database by using the \z option. If you are not using psql , you can use the following query to check the permissions within a database.

Example 6-2. Checking User Permissions on Tables

SELECT relname as "Table",
       relacl as "User Permissions"
FROM   pg_class
WHERE  relkind in ('r', 'v', 'S') AND
       relname !~ '^pg_'
ORDER BY relname;