Re: Creating a Read Only profile in 8.4

"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