Re: Table permissions - Mailing list pgsql-general

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