Thread: inconsistent owners in newly created databases?
Dear hackers, It seems to me that the current default setup for a new database which is given to some user is not consistent (createdb -O calvin foo or CREATE DATABASE foo WITH OWNER calvin). Indeed, although the database belongs to the owner, the "public" schema still belongs to the database super user, as it was the case in template1. As a consequence, the owner of the database CANNOT change the rights of the schema, hence he cannot prevent anyone from creating a new table in the public schema! However, has he owns the database, he can prevent user from creating temporary tables... Not really consistent. Dropping (the owner of a database can do that) and recreating the schema is not a real fix, because all installation performed on template1 (plpgsql, functions...) would be lost. So it seems to me that the "public" schema should also belong to the owner of the database. I cannot foresee all consequences, but the current situation is really inconsistent. Any comment? -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote:
>Dear hackers,
>
>It seems to me that the current default setup for a new database which is
>given to some user is not consistent (createdb -O calvin foo or
>CREATE DATABASE foo WITH OWNER calvin).
>
>Indeed, although the database belongs to the owner, the "public" schema
>still belongs to the database super user, as it was the case in template1.
>As a consequence, the owner of the database CANNOT change the rights of
>the schema, hence he cannot prevent anyone from creating a new table in
>the public schema! However, has he owns the database, he can prevent user
>from creating temporary tables... Not really consistent.
>
>
This is a real problem if that owner wants to drop or create types,
operators, or precreated tables in the template that was copied. It
seems that you would want to go through and give the owner all the
ownership on items that were possible. I've used a database template
with the pg_crypto added in and some other custom routines and found
that the owner of the database couldn't update or access those copied
tables because of the permission on those tables and objects.
* create the database with the new owner specified.
-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
update pg_type set typowner = {userid}
Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.
>Dropping (the owner of a database can do that) and recreating the schema
>is not a real fix, because all installation performed on template1
>(plpgsql, functions...) would be lost.
>
>So it seems to me that the "public" schema should also belong to the owner
>of the database. I cannot foresee all consequences, but the current
>situation is really inconsistent.
>
>Any comment?
>
>
>
Dear Thomas,
> * create the database with the new owner specified.
>
> -- As a superuser in the newly created database
> update pg_am set amowner = {userid}
> update pg_class set relowner = {userid}
You don't want to update ownership of tables in system schemas.
> update pg_conversion set conowner = {userid}
> update pg_namespace set nspowner = {userid}
As for SCHEMAs, I would not do that for system schemas
(pg_%, information_schema)...
> update pg_opclass set opcowner = {userid}
> update pg_operator set oprowner = {userid}
> update pg_proc set proowner = {userid}
I'm not sure system functions owner should be change. Also, call handlers
for languages should not change owner.
> update pg_type set typowner = {userid}
>
> Are there any security problems that this would cause? Perhaps these
> should be done by the system automatically.
I think that something along the line you describe should be done by the
system. However database creation does not actually connect to the new
database, the template base directory is simply copied with a "cp -r".
It is unclear to me at the time when these updates should be performed.
After the createdb? Deferred to the first connection to the database?
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote:
>Dear Thomas,
>
>
>
>>* create the database with the new owner specified.
>>
>>-- As a superuser in the newly created database
>>update pg_am set amowner = {userid}
>>update pg_class set relowner = {userid}
>>
>>
>
>You don't want to update ownership of tables in system schemas.
>
>
>
AFAICS, any changes they make are localized to their database not the
whole database system. In other words, they can change add drop types,
procs, tables as if they were a superuser but only in their database.
A normal account (the db owner in this case) still cannot select against
pg_shadow or add users (without createdb privilege).
>>update pg_conversion set conowner = {userid}
>>update pg_namespace set nspowner = {userid}
>>
>>
>
>As for SCHEMAs, I would not do that for system schemas
>(pg_%, information_schema)...
>
>
>
It doesn't seem any different than running as the superuser and changing
those. Again, I think it would be restricted to someone frying their
own database, but not the whole system.
>>update pg_opclass set opcowner = {userid}
>>update pg_operator set oprowner = {userid}
>>update pg_proc set proowner = {userid}
>>
>>
>
>I'm not sure system functions owner should be change. Also, call handlers
>for languages should not change owner.
>
>
Without this the db owner cannot drop types that may have been copied
from the template.
>
>
>>update pg_type set typowner = {userid}
>>
>>Are there any security problems that this would cause? Perhaps these
>>should be done by the system automatically.
>>
>>
>
>I think that something along the line you describe should be done by the
>system. However database creation does not actually connect to the new
>database, the template base directory is simply copied with a "cp -r".
>
>It is unclear to me at the time when these updates should be performed.
>After the createdb? Deferred to the first connection to the database?
>
>
>
It seems the logical place is for the createdb routine to connect to the
new database and make the ownership changes.
Thomas Swan <tswan@idigx.com> writes:
> Fabien COELHO wrote:
>> You don't want to update ownership of tables in system schemas.
>>
> AFAICS, any changes they make are localized to their database not the
> whole database system.
A database owner who is not a superuser should *not* be able to fool with
the built-in catalog entries.
Database owner != superuser, and I don't want us blurring the distinction...
regards, tom lane
Tom Lane wrote: >Thomas Swan <tswan@idigx.com> writes: > > >>Fabien COELHO wrote: >> >> >>>You don't want to update ownership of tables in system schemas. >>> >>> >>> >>AFAICS, any changes they make are localized to their database not the >>whole database system. >> >> > >A database owner who is not a superuser should *not* be able to fool with >the built-in catalog entries. > >Database owner != superuser, and I don't want us blurring the distinction... > > > With regards to changing ownership, is there a way to determine what is a "built-in" catalog entry? If the database that was the template was modified, can the createdb routine determine what is core and what was added?
> ... > Without this the db owner cannot drop types that may have been copied > from the template. Hmmm. I'm concerned about security, such as enabling the owner to load new trusted code. You may be right, but I'm afraid it is delicate to decide what owner fields should be changed. Owning a database does not mean being a super user in that database. But I may be just pessimistic about this issue. > >It is unclear to me at the time when these updates should be performed. > >After the createdb? Deferred to the first connection to the database? > > It seems the logical place is for the createdb routine to connect to the > new database and make the ownership changes. Yes, I agree. However I have not seen a simple api to create a new backend connected to another database and make it execute some sql commands. The fork/exec stuff is managed by postmaster (the server frontend) directly. But I've just given a quick look. Also, how should it deal with max allowed connections and so on... Hence deferring the stuff to the first connection may not be that bad, because it would avoid a lot of system stuff. Well, anyway someone agree with me that the situation is not appropriate. Thanks for your comments, -- Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien.Coelho@ensmp.fr CRI-ENSMP, 35, rue Saint-Honoré, 77305 Fontainebleaucedex, France phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08} ________ All opinionsexpressed here are mine _________
> A database owner who is not a superuser should *not* be able to fool with > the built-in catalog entries. > > Database owner != superuser, and I don't want us blurring the distinction... Yes sure. I agree, especially if the owner is one of my students;-) However, I feel that the owner should own the "public" schema and maybe some other stuff to be carefully selected, without bluring that important distinction? -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> However, I feel that the owner should own the "public" schema and maybe
> some other stuff to be carefully selected, without bluring that important
> distinction?
From a definitional standpoint I don't have a problem with that. From
an implementation standpoint, I fear it would be much more trouble than
it is worth. You can't easily connect to another database.
Possibly it would work to have this housekeeping done in the first
backend to connect to the new database, but I don't think it could be
done directly by CREATE DATABASE.
regards, tom lane
Dear Tom, > > However, I feel that the owner should own the "public" schema and maybe > > some other stuff to be carefully selected, without bluring that important > > distinction? > > From a definitional standpoint I don't have a problem with that. Good. > From an implementation standpoint, I fear it would be much more trouble > than it is worth. What is worth is having a sound tool with as few "little" surprises as possible. There are a lot of little surprises in pg. None of them worth the trouble, but collectively, this would make pg adoption easier. I've some time for this kind of small scale fix, but as this is not the main agenda here around, and it is very difficult to pass even small things. > You can't easily connect to another database. That's what I derived from browsing the source code. > Possibly it would work to have this housekeeping done in the first > backend to connect to the new database, but I don't think it could be > done directly by CREATE DATABASE. Well, this describe basically my feeling about the implementation. If I have some time I may send a proof of concept implementation. I though of the following lines: - in createdb, add a some "todo" file into the new database directory with the set of sql commands to be executed on thefirst connection. - on a connection in postgres backend, (1) check for this todo file (2) if it exists, lock something appropriate [orcould be directly in (1)?] if the file [still] exists, execute these commands as a super user removethe file unlock (3) back to normal processing This approach would it make easy to change/update the housekeeping updates if necessary. The set of appropriate sql commands in still to be discussed... Another heavier but more general approach would be to add a boolean to pg_database to tell whether the first connection housekeeping was performed, and maybe to maintain the set of queries to be executed in another system table, so that it could be updated by modules that would need it. This would be some kind of sql-hook for database creation events. I'm rather inclined to try the former option;-) -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Another heavier but more general approach would be to add a boolean to
> pg_database to tell whether the first connection housekeeping was
> performed,
I was envisioning a bool column added to pg_database, and having the set
of operations just hard-coded into the backend. The only input
information the process needs is the DB owner's ID, which it can of
course get from the pg_database row. I doubt that reading a file of SQL
commands is easier --- the file would have to be created somehow, and
since stuff would have to be interpolated into it (owner's ID) you'd end
up with a lot of mechanism that's very different from anything else in
the backend.
One definitional issue that remains to be resolved is "just what is the
public schema anyway?". It is not a built-in object in the same sense
that pg_catalog is. It could be deleted, or even deleted and
re-created. So you certainly have to be prepared for the possibility of
it not being there (strike one for the simple "file of SQL commands").
What I want to know is whether we want to forcibly change owner of any
random schema that happens to be named "public"? Or should we insist on
it having the original OID? Or some other way of identifying it?
In the same vein: we probably need to alter the ACL for public so that
its privileges appear to flow from the object owner and not from the
postgres user. What do we do if the ACL is in a non-default state?
regards, tom lane
Dear Tom, > > Another heavier but more general approach would be to add a boolean to > > pg_database to tell whether the first connection housekeeping was > > performed, > > I was envisioning a bool column added to pg_database, > and having the set of operations just hard-coded into the backend. Why not. indeed it simplifies as it avoids the intermediate file. Also, if there is no objection to modify a system catalog, it is fine for me. > The only input information the process needs is the DB owner's ID, > which it can of course get from the pg_database row. Sure. > I doubt that reading a file of SQL commands is easier. I agree, you're idea is simpler. > One definitional issue that remains to be resolved is "just what is the > public schema anyway?". What I want to know is whether we want to > forcibly change owner of any random schema that happens to be named > "public"? Or should we insist on it having the original OID? Or some > other way of identifying it? > In the same vein: we probably need to alter the ACL for public so that > its privileges appear to flow from the object owner and not from the > postgres user. I was thinking about something fuzzy enough as: UPDATE pg_catalog.pg_namespace SET nspowner=datdba, nspacl=NULL -- NULL means default rights... FROM pg_catalog.pg_database WHERE nspname NOT LIKE ALL(ARRAY['pg_%','information_schema']) AND datname=CURRENT_DATABASE(); But it can be changed to anything else... > What do we do if the ACL is in a non-default state? Well, one could suggest to fix the aclitem grantor to the owner, but as an array of opaque type it is not very easy to manipulate from a query. Or it could be a feature that non system schemas belong to the owner and are initialized with the default rights, just as they would have been if created by the owner with a "CREATE SCHEMA"... The later is simple and makes sense anyway for a newly created database. -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I was thinking about something fuzzy enough as:
> UPDATE pg_catalog.pg_namespace
> SET nspowner=datdba, nspacl=NULL -- NULL means default rights...
> The later is simple and makes sense anyway for a newly created database.
No, I don't think it does. The DBA presently can set up a site-wide
policy about use of "public" by altering its permissions in template1.
For example, he might revoke create access from most users. People will
be surprised if that fails to carry over to created databases.
regards, tom lane
Dear Tom, > > UPDATE pg_catalog.pg_namespace > > SET nspowner=datdba, nspacl=NULL -- NULL means default rights... > > The later is simple and makes sense anyway for a newly created database. > > No, I don't think it does. The DBA presently can set up a site-wide > policy about use of "public" by altering its permissions in template1. > For example, he might revoke create access from most users. People will > be surprised if that fails to carry over to created databases. Ok, I understand that. So that would mean switching all grantors to the owner in the aclitem array? Maybe some function would be useful for that, so as to stick to SQL: UPDATE pg_namespace SET nspowner = datdba, nspacl = aclitems_switch_grantor(nspacl, datdba) FROM ... WHERE ...; but I'm not sure adding such an horrible "user" function in pg_proc would be welcome, as aclitem accessors were removed two days ago. The alternative is to do it in C within the backend, but I would have liked the plain SQL better. Just a mater of taste, I guess. Pg backend philosophy: why writing SQL if you can do it in C? ;-) I'll have a look at it if I have time, maybe over the week-end. Thanks for your insight. -- Fabien Coelho.
Fabien COELHO <coelho@cri.ensmp.fr> writes: > nspacl = aclitems_switch_grantor(nspacl, datdba) Instead of having a hard coded list of template1 objects that need to be chowned to the database owner. Perhaps there should be a special user like dbowner which owns the schema and whatever other objects are necessary. Then createdb would chown over anything owned by dbowner but not by objects owned by postgres. This would have the advantage that a dba could add objects to template1 and choose whether to set them to be owned by postgres or owned by dbowner. Then create various databases owned by different users and automatically have the selected template objects be owned by the database owner. -- greg
Dear Greg, > > I agree with the advantage. > > > > But I'm uneasy to know what a special owner would be, pratically speaking. > > Well I can't think of anywhere else in the code that would need this special > case other than creating a database. I disagree, there are consequences. That could be overcome, but I just argue that is not "that" simple. For instance: It means the default setup would have a new user entry for that purpose. aclitem's are defined by refering to the user number for grantor and possibly grantee. It is unclear how the user could change the grantee/grantor of an entry for that purpose. There is no simple sql interface to access or modify aclitem entries, it is implemented down GRANT/REVOKE at the time. Also, could the "special" account be used as a login? If not, how to prevent it? Moreover, I'm not convinced yet that this fine granularity of control is actually required. Well, this opinion may change later! The last good point is that this changes are quite independent from putting a hook to modify the initial setup on the first connexion. Thus I can go ahead about the hook, and think about this later. If this is seen as useful, then that would just mean that "what is done" by the hook need be updated. Thanks for your point, have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr
> > nspacl = aclitems_switch_grantor(nspacl, datdba) > > Instead of having a hard coded list of template1 objects that need to be > chowned to the database owner. Perhaps there should be a special user like > dbowner which owns the schema and whatever other objects are necessary. > [...] I agree with the advantage. But I'm uneasy to know what a special owner would be, pratically speaking. If it would mean that everywhere in the source code where an owner is manipulated, there must be some kind of special test for that case, I'm not sure it would be that great... Also any database can be used as a template, not just template1. Moreover, template1 is a still usable database, that can be set with whatever you want in new created, so it is not "that" special... Well, that's grain for thoughts anyway;-) Thanks, have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes: > I agree with the advantage. > > But I'm uneasy to know what a special owner would be, pratically speaking. > If it would mean that everywhere in the source code where an owner is > manipulated, there must be some kind of special test for that case, I'm > not sure it would be that great... Well I can't think of anywhere else in the code that would need this special case other than creating a database. My thinking is it would otherwise act as a special user except when you're copying a database it would get mean "set the owner of this object to the owner of the new database". > Also any database can be used as a template, not just template1. > Moreover, template1 is a still usable database, that can be set with > whatever you want in new created, so it is not "that" special... Well perhaps it should be an option on create database? create database foo with template=template1 owner=bar templateowner=baz Or perhaps it should just default to the name of the template database maybe the owner of the template database. So these objects would be owned by user "template1" in the template1 database. Then If I create a database with create database user2 with template=user1 owner=user2 then any objects owned by user1 in the template become owned by user2 in the new database. Using the owner of the template database has the advantage that you can copy a copy of a database and get the same result as if you copied the original. Eg, this would work: create database template2 with template=template1 owner=template2create database template3 with template=template2 owner=template3 and the result would be the same as create database template3 with template=template1 owner=template3 -- greg