Hello, Thanks for the info, I have already solved this by writing the following function. Also, i think it is better than changing the schema tables directly
Regards CREATE OR REPLACE FUNCTION grant_permissions (org_tbl TEXT , new_tbl TEXT , change_owner BOOLEAN) RETURNS VOID AS $$ DECLARE tblOwner text := ''; roleName text := ''; privilegeType text := ''; grantSql text := ''; BEGIN -- Some checks IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = $1) THEN RAISE EXCEPTION 'The relation % does not exists', $1; ELSIF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = $2) THEN RAISE EXCEPTION 'The relation % does not exists', $2; ELSIF NOT EXISTS (SELECT 1 FROM information_schema.table_privileges WHERE table_name = $1) THEN RAISE EXCEPTION 'No privileges assigned to the relation %', $1; END IF; -- Change the table owner IF (change_owner) THEN SELECT tableowner INTO tblOwner FROM pg_tables; grantSql = 'ALTER TABLE ' || quote_ident ($2) || ' OWNER TO ' || tblOwner || E';\n' ; END IF; -- Grant the privileges FOR roleName IN SELECT DISTINCT grantee FROM information_schema.table_privileges WHERE table_name = $1 LOOP -- 6 = DELETE, INSERT, UPDATE, SELECT, TRIGGER, REFERENCE IF (SELECT count(*) FROM information_schema.table_privileges WHERE table_name = $1 AND grantee = roleName)::INTEGER = 6 THEN grantSql = grantSql || 'GRANT SELECT ON TABLE ' ||quote_ident ($2) || ' TO ' || roleName ||E';\n' ; END IF; FOR privilegeType IN SELECT privilege_type FROM information_schema.table_privileges WHERE table_name = $1 AND grantee = roleName LOOP grantSql = grantSql || 'GRANT ' || privilegeType ||' ON TABLE ' || quote_ident ($2) ||' TO '|| roleName || E';\n' ; END LOOP; END LOOP; --Execute ALL EXECUTE grantSQL; END $$ LANGUAGE 'plpgsql' ; ________________________________ From: A.M. <age...@themactionfaction.com> To: salah jubeh <s_ju...@yahoo.com> Cc: pgsql <pgsql-general@postgresql.org> Sent: Wednesday, January 18, 2012 5:44 PM Subject: Re: [GENERAL] Table permissions On Jan 18, 2012, at 8:48 AM, salah jubeh wrote: > Hello, > > I have create a table from another table such as > > CREATE TABLE tmp_XXX AS SELECT * FROM XXX; > > > The tmp_XXX tables has no permissions assigned to it and I want to assign > it with the same owner and access privileges of XXX table. I had a look on > pg catalog tables http://www.postgresql.org/docs/8.4/static/catalogs.html to > create a stored procedure to do this job for me. I have found some tables > which might be useful to get the original table iformation. For example, I > can use pg_table to get the owner of the original table. Also, I have found > pg_roles. However, I was not able to find where the access privileges are > stored. > > Is there a better way to do this task than extracting the access privileges > from pg catalog ? If not, where I can find the access privileges information > ? > You are looking for pg_catalog.pg_class.relacl. Just copy that from the original table to the duplicate (and perhaps relowner, depending on your situation), and you will have duplicate permissions. Cheers, M -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
