Обсуждение: Restricting database access to certain users

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.

--
Matt Perry | matt at primefactor dot com

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.

To make it easier, download pgadmin from pgadmin.postgresql.org. Get the
latest one. Look into creating a schema. You may have created tables in the
public schema. Create a different schema then grant create and usage
permissions to it accordingly. Revoke the same from public.



Stephan Szabo wrote:

> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Mon, 26 May 2003, Renney Thomas wrote:

> Look into creating a schema. You may have created tables in the public
> schema. Create a different schema then grant create and usage
> permissions to it accordingly. Revoke the same from public.

This sounds like just what I need.  I'll upgrade to 7.3 and try out
schemas.  Thanks Stephan and Renney.

 - .\\