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

On Mon, Sep 18, 2017 at 15:14 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

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

Good: clear and very helpful.

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

I got that statement from the book "Beginning Databases with PostgreSQL" by
Neil Matthew and Richard Stones (2nd Edition, p. 55).

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

I really don't want to fool with that.

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

I'm just saying what I've observed from experimenting. I think that was
from using sql to create users and I had to create the databases in a
separate step. (I was following some recipes from "The Official Ubuntu
Server Book" by Kyle Rankin and Benjamin Mako Hill, 2nd Edition, p. 169.)
Using createuser I guess takes care of that.

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

Good, that's reassuring.

Thanks, David, you've been very helpful!

Best regards,

-Tom