Restricting a database to it's owner

Drop the public schema so it can't be used in databases

# adduser exampleuser
# su - postgres
$ psql template1
psql> drop schema public

Create a database, with the schema for the user

# su - postgres
$ psql 
psql> CREATE ROLE exampleuser PASSWORD 'examplepw' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
psql> CREATE DATABASE exampleuser OWNER exampleuser;
psql> \connect exampleuser
psql> CREATE SCHEMA exampleuser AUTHORIZATION exampleuser;

Testing

# su - exampluser
$ psql
exampleuser=> create table test ( test int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

# adduser exampleuser2 
# su - postgres
$ psql
psql> CREATE ROLE exampleuser2 PASSWORD 'examplepw2' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

# su - exampleuser2
$ psql exampleuser

exampleuser=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

exampleuser=> SET search_path TO exampleuser;
SET

exampleuser=> \dn
         List of schemas
        Name        |    Owner    
--------------------+-------------
 exampleuser        | exampleuser
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
(5 rows)

exampleuser=> \dt
No relations found.

exampleuser=> drop schema exampleuser cascade;
ERROR:  must be owner of schema exampleuser

exampleuser=> select * from test;
ERROR:  relation "test" does not exist

• http://www.postgresql.org/docs/8.1/static/sql-createschema.html

• http://www.postgresql.org/docs/8.1/static/ddl-schemas.html

Situation I'm trying to avoid

While the security is pretty good, you can't delete another users tables, and you can't query them, you can still create tables in the public schema in their database, removing the public schema doesn't allow other users to see your table names, or to create tables in your database;

[devel@host]$ psql exampleuser
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

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

exampleuser=>\l
            List of databases
      Name         |    Owner       | Encoding  
-------------------+----------------+-----------
 exampleuser       | exampleuser    | UTF8

exampleuser=> \dt
No relations found.
exampleuser=> create table test ( test int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
exampleuser=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | devel
(1 row)

exampleuser=> 

[exampleuser@host]$ psql
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

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

exampleuser=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | devel
(1 row)

exampleuser=> drop table test;
ERROR:  must be owner of relation test

last edited 2008-06-08 07:45:05 by MichaelBest