On Mon, 2016-08-15 at 22:11 +0200, Paul Gevers wrote: > > - create only those objects as DB-admin-user, that need to be (e.g. > > other DB-users, the (empty) DBs, and so n)... and everything else > > (including populating the DBs) as the DB-(non-admin)-user > > > > AFAIU, you do the 2nd? > > I want to (upload pending). We're doing it already mostly. :-)
> >For security reasons I'd also prefer the 2nd, though there is > > probably > > not that much different from a security PoV, at least unless we > > assume > > that any debian package ships SQL code that may contain evil stuff. > > It does make a difference. I have had multiple request to enable > package > setup to remote db servers where the system administrator of the > system > installing the package doesn't know (and doesn't get) the > administrator > credentials of the database. Typically in such situations the > database > and the database user are already created and known to the system > administrator where the package is installing. That should be > sufficient > (and this is a security PoV as the installing system administrator > has > no need to now the database administrator credentials. But in these specific cases (i.e. installation to a remote system), the UNIX user under which we run psql/etc. locally shouldn't matter at al (that is unless the remote DB server would e.g. use identd based auth (with the local users), or similar things like GSSAPI). Apart from that, the remote system couldn't know which local UNIX user we use and should do auth based on password, IP, etc. Anyway, we both agree the 2nd way is the better one =) > > Anyway, for both I don't think there should be the need to test > > whether > > a corresponding system-user-name exists; that is at least for that > > types of databases where we know they have a mapping technique as > > postgresql does. > > So which system user should be used than? For sure it needs to be > something and I thing dbconfig doesn't have a choice, only the one > used > in the mapping works. I think you agree on this later in the mail. hmm... IMO the best thing would have been either: (1) There is a dedicated dbconfig user, this is the user under which any SQL, DB-clients, etc. are run, regardless of whether TCP or UNIX sockets, regardless of whether locally or remotely (2) This user is expected to be allowed to do all necessary things, like: creating users, databases, languages, populating schemas, upgrading schemas, etc. .... either by being that user (then using things like peer-auth of pgsql) or by plain password, in which case the dbconfig user is in principle moot, but it's better to use such user instead of root or postgresql to run these things, for the sake of privilege separation. (3) For certain operations (user creation, DB creation and similar), i.e. all the things a remote DB admin wouldn't typically grant normal users but supply them with,... dbconfig should: first check if that already exists (perhaps try to use it and see if that works - if possible) and if it does, not creating it again but simply continuing with the DB-non-admin-user stuff (i.e. creating tables, upgrading schemas, etc.) Perhaps a warning should be given, if the user/db already exists, asking whether one wants to (try to) continue (after all, the admin could have accidentally given you access to something). (4) For those auth methods, that aren't based on which UNIX user we run under, things would of course require some credentials (e.g. password) just as it's the case already now. And in general (i.e. also for the auth methods that are based on the UNIX user under which dbconfig runs its command: Appropriate pg_hba/pg_ident (and for other DBs similar) rules are expected to be in place. So while right now, in postgres, we depend e.g. on a line like this: local all postgres peer for creating users/DBs we would need one like this: local all dbconfig peer The above schema could be modified to: - use UNIX user AND DB-user "dbconfig" for everything (creation & population), expecting that it's allowed to do so by DB config. - use UNIX user AND DB-user "postgres" for creation but UNIX user "dbconfig" WITH the respective DB-user (like icinga) for population, expecting appropriate mappings in place - use UNIX user "dbconfig" WITH DB-user "postgres" for everything (creation & population), expecting that dbconfig maps to postgres =>bad, not even sure if this works... Pros: - we don't run any commands (locally) under UNIX users "postgres" or "root", which are not "our" (i.e. dbconfig's) domain and thus it's good for privilege separation. we don't run any commands under arbitrary other users like "icinga" or "phpbb" either, which are again not "our" users and we shouldn't su to them unless really necessary. - shouldn't make a difference whether remote DB or local, unless the remote user name is somehow transported to the remote side (GSSAPI, identd etc). Cons: - appropriate auth rules need to be in place on the DB server side, that allow the dbconfig user to do its business => could be provided by the packages in theory... - if another user (like postgres or dbconfig) is used to populate the databses with tables/etc., then the schemas must be carefully written to make everything properly owned not by postgres/dbconfig, but the actually intended DB user Perhaps even better then the above, would be hybrid between the above and the current way: 1) Allow the user to choose what is used for DB-admin-stuff: a) which UNIX user to use b) which DB user to use c) whether to expect that things (DB-users, databases) are already created, and that this shall be checked&verified and used. 2) Allow the user to choose what is used for DB-non-admin-stuff: a) which UNIX user to use b) which DB user to use And we could even provide sensible defaults depending on which users we find, or whether it was detected that a remote DB server was given (of course this is just heuristics). So in a remote server case, the user could choose: 1c 2a=dbconfig (in case the local UNIX user doesn't matter, because we don't do SSH socket forwarding or identd or similar). 3a=icinga In the local server case, one could have: 1a=postgres 1b=postgres 2a=icinga 2b=icinga Well... not sure if all the above really works out... it's basically just some start of brainstorming ;-) Perhaps another user category would be needed, e.g. "the DB user which shall own created objects", if that differs from 2b (if this is even possible or makes sense). > I'll read up on how PostgreSQL actually does this, but I would expect > that the system user starting the Unix connection MUST be the right > user > for the mapping. Yes this is the case. > > In the postgresql case we probably would need to use the UNIX > > system > > user "postgresql", as in the default pg_hba.conf "root" has no "DB- > > admin" permissions. > You can't rely on having the credentials of the postgres user as you > may > be working on a remote DB server. Sure... but that's a general problem isn't it? For those cases where we have no DB-admin rights, the remote server would need to be required to provide everything for us already (i.e. users, databases, and similar things)... and we just populate/upgrade the databases. Under which UNIX user we run this locally shouldn't matter much for the remote server (unless an auth method like postgresl's TCP-ident-with- ident*d* is used, or the local user has some other special rights to connect to remote server, which other users wouldn't have (perhaps a tunneled socket via SSH). > > But even in that case: > > Why would we need another question? AFAIU the following should > > work: > > - In order to create DBs/DB-usernames and stuff for which DB-admin- > > rights are needed: > > # sudo -u postgres psql doTheAdminStuff.sql > > True for local DB servers, but not for remote hosts. Same as above... if the remote (or local) server doesn't give you DB- amin-rights... we never can do anything about it... just check whether the databases/DB-users we want to use already exist and move on if they do. I don't see what difference it makes under which local UNIX user we run. > If you ask I want to remove the fall back, then, yes. +1 > > > > \"ON_ERROR_STOP=1\" -q -h 'localhost' -U 'icinga' icinga" 2>&1. > > > ^^^^ should have read icinga > > => well not really,... cause as in my case there is no (system)user > > icinga. > > That's just the thing you found out above (and which I needed so > > long > > to understand ^^)... either we ask the user for the user to connect > > as, > > or we always use one for which we know it exists and granted access > > (which would only be postgresql). > > Again, not for the remote system. But in case of the remote system, we never get the remote-SYSTEM-user context ever... we only connect via some networking, and do some authentication, right? > I'm tempted to say: > > - if unix-sockets are used AND the DB-normal-user doesn't exist as > > system user > > => ask for a connecting user for the "su". > > => in that case (cause the system user doesn't exist) we could > > either: > > - provide "postgres" as default, wich should-work™ > > - don't provide a default for that question at all, so the > > user > > would really need to find out. > > - do the later but mention in the documentation, that > > "postgres" should just work, but might have security > > implications > > OR > > - if not, do as it's done now > > We can choose (we do mention something like that in the FIRST > question > asked by dbconfig on behave of the package) to not implement this, > and > leave the system administrator to manual set up the configuration for > the package in this case. On the other hand, it doesn't look really > hard > to support this. Albeit a new question in the case of Sockets. > Interestingly you are the first to report on this for soooooo long... ;-) > > > > but: > > - the mapping system of postgresql not only works for UNIX sockets, > > but > > also TCP (e.g. in the case of GSSAPI) > > huh? Can you elaborate a bit? I don't really know that much about this either,.. I just read it in the postgres docs and GSSAPI and identd are mentioned. So I'd blindly assume that these two are used to map the local user into the remote user namespace. > Your second e-mail to this bug contained some information where you > tried to enable TCP login and it still failed for you because the > credentials were tried to be created with the $dbuser login. As TCP > is > the default answer, I am even more curious to know. Did this by any > chance happen during error handling? I may have found in the past few > months that there are situations where the $_dbc_asuser variable > isn't > properly restored. Ah I see... I'll try to reproduce that tomorrow... maybe I've just messed up some things :D Cheers, Chris.
smime.p7s
Description: S/MIME cryptographic signature
