Providing SELECT rights to a PG DB

[Werner van der Merwe]

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?

[Russell Mull]

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