Can't give the proper permission to a user in Postgresql with Ansible to be able to create a EF Core migration - Stack O

admin管理员组

文章数量:1389379

I have an ASP.NET Core app that utilizes Entity Framework Core in order to use a Postgresql database. I want to use two Postgres users, one for migration and one for normal CRUD usage on my tables. Now I want to deploy this app using Ansible, I installed Postgres and cloned my app and when I got to do the migration, I got an error

permission denied for schema public

I tried to give more privileges thinking that I just didn't give enough, and it didn't work. Now my migration user has all privileges on my database, all privileges on the public schema and SELECT,INSERT,UPDATE,DELETE privileges on the tables inside the public schema (I'm not sure if it does anything since I don't have tables yet) and the default privileges for the tables are SELECT,INSERT,UPDATE,DELETE. I tried looking for privileges with psql but all I could find was the database privileges with \l:

List of databases

   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |       Access privileges        
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+--------------------------------
 my_db     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =Tc/postgres                  +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres         +
           |          |          |                 |             |             |            |           | user_migration=CTc/postgres

Here's how I set my privileges in my playbook:

    - name: Grant all privileges on database to "{{ db_migration_user }}"
      community.postgresql.postgresql_privs:
        db: "{{ db_name }}"
        privs: ALL
        type: database
        role: "{{ db_migration_user }}"
      become: yes
      become_user: postgres

    - name: Grant all privileges on schema to "{{ db_migration_user }}"
      community.postgresql.postgresql_privs:
        db: "{{ db_name }}"
        privs: ALL
        type: schema
        obj: public
        role: "{{ db_migration_user }}"
      become: yes
      become_user: postgres

    - name: Grant access privileges to schema to "{{ db_user }}"
      community.postgresql.postgresql_privs:
        db: "{{ db_name }}"
        privs: USAGE
        type: schema
        obj: public
        role: "{{ db_user }}"
      become: yes
      become_user: postgres

    - name: Grant CRUD privileges
      community.postgresql.postgresql_privs:
        db: "{{ db_name }}"
        privs: SELECT,INSERT,UPDATE,DELETE
        type: table
        obj: ALL_IN_SCHEMA
        schema: public
        role: "{{ db_user }},{{ db_migration_user }}"
      become: yes
      become_user: postgres

    - name: Alter default priivileges on tables
      community.postgresql.postgresql_privs:
        db: "{{ db_name }}"
        objs: TABLES
        privs: SELECT,INSERT,UPDATE,DELETE
        type: default_privs
        role: "{{ db_user }},{{ db_migration_user }}"
      become: yes
      become_user: postgres

Edit: After running \dn+ I got:

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | user_migration=UC/pg_database_owner   +| 
        |                   | user_normal=U/pg_database_owner        | 
(1 row)

本文标签：