Thread: Anyone can create tables!
Ive been using PostgreSQL 7.0.3 since about the time it came out. Im very happy with it. Cheers! But im writting to ask about a problem ive noticed. Any user with access to a database on my system that isnt the owner still has the ability to create tables (and possibly functions, views etc.) with no aparent limits. Is this a design flaw or a bug or perhaps just something you guys havent got around to fixing yet? Personally I think that a normal user should not be allowed to create anything in a db he doesnt own. Anyways, i look forward to hearing back about this as it is kinda putting my latest project on halt until I find a way to stop this. Also, is there a plan of any kind of space/db/table size restrictions any time soon? Thanks
Dan McGrath <dmcgrath19@home.com> writes:
> Any user with access to a database on my system that isnt the
> owner still has the ability to create tables (and possibly functions,
> views etc.) with no aparent limits. Is this a design flaw or a bug or
> perhaps just something you guys havent got around to fixing yet?
There is no concept of database-level privileges in Postgres, other than
the right to connect to a DB in the first place (which is recorded and
enforced completely outside the database system itself).
As near as I can tell, the SQL standard doesn't have any such concept
either, although it does have some notion of privileges associated with
schemas. We don't support schemas yet, but hope to soon.
The privilege system in Postgres does badly need to be overhauled and
brought up to SQL spec compliance, but I dunno when that will happen
exactly. It probably doesn't make sense to worry about it until we
have schema support, since otherwise there's no clear mapping of the SQL
model to Postgres...
regards, tom lane
Thanks Tom. Actually its kinda holding me off really bad. I would like to switch to mysql, but I still feel confident in pgsql to stay. One fellow on irc I talked with mentioned the possibilty of creating a trigger on the internal pgsql tables to restrict adding or creating anything. I just think its very bad for me to consider opening my db to the world with that kind of access open to the public. Would be like hotmail allowing unlimited email size. You get my idea anyways Im sure. Curious, would this idea of using triggers actually work? I mean heck, all I really think we need is a system where if you dont own the table, you cant add to anything that doesnt already exist. I just dont see how hard this is. Oh well, thanks for the reply. I hope it comes soon so I can start deploying some db's. Thanks Dan McGrath Tom Lane wrote: > Dan McGrath <dmcgrath19@home.com> writes: > > Any user with access to a database on my system that isnt the > > owner still has the ability to create tables (and possibly functions, > > views etc.) with no aparent limits. Is this a design flaw or a bug or > > perhaps just something you guys havent got around to fixing yet? > > There is no concept of database-level privileges in Postgres, other than > the right to connect to a DB in the first place (which is recorded and > enforced completely outside the database system itself). > > As near as I can tell, the SQL standard doesn't have any such concept > either, although it does have some notion of privileges associated with > schemas. We don't support schemas yet, but hope to soon. > > The privilege system in Postgres does badly need to be overhauled and > brought up to SQL spec compliance, but I dunno when that will happen > exactly. It probably doesn't make sense to worry about it until we > have schema support, since otherwise there's no clear mapping of the SQL > model to Postgres... > > regards, tom lane
At 10:12 PM 3/10/01 -0500, Dan McGrath wrote: >Thanks Tom. Actually its kinda holding me off really bad. I would like to >switch to mysql, but I still feel confident in pgsql to stay. One fellow on >irc I talked with mentioned the possibilty of creating a trigger on the >internal pgsql tables to restrict adding or creating anything. I just think >its very bad for me to consider opening my db to the world with that kind of >access open to the public. Would be like hotmail allowing unlimited email >size. You get my idea anyways Im sure. I doubt that I would ever recommend opening any RDBMS to the world. There is a very high chance that even if controls are put in, there will be ways around it. It's as bad as giving a unix/cmd shell to the world. Aren't there other ways to do what you want without giving the world access to your database? Maybe each user could have their own postgresql engine running on their own server (real or virtual). Cheerio, Link.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> I doubt that I would ever recommend opening any RDBMS to the world.
Indeed. If filling your disk is the only form of denial-of-service
that an attacker can think of, then he's sadly lacking in creativity.
Bottom line for me is that if you're concerned about security then you
should NOT be allowing random people to issue SQL commands directly,
and so this issue isn't nearly as important as Dan makes it.
A more secure arrangement would be (for example) to provide access via
a website backed by CGI or PHP scripts, so that the only possible SQL
commands are those you've put into the scripts.
regards, tom lane
mmm, i think this is getting out of hand. I told you on irc that a db should be more conserned with being a db than a security policy of people accessing it. It sounds to me like you need to set up your network different and the security policies around this db(s). I mean only certian ip's should even be able to route to the box to begin with. why would you even let anyone access a db box in the first place?! this is just silly to me. and never, ever leave a db box open to the outside world, it should be hiding behind some firewall(s depending on your company) and not be routeable except by certian static internal ips. now if after this you have a problem, then people accessing the static ip boxes and/or spoofing the ip from another box, that is another problem that has nothing to do with postgresql at all. i dont even see why this should be on the list. yes all this security built into the db would be handy, but i'd rather they work on it just working and being compliant with standards, and keeping good data integrity and not crashing under heavy loads etc... anyways, i'm spend *gasping for air*, Chris Humphries Systems Administrator and DBA CD3 Storage Systems
From: "Tom Lane" <tgl@sss.pgh.pa.us> > Dan McGrath <dmcgrath19@home.com> writes: > > Any user with access to a database on my system that isnt the > > owner still has the ability to create tables (and possibly functions, > > views etc.) with no aparent limits. Is this a design flaw or a bug or > > perhaps just something you guys havent got around to fixing yet? > > There is no concept of database-level privileges in Postgres, other than > the right to connect to a DB in the first place (which is recorded and > enforced completely outside the database system itself). I'm guessing there's a Good Reason (TM) why setting permissions on pg_class et al don't seem to work. I can see how it might screw temporary tables (and views?) etc. but I couldn't seem to get perms to work at all the other day (I was playing). Failing that, is Dan's talk of triggers (other branch of this thread) plausible? Some disgusting hack to the client libraries to disable CREATE... and ALTER TABLE? - Richard Huxton
"Richard Huxton" <dev@archonet.com> writes:
> I'm guessing there's a Good Reason (TM) why setting permissions on pg_class
> et al don't seem to work.
Well, I dunno if it's a *good* reason, but none of the low-level
operations on system catalogs pay any attention to permissions on
the catalogs (nor to rules or triggers, if one were to try to set
such things on the catalogs). We would get into some interesting
definitional issues if we tried to make that happen. For example,
if I forbid you read access on pg_class, how does that get enforced?
(You'd have to read pg_class to discover the prohibition, after all.)
If I forbid you write access on pg_index, does that mean you can't
create indexes on your own tables? If I forbid you read access to
pg_relcheck, does that mean that you're suddenly able to do inserts
that violate constraints (since you can't see the constraints anymore)?
If I create an ON SELECT rule on pg_rewrite that alters the apparent
contents of pg_rewrite so as to hide itself from view, does that rule
actually get applied?
The implementation problems would be even more interesting ;-)
> Failing that, is Dan's talk of triggers (other branch of this thread)
> plausible?
See above.
> Some disgusting hack to the client libraries to disable CREATE...
> and ALTER TABLE?
Rather pointless, since an attacker could bypass it just by using a
de-hacked client library.
regards, tom lane
Wel guys, thanks for the advice. I guess my own custom CGI would work, or something similar. Your help has been greatly apreciated. I was merely trying to find out if its possible to do it any other way, and you guys have already given me some stuff to think about. Cheers! Dan Lincoln Yeoh wrote: > At 10:12 PM 3/10/01 -0500, Dan McGrath wrote: > >Thanks Tom. Actually its kinda holding me off really bad. I would like to > >switch to mysql, but I still feel confident in pgsql to stay. One fellow on > >irc I talked with mentioned the possibilty of creating a trigger on the > >internal pgsql tables to restrict adding or creating anything. I just think > >its very bad for me to consider opening my db to the world with that kind of > >access open to the public. Would be like hotmail allowing unlimited email > >size. You get my idea anyways Im sure. > > I doubt that I would ever recommend opening any RDBMS to the world. > > There is a very high chance that even if controls are put in, there will be > ways around it. > > It's as bad as giving a unix/cmd shell to the world. > > Aren't there other ways to do what you want without giving the world access > to your database? > > Maybe each user could have their own postgresql engine running on their own > server (real or virtual). > > Cheerio, > Link. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
I have not been following the start of this thread but I was myself wondering about the security of DB access over the web I have been playing with JSP and servlets with JDBC access to PG (I guess the principal is the same as using other scripting languages.) I noticed that it appears impossible for any web user to see the JSP source, all they will see is the generated HTML is they view page source. Even if they could see the JSP they would not see the Servlet which would be in a private directory You could then left them do things, even enter SQL into your form but your servlet could parse it and stop them doing unwanted things. BTW Are there any security issues with this that anyone knows of??? Thanks MC. -- NOTICE: The information contained in this electronic mail transmission is intended by Convergys Corporation for the use of the named individual or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone (collect), so that the sender's address records can be corrected.
martin.chantler@convergys.com wrote: > > I have not been following the start of this thread but I was myself > wondering about the security of DB access over the web [snip] > You could then left them do things, even enter SQL into your form but your > servlet > could parse it and stop them doing unwanted things. > > BTW Are there any security issues with this that anyone knows of??? > There are security issues with anything, but the crucial thing is to be strict with the parsing. Choose what to let through rather than what to block. One trick is to pass crafted text in to value fields in forms to force your own query to run. Buffer overflows etc should presumably be less of a problem with Java. - Richard Huxton