--- /dev/null
+# Define for granting permissions to roles. See README.md for more details.
+define postgresql::server::grant (
+ String $role,
+ String $db,
+ Optional[String] $privilege = undef,
+ String $object_type = 'database',
+ Optional[String[1]] $object_name = undef,
+ String $psql_db = $postgresql::server::default_database,
+ String $psql_user = $postgresql::server::user,
+ Integer $port = $postgresql::server::port,
+ Boolean $onlyif_exists = false,
+ Hash $connect_settings = $postgresql::server::default_connect_settings,
+) {
+
+ $group = $postgresql::server::group
+ $psql_path = $postgresql::server::psql_path
+
+ if ! $object_name {
+ $_object_name = $db
+ } else {
+ $_object_name = $object_name
+ }
+
+ #
+ # Port, order of precedence: $port parameter, $connect_settings[PGPORT], $postgresql::server::port
+ #
+ if $port != undef {
+ $port_override = $port
+ } elsif $connect_settings != undef and has_key( $connect_settings, 'PGPORT') {
+ $port_override = undef
+ } else {
+ $port_override = $postgresql::server::port
+ }
+
+ ## Munge the input values
+ $_object_type = upcase($object_type)
+ $_privilege = upcase($privilege)
+
+ ## Validate that the object type is known
+ validate_re($_object_type,[
+ #'^COLUMN$',
+ '^DATABASE$',
+ #'^FOREIGN SERVER$',
+ #'^FOREIGN DATA WRAPPER$',
+ #'^FUNCTION$',
+ #'^PROCEDURAL LANGUAGE$',
+ '^SCHEMA$',
+ '^SEQUENCE$',
+ '^ALL SEQUENCES IN SCHEMA$',
+ '^TABLE$',
+ '^ALL TABLES IN SCHEMA$',
+ '^LANGUAGE$',
+ #'^TABLESPACE$',
+ #'^VIEW$',
+ ]
+ )
+ # You can use ALL TABLES IN SCHEMA by passing schema_name to object_name
+ # You can use ALL SEQUENCES IN SCHEMA by passing schema_name to object_name
+
+ ## Validate that the object type's privilege is acceptable
+ # TODO: this is a terrible hack; if they pass "ALL" as the desired privilege,
+ # we need a way to test for it--and has_database_privilege does not
+ # recognize 'ALL' as a valid privilege name. So we probably need to
+ # hard-code a mapping between 'ALL' and the list of actual privileges that
+ # it entails, and loop over them to check them. That sort of thing will
+ # probably need to wait until we port this over to ruby, so, for now, we're
+ # just going to assume that if they have "CREATE" privileges on a database,
+ # then they have "ALL". (I told you that it was terrible!)
+ case $_object_type {
+ 'DATABASE': {
+ $unless_privilege = $_privilege ? {
+ 'ALL' => 'CREATE',
+ 'ALL PRIVILEGES' => 'CREATE',
+ default => $_privilege,
+ }
+ validate_re($unless_privilege, [ '^$', '^CREATE$','^CONNECT$','^TEMPORARY$','^TEMP$',
+ '^ALL$','^ALL PRIVILEGES$' ])
+ $unless_function = 'has_database_privilege'
+ $on_db = $psql_db
+ $onlyif_function = undef
+ }
+ 'SCHEMA': {
+ $unless_privilege = $_privilege ? {
+ 'ALL' => 'CREATE',
+ 'ALL PRIVILEGES' => 'CREATE',
+ default => $_privilege,
+ }
+ validate_re($_privilege, [ '^$', '^CREATE$', '^USAGE$', '^ALL$', '^ALL PRIVILEGES$' ])
+ $unless_function = 'has_schema_privilege'
+ $on_db = $db
+ $onlyif_function = undef
+ }
+ 'SEQUENCE': {
+ $unless_privilege = $_privilege ? {
+ 'ALL' => 'USAGE',
+ default => $_privilege,
+ }
+ validate_re($unless_privilege, [ '^$', '^USAGE$', '^SELECT$', '^UPDATE$', '^ALL$', '^ALL PRIVILEGES$' ])
+ $unless_function = 'has_sequence_privilege'
+ $on_db = $db
+ $onlyif_function = undef
+ }
+ 'ALL SEQUENCES IN SCHEMA': {
+ validate_re($_privilege, [ '^$', '^USAGE$', '^SELECT$', '^UPDATE$', '^ALL$', '^ALL PRIVILEGES$' ])
+ $unless_function = 'custom'
+ $on_db = $db
+ $onlyif_function = undef
+
+ $schema = $object_name
+
+ $custom_privilege = $_privilege ? {
+ 'ALL' => 'USAGE',
+ 'ALL PRIVILEGES' => 'USAGE',
+ default => $_privilege,
+ }
+
+ # This checks if there is a difference between the sequences in the
+ # specified schema and the sequences for which the role has the specified
+ # privilege. It uses the EXCEPT clause which computes the set of rows
+ # that are in the result of the first SELECT statement but not in the
+ # result of the second one. It then counts the number of rows from this
+ # operation. If this number is zero then the role has the specified
+ # privilege for all sequences in the schema and the whole query returns a
+ # single row, which satisfies the `unless` parameter of Postgresql_psql.
+ # If this number is not zero then there is at least one sequence for which
+ # the role does not have the specified privilege, making it necessary to
+ # execute the GRANT statement.
+ $custom_unless = "SELECT 1 FROM (
+ SELECT sequence_name
+ FROM information_schema.sequences
+ WHERE sequence_schema='${schema}'
+ EXCEPT DISTINCT
+ SELECT object_name as sequence_name
+ FROM (
+ SELECT object_schema,
+ object_name,
+ grantee,
+ CASE privs_split
+ WHEN 'r' THEN 'SELECT'
+ WHEN 'w' THEN 'UPDATE'
+ WHEN 'U' THEN 'USAGE'
+ END AS privilege_type
+ FROM (
+ SELECT DISTINCT
+ object_schema,
+ object_name,
+ (regexp_split_to_array(regexp_replace(privs,E'/.*',''),'='))[1] AS grantee,
+ regexp_split_to_table((regexp_split_to_array(regexp_replace(privs,E'/.*',''),'='))[2],E'\\s*') AS privs_split
+ FROM (
+ SELECT n.nspname as object_schema,
+ c.relname as object_name,
+ regexp_split_to_table(array_to_string(c.relacl,','),',') AS privs
+ FROM pg_catalog.pg_class c
+ LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
+ WHERE c.relkind = 'S'
+ AND n.nspname NOT IN ( 'pg_catalog', 'information_schema' )
+ ) P1
+ ) P2
+ ) P3
+ WHERE grantee='${role}'
+ AND object_schema='${schema}'
+ AND privilege_type='${custom_privilege}'
+ ) P
+ HAVING count(P.sequence_name) = 0"
+ }
+ 'TABLE': {
+ $unless_privilege = $_privilege ? {
+ 'ALL' => 'INSERT',
+ default => $_privilege,
+ }
+ validate_re($unless_privilege,[ '^$', '^SELECT$','^INSERT$','^UPDATE$','^DELETE$',
+ '^TRUNCATE$','^REFERENCES$','^TRIGGER$','^ALL$','^ALL PRIVILEGES$' ])
+ $unless_function = 'has_table_privilege'
+ $on_db = $db
+ $onlyif_function = $onlyif_exists ? {
+ true => 'table_exists',
+ default => undef,
+ }
+ }
+ 'ALL TABLES IN SCHEMA': {
+ validate_re($_privilege, [ '^$', '^SELECT$','^INSERT$','^UPDATE$','^DELETE$',
+ '^TRUNCATE$','^REFERENCES$','^TRIGGER$','^ALL$','^ALL PRIVILEGES$' ])
+ $unless_function = 'custom'
+ $on_db = $db
+ $onlyif_function = undef
+
+ $schema = $object_name
+
+ # Again there seems to be no easy way in plain SQL to check if ALL
+ # PRIVILEGES are granted on a table. By convention we use INSERT
+ # here to represent ALL PRIVILEGES (truly terrible).
+ $custom_privilege = $_privilege ? {
+ 'ALL' => 'INSERT',
+ 'ALL PRIVILEGES' => 'INSERT',
+ default => $_privilege,
+ }
+
+ # This checks if there is a difference between the tables in the
+ # specified schema and the tables for which the role has the specified
+ # privilege. It uses the EXCEPT clause which computes the set of rows
+ # that are in the result of the first SELECT statement but not in the
+ # result of the second one. It then counts the number of rows from this
+ # operation. If this number is zero then the role has the specified
+ # privilege for all tables in the schema and the whole query returns a
+ # single row, which satisfies the `unless` parameter of Postgresql_psql.
+ # If this number is not zero then there is at least one table for which
+ # the role does not have the specified privilege, making it necessary to
+ # execute the GRANT statement.
+ $custom_unless = "SELECT 1 FROM (
+ SELECT table_name
+ FROM information_schema.tables
+ WHERE table_schema='${schema}'
+ EXCEPT DISTINCT
+ SELECT table_name
+ FROM information_schema.role_table_grants
+ WHERE grantee='${role}'
+ AND table_schema='${schema}'
+ AND privilege_type='${custom_privilege}'
+ ) P
+ HAVING count(P.table_name) = 0"
+ }
+ 'LANGUAGE': {
+ $unless_privilege = $_privilege ? {
+ 'ALL' => 'USAGE',
+ 'ALL PRIVILEGES' => 'USAGE',
+ default => $_privilege,
+ }
+ validate_re($unless_privilege, [ '^$','^CREATE$','^USAGE$','^ALL$','^ALL PRIVILEGES$' ])
+ $unless_function = 'has_language_privilege'
+ $on_db = $db
+ $onlyif_function = $onlyif_exists ? {
+ true => 'language_exists',
+ default => undef,
+ }
+ }
+
+ default: {
+ fail("Missing privilege validation for object type ${_object_type}")
+ }
+ }
+
+ # This is used to give grant to "schemaname"."tablename"
+ # If you need such grant, use:
+ # postgresql::grant { 'table:foo':
+ # role => 'joe',
+ # ...
+ # object_type => 'TABLE',
+ # object_name => [$schema, $table],
+ # }
+ if is_array($_object_name) {
+ $_togrant_object = join($_object_name, '"."')
+ # Never put double quotes into has_*_privilege function
+ $_granted_object = join($_object_name, '.')
+ } else {
+ $_granted_object = $_object_name
+ $_togrant_object = $_object_name
+ }
+
+ $_unless = $unless_function ? {
+ false => undef,
+ 'custom' => $custom_unless,
+ default => "SELECT 1 WHERE ${unless_function}('${role}',
+ '${_granted_object}', '${unless_privilege}')",
+ }
+
+ $_onlyif = $onlyif_function ? {
+ 'table_exists' => "SELECT true FROM pg_tables WHERE tablename = '${_togrant_object}'",
+ 'language_exists' => "SELECT true from pg_language WHERE lanname = '${_togrant_object}'",
+ default => undef,
+ }
+
+ $grant_cmd = "GRANT ${_privilege} ON ${_object_type} \"${_togrant_object}\" TO
+ \"${role}\""
+ postgresql_psql { "grant:${name}":
+ command => $grant_cmd,
+ db => $on_db,
+ port => $port_override,
+ connect_settings => $connect_settings,
+ psql_user => $psql_user,
+ psql_group => $group,
+ psql_path => $psql_path,
+ unless => $_unless,
+ onlyif => $_onlyif,
+ require => Class['postgresql::server']
+ }
+
+ if($role != undef and defined(Postgresql::Server::Role[$role])) {
+ Postgresql::Server::Role[$role]->Postgresql_psql["grant:${name}"]
+ }
+
+ if($db != undef and defined(Postgresql::Server::Database[$db])) {
+ Postgresql::Server::Database[$db]->Postgresql_psql["grant:${name}"]
+ }
+}
projects / mirror / dsa-puppet.git / blobdiff