# PostgreSQL -  pg_hba.conf localhost access only

## Joseph_sys

In my "pg_hba.conf" I have:

```
local   all             all                                     trust

host    all             all             127.0.0.1/32            trust

host    all             all             ::1/128                 trust
```

Reading some explanation from various web-pages: *Quote:*   

>  local
> 
>     A local entry is semantically the same as a host entry. However, you do not need to specify a host that is allowed to connect. The local entry is used for client connections that are initiated from the same machine that the PostgreSQL server is operating on.
> 
> 

 

What is the difference between:

local   all             all                                     trust

vs.

host    all             all             127.0.0.1/32            trust

I was under impression that:

local   all             all                                     trust

is for access to all user on a machine on which postgresql is running

But to my surprise I can access my database from other machine on my network and even from another sub-network that I'm connected to via VPN

How this authentication/access works?

----------

## scherz0

 *Quote:*   

> What is the difference between:
> 
> local   all             all                                     trust
> 
> vs.
> ...

 

"local" is for Unix-domain socket, "host" is for TCP/IP socket

----------

## Joseph_sys

 *scherz0 wrote:*   

>  *Quote:*   What is the difference between:
> 
> local   all             all                                     trust
> 
> vs.
> ...

 

So why with only these three lines enabled all machine on my network can access my postgresql database?

Even with single line in "pg_hba.conf" 

local   all   all   trust

all other machine on the network can connect to my postgresql database.

Isn't it a security problem?

----------

## scherz0

Did you reload pg_hba.conf after changing it ?

----------

## Joseph_sys

 *scherz0 wrote:*   

> Did you reload pg_hba.conf after changing it ?

 

Yes, I restarted "postgresql-9.1"

----------

## limn

```
netstat -an | grep 5432
```

if you haven't changed the port, otherwise grep for the changed port value.

I have the same values as you in the conf and no other box can connect that postgresql database.

----------

## Joseph_sys

 *limn wrote:*   

> 
> 
> ```
> netstat -an | grep 5432
> ```
> ...

 

Can you check on your system "apache" groups if there is "postgres"

On my: groups apache

apache postgres

I'm using SQL-Ledger (firefox) to access the postgresql.

Brief history:

I had a problem in the past when I upgraded to posgresql-9.1, all of a sudden I could not access the sql-ledger.

The solution was to add "postgres group" to apache user.

The reason for it was the change in directory permission:

```
postgresql 8.x

drwxrwx--x 2 postgres postgres 4096 Dec 14 19:57 /var/run/postgresql/

postgresql 9.x

drwxrwx--- 2 postgres postgres 4096 Dec 19 13:21 /var/run/postgresql/
```

So: groups apache

apache postgres

But now with that setting anybody on local network can access my database via Firefox + sql-ledger.

----------

## Joseph_sys

I'm reading various webpages (documentation) on postgresql and none of if make sense to me, here is an example from:

http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c15679_002.htm

 *Quote:*   

>  Example 8-4. Valid pg_hba.conf comments
> 
> # Book Town host entries
> 
> #
> ...

 

If I use the above line postgresql will not even start, I get an error message in logfile:

FATAL:  could not load pg_hba.conf

LOG:  invalid IP mask "trust": Name or service not known

Next:  *Quote:*   

>  local
> 
>     A local entry is semantically the same as a host entry. However, you do not need to specify a host that is allowed to connect.  The local entry is used for client connections that are initiated from the same machine that the PostgreSQL server is operating on.

 

The above is not correct as users from any machine on a local network can connect to my database because in "apache" groups is: (apache postgres).  So any connection coming from local machine via firefox will originate from "apache" and it go through :-/

----------

## Ant P.

Increase the logging verbosity in postgresql.conf, reload the config, and then post here the part of the log showing these remote connections to the postgresql server.

----------

## Joseph_sys

 *Ant P. wrote:*   

> Increase the logging verbosity in postgresql.conf, reload the config, and then post here the part of the log showing these remote connections to the postgresql server.

 

I've changed in: postgresql.conf

```
log_error_verbosity = verbose

log_destination = 'syslog'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600 
```

it created the postgresql-date.log file but this file is empty!

I've tried options:

log_destination = 'syslog'

log_destination = 'eventlog' 

gives me error: 

```
 * Starting PostgreSQL ...

FATAL:  invalid value for parameter "log_destination": "eventlog"

DETAIL:  Unrecognized key word: "eventlog".
```

log_destination = 'csvlog'

When I logged IN from local external machine the log doesn't produces anything relevant:

```
tail /var/lib/postgresql/9.1/data/pg_log/postgresql-2013-04-24_225431.csv

2013-04-24 22:54:31.458 MDT,,,31534,,5178b707.7b2e,1,,2013-04-24 22:54:31 MDT,,0,LOG,00000,"database system was shut down at 2013-04-24 22:53:39 MDT",,,,,,,,"StartupXLOG, xlog.c:6084",""

2013-04-24 22:54:31.582 MDT,,,31532,,5178b705.7b2c,1,,2013-04-24 22:54:29 MDT,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,"reaper, postmaster.c:2452",""

2013-04-24 22:54:31.586 MDT,,,31537,,5178b707.7b31,1,,2013-04-24 22:54:31 MDT,,0,LOG,00000,"autovacuum launcher started",,,,,,,,"AutoVacLauncherMain, autovacuum.c:404",""

2013-04-24 22:55:57.871 MDT,,,31532,,5178b705.7b2c,2,,2013-04-24 22:54:29 MDT,,0,LOG,00000,"received smart shutdown request",,,,,,,,"pmdie, postmaster.c:2215",""

2013-04-24 22:55:57.872 MDT,,,31537,,5178b707.7b31,2,,2013-04-24 22:54:31 MDT,1/0,0,LOG,00000,"autovacuum launcher shutting down",,,,,,,,"AutoVacLauncherMain, autovacuum.c:781",""

2013-04-24 22:55:57.875 MDT,,,31535,,5178b707.7b2f,1,,2013-04-24 22:54:31 MDT,,0,LOG,00000,"shutting down",,,,,,,,"ShutdownXLOG, xlog.c:7462",""

2013-04-24 22:55:58.159 MDT,,,31535,,5178b707.7b2f,2,,2013-04-24 22:54:31 MDT,,0,LOG,00000,"database system is shut down",,,,,,,,"ShutdownXLOG, xlog.c:7484",""
```

----------

## Joseph_sys

I have try to enable various setting in postgresql.conf 

```
max_connections = 100               

shared_buffers = 24MB         

log_destination = 'syslog'      

logging_collector = on      

log_directory = 'pg_log'      

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'   

log_file_mode = 0600         

syslog_facility = 'LOCAL0'

syslog_ident = 'postgres'

client_min_messages = notice      

log_min_messages = notice      

log_checkpoints = on

log_connections = on

log_disconnections = on

log_error_verbosity = verbose       

log_hostname = on

datestyle = 'iso, mdy'

lc_messages = 'en_US.UTF-8'         

lc_monetary = 'en_US.UTF-8'         

lc_numeric = 'en_US.UTF-8'         

lc_time = 'en_US.UTF-8'            

default_text_search_config = 'pg_catalog.english'
```

but none of them producing any information from which remote computer is the connection coming.

----------

## Joseph_sys

I'm using Firefox web-application to access postgresql databese.

If a web application is accessing postgres then the web application chooses which database it wants to access. 

So this would explain why changing any setting in pg_hba.conf has no effect where the connection is coming from, am I correct?

----------

## titanofold

The pg_hba.conf has nothing to do with this issue.

Your issue lies with Apache. If you allow anyone to access the given Apache host, then Apace will do nothing to restrict access.

----------

## Joseph_sys

 *titanofold wrote:*   

> The pg_hba.conf has nothing to do with this issue.
> 
> Your issue lies with Apache. If you allow anyone to access the given Apache host, then Apace will do nothing to restrict access.

 

Yes, I just realized it, I can easily restrict access by IP with apache to my local postgresql server but how to further control access by database?

I'll explain what am doing and trying to accomplish.

On my sever (local desktop box) I run postgresql and have access to all databases.

I'm using sql-ledger program, which uses firefox via apache to access postgresql. In apache I can easily control which IP has access to my box, this is not a problem.

Postgresql has a user "sql-ledger" and I don't wont to create new users.  sql-ledger has access to two databases.

On localhost (where postgresql is running) I want to have access to both databases (eg. db1 and db2)

but I want to limit access from other computers on the network to only one database.

Is it possible?

I've tried various combination in pg_hba.conf but nothing works.

The first line line in pg_hba.conf (below) will allow connection to

both databases (db1 and db2) to a box that I'll allow via apache to

access postgresql. 

```
local   all     sql-ledger                 trust
```

the line below will have no effect 

```
host         clinic  sql-ledger      192.168.139.1/32        trust
```

How do I limit IP 192.168.139.1 to only one database and have full access from localhost to both databases, is it possible?

----------

## titanofold

Yes and no.

Yes, you can restrict direct access to that database so that only one IP address is permitted.

No, you can't use pg_hba.conf to enforce the policy on SQL Ledger used through Apache.

To do what you want to accomplish you will have to create additional roles for the database, and setup SQL Ledger to use the specific role for each database. You can then use the shorthand @sameuser for the database.

Much of the access restriction you need must be done in Apache. For example, use a seperate sub-domain for 192.168.139.1 and allow access only for 192.168.139.1, and have a seperate sub-domain that denies access to 192.168.139.1. (Apache is more flexible than this as you can allow/deny down to the file level. I am not intimately familiar with SQL Ledger so it may not be necessary to do sub-domains depending on how SQL Ledger handles the [DB|account]-to-user mapping.)

----------

