Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 7C2FFD84656 for ; Fri, 8 Jun 2012 10:23:15 -0300 (ADT) Received: from qmta03.westchester.pa.mail.comcast.net ([76.96.62.32]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Scz9h-000351-3Q for pgsql-admin@postgresql.org; Fri, 08 Jun 2012 13:23:14 +0000 Received: from omta22.westchester.pa.mail.comcast.net ([76.96.62.73]) by qmta03.westchester.pa.mail.comcast.net with comcast id KoPM1j00F1ap0As53pNtFl; Fri, 08 Jun 2012 13:22:53 +0000 Received: from homie ([68.45.149.190]) by omta22.westchester.pa.mail.comcast.net with comcast id KpNt1j00A46izrY3ipNtri; Fri, 08 Jun 2012 13:22:53 +0000 Received: by homie (Postfix, from userid 1000) id 1B8D7181CBF; Fri, 8 Jun 2012 09:22:53 -0400 (EDT) To: "Della-Croce\, Gregory" Cc: "pgsql-admin\@postgresql.org" Subject: Re: Creating a Read Only profile in 8.4 References: From: Jerry Sievers Date: Fri, 08 Jun 2012 09:22:53 -0400 In-Reply-To: (Gregory Della-Croce's message of "Fri\, 8 Jun 2012 08\:29\:30 -0400") Message-ID: <87fwa6lyz6.fsf@comcast.net> User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -0.7 (/) X-Archive-Number: 201206/33 X-Sequence-Number: 39624 "Della-Croce, Gregory" writes: > I am new at the Postgres DBA ó Admin stuff. So when asked to create a read only profile for our database in Postgres > 8.4 I did the following: > > · create role RO_User password 'xxxxxxxxxxx' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN; > > · select 'grant select on ' || tablename || ' to \"RO_User\"; 'from pg_tables where schemaname = 'public'; > You did actually save the output from the above select command and run it as SQL and commit, right? And FWIW; it's a good practice to have a RO *role* in the DB and then grant that role to new users as they are created to give RO access unless it's the case that this one user only will ever have full RO access. Going a step further, in multi schema DBs, it can be nice to define a DB wide RO role and per schema RO roles. The per schema roles are the ones given direct object grants and the DB wide RO role is granted each of the schema specific roles. Now you can easily grant RO across the whole DB by granting same to a user and of course do so on a schema by schema basis using those individual roles instead. > The commands worked fine. But when I connected as RO_User and did a select against a simple table in the DB, I got > access denied on the table. > > Can anyone point to my error? > > Greg Della-Croce > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 732.216.7255