Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to deny access to Postgres when connected from host/non-local

How to deny access to Postgres when connected from host/non-local

From
"A. Reichstadt"
Date:
Hello,

I try to deny access to all databases on my server if the user “postgres" tries to connect from a non-local host. Here is what I did in pg_hba.conf:


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
local   all             postgres                                trust
host    all             postgres        0.0.0.0/0               reject


But it continues to allow for Postgres to connect from anywhere through PGAdmin but also as a direct connection to port 5432. I also relaunched the server. This is version 12.

What else do I have to do?

Thanks for any help.

Cheers,
Alex

Re: How to deny access to Postgres when connected from host/non-local

From
Joe Conway
Date:
On 4/2/21 7:06 PM, A. Reichstadt wrote:
> Hello,
> 
> I try to deny access to all databases on my server if the user “postgres" tries 
> to connect from a non-local host. Here is what I did in pg_hba.conf:
> 
> 
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> 
> # "local" is for Unix domain socket connections only
> local   all             all                                     md5
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            md5
> # IPv6 local connections:
> host    all             all             ::1/128                 md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> local   replication     all                                     md5
> host    replication     all             127.0.0.1/32            md5
> host    replication     all             ::1/128                 md5
> host    all             all             0.0.0.0/0               md5
> local   all             postgres                                trust
> host    all             postgres        0.0.0.0/0               reject
> 
> 
> But it continues to allow for Postgres to connect from anywhere through PGAdmin 
> but also as a direct connection to port 5432. I also relaunched the server. This 
> is version 12.
> 
> What else do I have to do?
> 
> Thanks for any help.

See:
https://www.postgresql.org/docs/13/auth-pg-hba-conf.html

In particular:

   "Each record specifies a connection type, a client IP
    address range (if relevant for the connection type),
    a database name, a user name, and the authentication
    method to be used for connections matching these
    parameters. The first record with a matching
    connection type, client address, requested database,
    and user name is used to perform authentication."

So your reject line is never being reached.

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: How to deny access to Postgres when connected from host/non-local

From
"A. Reichstadt"
Date:
Thanks, works.

Sent from my iPhone

> On Apr 3, 2021, at 11:02, Joe Conway <mail@joeconway.com> wrote:
>
> On 4/2/21 7:06 PM, A. Reichstadt wrote:
>> Hello,
>> I try to deny access to all databases on my server if the user “postgres" tries to connect from a non-local host.
Hereis what I did in pg_hba.conf: 
>> # TYPE  DATABASE        USER            ADDRESS                 METHOD
>> # "local" is for Unix domain socket connections only
>> local   all             all                                     md5
>> # IPv4 local connections:
>> host    all             all             127.0.0.1/32            md5
>> # IPv6 local connections:
>> host    all             all             ::1/128                 md5
>> # Allow replication connections from localhost, by a user with the
>> # replication privilege.
>> local   replication     all                                     md5
>> host    replication     all             127.0.0.1/32            md5
>> host    replication     all             ::1/128                 md5
>> host    all             all             0.0.0.0/0               md5
>> local   all             postgres                                trust
>> host    all             postgres        0.0.0.0/0               reject
>> But it continues to allow for Postgres to connect from anywhere through PGAdmin but also as a direct connection to
port5432. I also relaunched the server. This is version 12. 
>> What else do I have to do?
>> Thanks for any help.
>
> See:
> https://www.postgresql.org/docs/13/auth-pg-hba-conf.html
>
> In particular:
>
>  "Each record specifies a connection type, a client IP
>   address range (if relevant for the connection type),
>   a database name, a user name, and the authentication
>   method to be used for connections matching these
>   parameters. The first record with a matching
>   connection type, client address, requested database,
>   and user name is used to perform authentication."
>
> So your reject line is never being reached.
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development