Re: NOCREATETABLE patch (was: Re: Please, help!(about Postgres))

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(at)mascari(dot)com
>
> -----Original Message-----
> From: Bruce Momjian [SMTP:pgman(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org
> > archive where was send original patch.
> >
> > Because you are not first person that ask me, I re-post (CC:) it
> to
> > hackers(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026