pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dan Tenenbaum <dandante@gmail.com>
To: pgsql-admin@postgresql.org
Subject: the "users" group and restricting privileges
Date: Tue, 29 Nov 2005 15:30:33 -0800
Message-ID: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com> (raw)

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

view thread (4+ messages)  latest in thread

Message-ID: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com>
Permalink:  ../65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com/
Also on:    postgresql.org/message-id/65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: dandante@gmail.com
  Subject: Re: the "users" group and restricting privileges
  In-Reply-To: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com>
  References: <65029d820511291530mc4a0f62nc1de5cf58d110765@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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