Thread: [NOVICE] Users: must all Pg users be system users?
I flat do not understand the user definitions for a PostgreSQL server, and my limited experience with running Pg has been a result more of trial and error than understanding.
For example, I have a server which is running Apache. How must I set upt pg_hba.conf so the Apache user can access Pg? Likewise for Mailman, etc.
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?
Thanks so much.
Best regards,
-Tom
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
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.
On Mon, Sep 18, 2017 at 15:14 David G. Johnston <david.g.johnston@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 presentedpassword = 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
Using createuser I guess takes care of that.
No, creation of roles (users and/or groups) and databases are completely independent - whether you use SQL directly or the shell wrappers.
The reason they seem related is that by convention, for the provided shell tools, if one does not specify a database name explicitly the connection routine will fill in the unknown with the name of the (if applicable, mapped) user. But this is only convention/convenience and, for an application developer, not particularly useful since usually I'll have one application database and multiple authenticated users with varying roles within my application.
David J.