def main(options):
con = connect()
cur = con.cursor()
cur.execute("""
SELECT relname FROM pg_class,pg_namespace
WHERE pg_class.relnamespace = pg_namespace.oid
AND pg_namespace.nspname='public'
AND pg_class.relkind = 'r'
ORDER BY relname
""")
for table in (row[0] for row in cur.fetchall()):
cur.execute("SELECT TRUE FROM public.%s LIMIT 1" %
quote_identifier(table))
if cur.fetchone() is None:
print table
def main(options):
con = connect()
cur = con.cursor()
cur.execute("""
SELECT relname FROM pg_class,pg_namespace
WHERE pg_class.relnamespace = pg_namespace.oid
AND pg_namespace.nspname='public'
AND pg_class.relkind = 'r'
ORDER BY relname
""")
for table in (row[0] for row in cur.fetchall()):
cur.execute(
"SELECT TRUE FROM public.%s LIMIT 1" % quote_identifier(table)
)
if cur.fetchone() is None:
print table
def alter_permissions(cur, which, revoke=False):
"""Efficiently apply a set of permission changes.
:param cur: a database cursor
:param which: an iterable of (object, role, permissions)
:param revoke: whether to revoke or grant permissions
"""
gatherers = {
'table': PermissionGatherer("TABLE"),
'function': PermissionGatherer("FUNCTION"),
'sequence': PermissionGatherer("SEQUENCE"),
}
for obj, role, perms in which:
gatherers.get(obj.type, gatherers['table']).add(
', '.join(perms), obj.fullname, quote_identifier(role))
for gatherer in gatherers.values():
if revoke:
gatherer.revoke(cur)
else:
gatherer.grant(cur)
def reset_permissions(con, config, options):
schema = DbSchema(con)
cur = CursorWrapper(con.cursor())
groups = set()
# Add our two automatically maintained groups
for group in ['read', 'admin']:
groups.add(group)
if group not in schema.roles:
log.debug("Creating %s role" % group)
cur.execute("CREATE GROUP %s" % quote_identifier(group))
schema.roles[group] = set()
# Create all required groups and users.
log.debug("Configuring roles")
for section_name in config.sections():
if section_name.lower() == 'public':
continue
assert not section_name.endswith('_ro'), (
'_ro namespace is reserved (%s)' % repr(section_name))
type_ = config.get(section_name, 'type')
assert type_ in ['user', 'group'], 'Unknown type %s' % type_
desired_opts = set(('INHERIT',))
if type_ == 'user':
desired_opts.add('LOGIN')
for username in [section_name, '%s_ro' % section_name]:
if type_ == 'group':
groups.add(username)
if username in schema.roles:
existing_opts = schema.roles[username]
if desired_opts != existing_opts:
# Note - we don't drop the user because it might own
# objects in other databases. We need to ensure they are
# not superusers though!
log.debug2("Resetting role options of %s role.", username)
changes = ' '.join(
list(desired_opts - existing_opts)
+ ['NO' + o for o in (existing_opts - desired_opts)])
cur.execute(
"ALTER ROLE %s WITH %s" % (
quote_identifier(username), changes))
else:
log.debug("Creating %s role.", username)
cur.execute(
"CREATE ROLE %s WITH %s"
% (quote_identifier(username), ' '.join(desired_opts)))
schema.roles[username] = set()
# Set default read-only mode for our roles.
cur.execute(
'ALTER ROLE %s SET default_transaction_read_only TO FALSE'
% quote_identifier(section_name))
cur.execute(
'ALTER ROLE %s SET default_transaction_read_only TO TRUE'
% quote_identifier('%s_ro' % section_name))
# Add users to groups
log.debug('Collecting group memberships')
memberships = defaultdict(set)
for user in config.sections():
if config.get(user, 'type') != 'user':
continue
groups = [
g.strip() for g in config.get(user, 'groups', '').split(',')
if g.strip()]
# Read-Only users get added to Read-Only groups.
if user.endswith('_ro'):
groups = ['%s_ro' % group for group in groups]
if groups:
log.debug2("Adding %s to %s roles", user, ', '.join(groups))
for group in groups:
memberships[group].add(user)
else:
log.debug2("%s not in any roles", user)
managed_roles = set(['read', 'admin'])
for section_name in config.sections():
managed_roles.add(section_name)
if section_name != 'public':
managed_roles.add(section_name + "_ro")
log.debug('Updating group memberships')
existing_memberships = list_role_members(cur, memberships.keys())
for group, users in memberships.iteritems():
cur_users = managed_roles.intersection(existing_memberships[group])
to_grant = users - cur_users
if to_grant:
cur.execute("GRANT %s TO %s" % (
quote_identifier(group),
', '.join(quote_identifier(user) for user in to_grant)))
to_revoke = cur_users - users
if options.revoke and to_revoke:
cur.execute("REVOKE %s FROM %s" % (
quote_identifier(group),
', '.join(quote_identifier(user) for user in to_revoke)))
if options.revoke:
log.debug('Resetting object owners')
# Change ownership of all objects to OWNER.
# We skip this in --no-revoke mode as ownership changes may
# block on a live system.
for obj in schema.values():
if obj.type in ("function", "sequence"):
pass # Can't change ownership of functions or sequences
else:
if obj.owner != options.owner:
log.info("Resetting ownership of %s", obj.fullname)
cur.execute("ALTER TABLE %s OWNER TO %s" % (
obj.fullname, quote_identifier(options.owner)))
else:
log.info("Not resetting ownership of database objects")
# Set of all tables we have granted permissions on. After we have assigned
# permissions, we can use this to determine what tables have been
# forgotten about.
found = set()
# Set permissions as per config file
desired_permissions = defaultdict(lambda: defaultdict(set))
valid_objs = set(schema.iterkeys())
# Any object with permissions granted is accessible to the 'read'
# role. Some (eg. the lp_* replicated tables and internal or trigger
# functions) aren't readable.
granted_objs = set()
log.debug('Collecting permissions')
for username in config.sections():
who = username
if username == 'public':
who_ro = who
else:
who_ro = '%s_ro' % username
for obj_name, perm in config.items(username):
if '.' not in obj_name:
continue
if obj_name not in valid_objs:
log.warn('Bad object name %r', obj_name)
continue
obj = schema[obj_name]
found.add(obj)
perm = perm.strip()
if not perm:
# No perm means no rights. We can't grant no rights, so skip.
continue
granted_objs.add(obj)
if obj.type == 'function':
desired_permissions[obj][who].update(perm.split(', '))
if who_ro:
desired_permissions[obj][who_ro].add("EXECUTE")
else:
desired_permissions[obj][who].update(perm.split(', '))
if who_ro:
desired_permissions[obj][who_ro].add("SELECT")
if obj.seqname in valid_objs:
seq = schema[obj.seqname]
granted_objs.add(seq)
if 'INSERT' in perm:
seqperm = 'USAGE'
elif 'SELECT' in perm:
seqperm = 'SELECT'
else:
seqperm = None
if seqperm:
desired_permissions[seq][who].add(seqperm)
desired_permissions[seq][who_ro].add("SELECT")
# read gets read access to all non-secure objects that we've granted
# anybody access to.
for obj in granted_objs:
if obj.type == 'function':
desired_permissions[obj]['read'].add("EXECUTE")
else:
if obj.fullname not in SECURE_TABLES:
desired_permissions[obj]['read'].add("SELECT")
# Set permissions on public schemas
public_schemas = [
s.strip() for s in config.get('DEFAULT', 'public_schemas').split(',')
if s.strip()]
log.debug("Granting access to %d public schemas", len(public_schemas))
for schema_name in public_schemas:
cur.execute("GRANT USAGE ON SCHEMA %s TO PUBLIC" % (
quote_identifier(schema_name),
))
for obj in schema.values():
if obj.schema not in public_schemas:
continue
found.add(obj)
if obj.type == 'function':
desired_permissions[obj]['public'].add('EXECUTE')
else:
desired_permissions[obj]['public'].add('SELECT')
# For every object in the DB, ensure that the privileges held by our
# managed roles match our expectations. If not, store the delta
# to be applied later.
# Also grants/revokes access by the admin role, which isn't a
# traditionally managed role.
unmanaged_roles = set()
required_grants = []
required_revokes = []
log.debug('Calculating permission delta')
for obj in schema.values():
# We only care about roles that are in either the desired or
# existing ACL, and are also our managed roles. But skip admin,
# because it's done at the end.
interesting_roles = set(desired_permissions[obj]).union(obj.acl)
unmanaged_roles.update(interesting_roles.difference(managed_roles))
for role in managed_roles.intersection(interesting_roles):
if role == 'admin':
continue
new = desired_permissions[obj][role]
old_privs = obj.acl.get(role, {})
old = set(old_privs)
if any(old_privs.itervalues()):
log.warning("%s has grant option on %s", role, obj.fullname)
if new == old:
continue
missing = new.difference(old)
extra = old.difference(new)
if missing:
required_grants.append((obj, role, missing))
if extra:
required_revokes.append((obj, role, extra))
# admin get all privileges on anything with privileges granted
# in security.cfg. We don't have a mapping from ALL to real
# privileges for each object type, so we just grant or revoke ALL
# each time.
if obj in granted_objs:
required_grants.append((obj, "admin", ("ALL",)))
else:
if "admin" in obj.acl:
required_revokes.append((obj, "admin", ("ALL",)))
log.debug("Unmanaged roles on managed objects: %r", list(unmanaged_roles))
alter_permissions(cur, required_grants)
if options.revoke:
alter_permissions(cur, required_revokes, revoke=True)
# Raise an error if we have database objects lying around that have not
# had permissions assigned.
forgotten = set()
for obj in schema.values():
if obj not in found:
forgotten.add(obj)
forgotten = [obj.fullname for obj in forgotten
if obj.type in ['table', 'function', 'view']]
if forgotten:
log.warn('No permissions specified for %r', forgotten)
if options.dryrun:
log.info("Dry run - rolling back changes")
con.rollback()
else:
log.debug("Committing changes")
con.commit()
