Re: [GENERAL] Making a schema read-only (was Unexpected message in grant/revoke script)
Webb Sprague [EMAIL PROTECTED] writes: Also, I revoked what I thought was everything possible on the public schema, but a user is still able to create a table in that schema -- could someone explain: oregon=# revoke create on schema public from foobar cascade; REVOKE You've got a conceptual error here: the above only does something if you'd previously done an explicit GRANT TO foobar. You haven't, so there's nothing to revoke. The reason people can create stuff in public is that by default, create on schema public is granted to PUBLIC, ie the world. Start with revoke all on schema public from public and then grant only what you want. 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] Making a schema read-only (was Unexpected message in grant/revoke script)
On Fri, Mar 14, 2008 at 12:55 PM, Tom Lane [EMAIL PROTECTED] wrote: Webb Sprague [EMAIL PROTECTED] writes: Also, I revoked what I thought was everything possible on the public schema, but a user is still able to create a table in that schema -- could someone explain: oregon=# revoke create on schema public from foobar cascade; REVOKE You've got a conceptual error here: Not surprising... SNIP Start with revoke all on schema public from public and then grant only what you want. I will give it a go, and thanks! 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] Making a schema read-only (was Unexpected message in grant/revoke script)
Start with revoke all on schema public from public and then grant only what you want. Oh -- to grant select permissions on all the tables in the public schema, do I have to do it table-by-table? I know I can write a loop an use information_schema if necessary, but if I don't have to I would like to know. Thx -- 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] Making a schema read-only (was Unexpected message in grant/revoke script)
On Mar 14, 2008, at 3:22 PM, Webb Sprague wrote: Start with revoke all on schema public from public and then grant only what you want. Oh -- to grant select permissions on all the tables in the public schema, do I have to do it table-by-table? I know I can write a loop an use information_schema if necessary, but if I don't have to I would like to know. Thx Typically what's done is to do that for one user group and then make all of your actual login users members of that group. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- 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] Making a schema read-only (was Unexpected message in grant/revoke script)
On Fri, Mar 14, 2008 at 1:30 PM, Erik Jones [EMAIL PROTECTED] wrote: On Mar 14, 2008, at 3:22 PM, Webb Sprague wrote: Start with revoke all on schema public from public and then grant only what you want. Oh -- to grant select permissions on all the tables in the public schema, do I have to do it table-by-table? I know I can write a loop an use information_schema if necessary, but if I don't have to I would like to know. Thx Typically what's done is to do that for one user group and then make all of your actual login users members of that group. Oh -- I guess overlapping groups would work, but wouldn't I still have to give select permissions to this collective role by going table-by-table? And I want a separate role for each person, so that they can't stomp all over each others data. And now I have a new problem -- what could be wrong with the alter schema owner to line below: revoke all on schema public from public; create or replace function new_student (text) returns void as $$ declare t_name text; begin -- personal schema execute 'create role ' || $1 || ' LOGIN'; execute 'create schema ' || $1; execute 'alter schema ' || $1 || ' owner to ' || $1; -- THIS THROWS AN ERROR see below execute 'grant all on schema ' || $1 || ' to ' || $1 || ' with grant option'; for t_name in select table_name from information_schema.tables where table_schema = 'public' order by table_name loop raise notice 'granting select to %s on %s', $1, t_name; execute 'grant select on ' || t_name || ' to ' || $1; end loop; end; $$ language plpgsql ; oregon=# select new_student('foobarbar'); ERROR: unrecognized node type: 1651470182 CONTEXT: SQL statement alter schema foobarbar owner to foobarbar PL/pgSQL function new_student line 7 at EXECUTE statement Thanks again for helping me understand this most tedious of database stuff w -- 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] Making a schema read-only (was Unexpected message in grant/revoke script)
On Mar 14, 2008, at 3:43 PM, Webb Sprague wrote: On Fri, Mar 14, 2008 at 1:30 PM, Erik Jones [EMAIL PROTECTED] wrote: On Mar 14, 2008, at 3:22 PM, Webb Sprague wrote: Start with revoke all on schema public from public and then grant only what you want. Oh -- to grant select permissions on all the tables in the public schema, do I have to do it table-by-table? I know I can write a loop an use information_schema if necessary, but if I don't have to I would like to know. Thx Typically what's done is to do that for one user group and then make all of your actual login users members of that group. Oh -- I guess overlapping groups would work, but wouldn't I still have to give select permissions to this collective role by going table-by-table? And I want a separate role for each person, so that they can't stomp all over each others data. Yes. The best way to handle this is to grant permissions to those groups when the tables are created rather than doing it retroactively, although the latter isn't really that difficult. Assuming you've revoked privileges from public to the public schema but then want to grant SELECT privileges to some group, using this function would work nicely: CREATE OR REPLACE FUNCTION grant_select_for_group_to_public(rolename varchar, tablename varchar) RETURNS VOID AS $$ BEGIN EXECUTE 'GRANT SELECT TO ' || rolename || ' ON ' || tablename || ';'; END; Then you can do: SELECT grant_select_for_group_to_public('some_group_name', c.relname) FROM pg_class c, pg_namespace n WHERE c.relnamespace=n.oid AND n.nspname='public'; Hopefully from that you can see how to use this type of setup for further specialization. And now I have a new problem -- what could be wrong with the alter schema owner to line below: revoke all on schema public from public; create or replace function new_student (text) returns void as $$ declare t_name text; begin -- personal schema execute 'create role ' || $1 || ' LOGIN'; execute 'create schema ' || $1; execute 'alter schema ' || $1 || ' owner to ' || $1; -- THIS THROWS AN ERROR see below execute 'grant all on schema ' || $1 || ' to ' || $1 || ' with grant option'; for t_name in select table_name from information_schema.tables where table_schema = 'public' order by table_name loop raise notice 'granting select to %s on %s', $1, t_name; execute 'grant select on ' || t_name || ' to ' || $1; end loop; end; $$ language plpgsql ; oregon=# select new_student('foobarbar'); ERROR: unrecognized node type: 1651470182 CONTEXT: SQL statement alter schema foobarbar owner to foobarbar PL/pgSQL function new_student line 7 at EXECUTE statement Not sure about the nodetype bit but I do know that the user execute the ALTER SCHEMA .. OWNER TO .. must be a member of the new owner role, either directly or indirectly. Could this be your problem. Btw, I typically do schema level creation and grants as superuser which would let you specify the schema's owner when you create the schema CREATE SCHEMA foobar AUTHORIZATION foobar; Also, you don't need to grant privileges on a schema to its owner. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- 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] Making a schema read-only (was Unexpected message in grant/revoke script)
Thanks to Eric and Tom, I think I have got it. Here is the function for adding a new student, who can select anything in public and can do anything at all in their own schema. revoke all on schema public from public; -- done only once create or replace function new_student (text) returns void as $$ declare t_name text; begin -- personal schema execute 'create role ' || $1 || ' LOGIN'; execute 'create schema authorization ' || $1 ; -- public schema execute 'revoke all on schema public from ' || $1; execute 'grant usage on schema public to ' || $1; for t_name in select table_name from information_schema.tables where table_schema = 'public' order by table_name loop raise notice 'granting select to %s on %s', $1, t_name; execute 'grant select on ' || t_name || ' to ' || $1; end loop; end; $$ language plpgsql ; select new_student ('fobar'); --etc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general