Thread: database privileges and access control
Hello, I have 2 questions. The first one (I know it been discussed already, but I did not find the solution). If I have user A and user B and database DB1 and database DB2 and only local connections. How can I configure the pg_hba.conf to let user A connect only to the DB1 database and let user B connect only to the DB2 database. The sameuser param is not good here. local sameuser password local all password admins The file $PGDATA/admins contains the usernames of all users that allowed to connect to all databases. I tried to add next line local DB1 password DB1_users local DB2 password DB2_users and added the users that allowed to connect to each database to those files accordingly. The user can connect to sameuser database but not to the DB1 or DB2 database with error incorrect password. What is the problem ? Second problem is : How can I make users to see only the database that they own with the \l command ? I don't want users to see all the databases on this host but only those that they own. Thanks Roman -- ----------------------------------------------------------------------------- Roman Gavrilov Aduva Inc., Web Development Services. work +972-3-7534373 mobile +972-54-834668 romio@aduva.com, romio@netvision.net.il
Artur Pietruk wrote: >On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote: > > >>Hello, >> >>I have 2 questions. >>If I have user A and user B and database DB1 and database DB2 and only >>local connections. >> >>How can I configure the pg_hba.conf to let user A connect only to the >>DB1 database and let user B connect only to the DB2 database. >>The sameuser param is not good here. >> >>local sameuser password >>local all password admins >> >>The file $PGDATA/admins contains the usernames of all users that allowed >>to connect to all databases. >>I tried to add next line >>local DB1 password DB1_users >>local DB2 password DB2_users >> >>and added the users that allowed to connect to each database to those >>files accordingly. >>The user can connect to sameuser database but not to the DB1 or DB2 >>database with error incorrect password. >> >>What is the problem ? >> >> > > Which version of PostgreSQL are you using? > > With PG 7.3 there is easy solution to your problem. In that >version, there is "user" field, for pg_hba.conf: > ># TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD > > For your PG configuration - try to change order of entries in >pg_hba.conf (order does matter), e.g. make it something like this: > >====8<==== >local DB1 password DB1_users >local DB2 password DB2_users >local all password admins >local sameuser password >====8<==== > > Restart pgsql and see. > > The version is 7.2.1 and I tried to play with the order like you saying , but still it didn't help :( > > >>Second problem is : >>How can I make users to see only the database that they own with the \l >>command ? >>I don't want users to see all the databases on this host but only those >>that they own. >> >> > > AFAIK that's not possible. But I might be wrong - things were >changing lately, check/search docs. > > Best regards, > > Thanks -- ----------------------------------------------------------------------------- Roman Gavrilov Aduva Inc., Web Development Services. work +972-3-7534373 mobile +972-54-834668 romio@aduva.com, romio@netvision.net.il
On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote:
> Hello,
>
> I have 2 questions.
> If I have user A and user B and database DB1 and database DB2 and only
> local connections.
>
> How can I configure the pg_hba.conf to let user A connect only to the
> DB1 database and let user B connect only to the DB2 database.
> The sameuser param is not good here.
>
> local sameuser password
> local all password admins
>
> The file $PGDATA/admins contains the usernames of all users that allowed
> to connect to all databases.
> I tried to add next line
> local DB1 password DB1_users
> local DB2 password DB2_users
>
> and added the users that allowed to connect to each database to those
> files accordingly.
> The user can connect to sameuser database but not to the DB1 or DB2
> database with error incorrect password.
>
> What is the problem ?
Which version of PostgreSQL are you using?
With PG 7.3 there is easy solution to your problem. In that
version, there is "user" field, for pg_hba.conf:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
For your PG configuration - try to change order of entries in
pg_hba.conf (order does matter), e.g. make it something like this:
====8<====
local DB1 password DB1_users
local DB2 password DB2_users
local all password admins
local sameuser password
====8<====
Restart pgsql and see.
> Second problem is :
> How can I make users to see only the database that they own with the \l
> command ?
> I don't want users to see all the databases on this host but only those
> that they own.
AFAIK that's not possible. But I might be wrong - things were
changing lately, check/search docs.
Best regards,
--
--- Artur Pietruk, arturp@plukwa.net
Roman Gavrilov wrote: > Artur Pietruk wrote: > >> On Wed, Mar 05, 2003 at 03:37:16PM +0200, Roman Gavrilov wrote: >> >> >>> Hello, >>> >>> I have 2 questions. >>> If I have user A and user B and database DB1 and database DB2 and >>> only local connections. >>> >>> How can I configure the pg_hba.conf to let user A connect only to >>> the DB1 database and let user B connect only to the DB2 database. >>> The sameuser param is not good here. >>> >>> local sameuser password >>> local all password admins >>> >>> The file $PGDATA/admins contains the usernames of all users that >>> allowed to connect to all databases. >>> I tried to add next line >>> local DB1 password DB1_users >>> local DB2 password DB2_users >>> >>> and added the users that allowed to connect to each database to >>> those files accordingly. >>> The user can connect to sameuser database but not to the DB1 or DB2 >>> database with error incorrect password. >>> >>> What is the problem ? >>> >> >> >> Which version of PostgreSQL are you using? >> >> With PG 7.3 there is easy solution to your problem. In that >> version, there is "user" field, for pg_hba.conf: >> >> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD >> >> For your PG configuration - try to change order of entries in >> pg_hba.conf (order does matter), e.g. make it something like this: >> >> ====8<==== >> local DB1 password DB1_users >> local DB2 password DB2_users >> local all password admins >> local sameuser password >> ====8<==== >> >> Restart pgsql and see. >> >> > The version is 7.2.1 > and I tried to play with the order like you saying , but still it > didn't help :( > >> >> >>> Second problem is : >>> How can I make users to see only the database that they own with the >>> \l command ? >>> I don't want users to see all the databases on this host but only >>> those that they own. >>> >> >> >> AFAIK that's not possible. But I might be wrong - things were >> changing lately, check/search docs. >> >> Best regards, >> >> > > Thanks -- ----------------------------------------------------------------------------- Roman Gavrilov Aduva Inc., Web Development Services. work +972-3-7534373 mobile +972-54-834668 romio@aduva.com, romio@netvision.net.il