pg.ddx.io  pgsql-testers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
4+ messages / 2 participants
[nested] [flat]

* testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
@ 2010-05-11 20:41  Lou Picciano <loupicciano@comcast.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Lou Picciano @ 2010-05-11 20:41 UTC (permalink / raw)
  To: pgsql-testers



[TEST REPORT] 

[Release]: 9.0Beta1 

[Test Type]: Privileges assignment on new database. 

[Test]: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges? 

[Platform]: Solaris 10 SPARC Enterprise 450 Quad 

[Parameters]: 

[Failure]: Don't know. Is this a (new) default behavior? 

[Results]: on a newly-installed cluster, 

1) create a new SUPERUSER 'admin', with all the bells and whistles. 

2) CREATE a new database 'test', including several tables, with owner 'admin' in PUBLIC. 

3) psql \dt command verifies owner 'admin' for all tables 

4) psql \dp command shows NO Access privileges listed. OK? 

5) INSERT data into (test)table1. WORKS. 

6) CREATE new user "Mr. User" LOGIN, etc. 

7) GRANT ALL ON DATABASE test TO "Mr. User"; 

8) SELECT * FROM table1; --------------- ERROR: permission denied for relation table1 

9) GRANT ALL ON TABLE table1 TO "Mr. User"; 

10) SELECT * FROM table1; --------------- WORKS 




[Comments]: Is this consistent behavior? Is it necessary to explicitly grant privileges to each object in a database? 

We are aware of the new ALTER DEFAULT PRIVILEGES command, but, per docs, it only affects tables (including views), sequences, and functions , correct? 
IE, it cannot affect database privileges. 

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

* Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
@ 2010-05-11 21:15  Josh Berkus <josh@agliodbs.com>
  parent: Lou Picciano <loupicciano@comcast.net>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2010-05-11 21:15 UTC (permalink / raw)
  To: Lou Picciano <loupicciano@comcast.net>; +Cc: pgsql-testers

Lou,

> *[Comments]:* Is this consistent behavior?  Is it necessary to
> explicitly grant privileges to each object in a database?  

This is correct, and how it's been for PostgreSQL since at least 7.2.

> We are aware of the new ALTER DEFAULT PRIVILEGES command, but, per docs,
> it only affects  tables (including views), sequences, and functions,
> correct?  
> IE, it cannot affect database privileges.

That's correct.  However, it's only necessary to set database priveleges
once.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com



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

* Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
@ 2010-05-11 23:04  Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2010-05-11 23:04 UTC (permalink / raw)
  To: pgsql-testers

On 5/11/10 3:55 PM, Lou Picciano wrote:
> Josh,
> 
> I'm beating a dead horse, I know:
> 
> Then, what is the effect of:
> GRANT ALL ON DATABASE fubar TO "Mr. Dataguy";
> GRANT ALL ON DATABASE fubar TO "Mrs. Datareader";  etc?
> 
> Isn't the above how we've been granting wholesale access to a db?  I
> must be missing something.

That grants CONNECT, USE and CREATE on the database to the user.  It
says nothing about individual objects.  If you want to grant on all
objects in a database, see 9.0's GRANT ON ALL feature.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com



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

* Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
@ 2010-05-12 21:56  Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2010-05-12 21:56 UTC (permalink / raw)
  To: Lou Picciano <loupicciano@comcast.net>; pgsql-testers

On 5/11/10 5:12 PM, Lou Picciano wrote:
> Oh, Oh, Oh...  OK, thanks for that.  I'll check it out.  L
> 
> (Wish the v9 docs were search-able.  I've been using them, really I have!)

So did GRANT ON ALL work for you?  I'm eager to have you test it,
*especially* since you don't seem to have a lot of prior experience with
GRANT/REVOKE in postgres.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com



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


end of thread, other threads:[~2010-05-12 21:56 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-11 20:41 testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges? Lou Picciano <loupicciano@comcast.net>
2010-05-11 21:15 ` Josh Berkus <josh@agliodbs.com>
2010-05-11 23:04 Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges? Josh Berkus <josh@agliodbs.com>
2010-05-12 21:56 Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges? Josh Berkus <josh@agliodbs.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