Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
PostgreSQL - pg_hba.conf localhost access only
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Networking & Security
View previous topic :: View next topic  
Author Message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Tue Apr 23, 2013 4:03 am    Post subject: PostgreSQL - pg_hba.conf localhost access only Reply with quote

In my "pg_hba.conf" I have:
Code:
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?
_________________
#Joseph
Back to top
View user's profile Send private message
scherz0
Apprentice
Apprentice


Joined: 02 Oct 2008
Posts: 154

PostPosted: Tue Apr 23, 2013 7:16 am    Post subject: Re: PostgreSQL - pg_hba.conf localhost access only Reply with quote

Quote:
What is the difference between:
local all all trust
vs.
host all all 127.0.0.1/32 trust


"local" is for Unix-domain socket, "host" is for TCP/IP socket
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Tue Apr 23, 2013 12:41 pm    Post subject: Re: PostgreSQL - pg_hba.conf localhost access only Reply with quote

scherz0 wrote:
Quote:
What is the difference between:
local all all trust
vs.
host all all 127.0.0.1/32 trust


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


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?
_________________
#Joseph
Back to top
View user's profile Send private message
scherz0
Apprentice
Apprentice


Joined: 02 Oct 2008
Posts: 154

PostPosted: Tue Apr 23, 2013 1:07 pm    Post subject: Reply with quote

Did you reload pg_hba.conf after changing it ?
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Tue Apr 23, 2013 1:09 pm    Post subject: Reply with quote

scherz0 wrote:
Did you reload pg_hba.conf after changing it ?

Yes, I restarted "postgresql-9.1"
_________________
#Joseph
Back to top
View user's profile Send private message
limn
l33t
l33t


Joined: 13 May 2005
Posts: 896

PostPosted: Tue Apr 23, 2013 2:17 pm    Post subject: Reply with quote

Code:
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.
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Tue Apr 23, 2013 6:07 pm    Post subject: Reply with quote

limn wrote:
Code:
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.


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:
Code:
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
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Thu Apr 25, 2013 12:58 am    Post subject: Reply with quote

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
#
#
host all 127.0.0.1 255.255.255.255 trust

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 :-/
_________________
#Joseph
Back to top
View user's profile Send private message
Ant P.
Advocate
Advocate


Joined: 18 Apr 2009
Posts: 2366
Location: UK

PostPosted: Thu Apr 25, 2013 3:56 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Thu Apr 25, 2013 4:36 am    Post subject: Reply with quote

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
Code:
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:
Code:
 * 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:
Code:
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
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Thu Apr 25, 2013 5:35 am    Post subject: Reply with quote

I have try to enable various setting in postgresql.conf
Code:
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
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Thu Apr 25, 2013 5:59 am    Post subject: Reply with quote

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?
_________________
#Joseph
Back to top
View user's profile Send private message
titanofold
Developer
Developer


Joined: 30 Dec 2003
Posts: 226
Location: Bryson City, NC USA

PostPosted: Tue Apr 30, 2013 5:34 pm    Post subject: Reply with quote

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.
_________________
The best things in life are free.
Guy-1: Surely, you will fold with me...
Guy-2: Alright, but don't call me Shirley
Back to top
View user's profile Send private message
Joseph_sys
Advocate
Advocate


Joined: 08 Jun 2004
Posts: 2405
Location: Edmonton, AB

PostPosted: Tue Apr 30, 2013 5:58 pm    Post subject: Reply with quote

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.
Code:
local   all     sql-ledger                 trust
the line below will have no effect
Code:
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?
_________________
#Joseph
Back to top
View user's profile Send private message
titanofold
Developer
Developer


Joined: 30 Dec 2003
Posts: 226
Location: Bryson City, NC USA

PostPosted: Tue Apr 30, 2013 8:08 pm    Post subject: Reply with quote

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.)
_________________
The best things in life are free.
Guy-1: Surely, you will fold with me...
Guy-2: Alright, but don't call me Shirley
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Networking & Security All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum