[gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

In my "pg_hba.conf" I have:

local	all             all             		        trust
host    all             all             127.0.0.1/32            trust

I was under impression that this is configuration is for localhost "127.0.0.1" access only.
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 work?

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Tue, April 23, 2013 02:17, Joseph wrote:
> In my "pg_hba.conf" I have:
>
> local	all             all             		        trust
> host    all             all             127.0.0.1/32            trust
>
> I was under impression that this is configuration is for localhost
> "127.0.0.1" access only.
> 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 work?

Normally that should be sufficient.
On which machine does the client-software run?

--
Joost Roeleveld

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/23/13 10:07, J. Roeleveld wrote:
>On Tue, April 23, 2013 02:17, Joseph wrote:
>> In my "pg_hba.conf" I have:
>>
>> local	all             all             		        trust
>> host    all             all             127.0.0.1/32            trust
>>
>> I was under impression that this is configuration is for localhost
>> "127.0.0.1" access only.
>> 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 work?
>
>Normally that should be sufficient.
>On which machine does the client-software run?
>
>--
>Joost Roeleveld

postgresql server runs on my machine but all other machines on the network including the one on remote location that I'm connected to via VPN can connect to postgresql 
database.
I don't want other machine to have access to my server database.

Even with a single line in pg_hba.conf
 local   all   all   trust

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

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Douglas J Hunley]

[-- Attachment #1: Type: text/plain, Size: 459 bytes --]

On Tue, Apr 23, 2013 at 8:37 AM, Joseph <syscon780@gmail.com> wrote:

> Even with a single line in pg_hba.conf
> local   all   all   trust
>
> all other machine on the network can connect to my postgresql database.
>

did you restart postgresql? editing pg_hba.conf requires a restart to take
effect


-- 
Douglas J Hunley (doug.hunley@gmail.com)
Twitter: @hunleyd                                               Web:
douglasjhunley.com
G+: http://goo.gl/sajR3

[-- Attachment #2: Type: text/html, Size: 1019 bytes --]

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Tue, April 23, 2013 14:37, Joseph wrote:
> On 04/23/13 10:07, J. Roeleveld wrote:
>>On Tue, April 23, 2013 02:17, Joseph wrote:
>>> In my "pg_hba.conf" I have:
>>>
>>> local	all             all             		        trust
>>> host    all             all             127.0.0.1/32            trust
>>>
>>> I was under impression that this is configuration is for localhost
>>> "127.0.0.1" access only.
>>> 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 work?
>>
>>Normally that should be sufficient.
>>On which machine does the client-software run?
>>
>>--
>>Joost Roeleveld
>
> postgresql server runs on my machine but all other machines on the network
> including the one on remote location that I'm connected to via VPN can
> connect to postgresql
> database.
> I don't want other machine to have access to my server database.
>
> Even with a single line in pg_hba.conf
>  local   all   all   trust
>
> all other machine on the network can connect to my postgresql database.

If the PostgreSQL database is running on machine X.
And you are using machine Y.

What command do you type to connect on machine Y?

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/23/13 15:57, J. Roeleveld wrote:
>On Tue, April 23, 2013 14:37, Joseph wrote:
>> On 04/23/13 10:07, J. Roeleveld wrote:
>>>On Tue, April 23, 2013 02:17, Joseph wrote:
>>>> In my "pg_hba.conf" I have:
>>>>
>>>> local	all             all             		        trust
>>>> host    all             all             127.0.0.1/32            trust
>>>>
>>>> I was under impression that this is configuration is for localhost
>>>> "127.0.0.1" access only.
>>>> 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 work?
>>>
>>>Normally that should be sufficient.
>>>On which machine does the client-software run?
>>>
>>>--
>>>Joost Roeleveld
>>
>> postgresql server runs on my machine but all other machines on the network
>> including the one on remote location that I'm connected to via VPN can
>> connect to postgresql
>> database.
>> I don't want other machine to have access to my server database.
>>
>> Even with a single line in pg_hba.conf
>>  local   all   all   trust
>>
>> all other machine on the network can connect to my postgresql database.
>
>If the PostgreSQL database is running on machine X.
>And you are using machine Y.
>
>What command do you type to connect on machine Y?
>
>--
>Joost

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

groups postgres
postgres

I hope this is correct as adding group "apache" to postgres user does not work.

But I just realized that any user from local network can access my sql-ledger using browser.
-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

Joseph <syscon780@gmail.com> wrote:

>On 04/23/13 15:57, J. Roeleveld wrote:
>>On Tue, April 23, 2013 14:37, Joseph wrote:
>>> On 04/23/13 10:07, J. Roeleveld wrote:
>>>>On Tue, April 23, 2013 02:17, Joseph wrote:
>>>>> In my "pg_hba.conf" I have:
>>>>>
>>>>> local	all             all             		        trust
>>>>> host    all             all             127.0.0.1/32           
>trust
>>>>>
>>>>> I was under impression that this is configuration is for localhost
>>>>> "127.0.0.1" access only.
>>>>> 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 work?
>>>>
>>>>Normally that should be sufficient.
>>>>On which machine does the client-software run?
>>>>
>>>>--
>>>>Joost Roeleveld
>>>
>>> postgresql server runs on my machine but all other machines on the
>network
>>> including the one on remote location that I'm connected to via VPN
>can
>>> connect to postgresql
>>> database.
>>> I don't want other machine to have access to my server database.
>>>
>>> Even with a single line in pg_hba.conf
>>>  local   all   all   trust
>>>
>>> all other machine on the network can connect to my postgresql
>database.
>>
>>If the PostgreSQL database is running on machine X.
>>And you are using machine Y.
>>
>>What command do you type to connect on machine Y?
>>
>>--
>>Joost
>
>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
>
>groups postgres
>postgres
>
>I hope this is correct as adding group "apache" to postgres user does
>not work.
>
>But I just realized that any user from local network can access my
>sql-ledger using browser.
>-- 
>Joseph

Joseph.

I am guessing Apache is running on the same machine as your Postgresql server?

In this case. The connection will always originate from localhost and Postgresql is behaving as it should.

You will need to secure access to the website to avoid people accessing it.

Kind regards

Joost Roeleveld
-- 
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/23/13 20:10, J. Roeleveld wrote:

[snip]
>>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
>>
>>groups postgres
>>postgres
>>
>>I hope this is correct as adding group "apache" to postgres user does
>>not work.
>>
>>But I just realized that any user from local network can access my
>>sql-ledger using browser.
>>--
>>Joseph
>
>Joseph.
>
>I am guessing Apache is running on the same machine as your Postgresql server?
>
>In this case. The connection will always originate from localhost and Postgresql is behaving as it should.
>
>You will need to secure access to the website to avoid people accessing it.
>
>Kind regards
>
>Joost Roeleveld

Yes, every machine I run has apache on it, so  Postgresql server runs on it as well.
If I'm connecting from another network machine to a server, how does it originate from localhost?

Something is not correct.

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Wed, April 24, 2013 00:16, Joseph wrote:
> On 04/23/13 20:10, J. Roeleveld wrote:

<SNIP>


>>I am guessing Apache is running on the same machine as your Postgresql
>> server?
>>
>>In this case. The connection will always originate from localhost and
>> Postgresql is behaving as it should.
>>
>>You will need to secure access to the website to avoid people accessing
>> it.
>>
>
> Yes, every machine I run has apache on it, so  Postgresql server runs on
> it as well.
> If I'm connecting from another network machine to a server, how does it
> originate from localhost?
>
> Something is not correct.

I'll try to explain.

When you connect to the website (Apache) the connection Apache sees
originates from your machine.

When Apache then needs to access PostgreSQL to access the data needed for
the website, Postgresql sees the connection originating from Apache, which
is running on the same machine.

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/24/13 07:11, J. Roeleveld wrote:
>On Wed, April 24, 2013 00:16, Joseph wrote:
>> On 04/23/13 20:10, J. Roeleveld wrote:
>
><SNIP>
>
>
>>>I am guessing Apache is running on the same machine as your Postgresql
>>> server?
>>>
>>>In this case. The connection will always originate from localhost and
>>> Postgresql is behaving as it should.
>>>
>>>You will need to secure access to the website to avoid people accessing
>>> it.
>>>
>>
>> Yes, every machine I run has apache on it, so  Postgresql server runs on
>> it as well.
>> If I'm connecting from another network machine to a server, how does it
>> originate from localhost?
>>
>> Something is not correct.
>
>I'll try to explain.
>
>When you connect to the website (Apache) the connection Apache sees
>originates from your machine.
>
>When Apache then needs to access PostgreSQL to access the data needed for
>the website, Postgresql sees the connection originating from Apache, which
>is running on the same machine.
>
>--
>Joost

Thank you for explanation.

That is what I'm confused about.  When I connect to "pstgresql" database from the same machine as postgres is running on I can understand.
It is a local connection from localhost (127.0.0.1) so everybody is allowed but I don't understand why users on the local network can connect to my machine and login 
using apache when their IP is different.

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

Joseph <syscon780@gmail.com> wrote:

>On 04/24/13 07:11, J. Roeleveld wrote:
>>On Wed, April 24, 2013 00:16, Joseph wrote:
>>> On 04/23/13 20:10, J. Roeleveld wrote:
>>
>><SNIP>
>>
>>
>>>>I am guessing Apache is running on the same machine as your
>Postgresql
>>>> server?
>>>>
>>>>In this case. The connection will always originate from localhost
>and
>>>> Postgresql is behaving as it should.
>>>>
>>>>You will need to secure access to the website to avoid people
>accessing
>>>> it.
>>>>
>>>
>>> Yes, every machine I run has apache on it, so  Postgresql server
>runs on
>>> it as well.
>>> If I'm connecting from another network machine to a server, how does
>it
>>> originate from localhost?
>>>
>>> Something is not correct.
>>
>>I'll try to explain.
>>
>>When you connect to the website (Apache) the connection Apache sees
>>originates from your machine.
>>
>>When Apache then needs to access PostgreSQL to access the data needed
>for
>>the website, Postgresql sees the connection originating from Apache,
>which
>>is running on the same machine.
>>
>>--
>>Joost
>
>Thank you for explanation.
>
>That is what I'm confused about.  When I connect to "pstgresql"
>database from the same machine as postgres is running on I can
>understand.
>It is a local connection from localhost (127.0.0.1) so everybody is
>allowed but I don't understand why users on the local network can
>connect to my machine and login 
>using apache when their IP is different.
>
>-- 
>Joseph

Joseph.

The connection to the database is done by apache. Apache connects from the server where Apache is running.

Postgresql does not know nor even care where the connection to apache originates from. It only sees apache connecting to it.

If you want to prevent people from accessing the website. You will need to configure the restriction in Apache or in a firewall.

A webbrowser will NOT connect directly to the database. With a lot of larger applications this will not even be possible because the database is on a seperate server where the firewall is only allowing the webserver to access the database.

Restricting access to a website by setting restrictions on the database server uswd by the website is pointless.

--
Joost Roeleveld
-- 
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/24/13 22:27, J. Roeleveld wrote:
[snip]
>>
>>Thank you for explanation.
>>
>>That is what I'm confused about.  When I connect to "pstgresql"
>>database from the same machine as postgres is running on I can
>>understand.
>>It is a local connection from localhost (127.0.0.1) so everybody is
>>allowed but I don't understand why users on the local network can
>>connect to my machine and login
>>using apache when their IP is different.
>>
>>--
>>Joseph
>
>Joseph.
>
>The connection to the database is done by apache. Apache connects from the server where Apache is running.
>
>Postgresql does not know nor even care where the connection to apache originates from. It only sees apache connecting to it.
>
>If you want to prevent people from accessing the website. You will need to configure the restriction in Apache or in a firewall.
>
>A webbrowser will NOT connect directly to the database. With a lot of larger applications this will not even be possible because the database is on a seperate server where the firewall is only allowing the webserver to access the database.
>
>Restricting access to a website by setting restrictions on the database server uswd by the website is pointless.
>
>--
>Joost Roeleveld

Thank you, now this is clear, so that pg_hba.conf has a limited use.
So simple statement in apache directory: Allow from localhost
will fix the issue.

When it comes to database. How can I limit certain users from certain IP to only one database.
I don't thing this is possible via apache!

The line: "local   all           all    trust"
will give access to everybody.

How those line in pg_hba.conf should look if I want user from remote computer to access only one database?
Is it:
local   my_database           all    trust
local   others_database       all    ident alex

Does "ident" refers to user who is allow to login into database?
How to list users for a particular database?

Thank you for your explanations.
-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/24/13 22:27, J. Roeleveld wrote:
>
>The connection to the database is done by apache. Apache connects from the server where Apache is running.
>
>Postgresql does not know nor even care where the connection to apache originates from. It only sees apache connecting to it.
>
>If you want to prevent people from accessing the website. You will need to configure the restriction in Apache or in a firewall.
>
>A webbrowser will NOT connect directly to the database. With a lot of larger applications this will not even be possible because the database is on a seperate server where the firewall is only allowing the webserver to access the database.
>
>Restricting access to a website by setting restrictions on the database server uswd by the website is pointless.
>
>--
>Joost Roeleveld

Those postgresql instructions are very,very confusing, for example on the following webpage:
http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c15679_002.htm

it states:
---copy----
 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.
---end copy---

The above is not correct as users from any machine on a local network can connect to my database.

If I put a line in pg_hba.conf
host all 127.0.0.1 255.255.255.255 trust

postgresql will not even starts, I get an error message:
FATAL:  could not load pg_hba.conf
LOG:  invalid IP mask "trust": Name or service not known


-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Dustin C. Hatch]

On 4/24/2013 19:23, Joseph wrote:
> The above is not correct as users from any machine on a local network
> can connect to my database.
>
In the scenario you described, as Joost explained, the users on your 
network are *not* connecting to your database; they are connecting to a 
website. The web server is connecting to the database on their behalf. 
PostgreSQL's host-based authentication controls only who access the 
database directly, not who access the applications that use it.

> If I put a line in pg_hba.conf
> host all 127.0.0.1 255.255.255.255 trust
>
This line is not valid. pg_hba.conf entries take the form

type	database	user	[address]	method	[options]

"type" can be 'local' (connections over a Unix socket), 'host' 
(connections over TCP, maybe using SSL), 'hostssl' (connections over TCP 
using SSL), 'hostnossl' (connections over TCP not using SSL).

To achieve what I think you are looking for, just remove all lines from 
pg_hba.conf except this one::

     local	all	all	trust

This will prevent anyone from connecting to your databases using TCP at 
all. If you really need TCP from the localhost instead of Unix sockets, 
you can also add this line::

     host	all	all	127.0.0.1/32	trust

> postgresql will not even starts, I get an error message:
> FATAL:  could not load pg_hba.conf
> LOG:  invalid IP mask "trust": Name or service not known
>
>

I would strongly advise you read all of the PostgreSQL documentation 
before you expose a database to the world. Specifically, please read the 
official page about pg_hba.conf[1].

[1] http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html

Regards,

-- 
♫Dustin
http://dustin.hatch.name/

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/25/13 00:16, Dustin C. Hatch wrote:
>On 4/24/2013 19:23, Joseph wrote:
>> The above is not correct as users from any machine on a local network
>> can connect to my database.
>>
>In the scenario you described, as Joost explained, the users on your
>network are *not* connecting to your database; they are connecting to a
>website. The web server is connecting to the database on their behalf.
>PostgreSQL's host-based authentication controls only who access the
>database directly, not who access the applications that use it.
>
>> If I put a line in pg_hba.conf
>> host all 127.0.0.1 255.255.255.255 trust
>>
>This line is not valid. pg_hba.conf entries take the form
>
>type	database	user	[address]	method	[options]
>
>"type" can be 'local' (connections over a Unix socket), 'host'
>(connections over TCP, maybe using SSL), 'hostssl' (connections over TCP
>using SSL), 'hostnossl' (connections over TCP not using SSL).
>
>To achieve what I think you are looking for, just remove all lines from
>pg_hba.conf except this one::
>
>     local	all	all	trust
>
>This will prevent anyone from connecting to your databases using TCP at
>all. If you really need TCP from the localhost instead of Unix sockets,
>you can also add this line::

I just tried as you suggested, the only active line in: pg_hba.conf 
 local     all     all     trust

anything else is commented out. I restarted the server but I still can connect to postgresql from another computer via Firefox.
I'm trying to produce meaning-full report to see if I can record remote connection from external computer so I have enable 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 I don't see any indication in any report which computer is the connection coming from.

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Thu, April 25, 2013 07:48, Joseph wrote:

<SNIP>

> I just tried as you suggested, the only active line in: pg_hba.conf
>  local     all     all     trust
>
> anything else is commented out. I restarted the server but I still can
> connect to postgresql from another computer via Firefox.

Joseph,

Let me put it in really simple terms:
1) Firefox is NOT a database client, it can NOT connect to a database
2) Firefox IS a webbrowser, it ONLY connects to a webserver

This means, Postgresql will NOT see ANY connection made by Firefox.

The website you have running ON TOP OFF apache makes the connection to
Postgresql.

Eg. it goes like the following:

User <-> Firefox <-> Apache/website <-> Postgresql

Any of the above can ONLY see their immediate neighbour.

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Thu, April 25, 2013 01:48, Joseph wrote:
> On 04/24/13 22:27, J. Roeleveld wrote:
> [snip]
>>>
>>>Thank you for explanation.
>>>
>>>That is what I'm confused about.  When I connect to "pstgresql"
>>>database from the same machine as postgres is running on I can
>>>understand.
>>>It is a local connection from localhost (127.0.0.1) so everybody is
>>>allowed but I don't understand why users on the local network can
>>>connect to my machine and login
>>>using apache when their IP is different.
>>>
>>>--
>>>Joseph
>>
>>Joseph.
>>
>>The connection to the database is done by apache. Apache connects from
>> the server where Apache is running.
>>
>>Postgresql does not know nor even care where the connection to apache
>> originates from. It only sees apache connecting to it.
>>
>>If you want to prevent people from accessing the website. You will need
>> to configure the restriction in Apache or in a firewall.
>>
>>A webbrowser will NOT connect directly to the database. With a lot of
>> larger applications this will not even be possible because the database
>> is on a seperate server where the firewall is only allowing the webserver
>> to access the database.
>>
>>Restricting access to a website by setting restrictions on the database
>> server uswd by the website is pointless.
>>
>>--
>>Joost Roeleveld
>
> Thank you, now this is clear, so that pg_hba.conf has a limited use.

It has use for connections made TO the database by whichever application
needs the connection. Firefox is NOT such an application.

> So simple statement in apache directory: Allow from localhost
> will fix the issue.

Please check the apache documentation, I believe you also need to add a
deny-rule.

> When it comes to database. How can I limit certain users from certain IP
> to only one database.

Will those users connect DIRECTLY to the database server?

> I don't thing this is possible via apache!
>
> The line: "local   all           all    trust"
> will give access to everybody.
>
> How those line in pg_hba.conf should look if I want user from remote
> computer to access only one database?
> Is it:
> local   my_database           all    trust
> local   others_database       all    ident alex
>
> Does "ident" refers to user who is allow to login into database?

Yes, provided the OS can identify the username.
Apache will likely connect using "apache".

> How to list users for a particular database?

Try using passwords instead of allowing everyone full access to all
databases.

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/25/13 09:10, J. Roeleveld wrote:
>On Thu, April 25, 2013 07:48, Joseph wrote:
>
><SNIP>
>
>> I just tried as you suggested, the only active line in: pg_hba.conf
>>  local     all     all     trust
>>
>> anything else is commented out. I restarted the server but I still can
>> connect to postgresql from another computer via Firefox.
>
>Joseph,
>
>Let me put it in really simple terms:
>1) Firefox is NOT a database client, it can NOT connect to a database
>2) Firefox IS a webbrowser, it ONLY connects to a webserver
>
>This means, Postgresql will NOT see ANY connection made by Firefox.
>
>The website you have running ON TOP OFF apache makes the connection to
>Postgresql.
>
>Eg. it goes like the following:
>
>User <-> Firefox <-> Apache/website <-> Postgresql
>
>Any of the above can ONLY see their immediate neighbour.
>
>--
>Joost

So pg_hba.conf only controls direct connections to postgreSQL. 
Since "apache" group is in postgres user; apache was given permission to access the database in this case py-passing the setting in pg_hba.conf
Is there a way to force sequence:
 Apache/website <-> pg_hba.conf <-> Postgresql

-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Thu, April 25, 2013 14:35, Joseph wrote:
> On 04/25/13 09:10, J. Roeleveld wrote:
>>On Thu, April 25, 2013 07:48, Joseph wrote:
>>
>><SNIP>
>>
>>> I just tried as you suggested, the only active line in: pg_hba.conf
>>>  local     all     all     trust
>>>
>>> anything else is commented out. I restarted the server but I still can
>>> connect to postgresql from another computer via Firefox.
>>
>>Joseph,
>>
>>Let me put it in really simple terms:
>>1) Firefox is NOT a database client, it can NOT connect to a database
>>2) Firefox IS a webbrowser, it ONLY connects to a webserver
>>
>>This means, Postgresql will NOT see ANY connection made by Firefox.
>>
>>The website you have running ON TOP OFF apache makes the connection to
>>Postgresql.
>>
>>Eg. it goes like the following:
>>
>>User <-> Firefox <-> Apache/website <-> Postgresql
>>
>>Any of the above can ONLY see their immediate neighbour.
>>
>>--
>>Joost
>
> So pg_hba.conf only controls direct connections to postgreSQL.

Correct.

> Since "apache" group is in postgres user; apache was given permission to
> access the database in this case py-passing the setting in pg_hba.conf

Wrong, Postgresql does not check group-ownership. Your pg_hba.conf file
will have a setting that allows Apache to connect.

> Is there a way to force sequence:
>  Apache/website <-> pg_hba.conf <-> Postgresql

Postgresql will always read the pg_hba.conf file and use that to determine
who can and can not connect directly to Postgresql.

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/25/13 18:57, J. Roeleveld wrote:
>> So pg_hba.conf only controls direct connections to postgreSQL.
>
>Correct.
>
>> Since "apache" group is in postgres user; apache was given permission to
>> access the database in this case py-passing the setting in pg_hba.conf
>
>Wrong, Postgresql does not check group-ownership. Your pg_hba.conf file
>will have a setting that allows Apache to connect.
>
>> Is there a way to force sequence:
>>  Apache/website <-> pg_hba.conf <-> Postgresql
>
>Postgresql will always read the pg_hba.conf file and use that to determine
>who can and can not connect directly to Postgresql.
>
>--
>Joost

I've tired with this line:
local   clinic           sql-ledger               trust

I can connect to "clinic" database form localhost and any box on the network. It works OK
But I when I tried to further limit the database to a single IP, postgresql refused to start.

local   clinic           sql-ledger    10.0.0.100/32              trust 


-- 
Joseph

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[J. Roeleveld]

On Thu, April 25, 2013 20:26, Joseph wrote:
> On 04/25/13 18:57, J. Roeleveld wrote:
>>> So pg_hba.conf only controls direct connections to postgreSQL.
>>
>>Correct.
>>
>>> Since "apache" group is in postgres user; apache was given permission
>>> to
>>> access the database in this case py-passing the setting in pg_hba.conf
>>
>>Wrong, Postgresql does not check group-ownership. Your pg_hba.conf file
>>will have a setting that allows Apache to connect.
>>
>>> Is there a way to force sequence:
>>>  Apache/website <-> pg_hba.conf <-> Postgresql
>>
>>Postgresql will always read the pg_hba.conf file and use that to
>> determine
>>who can and can not connect directly to Postgresql.
>>
>>--
>>Joost
>
> I've tired with this line:
> local   clinic           sql-ledger               trust
>
> I can connect to "clinic" database form localhost and any box on the
> network. It works OK
> But I when I tried to further limit the database to a single IP,
> postgresql refused to start.
>
> local   clinic           sql-ledger    10.0.0.100/32              trust

This line is wrong, please read the comments in the supplied "pg_hba.conf"
file:
# "local" is for Unix domain socket connections only

If you want to limit to an IP-address, then you nneed to use "host"

--
Joost

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[James Cloos]

>>>>> "J" == Joseph  <syscon780@gmail.com> writes:

J> In my "pg_hba.conf" I have:

J> local	all             all             		        trust
J> host    all             all             127.0.0.1/32            trust

J> I was under impression that this is configuration is for localhost "127.0.0.1" access only.

That tells pg how to authenticate users using the unix domain socket and
users using tcp over the loopback interface.

To limit the listen_address, edit postgresql.conf in that directory.

You want to have:

  listen_addresses = 'localhost'

or:

  listen_addresses = '127.0.0.1'

to prevent any access attempts from any non-localhost ip addresses.

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

Re: [gentoo-user] PosgreSQL - pg_hba.conf localhost access only

[Joseph]

On 04/26/13 20:25, James Cloos wrote:
>>>>>> "J" == Joseph  <syscon780@gmail.com> writes:
>
>J> In my "pg_hba.conf" I have:
>
>J> local	all             all             		        trust
>J> host    all             all             127.0.0.1/32            trust
>
>J> I was under impression that this is configuration is for localhost "127.0.0.1" access only.
>
>That tells pg how to authenticate users using the unix domain socket and
>users using tcp over the loopback interface.
>
>To limit the listen_address, edit postgresql.conf in that directory.
>
>You want to have:
>
>  listen_addresses = 'localhost'
>
>or:
>
>  listen_addresses = '127.0.0.1'
>
>to prevent any access attempts from any non-localhost ip addresses.
>
>-JimC
>-- 
>James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

Thanks James for your help.
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.

Thank you for your help!
-- 
Joseph