pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
the "users" group and restricting privileges
4+ messages / 3 participants
[nested] [flat]

* the "users" group and restricting privileges
@ 2005-11-29 23:30 Dan Tenenbaum <dandante@gmail.com>
  2005-11-30 00:13 ` Re: the "users" group and restricting privileges Jim C. Nasby <jnasby@pervasive.com>
  2005-11-30 00:21 ` Re: the "users" group and restricting privileges Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Dan Tenenbaum @ 2005-11-29 23:30 UTC (permalink / raw)
  To: pgsql-admin

I want to create a postgresql user with restricted permissions--all it
should be able to do is select on a few views that I specify.

So I did the following, as the database owner:
create user viewer password 'xxxx';
grant select on myview to viewer;

Then, when I start psql as the viewer user, specifying the same database
with the -d switch, I try this:
select * from myview;
and get this:
ERROR: permission denied for schema myschema
What do I need to do to get the correct permissions?

Also, there is a group called "users" and the database owner is in that
group. But I have not granted any explicit privileges to that group. And the
database is owned by a particular user, not a group. However, I notice that
when I added the "viewer" user to the "users" group, that the user seemed to
be able to do everything that the database owner could do. That is not what
I want. But the above (not being able to select a view that I thought I had
select permission for) is not what I want either.

The above paragraph would seem to suggest that a group called "users" has
some special properties. I could not find any documentation for that in the
postgres docs (I am using version 7.4). Perhaps I couldn't find it because
almost every page in the docs seems to have the word "users" in it, so it is
hard to disambiguate my search.
If someone could point me towards documentation of the special properties of
the "users" group that would be helpful...but the thing I most want help
with is creating a user with restricted views as described above.

Thanks

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

* Re: the "users" group and restricting privileges
  2005-11-29 23:30 the "users" group and restricting privileges Dan Tenenbaum <dandante@gmail.com>
@ 2005-11-30 00:13 ` Jim C. Nasby <jnasby@pervasive.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-11-30 00:13 UTC (permalink / raw)
  To: Dan Tenenbaum <dandante@gmail.com>; +Cc: pgsql-admin

On Tue, Nov 29, 2005 at 03:30:33PM -0800, Dan Tenenbaum wrote:
> I want to create a postgresql user with restricted permissions--all it
> should be able to do is select on a few views that I specify.
> 
> So I did the following, as the database owner:
> create user viewer password 'xxxx';
> grant select on myview to viewer;
> 
> Then, when I start psql as the viewer user, specifying the same database
> with the -d switch, I try this:
> select * from myview;
> and get this:
> ERROR: permission denied for schema myschema
> What do I need to do to get the correct permissions?

GRANT USAGE ON SCHEMA myschema TO viewer;

> Also, there is a group called "users" and the database owner is in that
> group. But I have not granted any explicit privileges to that group. And the
> database is owned by a particular user, not a group. However, I notice that
> when I added the "viewer" user to the "users" group, that the user seemed to
> be able to do everything that the database owner could do. That is not what
> I want. But the above (not being able to select a view that I thought I had
> select permission for) is not what I want either.
> 
> The above paragraph would seem to suggest that a group called "users" has
> some special properties. I could not find any documentation for that in the
> postgres docs (I am using version 7.4). Perhaps I couldn't find it because
> almost every page in the docs seems to have the word "users" in it, so it is
> hard to disambiguate my search.
> If someone could point me towards documentation of the special properties of
> the "users" group that would be helpful...but the thing I most want help
> with is creating a user with restricted views as described above.

There is no default users group, only PUBLIC (which isn't really a group
in the system afaict).

If you install newsysviews (http://pgfoundry.org/projects/newsysviews/)
you can query pg_user_grants to help diagnose where the permissions are
comming from. Or you could use a big, hairy query to do it...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



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

* Re: the "users" group and restricting privileges
  2005-11-29 23:30 the "users" group and restricting privileges Dan Tenenbaum <dandante@gmail.com>
@ 2005-11-30 00:21 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-11-30 02:36   ` Re: the "users" group and restricting privileges Dan Tenenbaum <dandante@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-11-30 00:21 UTC (permalink / raw)
  To: Dan Tenenbaum <dandante@gmail.com>; +Cc: pgsql-admin

Dan Tenenbaum <dandante@gmail.com> writes:
> ERROR: permission denied for schema myschema
> What do I need to do to get the correct permissions?

GRANT USAGE ON SCHEMA myschema TO whoever

Schema access is comparable to directory access in a filesystem: if you
can't look into the directory, it doesn't matter what privileges you
might have for the individual files in it.

			regards, tom lane



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

* Re: the "users" group and restricting privileges
  2005-11-29 23:30 the "users" group and restricting privileges Dan Tenenbaum <dandante@gmail.com>
  2005-11-30 00:21 ` Re: the "users" group and restricting privileges Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-30 02:36   ` Dan Tenenbaum <dandante@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Dan Tenenbaum @ 2005-11-30 02:36 UTC (permalink / raw)
  To: pgsql-admin

Thanks to both you and Jim--this was the missing piece that made things
work.


On 11/29/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dan Tenenbaum <dandante@gmail.com> writes:
> > ERROR: permission denied for schema myschema
> > What do I need to do to get the correct permissions?
>
> GRANT USAGE ON SCHEMA myschema TO whoever
>
> Schema access is comparable to directory access in a filesystem: if you
> can't look into the directory, it doesn't matter what privileges you
> might have for the individual files in it.
>
>                         regards, tom lane
>

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


end of thread, other threads:[~2005-11-30 02:36 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-29 23:30 the "users" group and restricting privileges Dan Tenenbaum <dandante@gmail.com>
2005-11-30 00:13 ` Jim C. Nasby <jnasby@pervasive.com>
2005-11-30 00:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-30 02:36   ` Dan Tenenbaum <dandante@gmail.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