Possibility of adding privileges to objects

[abyss-ms]

issue #737

[vitabaks]

@SDV109 Please check.

[SDV109]

@abyss-ms Hi! I am addressing you as the creator of the module, I have problems in order to grant all privileges to the user. According to the documentation of the module you are using, the command should be like this:

- name: GRANT ALL PRIVILEGES ON DATABASE library TO librarian
  community.postgresql.postgresql_privs:
    db: library
    privs: ALL
    type: database
    role: librarian

The command worked correctly, if filling in extra fields in vars/main will be an error, but after executing the playbook, the user did not get any rights

vars/main:
postgresql_privs:
  - { role: "test", privs: "SELECT,INSERT,UPDATE", type: "table", db: "test2", objs: "test", schema: "public" }
  - { role: "test", privs: "CREATE", type: "database", db: "test2", objs: "", schema: "" }
  - { role: "test", privs: "ALL", type: "database", db: "test3", objs: "", schema: "" }

playbook log:
TASK [postgresql-privs : Grant/revoke privileges on objects] *********************************************************************************************************************************
ok: [192.168.62.192] => (item={'role': 'test', 'privs': 'SELECT,INSERT,UPDATE', 'type': 'table', 'db': 'test2', 'objs': 'test', 'schema': 'public'})
ok: [192.168.62.192] => (item={'role': 'test', 'privs': 'CREATE', 'type': 'database', 'db': 'test2', 'objs': '', 'schema': ''})
changed: [192.168.62.192] => (item={'role': 'test', 'privs': 'ALL', 'type': 'database', 'db': 'test3', 'objs': '', 'schema': ''})

postgres=# \c test3 test
The password of the test user:
psql (16.2 (Debian 16.2-1.pgdg110+2), server 15.7 (Debian 15.7-1.pgdg110+1))
You are connected to the "test3" database as a "test" user.
test3=> select * from test;
ERROR:  permission denied for table test
test3=> SELECT *
  FROM information_schema.role_table_grants
 WHERE grantee = 'test';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 строк)

Also, I want to note that the assignment of the create right does not work either. I must say right away that when we select the database type, we do not need to specify the schema, otherwise there will be an error:

failed: [192.168.62.192] (item={'role': 'test', 'privs': 'CREATE', 'type': 'database', 'db': 'test2', 'objs': '', 'schema': 'public'}) => {"ansible_loop_var": "item", "changed": false, "item": {"db": "test2", "objs": "", "privs": "CREATE", "role": "test", "schema": "public", "type": "database"}, "msg": "Argument \"schema\" is not allowed for type \"database\"."}

[SDV109]

As for select/insert/update, there are no problems

test2=# \c test2 test
Test user password:
psql (16.2 (Debian 16.2-1.pgdg110+2), server 15.7 (Debian 15.7-1.pgdg110+1))
You are connected to the "test2" database as a "test" user.
test2=> select * from test;
ERROR:  permission denied for table test
test2=> select * from test;
 id |                     about                      | age
----+------------------------------------------------+-----
  1 | A cool player. A cool player. My number is 876 |  10
  2 | A cool player. A cool player. My number is 689 |  11
  3 | A cool player. A cool player. My number is 536 |  23
  4 | A cool player. A cool player. My number is 704 |  22
  5 | A cool player. A cool player. My number is 648 |  28
  6 | A cool player. A cool player. My number is 136 |  27
  7 | A cool player. A cool player. My number is 300 |  12
  8 | A cool player. A cool player. My number is 106 |  29
  9 | A cool player. A cool player. My number is 899 |  21
 10 | A cool player. A cool player. My number is 209 |  23
(10 lines)

test2=> update test set age='26' where id='4';
UPDATE 1
test2=> select * from test;
 id |                     about                      | age
----+------------------------------------------------+-----
  1 | A cool player. A cool player. My number is 876 |  10
  2 | A cool player. A cool player. My number is 689 |  11
  3 | A cool player. A cool player. My number is 536 |  23
  5 | A cool player. A cool player. My number is 648 |  28
  6 | A cool player. A cool player. My number is 136 |  27
  7 | A cool player. A cool player. My number is 300 |  12
  8 | A cool player. A cool player. My number is 106 |  29
  9 | A cool player. A cool player. My number is 899 |  21
 10 | A cool player. A cool player. My number is 209 |  23
  4 | A cool player. A cool player. My number is 704 |  26
(10 lines)

[abyss-ms]

Hi @SDV109 thank you for your check.

Also, I want to note that the assignment of the create right does not work either. I must say right away that when we select the database type, we do not need to specify the schema, otherwise there will be an error

Yes, when you specify the type as "database," you cannot provide a schema. Simply omit it. It should be like this: - { role: "test", privs: "CREATE", type: "database", db: "test2", objs: "test2" } schema is optional in this role.

In the following case: - { role: "test", privs: "CREATE", type: "database", db: "test2", objs: "", schema: "" } You might end up with something like this (ignoring the fact that database type cannot be used with 'schema'): GRANT ALL PRIVILEGES ON DATABASE '' TO test, since you haven't provided a valid database (objs).

You guys want me to add additional checks/asserts that for example when someone provide type database and provide schema then should get error or info that it is now allowed?

[vitabaks]

Let's just provide more examples that may be needed most often.

[SDV109]

@abyss-ms Hi, сhange the 2nd example, from grant create and al from type database to type schema. In PostgreSQL 15, when assigning rights to the database, an error of schema unavailability will be displayed. Here are the correct options that I tested:

- { role: "test", privs: "CREATE", type: "schema", db: "test3", objs: "public", schema: "" }
- { role: "test", privs: "ALL", type: "schema", db: "diamond", objs: "public", schema: "" }

In the example I gave, the create and ALL rights are assigned in the public scheme only for those databases to which the connection is specified, the privileges granted in other databases will not work.

[SDV109]

@abyss-ms Example, I created a new gnome user and gave him create rights in the public schema of the gnome database:

changed: [192.168.62.192] => (item={'role': 'gnome', 'privs': 'CREATE', 'type': 'schema', 'db': 'gnome', 'objs': 'public'})

After that, I connect to the gnome database under it and can create a table, but when connecting to other databases, this does not work

You are connected to the "gnome" database as a "gnome" user.
gnome=> CREATE TABLE test (id INT, about TEXT, age INT);
CREATE TABLE
You are connected to the "testdb" database as a "gnome" user.
testdb=> CREATE TABLE test (id INT, about TEXT, age INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test (id INT, about TEXT, age INT);

I think the right solution would be to give exactly working examples in vars/main. But if I grant the create privilege to the database, we will see an error

changed: [192.168.62.192] => (item={'role': 'gnome', 'privs': 'CREATE', 'type': 'database', 'db': 'testdb', 'objs': 'testdb'})
You are connected to the "testdb" database as a "gnome" user.
testdb=> CREATE TABLE test (id INT, about TEXT, age INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test (id INT, about TEXT, age INT);

[SDV109]

@abyss-ms And finally, do you have any ideas how to grant the following rights: "GRANT SELECT ON ALL TABLES IN SCHEMA public TO dba;" According to the ansible documentation, there should be a similar scheme:

- name: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA common TO caller
  community.postgresql.postgresql_privs:
    type: function
    state: present
    privs: EXECUTE
    roles: caller
    objs: ALL_IN_SCHEMA
    schema: common

Here is my vars/main, but I get the following error:

vars/main:
  - { role: "read", privs: "SELECT", type: "table", objs: "ALL_IN_SCHEMA", schema: "public" }
  
 log_ansible:
 failed: [192.168.62.192] (item={'role': 'read', 'privs': 'SELECT', 'type': 'table', 'objs': 'ALL_IN_SCHEMA', 'schema': 'public'}) => {"ansible_loop_var": "item", "changed": false, "item": {"objs": "ALL_IN_SCHEMA", "privs": "SELECT", "role": "read", "schema": "public", "type": "table"}, "msg": "missing required arguments: database"}

[vitabaks]

"missing required arguments: database"

it looks like you should always specify the database name

[vitabaks]

Doc: https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_privs_module.html#ansible-collections-community-postgresql-postgresql-privs-module

[SDV109]

@vitabaks Yes, I understand, but here I cited an error in the ansible documentation, in it, as I gave an example from the off documentation, db should not be passed, although this probably only works with functions, and when it comes to tables, you need to grant select rights at the database level.

But I ran into another problem, it turns out that granting rights to select in all tables in the public schema is not enough for the user to make a select in all databases.

So, I think it's worth describing in vars/main before the postgresql_privs: [] block that if a user wants to grant select/insert/update rights for a specific table, then this must be done at the database level and is assigned only to one database:

  - { role: "test", privs: "SELECT,INSERT,UPDATE", type: "table", db: "test2", objs: "test" }  # grant SELECT, INSERT, UPDATE on a table to role test

And if the user wants to grant rights to the entire database or the ability to create tables, in this case it is necessary to work at the schema level indicating the schema where he wants to make changes.:

  - { role: "test", privs: "ALL", type: "schema", db: "diamond", objs: "public", schema: "" } # grant ALL PRIVILEGES  on database to role test
  - { role: "test", privs: "CREATE", type: "schema", db: "gnome", objs: "public" } # grant CREATE   on database to role test

@abyss-ms FYI

[abyss-ms]

Hi @SDV109 so from your tests something like this (grant ALL on a test-db database to role test-user): - { role: "test-user", privs: "ALL", type: "database", db: "test-db", objs: "test-db" } doesn't work?