Thread: FINAL: Multi-User PostgreSQL usage SECURITY
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.
There has to be an easy solution. As a hosting solutions provider for a
small number of clients, I have always steered in the direction of MySQL for
this feature, but I am seeing some demand for PostgreSQL. I do not have the
resources to run each user with their own copy of PostgreSQL.
I have tried chaning pg_hba.conf to add the database field to the user,
but that doesn't seem to help at all.
Any thoughts? If it makes a difference, i can make the databases the same
name as the username if I must.
Please let me know if anyone knows of a way to do this.
Thanks in advance;
--
Mike
"Mike Rogers" <temp6453@hotmail.com> writes:
> I have tried chaning pg_hba.conf to add the database field to the user,
> but that doesn't seem to help at all.
Make the database field "sameuser", and then the line only allows
connection to one's own database. You will need an escape hatch to
allow you as superuser to get into everyone's db (else you can't run
pg_dumpall). The escape hatch should be a second line in pg_hba.conf
with a tighter authorization method (eg, a secondary password file
with an entry only for you).
regards, tom lane
On Fri, 7 Sep 2001, Mike Rogers wrote: > 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. > There has to be an easy solution. As a hosting solutions provider for a > small number of clients, I have always steered in the direction of MySQL for > this feature, but I am seeing some demand for PostgreSQL. I do not have the > resources to run each user with their own copy of PostgreSQL. > > I have tried chaning pg_hba.conf to add the database field to the user, > but that doesn't seem to help at all. > > Any thoughts? If it makes a difference, i can make the databases the same > name as the username if I must. IIRC, with dbnames same as user names you can use something like: host sameuser <ip> <addressmask> password to make the connections only to their own. Otherwise, I think you can use separate external password files for the different databases on different lines of the conf file... # password: Authentication is done by matching a password supplied # in clear by the host. If AUTH_ARGUMENT is specified then # the password is compared with the user's entry in that # file (in the $PGDATA directory). These per-host password # files can be maintained with the pg_passwd(1) utility. # If no AUTH_ARGUMENT appears then the password is compared # with the user's entry in the pg_shadow table.
If you want them with the ability to alter the schema within there
own database you could create them with createdb enable, create their db and
then remove that privilidge, or create the db and change the db owner.
I'm not sure how you'd grant them rights to create users. Maybe with
an external C function that would check that who was doing what was allowed
then update stuff appropriatly (maybe connecting to a special db storing any
extra user info you need, probably which owner controls which user/group,
although you might be able to do this by having a group for each db with all
users a member and a special user for a db which all groups have as a
member).
- Stuart
> -----Original Message-----
> From: Mike Rogers [SMTP:temp6453@hotmail.com]
> Sent: Friday, September 07, 2001 11:08 PM
> To: pgsql-admin@postgresql.org
> Subject: FINAL: Multi-User PostgreSQL usage SECURITY
>
> 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.
> There has to be an easy solution. As a hosting solutions provider for
> a
> small number of clients, I have always steered in the direction of MySQL
> for
> this feature, but I am seeing some demand for PostgreSQL. I do not have
> the
> resources to run each user with their own copy of PostgreSQL.
>
> I have tried chaning pg_hba.conf to add the database field to the
> user,
> but that doesn't seem to help at all.
>
> Any thoughts? If it makes a difference, i can make the databases the same
> name as the username if I must.
>
> Please let me know if anyone knows of a way to do this.
>
> Thanks in advance;
> --
> Mike
""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.
""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.
""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.
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
>
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?