Re: [SPAM] [NOVICE] Users: must all Pg users be system users?

On Mon, Sep 18, 2017 at 12:24 PM, Tom Browder <tom.browder@gmail.com> wrote:

That's what I'm trying to get a grip on.  And I have trouble
understanding the difference between auth methods of peer, trust, and
password.

​Something specific?

peer = I've already proven my identity to the O/S we are sharing, it will vouch for me.

trust = no identity validation performed - grant login for the user name presented

password = here is my username+password credential proving my identity; look them up within the cluster and if a matching entry is found grant the login request

1.  The default pg_hba.conf is initially set to allow all system users
(all in the passwd file) to login to a db of their system name without
a password.

​Not positive what the default is (probably distro specific anyway)...better to show the actual lines being questioned.

​

2.  As the superuser, I can drop all databases other than the default ones.

I suspect that you can drop the default ones too if you try hard enough...

3.  The db for each user then must be created, and it takes special
handling to ensure each user is the only one who intially has all
privileges (except createdb and dropdb) for their db.

​"createdb for their db" doesn't make sense - its already been created.  "dropdb" can only be issued by the owner of the DB or a superuser.  I wouldn't call that "special handling".

Does all that sound correct (and reasonably secure)?

​At some point I'd probably discard pg_hba filtering (i.e., use "all" for database) and use SQL GRANTs to control access.  Especially for "local" connections.  I might go the added mile for "host" entries depending on the environment in which remote machines can see the database. But you can indeed rely on pg_hba.conf to define and enforce database "connect" privileges.