Hi On 15-08-16 00:02, Christoph Anton Mitterer wrote: > On Sun, 2016-08-14 at 11:30 +0200, Paul Gevers wrote: > 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.
Fully ack. Fall-back to TCP was by accident, not by design. >> 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? 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. > 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. >> 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 This. and.... > Or do you mean connecting with that DB-admin-user, i.e. > (as root): # psql --username=myDBadminUser -f package-schemas.sql this. We do the combination already. > 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. 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. Again, I read you write something similar later in your mail. > 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. > 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. > - 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). Right. > 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. Indeed. >> 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. If you ask I want to remove the fall back, then, yes. >> 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. Right. >>> \"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. > => also it shouldn't to -h 'localhost", but rather something like: > -h "/var/run/postgresql" -p 5342... This is due to the wrong "fall back", it isn't even fall-backing, but doing it always wrong. > 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? > => 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. Good to know. >> Could you also create the debugging info for the TCP issue you >> reported? > Which TCP issue exactly do you mean now? 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. Paul
signature.asc
Description: OpenPGP digital signature
