grants/grants.pg_sql

Source

0001: \echo Start grants/grants.pg_sql (to be executed as owner of the logging facility)
0002: 
0003: \echo
0004: \echo Remove all grants on pglogger objects.
0005: 
0006: insert into LEVEL (
0007:     SCOPE,
0008:     LEVEL
0009: ) values (
0010:     'inline_code_block',
0011:     'INFO'
0012: );
0013: commit;
0014: 
0015: do language plpgsql
0016: $ANONYM$
0017:     declare
0018:         C_LB constant text :=
0019:           GET_PROPERTY_VALUE_STRING(I_PROPERTY_NAME => 'LINEBREAK');
0020:         V_REC record;
0021:         V_STATEMENT text;
0022:         V_MESSAGE text;
0023:     begin
0024:         -- tables and views
0025:         for V_REC in (
0026:                select TABLE_NAME,
0027:                       GRANTEE,
0028:                       PRIVILEGE_TYPE
0029:                  from INFORMATION_SCHEMA.TABLE_PRIVILEGES
0030:                 where TABLE_SCHEMA = current_user
0031:                       and GRANTEE != TABLE_SCHEMA
0032:              order by TABLE_NAME asc,
0033:                       GRANTEE asc,
0034:                       PRIVILEGE_TYPE asc
0035:         ) loop
0036:             begin
0037:                 V_STATEMENT := 'revoke ' || V_REC.PRIVILEGE_TYPE ||
0038:                   ' on table ' || V_REC.TABLE_NAME || ' from ' || V_REC.GRANTEE;
0039:                 execute V_STATEMENT;
0040:                 perform INFO(I_MESSAGE => V_STATEMENT);
0041:             exception when others then
0042:                 get stacked diagnostics V_MESSAGE = MESSAGE_TEXT;
0043:                 perform WARNING(
0044:                             I_MESSAGE => 'Following statement failed ' ||
0045:                               'execution !'   ||
0046:                               C_LB || V_STATEMENT || C_LB || V_MESSAGE
0047:                         );
0048:             end;
0049:         end loop;
0050: 
0051: 
0052:         -- functions
0053:         /* could be accomplished with following SQL statement but this solution
0054:          * was left for reference. (The query was hard to figure out). Also
0055:          * the SQL needs psql variable so is less robust.
0056:          * -- revoke EXECUTE on all functions in schema :SCHEMA_NAME from PUBLIC restrict;
0057:          */
0058:         for V_REC in (
0059:             with PRO_UNNESTED_TYPES as (
0060:                     select P.OID as PROOID,
0061:                            PRONAME,
0062:                            T.T as PROARGTYPE,
0063:                            T.I as ORD,
0064:                            PRONAMESPACE,
0065:                            PROOWNER
0066:                       from PG_CATALOG.PG_PROC P
0067:                 cross join lateral unnest(P.PROARGTYPES)
0068:                                    with ordinality as T(T, I)
0069:             ),
0070:             PRO_WITH_PARAM_LIST_AGG as (
0071:                      select P.PRONAME
0072:                             string_agg(T.TYPNAME, ', ')
0073:                               over (
0074:                                   partition by P.PROOID
0075:                                       order by P.ORD asc
0076:                               ) as PARAMETER_LIST_STRING,
0077:                             row_number()
0078:                               over (
0079:                                   partition by P.PROOID order by P.ORD desc
0080:                               ) as COMPLETE_LIST_EQ_1,
0081:                            G.GRANTEE
0082:                       from PRO_UNNESTED_TYPES P
0083:                 inner join PG_CATALOG.PG_TYPE T
0084:                         on P.PROARGTYPE = T.OID
0085:                 inner join PG_CATALOG.PG_NAMESPACE N
0086:                         on P.PRONAMESPACE = N.OID
0087:                 inner join INFORMATION_SCHEMA.ROUTINE_PRIVILEGES G
0088:                         on -- copied from INFORMATION_SCHEMA.ROUTINE_PRIVILEGES
0089:                            -- source as seen in DBeaver 4.3.2
0090:                            (
0091:                               (P.PRONAME::text || '_'::text) || P.PROOID::text
0092:                            )::INFORMATION_SCHEMA.SQL_IDENTIFIER =
0093:                              G.SPECIFIC_NAME
0094:                      where N.NSPNAME = current_user
0095:                            and G.GRANTEE != current_user
0096:             )
0097:               select PRONAME,
0098:                      PARAMETER_LIST_STRING,
0099:                      GRANTEE
0100:                 from PRO_WITH_PARAM_LIST_AGG
0101:                where COMPLETE_LIST_EQ_1 = 1
0102:             order by PRONAME asc,
0103:                      GRANTEE asc,
0104:                      PARAMETER_LIST_STRING asc
0105:         ) loop
0106:             begin
0107:                 V_STATEMENT := 'revoke EXECUTE on function ' ||
0108:                   V_REC.PRONAME || '(' || V_REC.PARAMETER_LIST_STRING ||
0109:                   ') from ' || V_REC.GRANTEE;
0110:                 execute V_STATEMENT;
0111:                 perform INFO(
0112:                             I_MESSAGE => V_STATEMENT
0113:                         );
0114:             exception when others then
0115:                 get stacked diagnostics V_MESSAGE = MESSAGE_TEXT;
0116:                 perform WARNING(
0117:                             I_MESSAGE => 'Following statement failed ' ||
0118:                               'execution!' || C_LB || V_STATEMENT || C_LB ||
0119:                               V_MESSAGE
0120:                         );
0121:             end;
0122:         end loop;
0123:     end;
0124: $ANONYM$;
0125: 
0126: delete from LEVEL where SCOPE = 'inline_code_block';
0127: commit;
0128: 
0129: \echo
0130: \echo Grants on pglogger objects to public
0131: 
0132: grant select on LOG to public;
0133: grant select on LOG_SORTED to public;
0134: grant select, insert, update, delete on LEVEL to public;
0135: -- No grants on PROPERTY. General settings should only be done by the logger
0136: -- user especially as the connect string property contains the logger user
0137: -- password, which is not that good an idea to expose to public.
0138: grant execute on function DEBUG(text) to public;
0139: grant execute on function LOG(text) to public;
0140: grant execute on function INFO(text) to public;
0141: grant execute on function NOTICE(text) to public;
0142: grant execute on function WARNING(text) to public;
0143: grant execute on function EXCEPTION(text) to public;
0144: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_DEBUG() to public;
0145: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_LOG() to public;
0146: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_INFO() to public;
0147: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_NOTICE() to public;
0148: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_WARNING() to public;
0149: grant execute on function SET_GENERAL_LOGGING_LEVEL_2_EXCEPTION() to public;
0150: 
0151: commit; -- unlike Oracle not all ddl commit implicitly
0152: 
0153: \echo End grants/grants.pg_sql