Thread: about permissions...

Hi,

how come, if you create a user with no permissions at all, having been granted nothing, he can still log into any
database,list available tables, create new here, and then delete them again. Seems odd...: 

medusa:~% createuser odd
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
medusa:~% psql -U odd cnv
Welcome to psql 7.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

cnv=> \dt
            List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+---------
 public | theaders      | table | jonasfh
 public | theadervalues | table | jonasfh
(2 rows)

cnv=> create table oddtable();
CREATE TABLE
cnv=> \dt
            List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+---------
 public | oddtable      | table | odd
 public | theaders      | table | jonasfh
 public | theadervalues | table | jonasfh

(3 rows)

cnv=> drop table oddtable;
DROP TABLE

Is this right, or is there something wrong with my settings in some way?

regards Jonas:))

--
Jonas F Henriksen
Institute of Marine Research
Norsk Marint Datasenter
PO Box 1870 Nordnes
5817 Bergen
Norway

Phone: +47 55238441

Henriksen, Jonas F wrote:
> Hi,
>
> how come, if you create a user with no permissions at all, having
> been granted nothing, he can still log into any database, list
> available tables, create new here, and then delete them again. Seems
> odd...:

> Is this right, or is there something wrong with my settings in some
> way?

Schema public has default access to group public, which your new user
has access to...

richardh=# GRANT ALL ON SCHEMA public TO richardh;
GRANT
richardh=# SELECT * FROM pg_namespace ;
    nspname   | nspowner |      nspacl
-------------+----------+-------------------
  public      |        1 | {=UC,richardh=UC}
...

richardh=# REVOKE ALL ON SCHEMA public FROM GROUP public;
REVOKE
richardh=# SELECT * FROM pg_namespace ;
    nspname   | nspowner |     nspacl
-------------+----------+-----------------
  public      |        1 | {=,richardh=UC}
...

*DO* make sure that one user has explict access before revoking all on
public though.

--
   Richard Huxton
   Archonet Ltd