2

We are running PostgreSQL 8.3 as the DB server for our ERP system. So far there was no necessity to create different databases under different users (roles in terms of 8.3) in postgres. And now it has appeared.

Question 1: Is it correct that a user with no superuser privilege can read/write only to its owned databases (assuming the user has the CREATEDB privilege)?

Question 2: How can I disable for a user the listing of all databases via the psql -l command? Even if the answer to the first question is yes then this listing is still available to an arbitrary user.

Thank you.

Grigory
  • 992
  • 2
  • 18
  • 34
  • More solid way to go would be with virtualaztion e.g. jails etc. – nate c Feb 13 '11 at 19:48
  • Finally, I made this way. Since all the databases that need to be created are with all the same parameters (they are created via the admin tool of our ERP not with raw SQL) I decided to create a fixed number of databases for all the developers. Those databases use their own role and must be of the same name with the database. So every developer is going to have say 10 databases to play with - dev001 (db) / dev001 (role), dev002 (db) / dev002 (role), etc. And in pg_hba.conf I set the configuration `sameuser all xx.xx.xx.xx md5`. – Grigory Feb 15 '11 at 12:16

4 Answers4

3

There's no way to turn off psql -l without possibly breaking things. You can use the sameuser configuration option in pg_hba.conf to only let users connect to their own databases.

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
2

http://wiki.postgresql.org/wiki/Shared_Database_Hosting

Postgres 8.4

Main case

We modify template1 to revoke all rights from "PUBLIC" to the public schema, to prevent access to the public schema of indiviudial customer databases by other customers.

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;
EOT
b1_
  • 2,069
  • 1
  • 27
  • 39
2

re 1) yes, that's correct

re 2) I think it can be done by using revoke select on pg_database from public but I'm not sure what side-effects that will have.

Edit
This discussion might be interesting for you:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg64005.html

2

Q1: No. This is all controlled by privileges at various levels. To write into a table, you need privileges on that table, etc. The only privileges on the database level (which the owner would have by default) are the ability to create schemas and temporary tables. That's probably not what you had in mind. You could probably set it up that way, but it's far from the default or the normal setup.

Q2: You could revoke the SELECT privilege on pg_database. But doing that it not really supported. I suggest you reconsider whether you really need that.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90