Re: FINAL: Multi-User PostgreSQL usage SECURITY - Mailing list pgsql-admin

Guys,

In my case, I wanted a user to access only database A, with limited privileges, and I wanted to keeep users passwords encrypted (users passwords are not encrypted in pg_shadow table.):

1. I loged in template1 with user postgres.
        > psql template1 postgres
2. I created a user named dbowner with CREATEDB and CREATEUSER options
        template1=# create user dbowner with createdb createuser;
3. I quit and then accessed db template1 with user dbowner
        > psql template1 dbowner
4. I created database A, so dbowner is the owner of db A.
        template1=# create database A;
5. I created a user to be the one with limited privileges, and then granted the privileges a wanted to the tables and sequences I wanted him to have access.

        A=> create user dbguest;
        A=> grant select,insert,update,delete on table1 to dbguest
6. I used the tool pg_passwd to create a file of passwords that postgresql is gona use to authenticate the users. I named this file pgpasswords

        $ cd /var/lib/pgsql/data
        $ pg_passwd pgpasswords
        File "pgpasswords" does not exist.  Create? (y/n):y
        Username: dbguest
        New password:
        Re-enter new password:
7. I created a passord to user postgres as well:
        $ pg_passwd pgpasswords
        Username:posgres
        New password:
        Re-enter new password:
8. Now I edit the pg_hba.conf file to use this arquive of passwords:
        $ vi pg_hba.conf
        local        all                                   password pgpasswords
        host         A   127.0.0.1     255.255.255.255     password pgpasswords

If you want to give access to database B to another user, and don´t want this new user to access database A, create another file of passwords with pg_passwd, and put another line in the pg_hba.conf:

        host         B   127.0.0.1     255.255.255.255     password pgpasswordsB

Helpfull?????

See ya...

Felipe

-----Original Message-----
From: Mike Krieger [mailto:temp6453@hotmail.com]
Sent: Thursday, September 20, 2001 12:01 PM
To: Kevin McFadden; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] FINAL: Multi-User PostgreSQL usage SECURITY

Is there any way I can make any user that _LOGS IN_ as postgres (using crypt
passwords) from localhost or through sockets, access to _ALL_ databases.
Currently I have all of my lines set as sameuser.  I have tried using ident
but it doesn't work, as the user that tries to connect varries from my
username, to that of the web server, etc.

    Anyway, is there anyway to force a user to be able to access ALL
databases on the server as opposed to just the sameuser without using IDENT?

Cheers;
--
Mike

----- Original Message -----
From: "Kevin McFadden" <jkm@patriot.net>
To: <pgsql-admin@postgresql.org>
Sent: Friday, September 14, 2001 5:22 PM
Subject: Re: [ADMIN] FINAL: Multi-User PostgreSQL usage SECURITY

> ""Mike Rogers"" <temp6453@hotmail.com> wrote in message
news:OE658VnurbAMeMfHUye00004188@hotmail.com...
> > Greets all;
> >     So this issue was raised quite some time ago by many many people and
> > seems to contantly be asked by new PostgreSQL users.  I never seem to
find
> > any real answers for it.
> >
> >     I am running a multi-user system and wish to have 10 user accounts
with
> > 10 different corresponding databases.  I do not want user 'a' to be able
to
> > access user 'b's database-  Only their own 'a' database.  It really
> > shouldn't be this difficult.  I realize that I can revoke access to all
> > users on the 'a' tables, but then user B can still create tables within
user
> > A's database.
>
> Hi Mike,
>
> I've just spent the last  hour trying to figure this out for myself.
> Coming from Oracle, user management in PG is quite a mess, but when
> they add the schema stuff in it should be easier.
>
> I'm not really sure why no one has posted a concise answer in the past
> (half of those 60 minutes were mostly spent searching groups.google),
> so I hope this does what everyone has been hoping for.
>
> What I tried to accomplish was to deny users the ability to enter
> other databases and to also deny them access to template1 (why should
> general non-admin users have access to this in the first place?)  It
> should also allow the postgres user access to all (for backups,
> etc...)
>
> ------------------------------------------------------
> pg_hba.conf:
>
> local   all                                     reject
> local   sameuser                                password
> host    sameuser  127.0.0.1   255.255.255.255   password
> host    all       127.0.0.1   255.255.255.255   ident postgres
>
> pg_ident.conf:
>
> #MAP       IDENT    POSTGRES USERNAME
> postgres    postgres  postgres
> -------------------------------------------------------
>
> - The first line restricts all local access.  It may not be necessary,
> but it shouldn't hurt.
> - The second line allows userA to connect to DB userA.
> - The third line provides tcp/ip access.
> - The fourth line provides the backdoor Tom Lane has mentioned.  It
> also allows access to template1 for postgres.  It requires that identd
> is running check inetd.conf or xinetd.d/identd.  MS users may be SOL.)
>  It also requires you to set PGHOST=localhost in your environment.
> (You can't do identd checking with local, unfortunately.)
>
> Kevin
>
> PS Users were created with the CREATEDB option which was removed once
> the database was created.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org