pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Limiting user privileges
8+ messages / 5 participants
[nested] [flat]

* Limiting user privileges
@ 2005-01-10 17:19 Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 8+ messages in thread

From: Tad Marko @ 2005-01-10 17:19 UTC (permalink / raw)
  To: pgsql-admin

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>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
@ 2005-01-10 18:31 ` Michael Fuhr <mike@fuhr.org>
  2005-01-11 20:26   ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Fuhr @ 2005-01-10 18:31 UTC (permalink / raw)
  To: Tad Marko <tmarko@metrosplash.com>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
@ 2005-01-11 20:26   ` Tad Marko <tmarko@metrosplash.com>
  2005-01-11 21:18     ` Re: Limiting user privileges Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 8+ messages in thread

From: Tad Marko @ 2005-01-11 20:26 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin

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>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
  2005-01-11 20:26   ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
@ 2005-01-11 21:18     ` Bruno Wolff III <bruno@wolff.to>
  2005-01-11 21:23       ` Re: Limiting user privileges Bradley Kieser <brad@kieser.net>
  0 siblings, 1 reply; 8+ messages in thread

From: Bruno Wolff III @ 2005-01-11 21:18 UTC (permalink / raw)
  To: Tad Marko <tmarko@metrosplash.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
  2005-01-11 20:26   ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-11 21:18     ` Re: Limiting user privileges Bruno Wolff III <bruno@wolff.to>
@ 2005-01-11 21:23       ` Bradley Kieser <brad@kieser.net>
  2005-01-11 21:49         ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Bradley Kieser @ 2005-01-11 21:23 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Tad Marko <tmarko@metrosplash.com>; Michael Fuhr <mike@fuhr.org>; pgsql-admin

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
>
>  
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
  2005-01-11 20:26   ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-11 21:18     ` Re: Limiting user privileges Bruno Wolff III <bruno@wolff.to>
  2005-01-11 21:23       ` Re: Limiting user privileges Bradley Kieser <brad@kieser.net>
@ 2005-01-11 21:49         ` Tad Marko <tmarko@metrosplash.com>
  2005-01-12 05:49           ` Re: Limiting user privileges Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 8+ messages in thread

From: Tad Marko @ 2005-01-11 21:49 UTC (permalink / raw)
  To: Bradley Kieser <brad@kieser.net>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-admin

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>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
  2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-10 18:31 ` Re: Limiting user privileges Michael Fuhr <mike@fuhr.org>
  2005-01-11 20:26   ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
  2005-01-11 21:18     ` Re: Limiting user privileges Bruno Wolff III <bruno@wolff.to>
  2005-01-11 21:23       ` Re: Limiting user privileges Bradley Kieser <brad@kieser.net>
  2005-01-11 21:49         ` Re: Limiting user privileges Tad Marko <tmarko@metrosplash.com>
@ 2005-01-12 05:49           ` Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 8+ messages in thread

From: Bruno Wolff III @ 2005-01-12 05:49 UTC (permalink / raw)
  To: Tad Marko <tmarko@metrosplash.com>; +Cc: Bradley Kieser <brad@kieser.net>; pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Limiting user privileges
@ 2005-01-11 21:51 Richard_D_Levine@raytheon.com
  0 siblings, 0 replies; 8+ messages in thread

From: Richard_D_Levine@raytheon.com @ 2005-01-11 21:51 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-admin@postgresql.org, pgsql-admin-owner@postgresql.org, 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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-01-12 05:49 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-10 17:19 Limiting user privileges Tad Marko <tmarko@metrosplash.com>
2005-01-10 18:31 ` Michael Fuhr <mike@fuhr.org>
2005-01-11 20:26   ` Tad Marko <tmarko@metrosplash.com>
2005-01-11 21:18     ` Bruno Wolff III <bruno@wolff.to>
2005-01-11 21:23       ` Bradley Kieser <brad@kieser.net>
2005-01-11 21:49         ` Tad Marko <tmarko@metrosplash.com>
2005-01-12 05:49           ` Bruno Wolff III <bruno@wolff.to>
2005-01-11 21:51 Re: Limiting user privileges Richard_D_Levine@raytheon.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox