user-defined default public acl

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Thus I'd like to add a user defined default public acl for postgresql
database, namespace, class, language and proc, instead of the hardwired
defaults in "src/backend/utils/adt/acl.c".

I'm unconvinced that this is a good idea. In the first place I am
unsure what sorts of surprising behaviors might result from
nontraditional defaults for these ACL settings. (I find the precedent
of "umask 077" unconvincing because a Unix filesystem only deals with
two kinds of objects, files and directories, with not-so-different
protection behaviors. I don't know that that scales up to SQL.)

Also, I'm afraid that empty acl arrays (from what is seen from acl fields
when dumping pg_database or other tables) have a meaning at the time,
which is "default"... this may interact with new user-defined defaults.

Yup, it would, and that strikes me as another fertile place for problems.
You will have to make some basic changes in the way that default ACLs
are handled, or else such a feature would introduce security holes.
I'm not sure how much user-facing behavior would have to change, but
I doubt it could be a completely transparent change.

Another area to think about is the implications for pg_dump, and
especially the implications for reloading existing dump scripts.
If the postgresql.conf settings for default ACLs are nonstandard,
wouldn't that result in reloaded objects acquiring different ACLs
than they had before? Is that a good idea?

Before buying into all this, it would be nice to see a better rationale
than "this surprised me and it's easy to fix". The latter at least
is wrong.

regards, tom lane

<sorry, I resend this as I have not received it from the list, and it does
not appear in the archives...>

Dear Tom,

defaults in "src/backend/utils/adt/acl.c".

I'm unconvinced that this is a good idea.

Hmmm.

In the first place I am unsure what sorts of surprising behaviors might
result from nontraditional defaults for these ACL settings.

Sure, this should be thought of.

(I find the precedent of "umask 077" unconvincing because a Unix
filesystem only deals with two kinds of objects, files and directories,
with not-so-different protection behaviors. I don't know that that
scales up to SQL.)

I wouldn't want default rights for different objects to be mixed.
Also, it is about "public" rights, that is "other" wrt unix umask.
"public" means anybody, and my opinion is that anybody should not
be given anything implicitly.

Also, I'm afraid that empty acl arrays (from what is seen from acl fields
when dumping pg_database or other tables) have a meaning at the time,
which is "default"... this may interact with new user-defined defaults.

Yup, it would, and that strikes me as another fertile place for problems.
You will have to make some basic changes in the way that default ACLs
are handled, or else such a feature would introduce security holes.

Sure.

I'm not sure how much user-facing behavior would have to change, but
I doubt it could be a completely transparent change.

Should be investigated.

Another area to think about is the implications for pg_dump, and
especially the implications for reloading existing dump scripts.
If the postgresql.conf settings for default ACLs are nonstandard,
wouldn't that result in reloaded objects acquiring different ACLs
than they had before? Is that a good idea?

Well, I thought that pg_dump/restore would restore anything, including
ACL. However, if some assumptions are made about default ACL this
may be an issue.

Before buying into all this, it would be nice to see a better rationale
than "this surprised me and it's easy to fix". The latter at least is
wrong.

For the former, I mean that I'm used to create something that has "no
rights" to other people by default. Then I give the rights I want, and if
I don't give anything, then nothing is given by default. That's my
idea of security by default.

The current status with postgresql is that some rights are given by
default, so I have to fix everything by hand with revoke (I'm pretty
sure I'll forget something) and then grant what I want. I'll never
grant anything to "public", but rather to some groups.

As I can't and don't want to make all people share my point of view, I
would need some parametrizable default setting, so that I can at least
share my point of view with myself;-)

Also maybe the default may be different and still hardwired for system
stuffs.

A still better fix for me would be that default public rights are the
most restrictive, as some comments suggest it could be the case:

case ACL_OBJECT_FUNCTION:
/* Grant EXECUTE by default, for now */
^^^^^^^
world_default = ACL_EXECUTE;
...
case ACL_OBJECT_LANGUAGE:
/* Grant USAGE by default, for now */
world_default = ACL_USAGE;

The patch would be quick;-)

Have a nice day,

--
Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien.Coelho@ensmp.fr
CRI-ENSMP, 35, rue Saint-Honoré, 77305 Fontainebleau cedex, France
phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08}
________ All opinions expressed here are mine _________

[ discussion re/ default state of minimal rights, as opposed to the
more generous situation today snipped ]

Just to add fuel to the fire, as an ex-college sys-admin having had to
deploy both Oracle and postgres, I would have to say that Oracle
allowed me to deploy a database container shared by many many students
relatively securely, while at the same time allowing them to perform
cross-schema queries to their teammates tables if and when they needed
to. The users could manage the ACLs of their own schema objects,
allowing their peers into their tables on a mutual need basis, but the
default action was to be closed off completely. Combined with per-user
quotas on the tablespaces and per-user connection limits, I could
create a new student user and be comfortable knowing they're not going
to be able to steal from others nor consume all disk space. I didn't
have to deal with CPU / memory based attacks on the box just 'cause I
was lucky I guess.

With postgres, I had to create a new database for each user (this was
pre-schema, anyway), then tweak the hba conf file and sighup. I had no
disk-based resource limits available to me (there's a creative use of
linux loopback mounts to enforce per-database quotas floatin' around on
the net somewhere, but I didn't think of that, as well as that probably
wouldn't scale to, say, thousands of users). I wasn't about to dblink
databases for 'em, so it ended up that the mass-student-consumption
learn-SQL box was Oracle, and the lesser-used DB was postgres.

So, finally, from the perspective of a college admin with a 'centralize
the student services' mindset, being able to sandbox SQL users
relatively easily while also being able to provide higher-level service
such as cross-schema queries, centralized / guaranteed backup, etc.
would have been fantastic using postgres.

I don't work there anymore, but I'm sure other university shops still
have the same issue to solve. If a tight schema sandbox isn't the
default, then a system-catalog expert managed schema patch would have
been greatly appreciated to solve at least the 'stay out of other
user's crud by default' issues.

----
James Robinson
Socialserve.com