Re: How to grant a user read-only access to a database?

Hi,

I tried this:

names=# grant select on database names to spice;
ERROR: invalid privilege type SELECT for database

The documentation seems to imply I need to grant SELECT
to each table separately. That's a lot of work, and what if
new tables are created?

Thanks,
Antonio

On 2 March 2010 11:12, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com> wrote:
> Hi,
>
> I tried this:
>
> names=# grant select on database names to spice;
> ERROR:  invalid privilege type SELECT for database
>
> The documentation seems to imply I need to grant SELECT
> to each table separately. That's a lot of work, and what if
> new tables are created?
>
> Thanks,
> Antonio
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

The privileges you can grant on a database are only related to the
creation of tables and connecting to that database.

You could create a role which has SELECT-only access, apply that role
to all your tables, and assign users (other roles) as members of that
role.

Regards

Thom

On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:

> On 2 March 2010 11:12, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
> wrote:
> > Hi,
> >
> > I tried this:
> >
> > names=# grant select on database names to spice;
> > ERROR: invalid privilege type SELECT for database
> >
> > The documentation seems to imply I need to grant SELECT
> > to each table separately. That's a lot of work, and what if
> > new tables are created?
> >
> > Thanks,
> > Antonio
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> The privileges you can grant on a database are only related to the
> creation of tables and connecting to that database.
>
> You could create a role which has SELECT-only access, apply that role
> to all your tables, and assign users (other roles) as members of that
> role.
>
> Regards
>
> Thom
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

How to create that ? I'm also interested in this as I need this for backing
up my databases.

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

On 2 March 2010 11:46, Nilesh Govindarajan <lists(at)itech7(dot)com> wrote:
> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
>>
>> On 2 March 2010 11:12, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
>> wrote:
>> > Hi,
>> >
>> > I tried this:
>> >
>> > names=# grant select on database names to spice;
>> > ERROR:  invalid privilege type SELECT for database
>> >
>> > The documentation seems to imply I need to grant SELECT
>> > to each table separately. That's a lot of work, and what if
>> > new tables are created?
>> >
>> > Thanks,
>> > Antonio
>> >
>> > --
>> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>> >
>>
>> The privileges you can grant on a database are only related to the
>> creation of tables and connecting to that database.
>>
>> You could create a role which has SELECT-only access, apply that role
>> to all your tables, and assign users (other roles) as members of that
>> role.
>>
>> Regards
>>
>> Thom
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> How to create that ? I'm also interested in this as I need this for backing
> up my databases.
>
> --

Okay, here's an example:

CREATE ROLE readonly; -- This user won't be able to do anything by
default, not even log in

GRANT SELECT on table_a TO readonly;
GRANT SELECT on table_b TO readonly;
GRANT SELECT on table_c TO readonly;

CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
this user to any group

SET ROLE testuser;
SELECT * FROM table_a;

We get:
ERROR: permission denied for relation table_a

SET ROLE postgres;

DROP ROLE testuser;
CREATE ROLE testuser WITH LOGIN IN ROLE readonly;

SET ROLE testuser;
SELECT * FROM table_a;

This would then return the results from table_a

Regards

Thom

On 2/03/2010 8:00 PM, Thom Brown wrote:

> CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
> this user to any group
>
> SET ROLE testuser;
> SELECT * FROM table_a;
>
> We get:
> ERROR: permission denied for relation table_a

... if table_a doesn't have grants to public, which it may well. I like
to revoke public access to my schema and to my database to make very,
very sure that only roles I've explicitly allowed can get in.

I prefer to explicitly revoke all rights from public on objects.

--
Craig Ringer

Op 02-03-10 13:00, Thom Brown schreef:
> On 2 March 2010 11:46, Nilesh Govindarajan<lists(at)itech7(dot)com> wrote:
>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown<thombrown(at)gmail(dot)com> wrote:
>>>
>>> On 2 March 2010 11:12, Antonio Goméz Soto<antonio(dot)gomez(dot)soto(at)gmail(dot)com>
>>> wrote:
>>>> Hi,
>>>>
>>>> I tried this:
>>>>
>>>> names=# grant select on database names to spice;
>>>> ERROR: invalid privilege type SELECT for database
>>>>
>>>> The documentation seems to imply I need to grant SELECT
>>>> to each table separately. That's a lot of work, and what if
>>>> new tables are created?
>>>>
>>>> Thanks,
>>>> Antonio
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>
>>>
>>> The privileges you can grant on a database are only related to the
>>> creation of tables and connecting to that database.
>>>
>>> You could create a role which has SELECT-only access, apply that role
>>> to all your tables, and assign users (other roles) as members of that
>>> role.
>>>
>>> Regards
>>>
>>> Thom
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>> How to create that ? I'm also interested in this as I need this for backing
>> up my databases.
>>
>> --
>
> Okay, here's an example:
>
> CREATE ROLE readonly; -- This user won't be able to do anything by
> default, not even log in
>
> GRANT SELECT on table_a TO readonly;
> GRANT SELECT on table_b TO readonly;
> GRANT SELECT on table_c TO readonly;
>
> CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
> this user to any group
>
> SET ROLE testuser;
> SELECT * FROM table_a;
>
> We get:
> ERROR: permission denied for relation table_a
>
> SET ROLE postgres;
>
> DROP ROLE testuser;
> CREATE ROLE testuser WITH LOGIN IN ROLE readonly;
>
> SET ROLE testuser;
> SELECT * FROM table_a;
>
> This would then return the results from table_a
>
> Regards
>
> Thom

But I still need to define access to each table separately?

Thanks,
Antonio.

On 2 March 2010 14:49, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com> wrote:
> Op 02-03-10 13:00, Thom Brown schreef:
>>
>> On 2 March 2010 11:46, Nilesh Govindarajan<lists(at)itech7(dot)com>  wrote:
>>>
>>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown<thombrown(at)gmail(dot)com>  wrote:
>>>>
>>>> On 2 March 2010 11:12, Antonio Goméz Soto<antonio(dot)gomez(dot)soto(at)gmail(dot)com>
>>>> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I tried this:
>>>>>
>>>>> names=# grant select on database names to spice;
>>>>> ERROR:  invalid privilege type SELECT for database
>>>>>
>>>>> The documentation seems to imply I need to grant SELECT
>>>>> to each table separately. That's a lot of work, and what if
>>>>> new tables are created?
>>>>>
>>>>> Thanks,
>>>>> Antonio
>>>>>
>>>>> --
>>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>>
>>>>
>>>> The privileges you can grant on a database are only related to the
>>>> creation of tables and connecting to that database.
>>>>
>>>> You could create a role which has SELECT-only access, apply that role
>>>> to all your tables, and assign users (other roles) as members of that
>>>> role.
>>>>
>>>> Regards
>>>>
>>>> Thom
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>> How to create that ? I'm also interested in this as I need this for
>>> backing
>>> up my databases.
>>>
>>> --
>>
>> Okay, here's an example:
>>
>> CREATE ROLE readonly; -- This user won't be able to do anything by
>> default, not even log in
>>
>> GRANT SELECT on table_a TO readonly;
>> GRANT SELECT on table_b TO readonly;
>> GRANT SELECT on table_c TO readonly;
>>
>> CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
>> this user to any group
>>
>> SET ROLE testuser;
>> SELECT * FROM table_a;
>>
>> We get:
>> ERROR:  permission denied for relation table_a
>>
>> SET ROLE postgres;
>>
>> DROP ROLE testuser;
>> CREATE ROLE testuser WITH LOGIN IN ROLE readonly;
>>
>> SET ROLE testuser;
>> SELECT * FROM table_a;
>>
>> This would then return the results from table_a
>>
>> Regards
>>
>> Thom
>
> But I still need to define access to each table separately?
>
> Thanks,
> Antonio.
>

As far as I'm aware. It's only in the upcoming version 9.0 that you
can do things like:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Other folk on here may have some alternative suggestions though.

Thom

On 02/03/2010 14:56, Thom Brown wrote:
>>
>> But I still need to define access to each table separately?
>>
>> Thanks,
>> Antonio.
>>
>
> As far as I'm aware. It's only in the upcoming version 9.0 that you
> can do things like:
>
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>
> Other folk on here may have some alternative suggestions though.

I think people have in the past posted queries that extract the table
names from the system catalogues and then grant privileges on them....
it might be worthwhile having a trawl through the archives.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

Op 02-03-10 16:14, Raymond O'Donnell schreef:
> On 02/03/2010 14:56, Thom Brown wrote:
>>>
>>> But I still need to define access to each table separately?
>>>
>>> Thanks,
>>> Antonio.
>>>
>>
>> As far as I'm aware. It's only in the upcoming version 9.0 that you
>> can do things like:
>>
>> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>>
>> Other folk on here may have some alternative suggestions though.
>
> I think people have in the past posted queries that extract the table
> names from the system catalogues and then grant privileges on them....
> it might be worthwhile having a trawl through the archives.
>
> Ray.
>

Ok, will do. Thanks.

Antonio

if you don't want to search the archives, it could just be easier to look at the catalog tables
yourself. If you have no experience with them, many times if you do pg_foo when you are interested
in 'foo' you will get something, i.e pg_user also exists.

#\d pg_tables
View "pg_catalog.pg_tables"
Column | Type | Modifiers
-------------+---------+-----------
schemaname | "name" |
tablename | "name" |
tableowner | "name" |
tablespace | "name" |
hasindexes | boolean |
hasrules | boolean |
hastriggers | boolean |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner,
t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0
AS hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'r'::"char";

and then the sql just comes naturally:

select 'grant select on '|| schemaname || '.' || tablename || ' to baz' from pg_tables where
schemaname = 'bar' ;

Note that it is important to select the schemaname because there could be two different tables in
two different schemas with the same tablename. Also you should keep in mind that this will only work
for tables, if you start adding views you have to add more to the generation of sql.
-Said

Said Ramirez
Raymond O'Donnell wrote:
> On 02/03/2010 14:56, Thom Brown wrote:
>>> But I still need to define access to each table separately?
>>>
>>> Thanks,
>>> Antonio.
>>>
>> As far as I'm aware. It's only in the upcoming version 9.0 that you
>> can do things like:
>>
>> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>>
>> Other folk on here may have some alternative suggestions though.
>
> I think people have in the past posted queries that extract the table
> names from the system catalogues and then grant privileges on them....
> it might be worthwhile having a trawl through the archives.
>
> Ray.
>

On Tue, Mar 2, 2010 at 8:26 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:

> On 2 March 2010 14:49, Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
> wrote:
> > Op 02-03-10 13:00, Thom Brown schreef:
> >>
> >> On 2 March 2010 11:46, Nilesh Govindarajan<lists(at)itech7(dot)com> wrote:
> >>>
> >>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown<thombrown(at)gmail(dot)com>
> wrote:
> >>>>
> >>>> On 2 March 2010 11:12, Antonio Goméz Soto<
> antonio(dot)gomez(dot)soto(at)gmail(dot)com>
> >>>> wrote:
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I tried this:
> >>>>>
> >>>>> names=# grant select on database names to spice;
> >>>>> ERROR: invalid privilege type SELECT for database
> >>>>>
> >>>>> The documentation seems to imply I need to grant SELECT
> >>>>> to each table separately. That's a lot of work, and what if
> >>>>> new tables are created?
> >>>>>
> >>>>> Thanks,
> >>>>> Antonio
> >>>>>
> >>>>> --
> >>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >>>>> To make changes to your subscription:
> >>>>> http://www.postgresql.org/mailpref/pgsql-general
> >>>>>
> >>>>
> >>>> The privileges you can grant on a database are only related to the
> >>>> creation of tables and connecting to that database.
> >>>>
> >>>> You could create a role which has SELECT-only access, apply that role
> >>>> to all your tables, and assign users (other roles) as members of that
> >>>> role.
> >>>>
> >>>> Regards
> >>>>
> >>>> Thom
> >>>>
> >>>> --
> >>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >>>> To make changes to your subscription:
> >>>> http://www.postgresql.org/mailpref/pgsql-general
> >>>
> >>> How to create that ? I'm also interested in this as I need this for
> >>> backing
> >>> up my databases.
> >>>
> >>> --
> >>
> >> Okay, here's an example:
> >>
> >> CREATE ROLE readonly; -- This user won't be able to do anything by
> >> default, not even log in
> >>
> >> GRANT SELECT on table_a TO readonly;
> >> GRANT SELECT on table_b TO readonly;
> >> GRANT SELECT on table_c TO readonly;
> >>
> >> CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned
> >> this user to any group
> >>
> >> SET ROLE testuser;
> >> SELECT * FROM table_a;
> >>
> >> We get:
> >> ERROR: permission denied for relation table_a
> >>
> >> SET ROLE postgres;
> >>
> >> DROP ROLE testuser;
> >> CREATE ROLE testuser WITH LOGIN IN ROLE readonly;
> >>
> >> SET ROLE testuser;
> >> SELECT * FROM table_a;
> >>
> >> This would then return the results from table_a
> >>
> >> Regards
> >>
> >> Thom
> >
> > But I still need to define access to each table separately?
> >
> > Thanks,
> > Antonio.
> >
>
> As far as I'm aware. It's only in the upcoming version 9.0 that you
> can do things like:
>
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>
> Other folk on here may have some alternative suggestions though.
>
> Thom
>

Eagerly waiting for 9.0....

--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> As far as I'm aware.  It's only in the upcoming version 9.0 that you
> can do things like:
>
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>
> Other folk on here may have some alternative suggestions though.

9.0 will also have the hot standby feature. setting up a standby is
pretty much always a good idea and access to the standby is
automatically read only. this would be a cheap way to get what you
want without dealing with privileges which is nice. you are also
relatively insulated from problematic queries the user might make like
accidental unconstrained joins, full table sorts etc..

merlin

On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
> On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> > As far as I'm aware. It's only in the upcoming version 9.0 that you
> > can do things like:
> >
> > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
> >
> > Other folk on here may have some alternative suggestions though.
>
> 9.0 will also have the hot standby feature. setting up a standby is
> pretty much always a good idea and access to the standby is
> automatically read only. this would be a cheap way to get what you
> want without dealing with privileges which is nice. you are also
> relatively insulated from problematic queries the user might make like
> accidental unconstrained joins, full table sorts etc..
>
> merlin

I believe all you have to do is this to create a read only user:

create user ro_user with password 'passwd';

alter user ro_user set default_transaction_read_only = true;

On 3 March 2010 14:51, Kevin Kempter <kevink(at)consistentstate(dot)com> wrote:
> On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
>> On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
>> > As far as I'm aware.  It's only in the upcoming version 9.0 that you
>> > can do things like:
>> >
>> > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>> >
>> > Other folk on here may have some alternative suggestions though.
>>
>> 9.0 will also have the hot standby feature.  setting up a standby is
>> pretty much always a good idea and access to the standby is
>> automatically read only.  this would be a cheap way to get what you
>> want without dealing with privileges which is nice.  you are also
>> relatively insulated from problematic queries the user might make like
>> accidental unconstrained joins, full table sorts etc..
>>
>> merlin
>
>
> I believe all you have to do is this to create a read only user:
>
> create user ro_user with password 'passwd';
>
> alter user ro_user set default_transaction_read_only = true;
>

I believe that will only affect the *default* setting of the
transaction. The user could still run the following before a query to
write again:

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

Thom

Kevin Kempter <kevink(at)consistentstate(dot)com> writes:
> I believe all you have to do is this to create a read only user:
> create user ro_user with password 'passwd';
> alter user ro_user set default_transaction_read_only = true;

You do realize the user can just unset that again?

regards, tom lane

On Wednesday 03 March 2010 08:19:46 am Tom Lane wrote:
> Kevin Kempter <kevink(at)consistentstate(dot)com> writes:
> > I believe all you have to do is this to create a read only user:
> > create user ro_user with password 'passwd';
> > alter user ro_user set default_transaction_read_only = true;
>
> You do realize the user can just unset that again?
>
> regards, tom lane

I did not. Thanks for the heads up.