Thread: database permissions:how to create read only user for db
Hello all. Please help, are there any way to set some (ex. deny creating tables) privileges on database (not for databases objects)? I want crant full access to one user (let say "adminuser" for database "testdb") and make some others (read only) users, who can read only (do SELECT from "testdb" tables), but not CREATE tables or some database objects. As I know CRANT command allows only set privileges for database objects but not for databases. So, can I realize this using Postgres 7.1, or it's not possible at all? Thanks in advance. /lexx/
On Sat, Dec 25, 2004 at 15:54:56 +0600, lexx@gorodok.net wrote: > Hello all. > > Please help, > are there any way to set some (ex. deny creating tables) privileges on database (not for databases objects)? > > I want crant full access to one user (let say "adminuser" for database "testdb") and make some others (read only) users, > who can read only (do SELECT from "testdb" tables), but not CREATE tables or some database objects. > > As I know CRANT command allows only set privileges for database objects but not for databases. You can write scripts that will set access for all existing objects in a database. Objects created in the future will not automatically get the same access rights. It mnay be possible to simplify granting rights by using 'public' to give select access, if all users get at least select access. > So, can I realize this using Postgres 7.1, > or it's not possible at all? You really don't want to be using 7.1. You should probably upgrade to 7.4.6, unless this project is in the development phase, in which case using an 8.0 prelease with an eye toward using 8.0 after it is released (probably in about two weeks) may make more sense. 7.1 isn't getting any more fixes now and there are some important bugs in that version. You may also see significantly better performance in later versions.
>> Hello all. >> >> Please help, >> are there any way to set some (ex. deny creating tables) privileges on database (not for databases objects)? >> >> I want crant full access to one user (let say "adminuser" for database "testdb") and make some others (read only) users, >> who can read only (do SELECT from "testdb" tables), but not CREATE tables or some database objects. >> >> As I know CRANT command allows only set privileges for database objects but not for databases. BWI> You can write scripts that will set access for all existing objects in BWI> a database. Objects created in the future will not automatically get the BWI> same access rights. It mnay be possible to simplify granting rights by BWI> using 'public' to give select access, if all users get at least select access. I see what you mean, but I want set access rights to database, not for database objects, I want to deny some users to create tables in database, but permit read values of existing tables. How to permit various access to tables I know, it's simlpy with `GRANT [..] ON TABLE ..', but I don't know how to deny creating new tables, and I think it's not possible with PostgreSQL, because as I can understand, GRANT don't support such ability for DATABASE. Am I right? will be very fine if I'm mistaken.. THanks. /lexx/
On Sun, Dec 26, 2004 at 13:41:22 +0600, lexx@gorodok.net wrote: > > I see what you mean, but I want set access rights to database, not > for database objects, I want to deny some users to create tables in > database, but permit read values of existing tables. There is a separate privilege for controlling creation of tables (at least in recent versions - I am not sure about 7.1). By default users will not have access to objects they didn't create (with a couple of minor exceptions - see the documentation on GRANT). > How to permit various access to tables I know, it's simlpy with `GRANT > [..] ON TABLE ..', but I don't know how to deny creating new tables, > and > I think it's not possible with PostgreSQL, because as I can > understand, GRANT don't support such ability for DATABASE. It certainly is possible in recent versions.