View previous topic :: View next topic |
Author |
Message |
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Tue Apr 23, 2013 4:03 am Post subject: PostgreSQL - pg_hba.conf localhost access only |
|
|
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? |
|
Back to top |
|
|
scherz0 Apprentice
Joined: 02 Oct 2008 Posts: 154
|
Posted: Tue Apr 23, 2013 7:16 am Post subject: Re: PostgreSQL - pg_hba.conf localhost access only |
|
|
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 |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Tue Apr 23, 2013 12:41 pm Post subject: Re: PostgreSQL - pg_hba.conf localhost access only |
|
|
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? |
|
Back to top |
|
|
scherz0 Apprentice
Joined: 02 Oct 2008 Posts: 154
|
Posted: Tue Apr 23, 2013 1:07 pm Post subject: |
|
|
Did you reload pg_hba.conf after changing it ? |
|
Back to top |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Tue Apr 23, 2013 1:09 pm Post subject: |
|
|
scherz0 wrote: | Did you reload pg_hba.conf after changing it ? |
Yes, I restarted "postgresql-9.1" |
|
Back to top |
|
|
limn l33t
Joined: 13 May 2005 Posts: 997
|
Posted: Tue Apr 23, 2013 2:17 pm Post subject: |
|
|
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 |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Tue Apr 23, 2013 6:07 pm Post subject: |
|
|
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. |
|
Back to top |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Thu Apr 25, 2013 12:58 am Post subject: |
|
|
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 :-/ |
|
Back to top |
|
|
Ant P. Watchman
Joined: 18 Apr 2009 Posts: 6920
|
Posted: Thu Apr 25, 2013 3:56 am Post subject: |
|
|
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 |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Thu Apr 25, 2013 4:36 am Post subject: |
|
|
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","" |
|
|
Back to top |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Thu Apr 25, 2013 5:35 am Post subject: |
|
|
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. |
|
Back to top |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Thu Apr 25, 2013 5:59 am Post subject: |
|
|
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? |
|
Back to top |
|
|
titanofold Developer
Joined: 30 Dec 2003 Posts: 235 Location: Bryson City, NC USA
|
|
Back to top |
|
|
Joseph_sys Advocate
Joined: 08 Jun 2004 Posts: 2712 Location: Edmonton, AB
|
Posted: Tue Apr 30, 2013 5:58 pm Post subject: |
|
|
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? |
|
Back to top |
|
|
titanofold Developer
Joined: 30 Dec 2003 Posts: 235 Location: Bryson City, NC USA
|
Posted: Tue Apr 30, 2013 8:08 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
|