Default template database

Dumped on 2004-09-28

Index of database - acl_admin


View: schema_and_relation_view

A view of all existing schemas and relations within them (excluding indices).

schema_and_relation_view Structure
F-Key Name Type Description
schema_oid oid
relation_oid oid
schema_name name
relation_name name
SELECT n.oid AS schema_oid
, c.oid AS relation_oid
, n.nspname AS schema_name
, c.relname AS relation_name 
FROM pg_class c
, pg_namespace n 
WHERE (
     (
           (
                 (c.relnamespace = n.oid)
               AND (NOT 
                       (c.relkind = 'i'::"char")
                 )
           )
         AND (NOT 
                 (n.nspname ~~ 'pg_%'::text)
           )
     )
   AND (NOT 
           (n.nspname = 'information_schema'::name)
     )
);

Index - Schema acl_admin


Function: chown_all( text )

Returns: text

Language: PLPGSQL

Given a user name, execute ALTER TABLE on all relations in all schemas OWNER TO user.

DECLARE
    usr ALIAS FOR $1;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view
    LOOP sql := 'ALTER TABLE ' rel.schema_name || '.' || rel.relation_name || ' OWNER TO ' || usr;
        RAISE NOTICE '%', sql;
        EXECUTE sql;
    END LOOP;
    RETURN 'OK';
END;

Function: comma_append( text, text )

Returns: text

Language: SQL

Function to append text with commas between them. If given a null first parameter, will return the second parameter without prepending a comma.

SELECT COALESCE($1 || ',' || $2, $2)::text

Function: comma_list( text )

Returns: text

Language: INTERNAL

Aggregate to join a list of text values into commas. Used by members and groups functions below.

aggregate_dummy

Function: grant_on_all( text )

Returns: text

Language: SQL

Given a user name, execute GRANT ALL ON all relations in all schemas TO user.

SELECT acl_admin.grant_on_all($1, 'ALL'::text);

Function: grant_on_all( text, text )

Returns: text

Language: PLPGSQL

Given a user name and privilidge, execute GRANT privilidge ON all relations in all schemas TO user.

DECLARE
    usr ALIAS FOR $1;
    prv ALIAS FOR $2;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view
    LOOP sql := 'GRANT ' || prv || ' ON ' || rel.schema_name || '.' || rel.relation_name || ' TO ' || usr;
        RAISE NOTICE '%', sql;
        EXECUTE sql;
    END LOOP;
    RETURN 'OK';
END;

Function: grant_on_visible( text )

Returns: text

Language: SQL

Given a user name and privilidge, execute GRANT ALL ON all relations in visible schemas TO user.

SELECT acl_admin.grant_on_visible($1, 'ALL'::text)

Function: grant_on_visible( text, text )

Returns: text

Language: PLPGSQL

Given a user name and privilidge, execute GRANT privilidge ON all relations in visible schemas TO user.

DECLARE
    usr ALIAS FOR $1;
    prv ALIAS FOR $2;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view
	WHERE pg_catalog.pg_table_is_visible(relation_oid)
    LOOP sql := 'GRANT ' || prv || ' ON ' || rel.schema_name || '.' || rel.relation_name || ' TO ' || usr;
        RAISE NOTICE '%', sql;
        EXECUTE sql;
    END LOOP;
    RETURN 'OK';
END;

Function: groups( text )

Returns: text

Language: SQL

Given a username, return a comma separated list of groups to which the user belongs.

SELECT acl_admin.comma_list(g.groname)
FROM pg_catalog.pg_group g
WHERE acl_admin.username_to_userid($1) = ANY(g.grolist)

Function: members( text )

Returns: text

Language: SQL

Given a group name, return a comma separated list of users in that group.

SELECT acl_admin.comma_list(u.usename)
FROM pg_catalog.pg_user u,
    pg_catalog.pg_group g
WHERE g.groname = $1
  AND u.usesysid = ANY(g.grolist)

Function: revoke_on_all( text )

Returns: text

Language: SQL

Given a user name, execute REVOKE ALL ON all relations in all schemas FROM user.

SELECT acl_admin.revoke_on_all($1, 'ALL'::text);

Function: revoke_on_all( text, text )

Returns: text

Language: PLPGSQL

Given a user name and privilidge, execute REVOKE privilidge ON all relations in all schemas FROM user.

DECLARE
    usr ALIAS FOR $1;
    prv ALIAS FOR $2;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view
    LOOP sql := 'REVOKE ' || prv || ' ON ' || rel.schema_name || '.' || rel.relation_name || ' FROM ' || usr;
        RAISE NOTICE '%', sql;
        EXECUTE sql;
    END LOOP;
    RETURN 'OK';
END;

Function: revoke_on_visible( text )

Returns: text

Language: SQL

Given a user name and privilidge, execute REVOKE ALL ON all relations in visible schemas FROM user.

SELECT acl_admin.revoke_on_visible($1, 'ALL'::text)

Function: revoke_on_visible( text, text )

Returns: text

Language: PLPGSQL

Given a user name and privilidge, execute REVOKE privilidge ON all relations in visible schemas FROM user.

DECLARE
    usr ALIAS FOR ;
    prv ALIAS FOR ;
    rel record;
    sql text;
BEGIN
    FOR rel IN SELECT pg_catalog.quote_ident(schema_name) AS schema_name,
                      pg_catalog.quote_ident(relation_name) AS relation_name
        FROM acl_admin.schema_and_relation_view
        WHERE pg_catalog.pg_table_is_visible(relation_oid)
    LOOP sql := 'REVOKE ' || prv || ' ON ' || rel.schema_name || '.' || rel.relation_name || ' FROM ' || usr;
        RAISE NOTICE '%', sql;
        EXECUTE sql;
    END LOOP;
    RETURN 'OK';
END;

Function: username_to_userid( text )

Returns: integer

Language: SQL

Utility function to translate username to userid.

SELECT usesysid FROM pg_catalog.pg_user u WHERE u.usename = $1

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict