Page MenuHomeSoftware Heritage
Files F9340997

No OneTemporary
Actions

View Options

diff --git a/manifests/server/grant.pp b/manifests/server/grant.pp
index 223d60b..246b403 100644
--- a/manifests/server/grant.pp
+++ b/manifests/server/grant.pp
@@ -1,278 +1,291 @@
# Define for granting permissions to roles. See README.md for more details.
define postgresql::server::grant (
$role,
$db,
$privilege = undef,
$object_type = 'database',
$object_name = undef,
$psql_db = $postgresql::server::default_database,
$psql_user = $postgresql::server::user,
$port = $postgresql::server::port,
$onlyif_exists = false,
$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
}
validate_bool($onlyif_exists)
#
# 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 = $psql_db
+ $onlyif_function = 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}'",
- default => undef,
+ '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}"]
}
}
diff --git a/spec/acceptance/server/grant_spec.rb b/spec/acceptance/server/grant_spec.rb
index c961d5f..01a85f0 100644
--- a/spec/acceptance/server/grant_spec.rb
+++ b/spec/acceptance/server/grant_spec.rb
@@ -1,224 +1,264 @@
require 'spec_helper_acceptance'
describe 'postgresql::server::grant:', :unless => UNSUPPORTED_PLATFORMS.include?(fact('osfamily')) do
let(:db) { 'grant_priv_test' }
let(:owner) { 'psql_grant_priv_owner' }
let(:user) { 'psql_grant_priv_tester' }
+ #testing grants on language requires a superuser
+ let(:superuser) { 'postgres' }
let(:password) { 'psql_grant_role_pw' }
let(:pp_install) { "class {'postgresql::server': }"}
let(:pp_setup) { pp_setup = <<-EOS.unindent
$db = #{db}
$owner = #{owner}
$user = #{user}
$password = #{password}
class { 'postgresql::server': }
postgresql::server::role { $owner:
password_hash => postgresql_password($owner, $password),
}
# Since we are not testing pg_hba or any of that, make a local user for ident auth
user { $owner:
ensure => present,
}
postgresql::server::database { $db:
owner => $owner,
require => Postgresql::Server::Role[$owner],
}
# Create a user to grant privileges to
postgresql::server::role { $user:
db => $db,
require => Postgresql::Server::Database[$db],
}
# Make a local user for ident auth
user { $user:
ensure => present,
}
# Grant them connect to the database
postgresql::server::database_grant { "allow connect for ${user}":
privilege => 'CONNECT',
db => $db,
role => $user,
}
EOS
}
+ context 'GRANT * on LANGUAGE' do
+ let(:pp_lang) { pp_setup + <<-EOS.unindent
+
+ postgresql_psql { 'make sure plpgsql exists':
+ command => 'CREATE OR REPLACE LANGUAGE plpgsql',
+ db => $db,
+ psql_user => $superuser,
+ unless => "SELECT 1 from pg_language where lanname = 'plpgsql'",
+ require => Postgresql::Server::Database[$db],
+ }
+
+ postgresql::server::grant { 'grant usage on plpgsql':
+ psql_user => '#{superuser}',
+ privilege => 'USAGE',
+ object_type => 'LANGUAGE',
+ object_name => 'plpgsql',
+ role => $user,
+ db => $db,
+ require => [ Postgresql_psql['make sure plpgsql exists'],
+ Postgresql::Server::Role[$user], ]
+ }
+ EOS
+ }
+
+ it 'is expected to run idempotently' do
+ apply_manifest(pp_lang, :catch_failures => true)
+ apply_manifest(pp_lang, :catch_changes => true)
+ end
+
+ it 'is expected to GRANT USAGE ON LANGUAGE plpgsql to ROLE' do
+ ## Check that the privilege was granted to the user
+ psql("-d #{db} --command=\"SELECT 1 WHERE has_language_privilege('#{user}', 'plpgsql', 'USAGE')\"", superuser) do |r|
+ expect(r.stdout).to match(/\(1 row\)/)
+ expect(r.stderr).to eq('')
+ end
+ end
+ end
+
context 'sequence' do
it 'should grant usage on a sequence to a user' do
begin
pp = pp_setup + <<-EOS.unindent
postgresql_psql { 'create test sequence':
command => 'CREATE SEQUENCE test_seq',
db => $db,
psql_user => $owner,
unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq'",
require => Postgresql::Server::Database[$db],
}
postgresql::server::grant { 'grant usage on test_seq':
privilege => 'USAGE',
object_type => 'SEQUENCE',
object_name => 'test_seq',
db => $db,
role => $user,
require => [ Postgresql_psql['create test sequence'],
Postgresql::Server::Role[$user], ]
}
EOS
apply_manifest(pp_install, :catch_failures => true)
#postgres version
result = shell('psql --version')
version = result.stdout.match(%r{\s(\d\.\d)})[1]
if version >= '9.0'
apply_manifest(pp, :catch_failures => true)
apply_manifest(pp, :catch_changes => true)
## Check that the privilege was granted to the user
psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq', 'USAGE')\"", user) do |r|
expect(r.stdout).to match(/\(1 row\)/)
expect(r.stderr).to eq('')
end
end
end
end
it 'should grant update on a sequence to a user' do
begin
pp = pp_setup + <<-EOS.unindent
postgresql_psql { 'create test sequence':
command => 'CREATE SEQUENCE test_seq',
db => $db,
psql_user => $owner,
unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq'",
require => Postgresql::Server::Database[$db],
}
postgresql::server::grant { 'grant update on test_seq':
privilege => 'UPDATE',
object_type => 'SEQUENCE',
object_name => 'test_seq',
db => $db,
role => $user,
require => [ Postgresql_psql['create test sequence'],
Postgresql::Server::Role[$user], ]
}
EOS
apply_manifest(pp_install, :catch_failures => true)
#postgres version
result = shell('psql --version')
version = result.stdout.match(%r{\s(\d\.\d)})[1]
if version >= '9.0'
apply_manifest(pp, :catch_failures => true)
apply_manifest(pp, :catch_changes => true)
## Check that the privilege was granted to the user
psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq', 'UPDATE')\"", user) do |r|
expect(r.stdout).to match(/\(1 row\)/)
expect(r.stderr).to eq('')
end
end
end
end
end
context 'all sequences' do
it 'should grant usage on all sequences to a user' do
begin
pp = pp_setup + <<-EOS.unindent
postgresql_psql { 'create test sequences':
command => 'CREATE SEQUENCE test_seq2; CREATE SEQUENCE test_seq3;',
db => $db,
psql_user => $owner,
unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq2'",
require => Postgresql::Server::Database[$db],
}
postgresql::server::grant { 'grant usage on all sequences':
privilege => 'USAGE',
object_type => 'ALL SEQUENCES IN SCHEMA',
object_name => 'public',
db => $db,
role => $user,
require => [ Postgresql_psql['create test sequences'],
Postgresql::Server::Role[$user], ]
}
EOS
apply_manifest(pp_install, :catch_failures => true)
#postgres version
result = shell('psql --version')
version = result.stdout.match(%r{\s(\d\.\d)})[1]
if version >= '9.0'
apply_manifest(pp, :catch_failures => true)
apply_manifest(pp, :catch_changes => true)
## Check that the privileges were granted to the user, this check is not available on version < 9.0
psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq2', 'USAGE') AND has_sequence_privilege('#{user}', 'test_seq3', 'USAGE')\"", user) do |r|
expect(r.stdout).to match(/\(1 row\)/)
expect(r.stderr).to eq('')
end
end
end
end
it 'should grant update on all sequences to a user' do
begin
pp = pp_setup + <<-EOS.unindent
postgresql_psql { 'create test sequences':
command => 'CREATE SEQUENCE test_seq2; CREATE SEQUENCE test_seq3;',
db => $db,
psql_user => $owner,
unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq2'",
require => Postgresql::Server::Database[$db],
}
postgresql::server::grant { 'grant usage on all sequences':
privilege => 'UPDATE',
object_type => 'ALL SEQUENCES IN SCHEMA',
object_name => 'public',
db => $db,
role => $user,
require => [ Postgresql_psql['create test sequences'],
Postgresql::Server::Role[$user], ]
}
EOS
apply_manifest(pp_install, :catch_failures => true)
#postgres version
result = shell('psql --version')
version = result.stdout.match(%r{\s(\d\.\d)})[1]
if version >= '9.0'
apply_manifest(pp, :catch_failures => true)
apply_manifest(pp, :catch_changes => true)
## Check that the privileges were granted to the user
psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq2', 'UPDATE') AND has_sequence_privilege('#{user}', 'test_seq3', 'UPDATE')\"", user) do |r|
expect(r.stdout).to match(/\(1 row\)/)
expect(r.stderr).to eq('')
end
end
end
end
end
end

File Metadata

Mime Type
text/x-diff
Expires
Fri, Jul 4, 11:29 AM (3 w, 2 d ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3337376

Event Timeline

About · Developer information · Legal