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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [SPAM] [NOVICE] Users: must all Pg users be system users?
Дата
Msg-id CAKFQuwa8C-snAjSyB92FDu35i1md4AV3DgRxPHCJ5FOUr4U4rw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [SPAM] [NOVICE] Users: must all Pg users be system users?  (Tom Browder <tom.browder@gmail.com>)
Ответы Re: [SPAM] [NOVICE] Users: must all Pg users be system users?  (Tom Browder <tom.browder@gmail.com>)
Список pgsql-novice
Дерево обсуждения
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
​
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.

В списке pgsql-novice по дате отправления:

Предыдущее
От: Tom Browder
Дата:
Сообщение: Re: [SPAM] [NOVICE] Users: must all Pg users be system users?
Следующее
От: Tom Browder
Дата:
Сообщение: Re: [SPAM] [NOVICE] Users: must all Pg users be system users?