1 require 'spec_helper_acceptance'
3 describe 'postgresql::server::grant:', :unless => UNSUPPORTED_PLATFORMS.include?(fact('osfamily')) do
5 let(:db) { 'grant_priv_test' }
6 let(:owner) { 'psql_grant_priv_owner' }
7 let(:user) { 'psql_grant_priv_tester' }
8 let(:password) { 'psql_grant_role_pw' }
9 let(:pp_install) { "class {'postgresql::server': }"}
11 let(:pp_setup) { pp_setup = <<-EOS.unindent
15 $password = #{password}
17 class { 'postgresql::server': }
19 postgresql::server::role { $owner:
20 password_hash => postgresql_password($owner, $password),
23 # Since we are not testing pg_hba or any of that, make a local user for ident auth
28 postgresql::server::database { $db:
30 require => Postgresql::Server::Role[$owner],
33 # Create a user to grant privileges to
34 postgresql::server::role { $user:
36 require => Postgresql::Server::Database[$db],
39 # Make a local user for ident auth
44 # Grant them connect to the database
45 postgresql::server::database_grant { "allow connect for ${user}":
46 privilege => 'CONNECT',
54 describe 'GRANT * ON LANGUAGE' do
55 #testing grants on language requires a superuser
56 let(:superuser) { 'postgres' }
57 let(:pp_lang) { pp_setup + <<-EOS.unindent
59 postgresql_psql { 'make sure plpgsql exists':
60 command => 'CREATE LANGUAGE plpgsql',
62 psql_user => '#{superuser}',
63 unless => "SELECT 1 from pg_language where lanname = 'plpgsql'",
64 require => Postgresql::Server::Database[$db],
67 postgresql::server::grant { 'grant usage on plpgsql':
68 psql_user => '#{superuser}',
70 object_type => 'LANGUAGE',
71 object_name => 'plpgsql',
74 require => [ Postgresql_psql['make sure plpgsql exists'],
75 Postgresql::Server::Role[$user], ]
80 it 'is expected to run idempotently' do
81 apply_manifest(pp_install)
84 result = shell('psql --version')
85 version = result.stdout.match(%r{\s(\d\.\d)})[1]
88 apply_manifest(pp_lang, :catch_failures => true)
89 apply_manifest(pp_lang, :catch_changes => true)
93 it 'is expected to GRANT USAGE ON LANGUAGE plpgsql to ROLE' do
94 result = shell('psql --version')
95 version = result.stdout.match(%r{\s(\d\.\d)})[1]
98 ## Check that the privilege was granted to the user
99 psql("-d #{db} --command=\"SELECT 1 WHERE has_language_privilege('#{user}', 'plpgsql', 'USAGE')\"", superuser) do |r|
100 expect(r.stdout).to match(/\(1 row\)/)
101 expect(r.stderr).to eq('')
106 let(:pp_onlyif) { pp_setup + <<-EOS.unindent
107 postgresql::server::grant { 'grant usage on BSql':
108 psql_user => '#{superuser}',
109 privilege => 'USAGE',
110 object_type => 'LANGUAGE',
111 object_name => 'bsql',
114 onlyif_exists => true,
119 #test onlyif_exists function
120 it 'is expected to not GRANT USAGE ON (dummy)LANGUAGE BSql to ROLE' do
121 apply_manifest(pp_install)
124 result = shell('psql --version')
125 version = result.stdout.match(%r{\s(\d\.\d)})[1]
127 if version >= '8.4.0'
128 apply_manifest(pp_onlyif, :catch_failures => true)
129 apply_manifest(pp_onlyif, :catch_changes => true)
135 context 'sequence' do
136 it 'should grant usage on a sequence to a user' do
138 pp = pp_setup + <<-EOS.unindent
140 postgresql_psql { 'create test sequence':
141 command => 'CREATE SEQUENCE test_seq',
144 unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq'",
145 require => Postgresql::Server::Database[$db],
148 postgresql::server::grant { 'grant usage on test_seq':
149 privilege => 'USAGE',
150 object_type => 'SEQUENCE',
151 object_name => 'test_seq',
154 require => [ Postgresql_psql['create test sequence'],
155 Postgresql::Server::Role[$user], ]
159 apply_manifest(pp_install, :catch_failures => true)
162 result = shell('psql --version')
163 version = result.stdout.match(%r{\s(\d\.\d)})[1]
166 apply_manifest(pp, :catch_failures => true)
167 apply_manifest(pp, :catch_changes => true)
169 ## Check that the privilege was granted to the user
170 psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq', 'USAGE')\"", user) do |r|
171 expect(r.stdout).to match(/\(1 row\)/)
172 expect(r.stderr).to eq('')
178 it 'should grant update on a sequence to a user' do
180 pp = pp_setup + <<-EOS.unindent
182 postgresql_psql { 'create test sequence':
183 command => 'CREATE SEQUENCE test_seq',
186 unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq'",
187 require => Postgresql::Server::Database[$db],
190 postgresql::server::grant { 'grant update on test_seq':
191 privilege => 'UPDATE',
192 object_type => 'SEQUENCE',
193 object_name => 'test_seq',
196 require => [ Postgresql_psql['create test sequence'],
197 Postgresql::Server::Role[$user], ]
201 apply_manifest(pp_install, :catch_failures => true)
204 result = shell('psql --version')
205 version = result.stdout.match(%r{\s(\d\.\d)})[1]
208 apply_manifest(pp, :catch_failures => true)
209 apply_manifest(pp, :catch_changes => true)
211 ## Check that the privilege was granted to the user
212 psql("-d #{db} --command=\"SELECT 1 WHERE has_sequence_privilege('#{user}', 'test_seq', 'UPDATE')\"", user) do |r|
213 expect(r.stdout).to match(/\(1 row\)/)
214 expect(r.stderr).to eq('')
221 context 'all sequences' do
222 it 'should grant usage on all sequences to a user' do
224 pp = pp_setup + <<-EOS.unindent
226 postgresql_psql { 'create test sequences':
227 command => 'CREATE SEQUENCE test_seq2; CREATE SEQUENCE test_seq3;',
230 unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq2'",
231 require => Postgresql::Server::Database[$db],
234 postgresql::server::grant { 'grant usage on all sequences':
235 privilege => 'USAGE',
236 object_type => 'ALL SEQUENCES IN SCHEMA',
237 object_name => 'public',
240 require => [ Postgresql_psql['create test sequences'],
241 Postgresql::Server::Role[$user], ]
245 apply_manifest(pp_install, :catch_failures => true)
248 result = shell('psql --version')
249 version = result.stdout.match(%r{\s(\d\.\d)})[1]
252 apply_manifest(pp, :catch_failures => true)
253 apply_manifest(pp, :catch_changes => true)
255 ## Check that the privileges were granted to the user, this check is not available on version < 9.0
256 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|
257 expect(r.stdout).to match(/\(1 row\)/)
258 expect(r.stderr).to eq('')
264 it 'should grant update on all sequences to a user' do
266 pp = pp_setup + <<-EOS.unindent
268 postgresql_psql { 'create test sequences':
269 command => 'CREATE SEQUENCE test_seq2; CREATE SEQUENCE test_seq3;',
272 unless => "SELECT 1 FROM information_schema.sequences WHERE sequence_name = 'test_seq2'",
273 require => Postgresql::Server::Database[$db],
276 postgresql::server::grant { 'grant usage on all sequences':
277 privilege => 'UPDATE',
278 object_type => 'ALL SEQUENCES IN SCHEMA',
279 object_name => 'public',
282 require => [ Postgresql_psql['create test sequences'],
283 Postgresql::Server::Role[$user], ]
287 apply_manifest(pp_install, :catch_failures => true)
290 result = shell('psql --version')
291 version = result.stdout.match(%r{\s(\d\.\d)})[1]
294 apply_manifest(pp, :catch_failures => true)
295 apply_manifest(pp, :catch_changes => true)
297 ## Check that the privileges were granted to the user
298 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|
299 expect(r.stdout).to match(/\(1 row\)/)
300 expect(r.stderr).to eq('')