Thread: New to PostgreSQL, is this a DoS?

I compiled and install 6.4 according to the INSTALL doc.

I created a database with "createdb test",

running as user "postgres", I connected "psql template1" and ran:

CREATE USER billybob WITH PASSWORD hehe CREATEDB CREATEUSER;

I then modified pg_hba.conf by adding:

host    all     10.0.0.2   255.255.255.255 crypt

I then killed and restarted postmaster with "-i".

From the remote machine "10.0.0.2" I connected to the database "test" as
user "billybob" and that worked.

However, I had problems trying to create a table.

From that remote machine, I ran:

CREATE TABLE weather (
city            varchar(80),
temp_lo         int,
temp_hi         int,
prcp            real,
date            date
);

And it supposedly "worked", it said "CREATE".  However, running

\d returned

Couldn't find any tables, sequences or indices!

Then from the machine actually running PostgreSQL, as user "postgres" I
connected to "test"

\d returned

Couldn't find any tables, sequences or indices!

So I tried running the CREATE TABLE weather command again, but it
returned:

ERROR:  weather relation already exists

but,

\d returned

Couldn't find any tables, sequences or indices!

From remotely as "billybob" or localy as "postgres" I could succesfully
drop this phantom table.

Is this a denial of service?  A remote user can connect and create as many
phantom tables as they want which could possibly interfere with normal
operation?  How would the admin even know the name of the table to drop?

If I connect locally as user "postgres" and I can successfully create and
see the table.

Is it normal behaviour that user "postgres" must first create the tables
for them to be useable?

Thanks,
Dax Kelson
Internet Connect, Inc.

On Sun, 15 Nov 1998, Dax Kelson wrote:

> Is this a denial of service?  A remote user can connect and create as many
> phantom tables as they want which could possibly interfere with normal
> operation?  How would the admin even know the name of the table to drop?
>
> If I connect locally as user "postgres" and I can successfully create and
> see the table.
>
> Is it normal behaviour that user "postgres" must first create the tables
> for them to be useable?

After more testing I found futher strange behavior.  After locally
user "postgres" creates any table in the database, any remote users can
then create any new table, and it isn't "phantom" (can see it/use it).

Is this desirable and/or expected behavior?  It doesn't seem to me that it
is.

Is it normal/desirable for any user to be able to select (haven't tried
insert/update) in the pg_* tables?

Is it possible to GRANT/REVOKE on the pg_* tables?  It seems it is.  How
can you see the permissions if \z doesn't work on the pg_* tables?

Given a multi-user environment were each user (and the sysadmin) values
security quite highly, what is the best way to secure PostgreSQL as
tightly as possible (not just looking at data in tables, but general
snooping around)?

In pg_hba.conf under "host" the second parameter is "dbname".  Is it
possible to have a list of databases?

Ie:

host   db1,db2,db3     10.0.0.3   255.255.255.255 crypt

Or is it limited to (all|samename|onedbname)?

Thanks for your help,
Dax Kelson
Internet Connect, Inc.