Skip to main content

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

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

Post a Comment

Popular posts from this blog

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…