Обсуждение: Permissions Scenerio
Hello list,
I've been a PostGres user for a while, but am just now having to implement some security for a project. The hope is that we can create an environment through Roles that would allow users the ability to create a database(s) and have access to their database(s) but not have access to others' databases. I've been able to get part of the way there to create a user with createDB privileges and recording them in the pg_hba.conf file. However, when my test user creates a new database, they are the owner of that database but can't use it. Is there a way to avoid having to add user /database entry in
the pg_hba.conf file every time a user creates a new database?
Here is the current hba conf file I have
host all postgres 127.0.0.1/32 md5 # Super user admin account
host samerole testuser 127.0.0.1/32 md5
host template_db all 127.0.0.1/32 md5
Under this conf file my testuser can create a new db, but then doesn't have access to it b/c no entry exsists in the conf file.
This is probably an easy change but being new to PostGres security I'm not seeing it.
If I can't do this with Roles, can I do it with schemas to give users only access to their own stuff within a database?
Thanks in advance for any guidance
- Trent
I've been a PostGres user for a while, but am just now having to implement some security for a project. The hope is that we can create an environment through Roles that would allow users the ability to create a database(s) and have access to their database(s) but not have access to others' databases. I've been able to get part of the way there to create a user with createDB privileges and recording them in the pg_hba.conf file. However, when my test user creates a new database, they are the owner of that database but can't use it. Is there a way to avoid having to add user /database entry in
the pg_hba.conf file every time a user creates a new database?
Here is the current hba conf file I have
host all postgres 127.0.0.1/32 md5 # Super user admin account
host samerole testuser 127.0.0.1/32 md5
host template_db all 127.0.0.1/32 md5
Under this conf file my testuser can create a new db, but then doesn't have access to it b/c no entry exsists in the conf file.
This is probably an easy change but being new to PostGres security I'm not seeing it.
If I can't do this with Roles, can I do it with schemas to give users only access to their own stuff within a database?
Thanks in advance for any guidance
- Trent
Trent Pingenot <pintj@hotmail.com> writes:
> I've been a PostGres user for a while, but am just now having to implement some security for a project. The hope is
thatwe can create an environment through Roles that would allow users the ability to create a database(s) and have
accessto their database(s) but not have access to others' databases. I've been able to get part of the way there to
createa user with createDB privileges and recording them in the pg_hba.conf file. However, when my test user creates a
newdatabase, they are the owner of that database but can't use it. Is there a way to avoid having to add user
/databaseentry in
> the pg_hba.conf file every time a user creates a new database?
Don't try to enforce per-database connect permissions in pg_hba.conf;
at least, not any such permissions you don't want to have to edit that
file to change. Instead use GRANT/REVOKE CONNECT ON DATABASE.
regards, tom lane