Hey Paul. On Sun, 2016-08-14 at 11:30 +0200, Paul Gevers wrote: > Control: clone -1 -2 -3 ouch... I didn't want to cause you so much work ;)
> And just to be sure, I assume everything we discuss here is done with > the backported version of dbconfig. There are too many changes since > Jessie to investigate that now (I may check once we identified all > issues). Yes,... # dpkg -l | grep dbconfig ii dbconfig-common 2.0.4~bpo8+1 all framework that helps packages to manage databases ii dbconfig-pgsql 2.0.4~bpo8+1 all dbconfig-common PostgreSQL support > > It thinks the account doesn't exist (which is correct, but doesn't > > really matter) and I'd blindly assume now it falls back to TCP for > > this > > reason? > > Yes, it does that by design and it does really matter. Well but as I've laid out before, when one uses pg_ident, than there's no need that a system user of the same name exists... > However, as seen > in this bug, I guess it should just fail instead, right? Well depends a bit... in the end it should just try with the user name I specified and that via UNIX sockets. Or perhaps it could warn the user like "hey, the username you gave doesn't exist as a system user name, so perhaps this may fail,.. still wanna continue?"? If the admin wasn't then careful enough to set up some mapping with e.g. pg_ident than it's his/her fault. In general though, I think that any auto-magical fall-back from a user- specified "use sockets" to TCP (or vice versa) is not something that should be even tried, regardless of which condition may arise or not. Consider the example where a user runs two different DB servers, one only listening on sockets, the other only on TCP,... both using the same port/socket-number. If dbconfig then falls back to the other, it may even destroy data, because the user intended that it would only connect to the other. Sure, this is a rather stupid setup, but if the user chose "use sockets" or "use TCP", than IMO only this should be done. > We fill the > database with the database user credentials to avoid the need for the > administrator credentials (requiring the administrator credentials > for > anything that isn't strictly needing it is a bug¹ that I intent to > close > soon), so using the administrator credentials to solve this issue is > a > no-go. Let me see whether I understand: "Normally" (or rather what I considered normal ;) ), I'd have expected that, when a tool like dbconfig creates DBs/DB-users respectively populates/upgrades schemas it would either: - do everything as DB-admin-user (which for Postgres is (per default) the DB-username "postgresl", which happens to be granted (per default) for the system user "posgresql")... BUT with owners set to the respective DB-(non-admin)-user OR: - 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? 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. 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. > I wasn't aware of the possibility to have system-user to > postgresql-user mapping, so I am afraid that we need an additional > debconf question to ask for the system-user name that will be used > for the connection in case of ident/peer. I don't understand why this should be needed. What do you mean by " the system-user name that will be used for the connection in case of ident/peer"? Do you mean that you connect *as* this system-user, i.e. doing something like: # sudo -u mySystemUser psql -f package-schemas.sql Or do you mean connecting with that DB-admin-user, i.e. (as root): # psql --username=myDBadminUser -f package-schemas.sql ? AFAIU, dbconfig should never need to use the actual (UNIX) system users (perhaps with the exception of the system user "postgres"), but only always work with the DB-users, and expect that the user has a pg_hba.conf (or similar for other DBs), that grants dbconfig with the necessary access permissions. 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. 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 - In order to create everything else for: ... okay here I see the problem... it couldn't just run doTheNormalStuff.sql as root... well it could but no package using dbconfig expects this right now, so no access permissions would have been set up. So either you do everything as DB-user/system-user postgres or something like: # sudo -u normalUser psql doTheNormalStuff.sql is needed, which then requires the normal user (unless there is mapping). hmm... well a) I think this could be a hint towards a design problem: Neither dbconfig, nor packages should expect that system-users matching the DB-user names exist (there's no reason for such restriction). psql should have rather always been run *as* either UNIX user "root" or perhaps "postgres"... AND connections should have been made as DB-admin-user "postgres" respectively "<normal-user(s)>" and pg_hba entries being expected to be in place (or perhaps added automatically, which is however dangerous IMO) Okay, now I also kinda understand why you want to ask for the system user name that should be used for connecting. In order to use the existing mapping rules to execute the doTheNormalStuff.sql. Well one kinda could do this as system-user-postgresql as in # sudo -u postgres psql --username=normalUser doTheAdminStuff.sql which you want to avoid for security reasons... > After writing the above, I found where the fallback to TCP happens: > the > logic of _dbc_psql_cmd_args checks for the password instead of the > method. Should be easy to fix, but alone it will change the fallback > from TCP/$dbc_dbuser to ident/root So, you generally drop the fall back? I think that would be good, as outlaid above. > and still fail (I think). Unless you connect as "postgresql" or the user would have added some pg_hba rule allowing system user root to do the stuff, probably yes. > > populating database via sql... warning: ident method specified but > > local account doesn't exist. > > warning: ident method specified but local account doesn't exist. > > su -s /bin/sh root -c "env HOME='/tmp/dbconfig- > > common.psql_home.RIlqrF' PGPASSFILE='/tmp/dbconfig- > > common.psql_home.RIlqrF/.pgpass' PGSSLMODE='prefer' psql --set > > \"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). => also it shouldn't to -h 'localhost", but rather something like: -h "/var/run/postgresql" -p 5342... 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 but: - the mapping system of postgresql not only works for UNIX sockets, but also TCP (e.g. in the case of GSSAPI) => does this affect us, i.e. do we somehow need to take care for the TCP case as well?? - and I wonder whether there can be situations in which the system user *does* exist, but pg_hab could result into a different DB-user based on the configured mappings. => probably, as long as you always use -U normalUser to psql, we're fine. > Could you also create the debugging info for the TCP issue you > reported? Which TCP issue exactly do you mean now? Cheers, Chris.
smime.p7s
Description: S/MIME cryptographic signature
