On Wednesday, October 19, 2016 at 6:11:33 PM UTC-7, Werner van der Merwe
wrote:
>
> I am having difficulty creating users with read-only access to all tables
> in a schema.
>
>
> I am using the following in the manifest
>
> postgresql::server::grant {<Unique friendly name>:
> db => <dbname>,
> object_name => <schema_name>,
> object_type => 'ALL TABLES IN SCHEMA',
> privilege => 'SELECT',
> role => <username>,
> }
>
>
> In the puppet run, I get the following error:
> Could not evaluate: Error evaluating 'unless' clause, returned pid 6933
> exit 1: 'ERROR: unrecognized privilege type: "SELECT ON ALL TABLES"
>
>
> Yet looking at the manifest:
>
> postgresql::server::grant
> Manages grant-based access privileges for roles. See PostgreSQL documentation
> for grant for more information.
>
> db - Specifies the database to which you are granting access.
> object_type - Specifies the type of object to which you are granting
> privileges. Valid options: DATABASE, SCHEMA, SEQUENCE, ALL SEQUENCES IN
> SCHEMA, TABLE or ALL TABLES IN SCHEMA.
> object_name - Specifies name of object_type to which to grant access.
> port - Port to use when connecting. Default: undef, which generally defaults
> to port 5432 depending on your PostgreSQL packaging.
> privilege - Specifies the privilege to grant. Valid options: ALL, ALL
> PRIVILEGES or object_type dependent string.
> psql_db - Specifies the database to execute the grant against. This should
> not ordinarily be changed from the default, which is postgres.
> psql_user - Sets the OS user to run psql. Default: the default user for the
> module, usually postgres.
> role - Specifies the role or user whom you are granting access to.
>
>
> Hunting down the 'privilege type':
> privilege_type='${custom_privilege}'
>
>
> $custom_privilege = $_privilege ? {
> 'ALL' => 'INSERT',
> 'ALL PRIVILEGES' => 'INSERT',
> default => $_privilege,
> }
>
>
> And finally
> validate_string($_privilege,'SELECT','INSERT','UPDATE','DELETE','TRUNCATE','REFERENCES','TRIGGER','ALL','ALL
>
> PRIVILEGES')
>
>
> Any ideas?
>
Hi Werner,
Unfortunately, the world of PostgreSQL permissions is rather more complex
than the model exposed by the module. I recommend dropping down to psql to
make sure you get exactly what you want, when doing any non-trivial
PostgreSQL permissions management. It may look something like this (hacked
up, untested code):
psql {"${unique_name}/tables":
db => $database,
command => "GRANT SELECT
ON ALL TABLES IN SCHEMA \"${schema}\"
TO \"${table_reader}\"",
onlyif => "SELECT *
FROM pg_tables
WHERE schemaname='${schema}'
AND has_table_privilege('${table_reader}', schemaname ||
'.' || tablename, 'SELECT')=false
)",
}
You'll probably need to do the same thing for sequences, and maybe for
functions if you're using them.
The other important part is to make sure permissions are set correctly for
newly created schema objects. Puppet will of course catch anything set
incorrectly and fix it, but it's nice to have the database configuration
completely correct so you don't have to rely on that. Here's a (hacked up)
define type I've used for that:
# Grant read permissions to table_reader by default, for new tables created
by
# table_creator.
define default_read_grant(
String $database,
String $schema,
String $table_creator,
String $table_reader,
) {
psql {"${title}/sql":
db => $database,
command => "ALTER DEFAULT PRIVILEGES
FOR USER \"${table_creator}\"
IN SCHEMA \"${schema}\"
GRANT SELECT ON TABLES
TO \"${table_reader}\"",
unless => "SELECT *
FROM pg_default_acl acl
JOIN pg_namespace ns ON acl.defaclnamespace=ns.oid
WHERE acl.defaclacl::text ~
'.*\\\\\"${table_reader}\\\\\"=r/\\\\\"${table_creator}\\\\\".*'
AND ns.nspname = '${schema}'",
}
}
I definitely recommend reading up on the details of the default permissions
and poking around in the various acl tables if you haven't already. The
format of the pg_default_acl table in particular is a little idiosyncratic.
The above is what worked for my application, but it may not be appropriate
for yours.
Hope this helps!
- Russell Mull
--
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/puppet-users/3c0aacf1-9e90-4cd1-8e0e-7cb14e73af95%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
