1 # Define for granting permissions to roles. See README.md for more details.
2 define postgresql::server::grant (
5 Optional[String] $privilege = undef,
6 String $object_type = 'database',
7 Optional[String[1]] $object_name = undef,
8 String $psql_db = $postgresql::server::default_database,
9 String $psql_user = $postgresql::server::user,
10 Integer $port = $postgresql::server::port,
11 Boolean $onlyif_exists = false,
12 Hash $connect_settings = $postgresql::server::default_connect_settings,
15 $group = $postgresql::server::group
16 $psql_path = $postgresql::server::psql_path
21 $_object_name = $object_name
25 # Port, order of precedence: $port parameter, $connect_settings[PGPORT], $postgresql::server::port
28 $port_override = $port
29 } elsif $connect_settings != undef and has_key( $connect_settings, 'PGPORT') {
30 $port_override = undef
32 $port_override = $postgresql::server::port
35 ## Munge the input values
36 $_object_type = upcase($object_type)
37 $_privilege = upcase($privilege)
39 ## Validate that the object type is known
40 validate_re($_object_type,[
44 #'^FOREIGN DATA WRAPPER$',
46 #'^PROCEDURAL LANGUAGE$',
49 '^ALL SEQUENCES IN SCHEMA$',
51 '^ALL TABLES IN SCHEMA$',
57 # You can use ALL TABLES IN SCHEMA by passing schema_name to object_name
58 # You can use ALL SEQUENCES IN SCHEMA by passing schema_name to object_name
60 ## Validate that the object type's privilege is acceptable
61 # TODO: this is a terrible hack; if they pass "ALL" as the desired privilege,
62 # we need a way to test for it--and has_database_privilege does not
63 # recognize 'ALL' as a valid privilege name. So we probably need to
64 # hard-code a mapping between 'ALL' and the list of actual privileges that
65 # it entails, and loop over them to check them. That sort of thing will
66 # probably need to wait until we port this over to ruby, so, for now, we're
67 # just going to assume that if they have "CREATE" privileges on a database,
68 # then they have "ALL". (I told you that it was terrible!)
71 $unless_privilege = $_privilege ? {
73 'ALL PRIVILEGES' => 'CREATE',
74 default => $_privilege,
76 validate_re($unless_privilege, [ '^$', '^CREATE$','^CONNECT$','^TEMPORARY$','^TEMP$',
77 '^ALL$','^ALL PRIVILEGES$' ])
78 $unless_function = 'has_database_privilege'
80 $onlyif_function = undef
83 $unless_privilege = $_privilege ? {
85 'ALL PRIVILEGES' => 'CREATE',
86 default => $_privilege,
88 validate_re($_privilege, [ '^$', '^CREATE$', '^USAGE$', '^ALL$', '^ALL PRIVILEGES$' ])
89 $unless_function = 'has_schema_privilege'
91 $onlyif_function = undef
94 $unless_privilege = $_privilege ? {
96 default => $_privilege,
98 validate_re($unless_privilege, [ '^$', '^USAGE$', '^SELECT$', '^UPDATE$', '^ALL$', '^ALL PRIVILEGES$' ])
99 $unless_function = 'has_sequence_privilege'
101 $onlyif_function = undef
103 'ALL SEQUENCES IN SCHEMA': {
104 validate_re($_privilege, [ '^$', '^USAGE$', '^SELECT$', '^UPDATE$', '^ALL$', '^ALL PRIVILEGES$' ])
105 $unless_function = 'custom'
107 $onlyif_function = undef
109 $schema = $object_name
111 $custom_privilege = $_privilege ? {
113 'ALL PRIVILEGES' => 'USAGE',
114 default => $_privilege,
117 # This checks if there is a difference between the sequences in the
118 # specified schema and the sequences for which the role has the specified
119 # privilege. It uses the EXCEPT clause which computes the set of rows
120 # that are in the result of the first SELECT statement but not in the
121 # result of the second one. It then counts the number of rows from this
122 # operation. If this number is zero then the role has the specified
123 # privilege for all sequences in the schema and the whole query returns a
124 # single row, which satisfies the `unless` parameter of Postgresql_psql.
125 # If this number is not zero then there is at least one sequence for which
126 # the role does not have the specified privilege, making it necessary to
127 # execute the GRANT statement.
128 $custom_unless = "SELECT 1 FROM (
130 FROM information_schema.sequences
131 WHERE sequence_schema='${schema}'
133 SELECT object_name as sequence_name
135 SELECT object_schema,
139 WHEN 'r' THEN 'SELECT'
140 WHEN 'w' THEN 'UPDATE'
141 WHEN 'U' THEN 'USAGE'
142 END AS privilege_type
147 (regexp_split_to_array(regexp_replace(privs,E'/.*',''),'='))[1] AS grantee,
148 regexp_split_to_table((regexp_split_to_array(regexp_replace(privs,E'/.*',''),'='))[2],E'\\s*') AS privs_split
150 SELECT n.nspname as object_schema,
151 c.relname as object_name,
152 regexp_split_to_table(array_to_string(c.relacl,','),',') AS privs
153 FROM pg_catalog.pg_class c
154 LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
155 WHERE c.relkind = 'S'
156 AND n.nspname NOT IN ( 'pg_catalog', 'information_schema' )
160 WHERE grantee='${role}'
161 AND object_schema='${schema}'
162 AND privilege_type='${custom_privilege}'
164 HAVING count(P.sequence_name) = 0"
167 $unless_privilege = $_privilege ? {
169 default => $_privilege,
171 validate_re($unless_privilege,[ '^$', '^SELECT$','^INSERT$','^UPDATE$','^DELETE$',
172 '^TRUNCATE$','^REFERENCES$','^TRIGGER$','^ALL$','^ALL PRIVILEGES$' ])
173 $unless_function = 'has_table_privilege'
175 $onlyif_function = $onlyif_exists ? {
176 true => 'table_exists',
180 'ALL TABLES IN SCHEMA': {
181 validate_re($_privilege, [ '^$', '^SELECT$','^INSERT$','^UPDATE$','^DELETE$',
182 '^TRUNCATE$','^REFERENCES$','^TRIGGER$','^ALL$','^ALL PRIVILEGES$' ])
183 $unless_function = 'custom'
185 $onlyif_function = undef
187 $schema = $object_name
189 # Again there seems to be no easy way in plain SQL to check if ALL
190 # PRIVILEGES are granted on a table. By convention we use INSERT
191 # here to represent ALL PRIVILEGES (truly terrible).
192 $custom_privilege = $_privilege ? {
194 'ALL PRIVILEGES' => 'INSERT',
195 default => $_privilege,
198 # This checks if there is a difference between the tables in the
199 # specified schema and the tables for which the role has the specified
200 # privilege. It uses the EXCEPT clause which computes the set of rows
201 # that are in the result of the first SELECT statement but not in the
202 # result of the second one. It then counts the number of rows from this
203 # operation. If this number is zero then the role has the specified
204 # privilege for all tables in the schema and the whole query returns a
205 # single row, which satisfies the `unless` parameter of Postgresql_psql.
206 # If this number is not zero then there is at least one table for which
207 # the role does not have the specified privilege, making it necessary to
208 # execute the GRANT statement.
209 $custom_unless = "SELECT 1 FROM (
211 FROM information_schema.tables
212 WHERE table_schema='${schema}'
215 FROM information_schema.role_table_grants
216 WHERE grantee='${role}'
217 AND table_schema='${schema}'
218 AND privilege_type='${custom_privilege}'
220 HAVING count(P.table_name) = 0"
223 $unless_privilege = $_privilege ? {
225 'ALL PRIVILEGES' => 'USAGE',
226 default => $_privilege,
228 validate_re($unless_privilege, [ '^$','^CREATE$','^USAGE$','^ALL$','^ALL PRIVILEGES$' ])
229 $unless_function = 'has_language_privilege'
231 $onlyif_function = $onlyif_exists ? {
232 true => 'language_exists',
238 fail("Missing privilege validation for object type ${_object_type}")
242 # This is used to give grant to "schemaname"."tablename"
243 # If you need such grant, use:
244 # postgresql::grant { 'table:foo':
247 # object_type => 'TABLE',
248 # object_name => [$schema, $table],
250 if is_array($_object_name) {
251 $_togrant_object = join($_object_name, '"."')
252 # Never put double quotes into has_*_privilege function
253 $_granted_object = join($_object_name, '.')
255 $_granted_object = $_object_name
256 $_togrant_object = $_object_name
259 $_unless = $unless_function ? {
261 'custom' => $custom_unless,
262 default => "SELECT 1 WHERE ${unless_function}('${role}',
263 '${_granted_object}', '${unless_privilege}')",
266 $_onlyif = $onlyif_function ? {
267 'table_exists' => "SELECT true FROM pg_tables WHERE tablename = '${_togrant_object}'",
268 'language_exists' => "SELECT true from pg_language WHERE lanname = '${_togrant_object}'",
272 $grant_cmd = "GRANT ${_privilege} ON ${_object_type} \"${_togrant_object}\" TO
274 postgresql_psql { "grant:${name}":
275 command => $grant_cmd,
277 port => $port_override,
278 connect_settings => $connect_settings,
279 psql_user => $psql_user,
280 psql_group => $group,
281 psql_path => $psql_path,
284 require => Class['postgresql::server']
287 if($role != undef and defined(Postgresql::Server::Role[$role])) {
288 Postgresql::Server::Role[$role]->Postgresql_psql["grant:${name}"]
291 if($db != undef and defined(Postgresql::Server::Database[$db])) {
292 Postgresql::Server::Database[$db]->Postgresql_psql["grant:${name}"]