Re: Schema (namespace) privilege details

I've been thinking about exactly what to do with access privileges for
namespaces (a/k/a schemas). The SQL99 spec isn't much guidance, since
as far as I can tell it doesn't have explicit privileges for schemas
at all --- and in any case, since it identifies schemas and ownership,
the really interesting cases don't arise.

Here is a straw-man definition --- any comments appreciated.

We'll define two privilege bits for namespaces/schemas: "read" and
"create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
choices). "Read" controls the ability to look up objects within
that namespace --- it's similar to "execute" permission on directories
in Unix. "Create" controls the ability to create new objects within
a namespace. As usual, superusers bypass these checks.

The initial state of the database will be: pg_catalog is world readable,
but has no create permissions; public has world read and create
permissions; pg_toast has no permissions (you can't explicitly inspect
toast tables). Newly created schemas will initially have all permissions
for the owner, no permissions for anyone else. Whenever a pg_temp
namespace is created or recycled by a fresh backend, it will be set to be
owned by the user running that backend, with all permissions for him and
none for anyone else.

Renaming of an object is allowed to the owner of that object regardless of
schema permissions. While we could invent an UPDATE privilege on schemas
to control this, leaving it with the owner seems simpler.

Deletion of an object is allowed either to the owner of the object, or to
the owner of the containing schema. (Without the latter provision, you
couldn't DROP a schema containing objects created by other people; which
seems wrong.) Again, I'd rather keep this based on ownership than invent,
say, a DELETE privilege for schemas.

It's not quite clear what should happen if User A allows User B to create
an object in a schema owned by A, but then revokes read access on that
schema from B. Presumably, B can no longer access the object, even though
he still owns it. A would have the ability to delete the object under
these rules, but is that enough?

One of the things I'd like this mechanism to do is answer the request
we've heard so often about preventing users from creating new tables.
If the DBA revokes write access on the public namespace from a particular
user, and doesn't create a personal schema for that user, then under this
proposal that user would have noplace to create tables --- except TEMP
tables in his temp schema. Is that sufficient, or do the folks who want
this also want a way to prevent TEMP table creation?

Another thing that would be needed to prevent users from creating new
tables is to prevent them from creating schemas for themselves. I am not
sure how to handle that --- should the right to create schemas be treated
as a user property (a column of pg_shadow), or should it be attached
somehow to the database (and if the latter, how)?

As sketched so far, the schema privilege bits would be the same for all
object types --- whether table, type, function, or operator, either you
can look it up (resp. create it) in a given namespace, or you can't.
Offhand I see no need to distinguish different kinds of objects for this
purpose; does anyone think differently?

Should the owner of a database (assume he's not a superuser) have the
right to drop any schema in his database, even if he doesn't own it?
I can see arguments either way on that one.

regards, tom lane

Hi Tom,

> One of the things I'd like this mechanism to do is answer the request
> we've heard so often about preventing users from creating new tables.
> If the DBA revokes write access on the public namespace from a particular
> user, and doesn't create a personal schema for that user, then under this
> proposal that user would have noplace to create tables --- except TEMP
> tables in his temp schema. Is that sufficient, or do the folks who want
> this also want a way to prevent TEMP table creation?

I can't think of a reason that temp tables should be prevented. Being able
to prevent a user from creating permanent objects is good IMHO.

> Another thing that would be needed to prevent users from creating new
> tables is to prevent them from creating schemas for themselves. I am not
> sure how to handle that --- should the right to create schemas be treated
> as a user property (a column of pg_shadow), or should it be attached
> somehow to the database (and if the latter, how)?

Connecting this right to a database sounds like the right thing to do. (ISP
case: allow a user to do with his database whatever he wants, as long as he
stays away from other databases) But I don't know a good way to do it...

> Should the owner of a database (assume he's not a superuser) have the
> right to drop any schema in his database, even if he doesn't own it?
> I can see arguments either way on that one.

I think that if he owns it, he should be able to control it... Someone
owning a database should be responsible enough to manage it.

I hope these comments can help you,
Sander.

> Should the owner of a database (assume he's not a superuser) have
the
> right to drop any schema in his database, even if he doesn't own it?
> I can see arguments either way on that one.

Given that you've chosen to allow the owner of a schema or the table
to drop a table, it would be consistent to allow the owner of the
database, schema or table to drop the table.

Much as I'd tend to allow the owner of a trigger, the table it's on,
the schema, or the database to drop the trigger.

Technically if the owner of a database doesn't have permission to drop
a table, do they have permission to drop the database? In which case,
pg_dump, drop create table statement, drop db, create db, restore data
will accomplish the same thing. All we've done is make the process
long and drawn out.

"Rod Taylor" <rbt(at)zort(dot)ca> writes:
>> Should the owner of a database (assume he's not a superuser) have the
>> right to drop any schema in his database, even if he doesn't own it?
>> I can see arguments either way on that one.

> Given that you've chosen to allow the owner of a schema or the table
> to drop a table, it would be consistent to allow the owner of the
> database, schema or table to drop the table.

> Much as I'd tend to allow the owner of a trigger, the table it's on,
> the schema, or the database to drop the trigger.

Hmm, interesting analogy. I don't much like the idea of allowing a
non-owner of a table to drop a trigger; that could lead directly to
data consistency problems, etc. I was envisioning granting the
schema owner the right to drop another user's table in toto --- but not
to have ownership rights to mess with its innards.

That would suggest that a database owner should be allowed to drop a
schema in toto, but not to selectively drop objects within it. Just
as with a table, a schema might have some consistency requirements that
would be broken by zapping individual elements.

> Technically if the owner of a database doesn't have permission to drop
> a table, do they have permission to drop the database? In which case,
> pg_dump, drop create table statement, drop db, create db, restore data
> will accomplish the same thing. All we've done is make the process
> long and drawn out.

If the owner is not superuser, he does not have the privileges to do
dump and restore --- even if he can read everything to dump it, he
won't be allowed to recreate objects under other people's names. So
this analogy is faulty.

However, the database owner definitely does have the right to drop the
whole database, so at some level he should have the right to drop
contained objects. The question is, how selectively can he do it?

regards, tom lane

Tom Lane writes:

> We'll define two privilege bits for namespaces/schemas: "read" and
> "create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
> choices). "Read" controls the ability to look up objects within
> that namespace --- it's similar to "execute" permission on directories
> in Unix. "Create" controls the ability to create new objects within
> a namespace. As usual, superusers bypass these checks.

I think other databases actually use GRANT CREATE.

About the read permission, I think that other databases use the rule that
you can "see" an object if and only if you have some sort of privilege on
it. I see little reason to create an extra privilege to just see the
existence of objects.

> It's not quite clear what should happen if User A allows User B to create
> an object in a schema owned by A, but then revokes read access on that
> schema from B. Presumably, B can no longer access the object, even though
> he still owns it. A would have the ability to delete the object under
> these rules, but is that enough?

That concern would be eliminated by the system above. B can still access
anything it owns. If A doesn't like B anymore, just delete B's stuff in
A's schemas.

> One of the things I'd like this mechanism to do is answer the request
> we've heard so often about preventing users from creating new tables.
> If the DBA revokes write access on the public namespace from a particular
> user, and doesn't create a personal schema for that user, then under this
> proposal that user would have noplace to create tables --- except TEMP
> tables in his temp schema. Is that sufficient, or do the folks who want
> this also want a way to prevent TEMP table creation?

Maybe the temp schema should be a permanent catalog entry. That way the
DBA can revoke create access from it as a means to disallow users to
create temp tables.

> Another thing that would be needed to prevent users from creating new
> tables is to prevent them from creating schemas for themselves. I am not
> sure how to handle that --- should the right to create schemas be treated
> as a user property (a column of pg_shadow), or should it be attached
> somehow to the database (and if the latter, how)?

An aclitem[] column on pg_database seems like the most flexible solution
to me.

> Offhand I see no need to distinguish different kinds of objects for this
> purpose; does anyone think differently?

Not me.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Tom Lane wrote:
> We'll define two privilege bits for namespaces/schemas: "read" and
> "create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
> choices). "Read" controls the ability to look up objects within
> that namespace --- it's similar to "execute" permission on directories
> in Unix. "Create" controls the ability to create new objects within
> a namespace. As usual, superusers bypass these checks.

If user1, the owner of the schema1, creates a new table tab1, will user2
who has "Read" privilege to schema1, be automatically granted SELECT
privilege on tab1? Or will he be able to see that tab1 exists, but not
select from it (continuing the analogy with directories)?

>
> The initial state of the database will be: pg_catalog is world readable,
> but has no create permissions; public has world read and create
> permissions; pg_toast has no permissions (you can't explicitly inspect
> toast tables). Newly created schemas will initially have all permissions
> for the owner, no permissions for anyone else. Whenever a pg_temp
> namespace is created or recycled by a fresh backend, it will be set to be
> owned by the user running that backend, with all permissions for him and
> none for anyone else.

This looks good to me. I only wonder if public should default to world
read and no create?

> Renaming of an object is allowed to the owner of that object regardless of
> schema permissions. While we could invent an UPDATE privilege on schemas
> to control this, leaving it with the owner seems simpler.

Agreed.

>
> Deletion of an object is allowed either to the owner of the object, or to
> the owner of the containing schema. (Without the latter provision, you
> couldn't DROP a schema containing objects created by other people; which
> seems wrong.) Again, I'd rather keep this based on ownership than invent,
> say, a DELETE privilege for schemas.

I'd agree with other posted comments -- db owner should also be
essentially a superuser in there own db.

>
> It's not quite clear what should happen if User A allows User B to create
> an object in a schema owned by A, but then revokes read access on that
> schema from B. Presumably, B can no longer access the object, even though
> he still owns it. A would have the ability to delete the object under
> these rules, but is that enough?

I like this. That way I can lock out a particular user if I need to with
a single command. Would A automatically get ALL privileges on objects
created in his schema by others? I think he should.

>
> One of the things I'd like this mechanism to do is answer the request
> we've heard so often about preventing users from creating new tables.
> If the DBA revokes write access on the public namespace from a particular
> user, and doesn't create a personal schema for that user, then under this
> proposal that user would have noplace to create tables --- except TEMP
> tables in his temp schema. Is that sufficient, or do the folks who want
> this also want a way to prevent TEMP table creation?

I think there should be a way to prevent temp table creation, but not
set that way as the default. Presumably you could REVOKE INSERT on the
temp schema?

>
> Another thing that would be needed to prevent users from creating new
> tables is to prevent them from creating schemas for themselves. I am not
> sure how to handle that --- should the right to create schemas be treated
> as a user property (a column of pg_shadow), or should it be attached
> somehow to the database (and if the latter, how)?

I think only the database owner should be able to create schemas in
their own database. That way if I want a user to be able to create
tables, I just grant them CREATE in the public schema, or create a
schema for them.

>
> As sketched so far, the schema privilege bits would be the same for all
> object types --- whether table, type, function, or operator, either you
> can look it up (resp. create it) in a given namespace, or you can't.
> Offhand I see no need to distinguish different kinds of objects for this
> purpose; does anyone think differently?
>

Agreed. How would it work though if say I wanted to create a view in the
public schema, which pointed at a table in a schema which has had SELECT
revoked? Same question for a public function/private table. It would be
ideal if you could do this.

> Should the owner of a database (assume he's not a superuser) have the
> right to drop any schema in his database, even if he doesn't own it?
> I can see arguments either way on that one.
>

I think the database owner should be just like a superuser in his little
world. The db owner should be able to drop contained schemas or other
objects at will.

Just my 2 cents.

Joe

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> We'll define two privilege bits for namespaces/schemas: "read" and
>> "create" (GRANT SELECT and GRANT INSERT seem like reasonable keyword
>> choices).

> I think other databases actually use GRANT CREATE.

Okay, I'm not picky about the keywords.

> About the read permission, I think that other databases use the rule that
> you can "see" an object if and only if you have some sort of privilege on
> it. I see little reason to create an extra privilege to just see the
> existence of objects.

Hm. That seems like it would not interact at all well with resolution
of ambiguous functions and operators. In the first place, I don't want
to execute a permission check for every candidate function/operator
before I can assemble the list of candidates to be chosen among. (For
example, on every use of an '=' operator that would cost us seventy-three
permissions checks, rather than one.) In the second place, that would
mean that granting or revoking access to a particular operator could
change resolution decisions for *other* operators of the same name ---
which is certainly surprising. In the third place, it's wrong to be
applying permissions checks at parse-analysis time; they should be done
at run-time. Otherwise rules have big problems. I realize that we have
to apply the namespace permissions checks at parse time, but I don't
want to do it for ordinary objects.

>> If the DBA revokes write access on the public namespace from a particular
>> user, and doesn't create a personal schema for that user, then under this
>> proposal that user would have noplace to create tables --- except TEMP
>> tables in his temp schema. Is that sufficient, or do the folks who want
>> this also want a way to prevent TEMP table creation?

> Maybe the temp schema should be a permanent catalog entry. That way the
> DBA can revoke create access from it as a means to disallow users to
> create temp tables.

Hm, we could clone a prototype pg_temp schema entry as a means of
getting this set up, I suppose. But the first question should be is it
worth troubling with?

>> Another thing that would be needed to prevent users from creating new
>> tables is to prevent them from creating schemas for themselves. I am not
>> sure how to handle that --- should the right to create schemas be treated
>> as a user property (a column of pg_shadow), or should it be attached
>> somehow to the database (and if the latter, how)?

> An aclitem[] column on pg_database seems like the most flexible solution
> to me.

Yeah, I was afraid you would say that ;-). I'd prefer to avoid it
because I think we'd need to have a TOAST table for pg_database then.
And I'm not at all sure how to setup a shared toast table. Can we get
away with constraining pg_database rows to 8K if they contain ACL lists?
(We might get some benefit from compression of the ACL list, but
probably not a heck of a lot.)

regards, tom lane

Joe Conway <mail(at)joeconway(dot)com> writes:
> If user1, the owner of the schema1, creates a new table tab1, will user2
> who has "Read" privilege to schema1, be automatically granted SELECT
> privilege on tab1? Or will he be able to see that tab1 exists, but not
> select from it (continuing the analogy with directories)?

No, and yes.

> This looks good to me. I only wonder if public should default to world
> read and no create?

That would be non-backwards-compatible. Since the main reason for
having the public namespace at all is backwards compatibility of the
out-of-the-box behavior, I think we have to let it default to world
write. DBAs can revoke world write, or even remove the public namespace
altogether, if they want to run a tighter ship.

> I like this. That way I can lock out a particular user if I need to with
> a single command. Would A automatically get ALL privileges on objects
> created in his schema by others? I think he should.

Hmm, I'd argue not; see nearby messages. The analogy with Unix
directory permissions seems to hold good here. If you are owner of
a directory you can delete files therein, but not necessarily do
anything else with 'em.

> I think only the database owner should be able to create schemas in
> their own database.

That seems overly restrictive to me; it'd be the equivalent of getting
rid of users that have createdb rights but aren't superusers.

Also, if a database owner is not superuser, I do not think he should be
able to create objects that are marked as belonging to other users.
At least not in general. Do we need to make an exception for schemas?

> Agreed. How would it work though if say I wanted to create a view in the
> public schema, which pointed at a table in a schema which has had SELECT
> revoked? Same question for a public function/private table. It would be
> ideal if you could do this.

AFAICS this would not be checked at creation time, but when someone
tries to use the view; just the same as now.

regards, tom lane

On Fri, 2002-04-19 at 00:14, Tom Lane wrote:

> It's not quite clear what should happen if User A allows User B to create
> an object in a schema owned by A, but then revokes read access on that
> schema from B. Presumably, B can no longer access the object, even though
> he still owns it. A would have the ability to delete the object under
> these rules, but is that enough?

Then A should take over ownership. It would be like the expiry of a
lease on a piece of land: any buildings erected by the lessee become the
property of the landowner. (If this consequence was not desired, the
objects should not have been created in a database/schema outside the
owner's control.)

> Another thing that would be needed to prevent users from creating new
> tables is to prevent them from creating schemas for themselves. I am not
> sure how to handle that --- should the right to create schemas be treated
> as a user property (a column of pg_shadow), or should it be attached
> somehow to the database (and if the latter, how)?

I think it could be both: a database owner may not want any schemas
created by anyone else, or by some particular user; alternatively, the
administrator may not want a particular user to create any schemas
anywhere. These are two different kinds of restriction:

GRANT CREATE SCHEMA TO user | PUBLIC
REVOKE CREATE SCHEMA FROM user | PUBLIC

would allow/disallow the user (other than the database owner) the
theoretical right to create a schema, whereas

GRANT CREATE SCHEMA IN database TO user | PUBLIC
REVOKE CREATE SCHEMA IN database FROM user | PUBLIC

would allow/disallow him it on a particular database. Having both gives
more flexibility and allows different people control for different
purposes (suppose someone needs to pay for the privilege to create
schemas in a variable set of databases; the general permission could be
turned on or off according to whether the bill was paid.). A general
permission would be needed before permission could be effective on a
particular database.

> Should the owner of a database (assume he's not a superuser) have the
> right to drop any schema in his database, even if he doesn't own it?
> I can see arguments either way on that one.

I think a database owner should be able to override the owner of a
schema within the database; similarly a schema owner should be able to
override the owner of an object within the schema. This makes sense in
practice, since the higher owner can delete the schema/object and
recreate it under his own ownership; so there is little point in not
allowing him to change it directly.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"For I am persuaded, that neither death, nor life, nor
angels, nor principalities, nor powers, nor things
present, nor things to come, nor height, nor depth,
nor any other creature, shall be able to separate us
from the love of God, which is in Christ Jesus our
Lord." Romans 8:38,39

On Fri, 2002-04-19 at 01:10, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>
> >> Another thing that would be needed to prevent users from creating new
> >> tables is to prevent them from creating schemas for themselves. I am not
> >> sure how to handle that --- should the right to create schemas be treated
> >> as a user property (a column of pg_shadow), or should it be attached
> >> somehow to the database (and if the latter, how)?
>
> > An aclitem[] column on pg_database seems like the most flexible solution
> > to me.
>
> Yeah, I was afraid you would say that ;-). I'd prefer to avoid it
> because I think we'd need to have a TOAST table for pg_database then.
> And I'm not at all sure how to setup a shared toast table. Can we get
> away with constraining pg_database rows to 8K if they contain ACL lists?
> (We might get some benefit from compression of the ACL list, but
> probably not a heck of a lot.)

Creating schemas is not the kind of thing people do very frequently.
Why not simply normalise the relationship into another table? the extra
expense of the lookup would be insignificant in the total context of
schema creation.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"For I am persuaded, that neither death, nor life, nor
angels, nor principalities, nor powers, nor things
present, nor things to come, nor height, nor depth,
nor any other creature, shall be able to separate us
from the love of God, which is in Christ Jesus our
Lord." Romans 8:38,39

> > Another thing that would be needed to prevent users from creating
new
> > tables is to prevent them from creating schemas for themselves. I
am not
> > sure how to handle that --- should the right to create schemas be
treated
> > as a user property (a column of pg_shadow), or should it be
attached
> > somehow to the database (and if the latter, how)?
>
> I think only the database owner should be able to create schemas in
> their own database. That way if I want a user to be able to create
> tables, I just grant them CREATE in the public schema, or create a
> schema for them.

If owners could be groups, I'd tend to agree. I'm tired of setting up
general admin logins and giving a group of people a single key for
doing system changes. Anytime someone has to leave the company we run
around and issue new keys.

I really want to allow a small group to have control of the
development db but not in other DBs (other projects generally).
Granting superuser status isn't appropriate. But, giving a group
control over an individual database (schema or otherwise) is extreamly
useful. Production basically has the same thing but a different
group -- who know enough not to touch anything without a patch and
change control being issued by development which has been approved by
the resident DBA.

I'd really like to see a schema owner have full control over all
objects in a schema, and likewise a database owner have full control
over their database. My POV for large systems.

Lets look at small ones. Database usage in webhosting companies is on
the rise. With the changes to pg_hba.conf to allow specific users
access to specific databases it can now be easily sold as a part of a
hosting package.

FTP accounts on a server always have a master. Larger clients will
often create a directory structure in such a way that various web
developers can work in various parts without having to worry about
accidentally touching others stuff. BUT the master account can still
override the entire set if necessary. They own parent, they flip
permissions to suit themselves if they're blocked by them.

Postgresql needs something similar to be easily sold as a service.
The person actually paying for the DB installation would of course be
the owner of the DB.

In the event of a company, the buyer may allow others to do work
(consultants? employee? friend?). They create a user, a schema and
put the user to work. User does something they shouldn't and is
removed for it. Owner wants to clean up the mess or continue
maintainence. How do they do this? Owner isn't a superuser as
they're simply buying DB services from an Application hosting company.
They can't login as the user as they don't have the password (user
took it with them). ** I forget whether changing ownership of an
object would require superuser access or just ownership of the parent
object. ** So, they're left with calling the hosting company to clean
up the mess for them (not something we'd want to do).

With Postgresql 7.3 the above is a likley scenario at the company I
work for as we would like to offer this type of service along side the
other DBs we currently host -- and it's very close to being feasible.
What I need is a per DB superuser / supergroup which cannot do things
like drop database (even their own preferably as that ends in a tech
support call to have it recreated), create untrusted procedures /
languages, and other nerveracking abilities.

Giving the database owner, or better a group at the database level an
ACL to accomplish any job within their own database (including user
creation -- but we can get around that with a control panel to do it
for them) that an otherwise untrusted user should be allowed to looks
very good to me.

"Rod Taylor" <rbt(at)zort(dot)ca> writes:
> [ how it ought to be to support hosting companies ]

I'm not real comfortable with this. The design I proposed is based
fairly firmly on the Unix directory/file protection model --- which
is assuredly not perfect, but it's survived a lot of use and is not
known to have major flaws. You're suggesting that we should invent
a protection model off-the-cuff on the basis of the supposed needs
of one class of application. I think that's a recipe for trouble...

> I'd really like to see a schema owner have full control over all
> objects in a schema, and likewise a database owner have full control
> over their database. My POV for large systems.

Those things are both easily done: just don't allow anyone else to
create objects in your schema (resp. database). This is indeed what
SQL99 envisions. However, in a database where there are multiple
users sharing schemas, I am not convinced that the notion "the schema
owner has ALL rights to objects within the schema" is appropriate.
That seems to me to go way too far; if we are troubling to maintain
distinct ownership of objects within a schema, that should mean
something. In particular, the guy who is not the schema owner should
be able to have some confidence that the guy who is can't make arbitrary
changes in his table. Otherwise the schema owner is effectively
superuser, and what's the point of pretending he's not?

regards, tom lane

Oliver Elphick wrote:
> On Fri, 2002-04-19 at 00:14, Tom Lane wrote:
> I think it could be both: a database owner may not want any schemas
> created by anyone else, or by some particular user; alternatively, the
> administrator may not want a particular user to create any schemas
> anywhere. These are two different kinds of restriction:
>
> GRANT CREATE SCHEMA TO user | PUBLIC
> REVOKE CREATE SCHEMA FROM user | PUBLIC
>
> would allow/disallow the user (other than the database owner) the
> theoretical right to create a schema, whereas
>
> GRANT CREATE SCHEMA IN database TO user | PUBLIC
> REVOKE CREATE SCHEMA IN database FROM user | PUBLIC
>
> would allow/disallow him it on a particular database. Having both gives
> more flexibility and allows different people control for different
> purposes (suppose someone needs to pay for the privilege to create
> schemas in a variable set of databases; the general permission could be
> turned on or off according to whether the bill was paid.). A general
> permission would be needed before permission could be effective on a
> particular database.

I like this general idea and syntax. But it seems awkward to have to
have the privilege granted twice. What about:

GRANT CREATE SCHEMA [IN { database | ALL }] TO user | PUBLIC
REVOKE CREATE SCHEMA [IN { database | ALL }] FROM user | PUBLIC

where lack of the IN clause implies the current database, and ALL
implies a system-wide grant/revoke. System-wide could only be issued by
a superuser, while a specific database command could be issued by the DB
owner or a superuser.

>
>>Should the owner of a database (assume he's not a superuser) have the
>>right to drop any schema in his database, even if he doesn't own it?
>>I can see arguments either way on that one.
>
>
> I think a database owner should be able to override the owner of a
> schema within the database; similarly a schema owner should be able to
> override the owner of an object within the schema. This makes sense in
> practice, since the higher owner can delete the schema/object and
> recreate it under his own ownership; so there is little point in not
> allowing him to change it directly.

Yeah, I still feel that the owner of a "container" object like a
database or schema should have complete control of whatever is contained
therein. Anything else would strike me as surprising behavior.

Joe

On Fri, 2002-04-19 at 02:24, Joe Conway wrote:
> I like this general idea and syntax. But it seems awkward to have to
> have the privilege granted twice. What about:
>
> GRANT CREATE SCHEMA [IN { database | ALL }] TO user | PUBLIC
> REVOKE CREATE SCHEMA [IN { database | ALL }] FROM user | PUBLIC

I would naturally interpret granting permission IN ALL to mean that the
user would certainly be allowed permission in all databases, whereas it
ought to be clear that the permission given is only hypothetical and
subject to permission's being granted for a specific database.

> where lack of the IN clause implies the current database, and ALL
> implies a system-wide grant/revoke. System-wide could only be issued by
> a superuser, while a specific database command could be issued by the DB
> owner or a superuser.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"For I am persuaded, that neither death, nor life, nor
angels, nor principalities, nor powers, nor things
present, nor things to come, nor height, nor depth,
nor any other creature, shall be able to separate us
from the love of God, which is in Christ Jesus our
Lord." Romans 8:38,39

> I'm not real comfortable with this. The design I proposed is based
> fairly firmly on the Unix directory/file protection model --- which
> is assuredly not perfect, but it's survived a lot of use and is not
> known to have major flaws. You're suggesting that we should invent

Will we be able to accomplish the equivelent of the below?

knight# ls -la
total 3
drwxr-xr-x 2 rbt rbt 512 Apr 18 21:53 .
drwxr-xr-x 43 rbt rbt 2048 Apr 18 21:36 ..
-rwx------ 1 root wheel 0 Apr 18 21:53 file

knight# head /etc/group
# $FreeBSD: src/etc/group,v 1.19.2.1 2001/11/24 17:22:24 gshapiro Exp
$
#
wheel:*:0:root
daemon:*:1:daemon
kmem:*:2:root
sys:*:3:root
tty:*:4:root
operator:*:5:root
mail:*:6:
bin:*:7:

knight# exit
exit

bash-2.05a$ whoami
rbt

bash-2.05a$ rm file
override rwx------ root/wheel for file? y

bash-2.05a$ ls -la
total 3
drwxr-xr-x 2 rbt rbt 512 Apr 18 21:55 .
drwxr-xr-x 43 rbt rbt 2048 Apr 18 21:36 ..

> > I'd really like to see a schema owner have full control over all
> > objects in a schema, and likewise a database owner have full
control
> > over their database. My POV for large systems.

> Those things are both easily done: just don't allow anyone else to
> create objects in your schema (resp. database). This is indeed what

Yes, basically what we do now. I'm hoping to add the ability to
enable a group (ROLES) to have ownership of items as well as users
when I complete the other tasks I've set before myself.

> Will we be able to accomplish the equivelent of the below?
>
>
> knight# ls -la
> total 3
> drwxr-xr-x 2 rbt rbt 512 Apr 18 21:53 .
> drwxr-xr-x 43 rbt rbt 2048 Apr 18 21:36 ..
> -rwx------ 1 root wheel 0 Apr 18 21:53 file
>
> knight# head /etc/group
> # $FreeBSD: src/etc/group,v 1.19.2.1 2001/11/24 17:22:24 gshapiro Exp
> $
> #
> wheel:*:0:root
> daemon:*:1:daemon
> kmem:*:2:root
> sys:*:3:root
> tty:*:4:root
> operator:*:5:root
> mail:*:6:
> bin:*:7:
>
> knight# exit
> exit
>
> bash-2.05a$ whoami
> rbt
>
> bash-2.05a$ rm file
> override rwx------ root/wheel for file? y
>
> bash-2.05a$ ls -la
> total 3
> drwxr-xr-x 2 rbt rbt 512 Apr 18 21:55 .
> drwxr-xr-x 43 rbt rbt 2048 Apr 18 21:36 ..

That is, of course, a BSD-ism that would confuse a lot of the SysV people...
:)

Chris

"Rod Taylor" <rbt(at)zort(dot)ca> writes:
> Will we be able to accomplish the equivelent of the below?

I think what you're depicting is the equivalent of a schema owner
dropping a table in his schema, right? Yes, I proposed allowing that,
but not granting the schema owner any other ownership rights over
contained tables. This is analogous to the way that ownership of a Unix
directory lets you rm a contained file ... but not necessarily alter
that file in any way short of rm'ing it.

> Yes, basically what we do now. I'm hoping to add the ability to
> enable a group (ROLES) to have ownership of items as well as users
> when I complete the other tasks I've set before myself.

That could be a good extension, but I think it's orthogonal to the
immediate issue...

regards, tom lane

> That is, of course, a BSD-ism that would confuse a lot of the SysV
people...
> :)

Yup.. But it's been around quite a while and I don't know of any
horrible problems with it -- that said I've not actually tried it on
OpenBSD (different mindset) but would be surprised if it wasn't the
same.

Sure, it may not be the smartest thing to allow user Y to create a
table in my schema BUT if I decide to reverse that decision (for
whatever reason) I want to be able to drop the junk user Y littered
around my schema along with the user even if I'm not allowed to look
at it, use it or otherwise fiddle around with it.

But if I'm the only one who feels this way, so be it.

> > Will we be able to accomplish the equivelent of the below?
>
> I think what you're depicting is the equivalent of a schema owner
> dropping a table in his schema, right? Yes, I proposed allowing
that,

Yes, thats what I was looking for. Sorry if I missed that in the
initial proposal.

> > Yes, basically what we do now. I'm hoping to add the ability to
> > enable a group (ROLES) to have ownership of items as well as users
> > when I complete the other tasks I've set before myself.
>
> That could be a good extension, but I think it's orthogonal to the
> immediate issue...

Yes it is.

Tom Lane wrote:
>>This looks good to me. I only wonder if public should default to world
>>read and no create?
>
>
> That would be non-backwards-compatible. Since the main reason for
> having the public namespace at all is backwards compatibility of the
> out-of-the-box behavior, I think we have to let it default to world
> write. DBAs can revoke world write, or even remove the public namespace
> altogether, if they want to run a tighter ship.

Ah yes, I forgot about that aspect.

>
> Also, if a database owner is not superuser, I do not think he should be
> able to create objects that are marked as belonging to other users.
> At least not in general. Do we need to make an exception for schemas?
>

Well, I like to think of the database owner as the superuser within that
one database. This is similar to (at least) SQL Server and Oracle. But I
don't think either of those systems have quite this issue because the
notion of schema and login user are so tightly coupled, something you
were specifically trying to avoid ;-)

>
>>Agreed. How would it work though if say I wanted to create a view in the
>>public schema, which pointed at a table in a schema which has had SELECT
>>revoked? Same question for a public function/private table. It would be
>>ideal if you could do this.
>
>
> AFAICS this would not be checked at creation time, but when someone
> tries to use the view; just the same as now.

Great!

Thanks,

Joe

I said:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> An aclitem[] column on pg_database seems like the most flexible solution
>> to me.

> Yeah, I was afraid you would say that ;-). I'd prefer to avoid it
> because I think we'd need to have a TOAST table for pg_database then.
> And I'm not at all sure how to setup a shared toast table. Can we get
> away with constraining pg_database rows to 8K if they contain ACL lists?

After further thought, ACLs in pg_database are clearly the right way to
go, and we shouldn't let some possible implementation ugliness stop us.
I think we can probably get away without a TOAST table for the time
being, but if we get lots of squawks some way can be found to make one
happen.

So my second pass at a proposal goes like this:

Schemas (namespaces) have two grantable rights: SELECT allows looking up
objects within the namespace, and CREATE allows creating new objects
within the namespace. A newly created schema allows both rights to its
owner and none to anyone else. The predefined schemas have rights as
previously stated.

Databases have two grantable rights: CREATE allows creating new regular
(permanent) schemas within the database, while TEMP allows creation of
a temp schema (and thus temp tables). A new database will initially
allow both these rights to world. I am inclined to think that template1
should have both rights turned off, however, to prevent the common
I-created-a-lot-of-trash-in-template1 error. (Not that this will help,
if you do it as superuser. So maybe it's not worth the trouble.)

To delete an object you must be either owner of that object or owner of
its containing namespace. (Ownership of a namespace doesn't grant any
other ownership rights over contained objects.) You will need SELECT
rights on the namespace to look up the object in the first place, but
there's no specific namespace-level right associated with deletion.

To delete a namespace you must be either owner of the namespace or owner
of the database. All contained objects are dropped. (The database
owner can thus drop things he does not own, but only as part of deleting
a whole namespace.)

Renaming an object is a right reserved to the object owner. Possibly
we should also check that the owner (still) has CREATE rights in the
containing namespace; any thoughts there? Should we allow renaming
to move an object from one namespace to another?

Similarly, renaming a namespace is reserved to the namespace owner,
and perhaps should require that he (still) have schema CREATE rights.

BTW, it occurs to me that once we have ACLs on pg_database entries,
we could define a CONNECT right for databases, and then eliminate
most of the complexity of pg_hba.conf in favor of GRANT/REVOKE CONNECT.
But that's a separate discussion.

regards, tom lane

On Fri, 19 Apr 2002, Sander Steffann wrote:

> I can't think of a reason that [creation of] temp tables should
> be prevented.

Maybe to keep hostile users from filling up your disk?

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Curt Sampson <cjs(at)cynic(dot)net> writes:
> On Fri, 19 Apr 2002, Sander Steffann wrote:
>> I can't think of a reason that [creation of] temp tables should
>> be prevented.

> Maybe to keep hostile users from filling up your disk?

That does come to mind --- but if you've let hostile users into
your database, filling your disk is not exactly the smallest problem
they could cause. They can very easily cause DOS problems just based
on overconsumption of CPU cycles, or on crashing your server constantly.
(Cm'on, we all know that can be done.) Even more to the point, is there
nothing in your database that you'd not want published to the entire
world? There's got to be a certain amount of trust level between you
and the persons you allow SQL-command-level access to your database.
If not, you ought to be interposing another level of software.

My current proposal for schema protection does include a TEMP-table-
creation right ... but to be honest I am not convinced that it'd be
worth the trouble to implement it. Comments anyone?

regards, tom lane

Hi,

> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > On Fri, 19 Apr 2002, Sander Steffann wrote:
> >> I can't think of a reason that [creation of] temp tables should
> >> be prevented.
>
> > Maybe to keep hostile users from filling up your disk?
>
> That does come to mind --- but if you've let hostile users into
> your database, filling your disk is not exactly the smallest problem
> they could cause. They can very easily cause DOS problems just based
> on overconsumption of CPU cycles, or on crashing your server constantly.
> (Cm'on, we all know that can be done.) Even more to the point, is there
> nothing in your database that you'd not want published to the entire
> world? There's got to be a certain amount of trust level between you
> and the persons you allow SQL-command-level access to your database.
> If not, you ought to be interposing another level of software.
>
> My current proposal for schema protection does include a TEMP-table-
> creation right ... but to be honest I am not convinced that it'd be
> worth the trouble to implement it. Comments anyone?

I see your point, but I think Curt is right... If users are always allowed
to make temp tables, you can't give someone real read-only access to the DB.
I agree that there has to be more protection to prevent other abuses, but at
least the disk is safe.

Sander

On Sat, 20 Apr 2002, Sander Steffann wrote:

> > > Maybe to keep hostile users from filling up your disk?

Actually, I was serious, not sarcastic, about that "maybe." Like
Tom, I'm not entirely sure that it's necessary to add this complexity,
because there are so many other ways to abuse the system.

> I think Curt is right... If users are always allowed
> to make temp tables, you can't give someone real read-only access to the DB.

Well, I'm not sure you can give "real" read-only access anyway.
After all, if you've got a big enough table, all a user has to do
is submit a few queries that sort the entire thing and you'll be
eating up disk space like mad. But I think you can arrange for the
sort files to go on another partition, to help limit the problems
this would cause.

Another question is about the best place to put temporary tables.
Right now they go in the database you're connected to, right? So
it's possible for users that can create temporary tables to stop
all inserts into that database by filling up its partition, but
other DBs might be on different partitions and be unaffected.

Another way to go is to do what MS SQL server does, which is to
put temp tables in a separate database. If you put that on its own
partition, you can limit the damage users can do to the database
that they're connected to, but then users can stop all other users
from creating temporary tables.

Personally, I feel the Postgres approach is better for postgres at
this time, but there are other differences that help to make this
so. In SQL Server, a "database" is really more a schema in the
postgres sense, except that it's also a separate tablespace. So
the two approaches are not directly comparable.

In the end, it seems to me that there's only so much security you
can implement in a database. I don't think that anybody produces
a database server where I'd let random users connect directly,
rather than going though an application that implements further
security. Thus, one probably doesn't want to spend a lot of time
trying to implement perfect security.

Am I siding with you or Tom here? I'm not sure. :-)

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Hi,

> > > > Maybe to keep hostile users from filling up your disk?
>
> Actually, I was serious, not sarcastic, about that "maybe." Like
> Tom, I'm not entirely sure that it's necessary to add this complexity,
> because there are so many other ways to abuse the system.

I know... But we have to start somewhere :)

> > I think Curt is right... If users are always allowed
> > to make temp tables, you can't give someone real read-only access to the
DB.
>
> Well, I'm not sure you can give "real" read-only access anyway.
> After all, if you've got a big enough table, all a user has to do
> is submit a few queries that sort the entire thing and you'll be
> eating up disk space like mad.

Ok. I forgot about that.

> But I think you can arrange for the
> sort files to go on another partition, to help limit the problems
> this would cause.

Sounds good.

> Another question is about the best place to put temporary tables.
> Right now they go in the database you're connected to, right? So
> it's possible for users that can create temporary tables to stop
> all inserts into that database by filling up its partition, but
> other DBs might be on different partitions and be unaffected.

At the moment all our DBs are on one partition. This would be a good reason
to split them, but it also makes it difficult if someone needs more space.

> Another way to go is to do what MS SQL server does, which is to
> put temp tables in a separate database. If you put that on its own
> partition, you can limit the damage users can do to the database
> that they're connected to, but then users can stop all other users
> from creating temporary tables.

That is true, but when I look at how many of our customers actually use temp
tables, I think this is not a very big problem (for us!)

> Personally, I feel the Postgres approach is better for postgres at
> this time, but there are other differences that help to make this
> so. In SQL Server, a "database" is really more a schema in the
> postgres sense, except that it's also a separate tablespace. So
> the two approaches are not directly comparable.
>
> In the end, it seems to me that there's only so much security you
> can implement in a database. I don't think that anybody produces
> a database server where I'd let random users connect directly,
> rather than going though an application that implements further
> security. Thus, one probably doesn't want to spend a lot of time
> trying to implement perfect security.

Only the idea of real read-only users seems useful to me. Maybe if temp
tables and big sorts could be limited this would be possible? Maybe a
restriction on CPU time... I don't know if there are any other places where
a user can eat resources, but the more I think about it, the more
complicated it gets. :-(

> Am I siding with you or Tom here? I'm not sure. :-)

I don't realy care, as long as we reach a good sollution! :-)

- Sander

On Sun, 21 Apr 2002, Sander Steffann wrote:

> At the moment all our DBs are on one partition.

Not really, no. It's easy to put in a symlink to put a database on
another partition. It's easy for any object, for that matter, so long as
it's not the sort of thing that gets deleted and re-created by users.

> That is true, but when I look at how many of our customers actually use temp
> tables, I think this is not a very big problem (for us!)

Oh, of course! I was still in SQL Server mode, thinking that sorts were
done via temp tables. But of course Postgres doesn't do it this way.

> I don't know if there are any other places where
> a user can eat resources, but the more I think about it, the more
> complicated it gets. :-(

Yeah, exactly.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Tom Lane writes:

[ All the rest looks good to me. ]

> Databases have two grantable rights: CREATE allows creating new regular
> (permanent) schemas within the database, while TEMP allows creation of
> a temp schema (and thus temp tables).

Couldn't the temp schema be permanent (and unremovable), and thus the
privilege to create temp tables can be handled by GRANT CREATE ON SCHEMA
temp. It seems to me that creating an extra type of privilege to be able
to create one specific schema that exists by default anyway(?) is
overkill.

> A new database will initially allow both these rights to world.

Should it? Shouldn't the database owner have to give out schemas
explicitly? This would be consistent with not being able to create
subobjects in other people's schemas by default.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> Databases have two grantable rights: CREATE allows creating new regular
>> (permanent) schemas within the database, while TEMP allows creation of
>> a temp schema (and thus temp tables).

> Couldn't the temp schema be permanent (and unremovable), and thus the
> privilege to create temp tables can be handled by GRANT CREATE ON SCHEMA
> temp. It seems to me that creating an extra type of privilege to be able
> to create one specific schema that exists by default anyway(?) is
> overkill.

Well, it's not a single schema but a schema-per-backend. I suppose we
could do it as you suggest if we invent a "prototype" pg_temp schema
on which the rights can be stuck. But it doesn't seem obviously cleaner
to do it that way than to attach the rights to the database. In
particular, the idea of cloning a temp schema bothers me: if someone
sticks some tables into the prototype schema, should we clone those
too upon backend startup? If not, why not?

>> A new database will initially allow both these rights to world.

> Should it? Shouldn't the database owner have to give out schemas
> explicitly? This would be consistent with not being able to create
> subobjects in other people's schemas by default.

Well, I've been dithering about that. Zero public rights on creation
would clearly be more compatible with the way we handle other kinds
of rights. It would also clearly *not* be backwards-compatible with
our historical behavior for new databases.

It seems relevant here that existing pg_dumpall scripts will fail
miserably if CREATE DATABASE does not allow connect/create rights
to world by default.

Unless you see a way around that, my inclination is to allow rights as
I suggested. We could perhaps tighten this up in a release or three,
after we've fixed pg_dumpall to do something appropriate.

regards, tom lane