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

On Mon, Sep 18, 2017 at 12:24 PM, Tom Browder <tom(dot)browder(at)gmail(dot)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
​

> But in general, then, for only local users and services and a clean
> out of an old system, is the following true:
>
> 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...you can certainly drop "postgres"; template0 and template1 are a
bit trickier (I think).

> 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.

David J.