This is me, Wu!
» e-shell.org
In this page
Rate this page!
Search inside the wiki!
  Home >> sources >> PostgreSQL grant access

PostgreSQL grant access

This script is a little one created with the purpose of let me change privileges on all the tables and sequences from a database in a PostgreSQL server. You only need to provide a database name and the name of a user that has grant privileges on that database. In order to use this script, you will need PyGreSQL, a Python module that let the script connects to the database and perform the needed operations.

Download: grant_access.py v0.1 (September 22, 2004) | tar.gz | tar.bz2 | zip |

Source:

  #!/usr/bin/env python
# coding: iso-8859-1
#  -*- Python -*-

"""grant_access.py
This script gives some rights over all tables/sequences/indexes of a given database
to a given user. Database name and user name are passed as parameters.

how_to_use()
This function sets the "how to use" message if the script is invoked without the correct
parameters

grant_privileges()
This function gets as parameters the connection information to the
PostgreSQL backend, the database name and the user name received as
parameters to the script.

To use This script you must change the connection information showed between the lines:
# CHANGE THIS, some lines below this comment."""


import pg, sys

# CHANGE THIS
db_host = 'localhost'
db_user = 'USERNAME'
db_passwd = 'PASSWORD'
# CHANGE THIS

def how_to_use(script):

    msg = """
    Use:

               $ %s dbname dbuser
    Where:

               dbname - The name of the database we want to change the table's permissions.
               dbuser - The name of the user we want to give privileges to.
    """ % script

    print msg


def grant_privileges(db_host, db_user, db_passwd, granted_db, granted_user):

    db_conn = pg.DB(granted_db, db_host, 5432, None, None, db_user, db_passwd)

    for i in db_conn.get_tables():
        if i[:3] == 'sql':
            pass # do nothing, it is a built in table from postgresql
        else:
            print 'Granting select, insert, update, delete on %s to %s' % (i, granted_user)
            db_conn.query("grant select, insert, update, delete on %s to %s" % (i, granted_user))
            # sequence privileges, dirty way, we assume that there is a equence
            # for each table we have found, would be nice to get a list of real sequences,
            # or check if each of this sequences is valid, before executing grant
            try:
                print 'Granting select, insert, update, delete on %s_id_seq to %s' % (i, granted_user)
                db_conn.query("grant select, insert, update, delete on %s_id_seq to %s" % (i, granted_user))
            except:
                print 'Sequence %s_id_seq does not exist' % i


if len(sys.argv) == 3:

    grant_privileges(db_host, db_user, db_passwd, sys.argv[1], sys.argv[2])

else:

    how_to_use(sys.argv[0])
    raise SystemExit