RE: NOCREATETABLE patch (was: Re: Please, help!(about Postgres)) - Mailing list pgsql-hackers

Peter E. posted his proposal for the revamping of the 
authentication/security system a few weeks ago. There was a 
discussion, but I don't know if he came to any definitive 
conclusions, such as implementing System Privileges as well as Object 
Privileges. If he does, then the dba (or anyone who has been granted 
GRANT ANY PRIVILEGE system privilege & CREATE USER system privilege) 
should be able to do:

CREATE USER mascarm IDENTIFIED BY manager;
GRANT CREATE TABLE to mascarm;

It would also be good if PostgreSQL came with 2 groups by default - 
connect and dba.

The connect group would be granted these System Privileges:

CREATE AGGREGATE privilege
CREATE INDEX privilege
CREATE FUNCTION privilege
CREATE OPERATOR privilege
CREATE RULE privilege
CREATE SESSION privilege
CREATE SYNONYM privilege
CREATE TABLE privilege
CREATE TRIGGER privilege
CREATE TYPE privilege
CREATE VIEW privilege

These allow the user to create the above objects in their own schema 
only. We're getting schemas in 7.2, right? ;-).

The dba group would be granted the rest, like these:

CREATE ANY AGGREGATE privilege
CREATE ANY INDEX privilege...
(and so on)

as well as:

CREATE/ALTER/DROP USER
GRANT ANY PRIVILEGE
COMMENT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
SELECT ANY TABLE
ANALYZE ANY TABLE
LOCK ANY TABLE
CREATE PUBLIC SYNONYM (needed when schemas roll around)
DROP PUBLIC SYNONYM
(and so on)

Then, the dba could do a:

GRANT connect TO mascarm;

Or a:

CREATE USER mascarm
IDENTIFIED BY manager
IN GROUP connect;

It seems Karel's patch is a solution to the problem of people who 
want to create separate PostgreSQL user accounts, but want to ensure 
that a user can't create tables. In Oracle, I would just do a:

CREATE USER mascarm
IDENTIFIED BY manager;

GRANT CREATE SESSION TO mascarm;

Now mascarm has the ability to connect, but that's it.

Currently, if I know for instance that a background process DROPS a 
table, CREATES a new one, and then imports some data, I can create my 
own table by the same name, in between the DROP and CREATE and can 
cause havoc (if its not done in a single transaction). Hopefully 
Peter E's ACL design will allow for Oracle-like System Privileges to 
take place. That would allow for a much finer granularity of 
permissions then everyone either being the Unix equivalent of 'root' 
or 'user'.

Just my humble opinion though,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Bruce Momjian [SMTP:pgman@candle.pha.pa.us]

Can someone remind me what we are going to do with this?


[ Charset ISO-8859-2 unsupported, converting... ]
>
> On Fri, 26 Jan 2001, [koi8-r] ______ _. _______ wrote:
>
> >     Good Day, Dear Karel Zak!
> >
> > Please, forgive me for my bad english and if i do not right with 
your
> > day time.
>
> my English is more poor :-)
>
>  You are right, it is (was?) in TODO and it will implemented - I 
hope -
> in some next release (may be in 7.2 during ACL overhaul, Peter?).
>
> Before some time I wrote patch that resolve it for 7.0.2 (anyone -
> I forgot his name..)  port it to 7.0.2, my original patch was for 
7.0.0.
> May be will possible use it for last stable 7.0.3 too.
>
> The patch is at:
>      ftp://ftp2.zf.jcu.cz/users/zakkr/pg/7.0.2-user.patch.gz
>
> This patch add to 7.0.2 code NOCREATETABLE and NOLOCKTABLE feature:
>
> CREATE USER username
>     [ WITH
>      [ SYSID uid ]
>      [ PASSWORD 'password' ] ]
>     [ CREATEDB   | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
> ->  [ CREATETABLE | NOCREATETABLE ] [ LOCKTABLE | NOLOCKTABLE ]
>     ...etc.
>
>  If CREATETABLE or LOCKTABLE is not specific in CREATE USER 
command,
> as default is set CREATETABLE or LOCKTABLE (true).
>
>
>  But, don't forget - it's temporarily solution, I hope that some 
next
> release resolve it more systematic. More is in the 
patche@postgresql.org
> archive where was send original patch.
>
>  Because you are not first person that ask me, I re-post (CC:) it 
to
> hackers@postgresql.org, more admins happy with this :-)
>
>                 Karel
>
> > I want to ask You about "access control over who can create 
tables and
> > use locks in PostgreSQL". This message was placed in PostgreSQL 
site
> > TODO list. But now it was deleted. I so need help about this 
question,
> > becouse i'll making a site witch will give hosting for our users. 
> > And i want to make a PostgreSQL access to their own databases. 
But there
> > is (how You now) one problem. Anyone user may to connect to the 
different
> > user database and he may to create himself tables.
> > I don't like it.
>
>
>

-- Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania
 
19026