Thread: Creating a Read Only profile in 8.4

On Fri, 8 Jun 2012 08:29:30 -0400, Della-Croce, Gregory wrote:
> 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.

Check the permissions for that user on the schema public, too.

Jan

--
professional: http://www.oscar-consult.de
private: http://neslonek.homeunix.org/drupal/

"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

"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
Postgres8.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';

Uh, that only printed the GRANT commands you needed, it didn't execute
them.

            regards, tom lane

Am 08.06.2012 08:59, schrieb Tom Lane:

"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';

Uh, that only printed the GRANT commands you needed, it didn't execute
them.
		regards, tom lane

You'll need to grant usage on the schema to your RO_User role as well.

GRANT USAGE ON SCHEMA public TO RO_User;
 
for each database you'll allow him to connect to.