Re: Restricting database access to certain users

On Mon, 26 May 2003, Matt Perry wrote:

> I'm looking into migrating several users from MySQL to Postgres and I'm
> running into a problem with security.  I don't think I understand how
> security is handled within postgres.  I'm using PostgreSQL v7.1.3 on a Red
> Hat 7.2 system.
>
> The problem is that there appears to not be a way to prevent users from
> accessing each other's databases and creating new objects in them.  As a
> test, I created userB and userB and then created databaseA and databaseB.
> I then connected to databasea as usera and created a table with one row of
> data.  I did the same with databaseb and userb.
>
> Next, I connected to databasea as userb and created a table.  Sure enough,
> postgres allowed me to create the table in databasea as userb.  Only userb
> could access this table.
>
> I wish to prevent such a situation.  There doesn't seem to be a way to do
> so.  I can grant and revoke permissions on tables but not on databases as
> a whole.

In 7.3 you can pretty much do what you want.  You may want to look into
upgrading.