Thread: Granting Permissions to User To Access Database

Granting Permissions to User To Access Database

From
"Kevin Schachter"
Date:
I recently installed Postgres on my server to rid of the horrible
limitations of Mysql. However I am having some trouble setting up
permissions as they are in MySQL.

In MySQL you can grant a user select permissions to all tables in a database
with the command "GRANT select on db.* to .....". Is there a similar way to
accomplish this in Postgres? Reading up on GRANT I see that it only works
for individual tables and not a full database.

As well, how can I restrict a user to only one database. In pg_hba.conf
there is nothing that specifies the user name. I can grant access to the
database to all users on an ip, but I can't grant it to only one user.

Thanks in advance,

Kevin


Re: Granting Permissions to User To Access Database

From
R D
Date:
--- Kevin Schachter <kevin@kevinity.com> wrote:
> I recently installed Postgres on my server to rid of
> the horrible
> limitations of Mysql. However I am having some
> trouble setting up
> permissions as they are in MySQL.
>
> In MySQL you can grant a user select permissions to
> all tables in a database
> with the command "GRANT select on db.* to .....". Is
> there a similar way to
> accomplish this in Postgres? Reading up on GRANT I
> see that it only works
> for individual tables and not a full database.
>
> As well, how can I restrict a user to only one
> database. In pg_hba.conf
> there is nothing that specifies the user name. I can
> grant access to the
> database to all users on an ip, but I can't grant it
> to only one user.
>
> Thanks in advance,
>
> Kevin
>

You can do this by using external password file. Edit
pg_hba.conf
host database x.x.x.x y.y.y.y password passwdfile

where database is database name, x.x.x.x and y.y.y.y
are ip and netmask and passwdfile is the name of the
file with passwords for the allowed users.
Read the documents for more info!

This is one of the features of PgSQL that I do not
like. It is much nicer to type:
"grant all on database.table to ....."
And I asked the developers to do that but they did not
take it very seriously.
There is one thing I do not like: you can not disallow
users to create tables. Every user having access to a
database can create a table in it. This was in the
todo list of 7.1 but now it is removed. Stupid...
But....

Anyway Postgres is an excellent database!

Rumen

__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/

Re: Granting Permissions to User To Access Database

From
Peter Eisentraut
Date:
R D writes:

> This is one of the features of PgSQL that I do not
> like. It is much nicer to type:
> "grant all on database.table to ....."
> And I asked the developers to do that but they did not
> take it very seriously.

The problem with this approach is that if you do "revoke all on database
from all" you have hosed your system.  Text files allow recovery in these
situations.

> There is one thing I do not like: you can not disallow
> users to create tables. Every user having access to a
> database can create a table in it. This was in the
> todo list of 7.1 but now it is removed. Stupid...

It was not possible to implement it without busting pg_dumpall, thus
creating an upgrading nightmare.  It will be in 7.2.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Re: Granting Permissions to User To Access Database

From
R D
Date:
--- Peter Eisentraut <peter_e@gmx.net> wrote:
> R D writes:
>
> > This is one of the features of PgSQL that I do not
> > like. It is much nicer to type:
> > "grant all on database.table to ....."
> > And I asked the developers to do that but they did
> not
> > take it very seriously.
>
> The problem with this approach is that if you do
> "revoke all on database
> from all" you have hosed your system.  Text files
> allow recovery in these
> situations.

It could be found some workarround of the problem.
Probably some key in postgres will do to use the
pg_hba.conf instead. So you will be able to connect
and
repare the problem ;).

Another solution could be some statement in
pg_hba.conf telling the postgresql to use internal
database privileges instead of this defined in
pg_hba.conf, and when the problem occurs you just
swich to the db privileges defined in pg_hba.conf.This
will make you able to connect and repare the problem
(What i meen with internal and external privileges?
OK! same as the internal and the external passwords)

The second one has some advantages: you can repare the
problem without restarting the postgresql.

> > There is one thing I do not like: you can not
> disallow
> > users to create tables. Every user having access
> to a
> > database can create a table in it. This was in the
> > todo list of 7.1 but now it is removed. Stupid...
>
> It was not possible to implement it without busting
> pg_dumpall, thus
> creating an upgrading nightmare.  It will be in 7.2.
>

I hope to see this :)

regards,
Rumen


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/

Re: Granting Permissions to User To Access Database

From
Radoslaw Stachowiak
Date:
*** Peter Eisentraut <peter_e@gmx.net> [Tuesday, 09.January.2001, 18:50 +0100]:
> > This is one of the features of PgSQL that I do not
> > like. It is much nicer to type:
> > "grant all on database.table to ....."
> > And I asked the developers to do that but they did not
> > take it very seriously.
>
> The problem with this approach is that if you do "revoke all on database
> from all" you have hosed your system.  Text files allow recovery in these
> situations.

thats completly wrong :) look at the whole UNIX dir permissions topic.
Using postgres super user, there is NO check on permission (or at least
there should not be).


--
radoslaw.stachowiak.........................................http://alter.pl/

Re: Granting Permissions to User To Access Database

From
Peter Eisentraut
Date:
Radoslaw Stachowiak writes:

> > The problem with this approach is that if you do "revoke all on database
> > from all" you have hosed your system.  Text files allow recovery in these
> > situations.
>
> thats completly wrong :) look at the whole UNIX dir permissions topic.
> Using postgres super user, there is NO check on permission (or at least
> there should not be).

But, unlike in Unix, you don't know what user a connection wants to be
before you have done authentication (pg_hba.conf), nor do you know whether
a user is a superuser before querying the database.

We could probably get past the first problem by separating the
_authentication_ and _authorization_ modules, and we could get by the
second issue by doing the connection, checking, and then disconnecting,
but that exposes the database to possible attacks by unprivileged users,
so it has to be evaluated carefully.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/