Thread: Limiting user privileges
Howdy! I'm coming to postgresql from mysql, so I have a few preconceived notions that are causing me some trouble. Specifically, I'm trying to create users that have limited privileges on one database. It appears that by default, a new user has a lot of privileges all over all the databases. 1) How can I remove all privileges from all databases for a given user? 2) If I put back insert, delete and table creation privileges only on one database for a given user, will that user magically have all sorts of privileges on any databases that are created after doing #1 above? Thanks, Tad -- Tad Marko <tmarko@metrosplash.com>
On Mon, Jan 10, 2005 at 11:19:10AM -0600, Tad Marko wrote: > I'm coming to postgresql from mysql, so I have a few preconceived > notions that are causing me some trouble. Preconceived notions will do that. Execute a DROP PRECONCEIVED NOTIONS statement. > Specifically, I'm trying to create users that have limited privileges > on one database. It appears that by default, a new user has a lot > of privileges all over all the databases. In typical default configurations users can connect to any database and they have CREATE and USAGE privileges on the "public" schema. See the "Schemas" section in the "Data Definition" chapter of the documentation, in particular "The Public Schema" and "Schemas and Privileges." See also the "Client Authentication" chapter, the "Template Databases" section in the "Managing Databases" chapter, and the documentation for GRANT and REVOKE under "SQL Commands" in the "Reference" part. > 1) How can I remove all privileges from all databases for a given user? A user's ability to connect to a database is based on pg_hba.conf (see the "Client Authentication" chapter), so you can configure that file to define which users can connect to which databases. Within each database you can use GRANT and REVOKE to set privileges on the "public" schema and on other database objects (schemas, tables, views, etc.). For example, you could execute "REVOKE CREATE ON SCHEMA public FROM PUBLIC" to remove CREATE privilege on the "public" schema from all users (the PUBLIC keyword). You could even DROP the "public" schema if you don't need it. You can change how newly-created databases are set up by modifying the template1 database. See "Template Databases" as mentioned above. > 2) If I put back insert, delete and table creation privileges only on > one database for a given user, will that user magically have all sorts > of privileges on any databases that are created after doing #1 above? No, not if you've prevented the user from connecting to the other databases or revoked whatever privileges they had there (or created the database after modifying template1 so users have limited privileges by default). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, 2005-01-10 at 11:31 -0700, Michael Fuhr wrote: > On Mon, Jan 10, 2005 at 11:19:10AM -0600, Tad Marko wrote: > ... earlier comments regarding schemas and privileges ... Now I'm a bit closer to having things working, but I seem to be running into another problem granting the privileges I want for a given user. I can GRANT ALL ON a_specific_table TO user but I can't figure out how to simply give some privilege to a user on all tables. I'm pretty sure that I don't have my head wrapped around schemas well enough, so maybe what I ought to be asking is how do I create a database in a new schema and then use the schema to control user access??? Thanks, Tad -- Tad Marko <tmarko@metrosplash.com>
On Tue, Jan 11, 2005 at 14:26:15 -0600, Tad Marko <tmarko@metrosplash.com> wrote: > > I can > > GRANT ALL ON a_specific_table TO user > > but I can't figure out how to simply give some privilege to a user on > all tables. You can't do it with a single GRANT statement. You need to write a script or function to do it.
Or, the best way is to use views and not grant any priviledges on the table. The view must be created by the table owner and then grant priviledges on that view to a user. You can then create more than one view if you need, or else you can set up rules, triggers and procedures to implement what you want to implement. This means, for exmample, that if you have a user table that contains the priviledges that a user might have, you can join in that table in the view using current_user to get to the row for the present user and set behaviour accordingly. Bruno Wolff III wrote: >On Tue, Jan 11, 2005 at 14:26:15 -0600, > Tad Marko <tmarko@metrosplash.com> wrote: > > >>I can >> >>GRANT ALL ON a_specific_table TO user >> >>but I can't figure out how to simply give some privilege to a user on >>all tables. >> >> > >You can't do it with a single GRANT statement. You need to write a script >or function to do it. > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > >
On Tue, 2005-01-11 at 21:23 +0000, Bradley Kieser wrote: > Or, the best way is to use views and not grant any priviledges on the > table. The view must be created by the table owner and then grant > priviledges on that view to a user. You can then create more than one > view if you need, or else you can set up rules, triggers and procedures > to implement what you want to implement. > > This means, for exmample, that if you have a user table that contains > the priviledges that a user might have, you can join in that table in > the view using current_user to get to the row for the present user and > set behaviour accordingly. OK...I think... I'm not understanding something well enough. In MySQL (the only DB I'm very familiar with), I can create a database, import tables, create a user, and then GRANT ALL ON dbname.* TO whateveruser and then whateveruser is essentially the super user on that database. I think that I understand that in PostgreSQL, I need to make whateveruser the owner of the database, then I won't have to go through the GRANT step for him. But, what do I need to do if I need to say easily create users that have INSERT and SELECT privileges on all (of a large number of) tables in a given database? Thanks, Tad -- Tad Marko <tmarko@metrosplash.com>
I do this using the following:
CREATE GROUP agroup;
ALTER GROUP agroup ADD USER auser;
CREATE TABLE atable ...;
GRANT ALL ON atable TO GROUP agroup;
If you grant permissions to some set of groups on all tables at schema
creation time, then you only need to alter the groups to add and remove
users.
I generally create three groups, one that can modify the schema, one that
can modify the data, and one that can only read the data.
CREATE GROUP admins;
CREATE GROUP writers;
CREATE GROUP readers;
GRANT ALL ON atable TO GROUP admins;
GRANT SELECT, INSERT, UPDATE, DELETE, TEMPORARY ON atable TO GROUP writers;
-- you may want to consider EXECUTE and USAGE also, depending on what your
users are doing.
GRANT SELECT ON atable TO GROUP readers;
ALTER GROUP admins ADD USER smartguy;
ALTER GROUP writers ADD USER mostlyharmless;
ALTER GROUP readers ADD USER idiot;
Bruno Wolff III
<bruno@wolff.to> To: Tad Marko <tmarko@metrosplash.com>
Sent by: cc: Michael Fuhr <mike@fuhr.org>, pgsql-admin@postgresql.org
pgsql-admin-owner@pos Subject: Re: [ADMIN] Limiting user privileges
tgresql.org
01/11/2005 04:18 PM
On Tue, Jan 11, 2005 at 14:26:15 -0600,
Tad Marko <tmarko@metrosplash.com> wrote:
>
> I can
>
> GRANT ALL ON a_specific_table TO user
>
> but I can't figure out how to simply give some privilege to a user on
> all tables.
You can't do it with a single GRANT statement. You need to write a script
or function to do it.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Tue, Jan 11, 2005 at 15:49:32 -0600, Tad Marko <tmarko@metrosplash.com> wrote: > > In MySQL (the only DB I'm very familiar with), I can create a database, > import tables, create a user, and then > > GRANT ALL ON dbname.* TO whateveruser > > and then whateveruser is essentially the super user on that database. I > think that I understand that in PostgreSQL, I need to make whateveruser > the owner of the database, then I won't have to go through the GRANT > step for him. Making someone the owner of a database isn't going to give that person access to all other objects in the database. When other users create objects the database owner won't in general have access to them. > But, what do I need to do if I need to say easily create users that have > INSERT and SELECT privileges on all (of a large number of) tables in a > given database? You need to write a script or function that gives them appropiate access to all existing objects. Another possible solution is to have a policy of giving a specific group access to all objects that are created in the database. Then you can give new users access to these objects by just adding them to the group.