Re: RFD: schemas and different kinds of Postgres objects

Tom Lane wrote:
>
> Continuing to think about implementing SQL schemas for 7.3 ...
>
> Today's topic for discussion: which types of Postgres objects should
> belong to schemas, and which ones should have other name scopes?
...
>
> I am leaning towards keeping functions/operators database-wide, but would
> like to hear comments. Is there any real value in, eg, allowing different
> users to define different "+" operators *on the same datatypes*?

With regard to functions, I believe they should be schema specific.
Oracle allows the creation of procedures/functions in specific schema.
User2 may then execute user1's function as:

EXECUTE user1.myfunction();

However, as you suggest, the fully qualified naming of functions gets
messy. So Oracle allows (and I think we would need) PUBLIC SYNONYMs.
This allows user1 to do:

CREATE TABLE employees(key integer, name VARCHAR(20));

CREATE SEQUENCE s;

CREATE PROCEDURE newemployee(n IN VARCHAR)
AS
BEGIN
INSERT INTO employees
SELECT s.nextval, n
FROM DUAL;
END;
/

GRANT INSERT ON employees TO user2;
GRANT EXECUTE ON newemployee TO user2;
CREATE PUBLIC SYNONYM newemployee FOR user1.newemployee;

Now, user2 just does:

EXECUTE newemployee(10);

In fact, with regard to the package discussion a while back, Oracle
allows this:

Database->Schema->Package->Procedure

and this:

Database->Schema->Procedure

and effectively this:

Database->Procedure via Database->PUBLIC Schema->Procedure

I really think that the main purpose of schemas is to prevent an
ill-informed or malicious user from engaging in unacceptable behavior.
By placing everything in schemas, it allows the Oracle DBA to have a
very fine-grained control over the ability of user1 to interfere with
user2. Before user1 above could pollute the global namespace, the dba
must have:

GRANT user1 CREATE PUBLIC SYNONYM

privilege, or created the synonym himself. This allows things like
pg_class to reside within their own schema, as well as all built-in
PostgreSQL functions. After the bootstrapping, PUBLIC SYNONYMs are
created for all of the system objects which should have global scope:

CREATE PUBLIC SYNONYM pg_class FOR system.pg_class;
CREATE PUBLIC SYNONYM abs(int) FOR system.abs(int);

One major benefit of Oracle is that the DBA, through the use of
STATEMENT privileges (i.e. GRANT CREATE TABLE to user1), resource
PROFILEs, and TABLESPACES can easily admin a database used by 20
different deparments and 1000 different users without the fear that one
might step on the other's toes. If the accounting department wants to
create an addtax() function, it shouldn't have to ask the receiving
deptartment to do so.

Just my thoughts,

Mike Mascari
mascarm(at)mascari(dot)com