Horton
Menu

Thread

  1. Anyone can create tables!

    Dan McGrath <dmcgrath19@home.com> — 2001-03-11T01:28:33Z

    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

  2. Re: Anyone can create tables!

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-11T02:56:01Z

    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

  3. Re: Anyone can create tables!

    Dan McGrath <dmcgrath19@home.com> — 2001-03-11T03:12:55Z

    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

  4. Re: Anyone can create tables!

    Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-03-11T13:51:24Z

    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.

  5. Re: Anyone can create tables!

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-11T17:22:01Z

    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

  6. Re: Anyone can create tables!

    Richard Huxton <dev@archonet.com> — 2001-03-11T21:25:44Z

    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

  7. Re: Re: Anyone can create tables!

    Dan McGrath <dmcgrath19@home.com> — 2001-03-12T01:34:43Z

    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

  8. Re: Anyone can create tables!

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-12T01:46:51Z

    "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