Deprecated Garbage Collections – Kenny Lee Chee Wei

A truckload of garbage by Kenny Lee Chee Wei

Postgres – Grant privileges to all tables in a database

with 5 comments

Grant privileges to all tables in a database (select, update, insert, delete)

Eg:( Creating a read-only user in postgres)

For Postgres 8.*

For Postgres 7.*

–Function to grant access(select,insert,update,delete) to users

CREATE FUNCTION pg_grant(TEXT, TEXT, TEXT, TEXT)
RETURNS integer AS '
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in (''r'',''v'',''S'') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE ''GRANT '' || $2 || '' ON '' || obj.relname || '' TO '' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
' LANGUAGE plpgsql SECURITY DEFINER;

–Function to revoke access(select,insert,update,delete) from users

CREATE FUNCTION pg_revoke(TEXT, TEXT, TEXT, TEXT)
RETURNS integer AS '
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in (''r'',''v'',''S'') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE ''REVOKE '' || $2 || '' ON '' || obj.relname || '' FROM '' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
' LANGUAGE plpgsql SECURITY DEFINER;

–Create users for your database

CREATE USER userreadonly WITH PASSWORD 'userr3ad0nly';
CREATE USER userall WITH PASSWORD 'usersh0pa11';

–Grant respective access to users

select pg_grant('
userreadonly ','select','%','public');
select pg_grant('
userall ','select,insert,update,delete','%','public');

You might need to create lang for plpgsql if you had not done so

createlang plpgsql yrdatabasename

Advertisements

Written by kennii

September 21, 2007 at 2:50 am

Posted in Database, Postgres

5 Responses

Subscribe to comments with RSS.

  1. You can use:
    grant all privileges on database {mydatabase} to {username} ;
    It is far less complicated !

    bat

    May 12, 2008 at 11:59 am

  2. Bat, it’s less complicated, but grant really ALL privileges (including drop).

    Walter Cruz

    September 14, 2008 at 4:10 pm

  3. Hi Kenny Lee Chee Wei

    You have written thees two function for grant and revoke is very useful for me.
    I have modified these according to my project requirements.
    Thanks Man:)
    Thanks
    Brijesh Baser

    Brijesh

    August 19, 2009 at 12:16 pm

  4. Thank you for these very useful functions! I suggest that you define these functions with:
    ‘ LANGUAGE plpgsql SECURITY INVOKER;

    instead of SECURITY DEFINER. SECURITY DEFINER allows any user to run the functions and elevate their privileges. I think SECURITY INVOKER is the default, so you could just leave it off…

    Thanks for sharing!

    TomG

    August 25, 2009 at 7:35 pm

  5. wow, I have never likes Postgres, but this takes the cake.

    Iwant to allow select only access on all table in a nominated database to a new user and I have to go throgh this?

    standard sql is
    grant select on dbname.* to new_user;

    I really find Postgresql hard to work with, it seems that everything is deliberately designed to be difficult.

    Beach Defender

    February 10, 2010 at 4:56 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: