From: Jerry Sievers <gsievers19@comcast.net>
To: Della-Croce\, Gregory <Greg_Della-Croce@wycliffe.org>
Cc: pgsql-admin\@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Creating a Read Only profile in 8.4
Date: Fri, 08 Jun 2012 09:22:53 -0400
Message-ID: <87fwa6lyz6.fsf@comcast.net> (raw)
In-Reply-To: <F1CFB1E7A51CD846898BB039F441E04F095A53C641@elijah>
References: <F1CFB1E7A51CD846898BB039F441E04F095A53C641@elijah>
"Della-Croce, Gregory" <Greg_Della-Croce@wycliffe.org> 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
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, Greg_Della-Croce@wycliffe.org
Subject: Re: Creating a Read Only profile in 8.4
In-Reply-To: <87fwa6lyz6.fsf@comcast.net>
* 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