Thread: Granting Permissions to User To Access Database
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
--- 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/
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/
--- 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/
*** 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/
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/