Re: [GENERAL] How to grant a user read-only access to a database?
On Wednesday 03 March 2010 08:19:46 am Tom Lane wrote: > Kevin Kempter 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
Kevin Kempter 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On 3 March 2010 14:51, Kevin Kempter wrote: > On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote: >> On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote: > On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown 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; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On Tue, Mar 2, 2010 at 8:26 PM, Thom Brown wrote: > On 2 March 2010 14:49, Antonio Goméz Soto > wrote: > > Op 02-03-10 13:00, Thom Brown schreef: > >> > >> On 2 March 2010 11:46, Nilesh Govindarajan wrote: > >>> > >>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown > wrote: > > On 2 March 2010 11:12, Antonio Goméz Soto< > antonio.gomez.s...@gmail.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@postgresql.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@postgresql.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
Re: [GENERAL] How to grant a user read-only access to a database?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On 2 March 2010 14:49, Antonio Goméz Soto wrote: > Op 02-03-10 13:00, Thom Brown schreef: >> >> On 2 March 2010 11:46, Nilesh Govindarajan wrote: >>> >>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: On 2 March 2010 11:12, Antonio Goméz Soto 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@postgresql.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@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
Op 02-03-10 13:00, Thom Brown schreef: On 2 March 2010 11:46, Nilesh Govindarajan wrote: On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: On 2 March 2010 11:12, Antonio Goméz Soto 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@postgresql.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@postgresql.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On 2 March 2010 11:46, Nilesh Govindarajan wrote: > On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: >> >> On 2 March 2010 11:12, Antonio Goméz Soto >> 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@postgresql.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@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to grant a user read-only access to a database?
On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: > On 2 March 2010 11:12, Antonio Goméz Soto > 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@postgresql.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@postgresql.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
Re: [GENERAL] How to grant a user read-only access to a database?
On 2 March 2010 11:12, Antonio Goméz Soto 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@postgresql.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
