pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPermissions Scenerio
2+ messages / 2 participants
[nested] [flat]
* Permissions Scenerio
@ 2008-09-15 21:07 Trent Pingenot <pintj@hotmail.com>
2008-09-15 22:02 ` Re: Permissions Scenerio Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Trent Pingenot @ 2008-09-15 21:07 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Permissions Scenerio
2008-09-15 21:07 Permissions Scenerio Trent Pingenot <pintj@hotmail.com>
@ 2008-09-15 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2008-09-15 22:02 UTC (permalink / raw)
To: Trent Pingenot <pintj@hotmail.com>; +Cc: pgsql-admin
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 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?
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-09-15 22:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-09-15 21:07 Permissions Scenerio Trent Pingenot <pintj@hotmail.com>
2008-09-15 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox