#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12

    How restrict users to a database ?


    I need to restrict users access to a database.

    Say, 'user1' can access only database 'db1'
    'user2' can access database 'db2'
    'user3' can access databases 'db1' & 'db2'

    Can I do this using any configuration file, like pg_hba.conf, etc.?

    Thanks for the input.

    Regards.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    UA
    Posts
    2
    Rep Power
    0

    pg_ident.conf & pg_hba.conf


    If you do not want use GRANT and REVOKE,
    you can use pg_ident.conf and pg_hba.conf



    pg_hda.conf
    --

    host db1 0.0.0.0 0.0.0.0 ident i1
    host db2 0.0.0.0 0.0.0.0 ident i2


    pg_ident.conf
    --

    i1 user1 postgres
    i1 user3 postgres
    i2 user2 postgres
    i2 user3 postgres


    ----
    Sincerely,
    Andrew A.Romanchenko
    Programmer of Rapid Internet Development Department
    Alar Information Technologies, www.alarit.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    an easy way to do this is to simply create the database as the user you'd like to restrict the database to

    -niyogi

    PostgreSQL web Hosting http://www.pghoster.com
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12
    From what I see, pg_ident.conf is only a mapping of OS usernames to PostgreSQL usernames. Correct me if I am wrong.

    However, what I need is a mapping of database username to databases...

    For eg., if 'u_hris' is database username and
    if 'hris' & 'dev' are PostgreSQL databases...

    ... I need a mapping of PostgreSQL username 'u_hris' to PostgreSQL database 'hris'.

    Thanks.
    Last edited by ggnanaraj; October 21st, 2002 at 05:31 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    Only database users that have been granted explicit access to the database with GRANT can access that database. By default, nobody can access a database unless you tell them they can.

    It will be something like this:


    GRANT ALL ON hris TO u_hris

    or

    GRANT SELECT ON hris TO u_hris

    etc, etc,

    http://www.postgresql.org/idocs/inde...rivileges.html

IMN logo majestic logo threadwatch logo seochat tools logo