Skip site navigation (1) Skip section navigation (2)

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

From: Lou Picciano <loupicciano(at)comcast(dot)net>
To: pgsql-testers <pgsql-testers(at)postgresql(dot)org>
Subject: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
Date: 2010-05-11 20:41:00
Message-ID: 1987554247.9039561273610460655.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net (view raw or flat)
Thread:
Lists: 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. 

Responses

pgsql-testers by date

Next:From: Josh BerkusDate: 2010-05-11 21:15:02
Subject: Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
Previous:From: Lou PiccianoDate: 2010-05-07 23:47:29
Subject: ERROR: cannot drop objects owned by role postgres because they are required by the database system

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group