Thread: Postgres Pain Points: 1 pg_hba conf
We have always been impressed with the Postgres project and team. The whole hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed and stability rock. At the command line, Postgres rocks. But in applications we have had some real, and not improving pain points: #1) pg_hba conf Out of the box the md5 setting blocks access. Most "advice" say change to "all all trust" and indeed that works. But that seems a big security issue. Specifying a postgres role, password, and peer does not seem to work. And this approach is problematic if there are many roles or even dynamically created roles. Or is pb_hba conf set up for web sockets and we should be using sockets? For general use, it seems we should not have to modify this file - it should "just work" with good security. -- Support Dept Tiger Nassau, Inc. www.tigernassau.com 406-624-9310
On 08/11/2016 10:04 AM, support-tiger wrote: > We have always been impressed with the Postgres project and team. The > whole hybrid SQL / JSONB functionality rocks. The scalability rocks. > The speed and stability rock. At the command line, Postgres rocks. But > in applications we have had some real, and not improving pain points: > > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change > to "all all trust" and indeed that works. But that seems a big security > issue. Specifying a postgres role, password, and peer does not seem to > work. And this approach is problematic if there are many roles or even > dynamically created roles. Well pg_hba.conf is a combination of auth methods and client connection source, so you will need to be more specific about the issues you are having. Also remember first match wins, therefore if you have a restrictive match above more permissive matches it will hide them. > > Or is pb_hba conf set up for web sockets and we should be using sockets? > > For general use, it seems we should not have to modify this file - it > should "just work" with good security. > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Aug 11, 2016 at 7:04 PM, support-tiger <support@tigernassau.com> wrote: .... > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change to > "all all trust" and indeed that works. But that seems a big security issue. Indeed it is. I do not know where do you get this "advice" from, but I'll seriously consider blackholing the source. > Specifying a postgres role, password, and peer does not seem to work. And > this approach is problematic if there are many roles or even dynamically > created roles. In general this works, in nearly every situation. If you have problems, consider explaining it and may be you wil get some ``advice''. Francisco Olarte.
On Thu, Aug 11, 2016 at 11:04:37AM -0600, support-tiger wrote: > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change to > "all all trust" and indeed that works. But that seems a big security issue. > Specifying a postgres role, password, and peer does not seem to work. And > this approach is problematic if there are many roles or even dynamically > created roles. > > Or is pb_hba conf set up for web sockets and we should be using sockets? > > For general use, it seems we should not have to modify this file - it should > "just work" with good security. While I agree this sounds desirable I don't think it is possible (depending on the definition of "possible"). Would you like to offer a suggestion as to what pg_hba.conf should be configured as by default ? (Note that I am not soliciting a suggestion on behalf of the PostgreSQL team.) Methinks "deny-by-default" is Good Practice security-wise ? Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Aug 11, 2016 at 10:04 AM, support-tiger <support@tigernassau.com> wrote: > We have always been impressed with the Postgres project and team. The whole > hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed > and stability rock. At the command line, Postgres rocks. But in > applications we have had some real, and not improving pain points: > > #1) pg_hba conf > Out of the box the md5 setting blocks access. That depends on which box you got it out of. If you compile the source yourself, its default settings are 'trust', not 'md5'. If you get it from a repository, it is up to the repository's policies and/or the packager's tastes to decide what defaults to use. In my hands, md5 blocks access exactly when it should, when the user fails to provide the valid password. If you want things to be improved, you will have to be more specific about what problem you are having, and what package you are using. Cheers, Jeff
On 8/11/2016 1:48 PM, Jeff Janes wrote:
#1) pg_hba conf > Out of the box the md5 setting blocks access.That depends on which box you got it out of. If you compile the source yourself, its default settings are 'trust', not 'md5'. If you get it from a repository, it is up to the repository's policies and/or the packager's tastes to decide what defaults to use. In my hands, md5 blocks access exactly when it should, when the user fails to provide the valid password.
my defacto pg_hba.conf reads like this...
local all all peer
host all all 127.0.0.0/8 md5
host all all ::1 md5
# host all all xxx.yyy.zzz.0/24 md5 ## uncomment and adjust hostmask to suit LAN client addresses
with this setup, if user X runs psql with no arguments, it authenticates them as sql user X. if user X wants to connect to postgres as sql user Y, then `psql -h localhost -U Y ...` (or equivalent in whatever API) and use the sql Y role password to authenticate.
-- john r pierce, recycling bits in santa cruz
On 8/13/2016 8:29 AM, support-tiger wrote: > experimented with this some more with no progress. only trust seems to > work which is not what we want - will try some more versions with md5 > but this is why I've called this out as such a pain point. show us your pg_hba.conf (sans comments), and the connections that aren't working the way you want, along with whatever error messages they are generating. -- john r pierce, recycling bits in santa cruz
On Sat, Aug 13, 2016 at 9:37 PM, John R Pierce <pierce@hogranch.com> wrote:
On 8/13/2016 8:29 AM, support-tiger wrote:experimented with this some more with no progress. only trust seems to work which is not what we want - will try some more versions with md5 but this is why I've called this out as such a pain point.
show us your pg_hba.conf (sans comments), and the connections that aren't working the way you want, along with whatever error messages they are generating.
It's worth noting how I normally set up this file:
1. Set up postgres user on local connections with peer authentication
2. Set up initial roles, passwords, etc. (sudo -u postgres psql, then create role ... with noinherit login password = ...)
3. Configure for MD5 auth over the network and for all users on local except postgres.
Of course that differs depending on environment and requirements but it is a decent starting point.
--
john r pierce, recycling bits in santa cruz--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On 08/13/2016 01:37 PM, John R Pierce wrote:
On 8/13/2016 8:29 AM, support-tiger wrote:sorry for delay (traveling and rural usa wifi is pathetic)experimented with this some more with no progress. only trust seems to work which is not what we want - will try some more versions with md5 but this is why I've called this out as such a pain point.
show us your pg_hba.conf (sans comments), and the connections that aren't working the way you want, along with whatever error messages they are generating.
attached is the pg_hba file that is working on a fedora development machine - we've added a few comments to put case examples right in the file - hope the spacing did not get mangled
obviously "trust" is not good but need working alternative
also, if using web sockets (ie socket.io) would this change ?
hope we can make this file more clear
-- Support Dept Tiger Nassau, Inc. www.tigernassau.com 406-624-9310
Attachment
On 8/16/2016 1:32 PM, support-tiger wrote: > local all all trust so all unix 'domain' connections will allow any process on the system to authenticate as any SQL user. I nearly always use peer here. my applications which want to connect as a different db user than their os user, I specify host=localhost so it uses below instead... > host all all 127.0.0.1/32 trust anyone coming in via ipv4 localhost can also authenticate as any SQL user. I always use md5 here, so apps connecting with host=localhost can specify a sql user, with a password. > host all all ::1/128 ident anyone coming in as ipv6 localhost will require authd/identd protocol to identify them... ugh, hardly noone runs authd anymore, its considered bad security practice. its also inconsistent with ipv4 localhost above, so if you -h localhost, you're not sure what you're getting... I would always use md5 here. -- john r pierce, recycling bits in santa cruz