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

Added to TODO.detail/privileges.

> 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
> 
> 
> 

--  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,
Pennsylvania19026