Post by Paul GeversPost by Christoph Anton Mitterer- 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.
:-)
Post by Paul GeversPost by Christoph Anton MittererFor 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 =)
Post by Paul GeversPost by Christoph Anton MittererAnyway, 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).
Post by Paul GeversI'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.
Post by Paul GeversPost by Christoph Anton MittererIn 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).
Post by Paul GeversPost by Christoph Anton Mitterer- In order to create DBs/DB-usernames and stuff for which DB-admin-
 # 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.
Post by Paul GeversIf you ask I want to remove the fall back, then, yes.
+1
Post by Paul GeversPost by Christoph Anton MittererPost by Paul GeversPost by Christoph Anton Mitterer\"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?
Post by Paul GeversPost by Christoph Anton Mitterer- 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
        - 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...
;-)
Post by Paul GeversPost by Christoph Anton Mitterer- 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.
Post by Paul GeversYour 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.