[lug] postgres question

[Hugh Brown]

On Thu, 2003-11-13 at 01:40, dan radom wrote:
> * Hugh Brown (hugh at math.byu.edu) wrote:
> > On Wed, 2003-11-12 at 21:40, dan radom wrote:
> > > * Hugh Brown (hugh at math.byu.edu) wrote:
> > > > I am doing something that I could swear I've done a number of times.
> > > > 
> > > > As user postgres I create a user and a database.  Then as postgres I run
> > > > GRANT ALL ON DATABASE newdb TO newuser;
> > > > 
> > > > In the past, that has been sufficient to give newuser privileges on
> > > > everything.
> > > > 
> > > > This time when I do it, I find that it hasn't granted me privileges to
> > > > anything.
> > > > 
> > > > newuser connects, tries a select and it fails with a permission denied.
> > > > 
> > > > It seems terribly stupid to have GRANT ALL ON DATABASE do absolutely
> > > > noting except print "GRANT"
> > > > 
> > > 
> > > pdel -s newdb
> > > 
> > > newdb=#\dp
> > > 
> > > will show you the permissions.  grant all on db to user; should get it.
> > > 
> > > dan
> > 
> > I tried that.  No perms show up.  I think all that grant all on database
> > allows you to do is create new tables, new views, new triggers etc.
> > 
> > So, apparently in the past I have populated as the new user, not as
> > postgres and then a grant all.  Disappointing command.
> > 
> > Hugh
> 
> Nah.  I do that all the time.  Here's a snippit from a sql file ...
> 
> GRANT ALL on lostfound_id_seq,screenshots_id_seq,pictures_id_seq,downloads_id_seq,users_id_seq to graffix;
> 
> It works just fine and does in fact grant the appropriate premissions.
> Does the user in fact exist?  \du from psql will show you the users.
> Perhaps you need to CREATE USER foo; first?  What version of postgres ..
> SELECT version(); just in case :)
> 
> dan

We're going at right angles.  If you look at \h GRANT, it describes
grants on tables, databases, functions, languages, and schemas.  I've
got a database with 20+ of the above.  Doing the grant as you describe
is tedious.  I falsely assumed that 

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
    ON DATABASE dbname [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...]

meant that I could do a grant on DATABASE dbname and have all
tables,views,sequences, etc. automatically get their privileges
updated.  Postgres is version 7.3.2 (built from the redhat rpms for rh9
I believe).

Hugh


> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: lug.boulder.co.us port=6667 channel=#colug