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

On Mon, Sep 18, 2017 at 11:05 Moreno Andreo <moreno.andreo@evolu-s.it> wrote:

> Il 17/09/2017 12:25, Tom Browder ha scritto:
> >
> > Can anyone point me to a good cookbook example or a detailed
> > discussion of a set up for allowing access to server services as well
> > as human users?
> Have you tried reading ph_hba.conf header and
> https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
> ?

I have, but I have trouble getting the picture of a fresh install
versus an existing system that I want to change to a pristine one.

> Keep in mind that when you create a postgreSQL role (not a system user)
> it can be used both by humans and by services, it's all about the
> configuration you provide.

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.

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.

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

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.  That is
hopefully taken care of by making my pg_hba.conf file look like this:

# TYPE       DATABASE      USER      ADDRESS      METHOD
local            sameuser         all                                   peer
local            all                    @adminspeer

(Taken from the 9.6 docs, pg_hba.conf example, but with method "peer"
instead of "md5".)

If the above is all true, then the next steps are probably to refine
privileges as necessary as the system and data grow and fancier
handling is required.  That would include perhaps using name maps in
pg_ident.conf to add all the databases owned by each user.

Does all that sound correct (and reasonably secure)?

Thanks, Moreno.

-Tom






>
>
> HTH,
> Moreno.-
>


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice