Thread: NOCREATETABLE patch (was: Re: Please, help!(about Postgres))
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.
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, Pennsylvania19026
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 <pgman@candle.pha.pa.us> writes:
> Can someone remind me what we are going to do with this?
I'd like to see some effort put into implementing the SQL-standard
privilege model, rather than adding yet more ad-hoc user properties.
The more of these we make, the more painful it's going to be to meet
the spec later.
Possibly, after we have the SQL semantics we'll still feel that we
need some additional features ... but how about spec first and
extensions afterwards?
regards, tom lane
I have added this to the TODO.detail/privileges file. We can work from there to decide what needs to be in 7.2. [ 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, Pennsylvania19026
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
On Mon, May 07, 2001 at 02:48:11PM -0400, Bruce Momjian wrote:
>
> Can someone remind me what we are going to do with this?
>
> > This patch add to 7.0.2 code NOCREATETABLE and NOLOCKTABLE feature:
It's my old patch, it's usable and some people use it for 7.0.x. But
it's really temporary solution and it was 1 day in official CVS :-)
We remove it after discussion with Peter E. More correct will implement
better privilege system.
A privilege system is *very* important for real multiuser and
sophisticated systems. For example if you compare PostgreSQL with Oracle,
the PostgreSQL is really not winner in this part. Peter has some idea
about it and Jan sent something about it too, but I not sure if somebody
works on this and plannig it for some next release (or...? -- will good
if I not right:-)
Karel
> It's my old patch, it's usable and some people use it for 7.0.x. But > it's really temporary solution and it was 1 day in official CVS :-) > We remove it after discussion with Peter E. More correct will implement > better privilege system. > > A privilege system is *very* important for real multiuser and > sophisticated systems. For example if you compare PostgreSQL with Oracle, > the PostgreSQL is really not winner in this part. Peter has some idea > about it and Jan sent something about it too, but I not sure if somebody > works on this and plannig it for some next release (or...? -- will good > if I not right:-) If we can get something more sophisticated in 7.2, great. If not, we may have to apply it later. -- 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