Friday, April 4, 2014

Normal User As Super User


Recently i faced a problem with some catalog views, which do not give you the complete information as a normal user. For example, take pg_stat_activity, pg_stat_replication, pg_settings, e.t.c. If we run the above catalog views as non super user, you don't get the result what we get as a superuser. This is really a good security between super user and normal user.

What we need to do, if we want to collect these metrics as normal user. I think the possible solution is "Write a wrapper function with security definer as like below" and grant/revoke the required privileges to the user/public.

CREATE OR REPLACE FUNCTION pg_stat_activity(
RETURNS SETOF pg_catalog.pg_stat_activity
AS
$$
BEGIN
RETURN QUERY(SELECT * FROM pg_catalog.pg_stat_activity);
END
$$
LANGUAGE PLPGSQL SECURITY DEFINER;

REVOKE ALL ON FUNCTION pg_stat_activity() FROM public;
CREATE VIEW pg_stat_activity AS SELECT * FROM pg_stat_activity();
REVOKE ALL ON pg_stat_activity FROM public;

This is really a good approach to get the statistics from pg_stat_activity. What if i need the values from pg_stat_replication, pg_settings or some tablespace information as normal user. So, do we need to create wrapper function for each catalog view ? { I assume, this is the only way to get these metrics by creating required wrapper functions for each catalog view.}

Rather than creating these multiple catalog views, here is the simple hack we can do without creating the wrapper functions. Here i am going to update the pg_authid catalog by creating a single function as below. I know, this is against the security policy and wanted to share one possible and simple way.
Function
CREATE OR REPLACE FUNCTION make_me_superuser(isSuper bool)
RETURNS
VOID
AS $$
BEGIN
UPDATE pg_catalog.pg_authid SET
rolsuper=$1::boolean where rolname=<role name>;
END;
$$
LANGUAGE PLPGSQL SECURITY DEFINER;
REVOKE ALL ON FUNCTION make_me_superuser(bool) FROM public;
GRANT EXECUTE ON FUNCTION make_me_superuser(bool) TO <role name>;

Sample Case

postgres=>BEGIN WORK;
BEGIN
postgres=> select make_me_superuser(TRUE);
make_me_superuser
-------------------
(1 row)
postgres=> show data_directory;
  data_directory
--------------------------------------------
C:/Program Files (x86)/PostgreSQL/9.2/data
(1 row)
postgres=> select make_me_superuser(false);
make_me_superuser
-------------------
(1 row)
postgres=> END WORK;
COMMIT

Since we are running this in a transaction mode, which restricts the other same user session's superuser activities.

**Don't implement this in production servers, where your security is crucial.

--Dinesh Kumar

2 comments:

  1. This is bad hack. Why don't you use ALTER USER command. Updating catalog view directly never been recommended:
    Here is an example which u can use:
    CREATE OR REPLACE FUNCTION make_me_superuser(isSuper bool)
    RETURNS boolean
    LANGUAGE PLPGSQL SECURITY DEFINER
    AS $function$
    BEGIN
    IF $1 = TRUE THEN
    EXECUTE 'ALTER USER '||session_user||' WITH SUPERUSER';
    RETURN TRUE;
    ELSE
    RAISE NOTICE 'no change occurred for user %', session_user;
    RETURN FALSE;
    END IF;
    EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE '% %', SQLCODE, SQLERRM;
    RETURN false;
    END;
    $function$;

    REVOKE ALL ON FUNCTION make_me_superuser(bool) FROM public;
    GRANT EXECUTE ON FUNCTION make_me_superuser(bool) TO test;

    review=> select make_me_superuser(false);
    NOTICE: no change occurred for user test
    make_me_superuser
    -------------------
    f
    (1 row)

    review=> select make_me_superuser(true);
    make_me_superuser
    -------------------
    t
    (1 row)

    review=> \du+ test
    List of roles
    Role name | Attributes | Member of | Description
    -----------+------------+-----------+-------------
    test | Superuser | {} |



    ReplyDelete
    Replies
    1. Thanks Vibhor. I agreed with your comment.

      I think you miss the case in "ELSE" part. You need to revert the user from "Superuser" to "NonSuperUser".

      Delete