How to create user in postgres using ansible having only read only (SELECT) access

Question

I am creating postgres user using below code. and it is working fine.

- name: create database user
  become_user: postgres
  postgresql_user: db={{ db_name }} name=username password=userpassword priv=CONNECT encrypted=yes state=present

However i would like this user to give only readonly access. I want to grant only select operation on tables in public schema. I can do this using below queries

CREATE USER rouser WITH PASSWORD 'pass';
GRANT CONNECT ON DATABASE mydb TO rouser;
GRANT USAGE ON SCHEMA public TO rouser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rouser;

I am able to create such user using

- name: modify user role 
  become_user: postgres 
  postgresql_privs: 
   db: mydb 
   role: qarouser 
   objs: ALL_IN_SCHEMA 
   privs: SELECT

However when i create a new table using different user in the same db. this user do not have select access to such table created by different user, How can i fix this?

Since you are already using the module postgresql_user module you may also have a look into Community.Postgresql. — U880D
– U880D, Commented Aug 11, 2022 at 15:46
@kildisismail I am able to give select access using - name: modify user role become_user: postgres postgresql_privs: db: mydb role: qarouser objs: ALL_IN_SCHEMA privs: SELECT but when i add new table, this user do not get permission to select from new table — Shiv
– Shiv, Commented Aug 12, 2022 at 4:10
correction to above comment "when new table is added by different user" — Shiv
– Shiv, Commented Aug 12, 2022 at 4:21

Marlon · Accepted Answer · 2023-01-13 14:58:35Z

You already have the modify user role task which gives access to current objects but you are missing ALTER DEFAULT PRIVS... part of your statements. You can get more samples and much better explanation here Specifically the sample for adding SELECT and USAGE (modified for your use):

# Available since version 2.7
# Objs must be set, ALL_DEFAULT to TABLES/SEQUENCES/TYPES/FUNCTIONS
# ALL_DEFAULT works only with privs=ALL
# For specific
- name: ALTER DEFAULT PRIVILEGES ON DATABASE mydb TO qarouser, step 1
  community.postgresql.postgresql_privs:
    db: mydb
    objs: TABLES
    privs: SELECT
    type: default_privs
    role: qarouser

- name: ALTER DEFAULT PRIVILEGES ON DATABASE mydb TO qarouser, step 2
  community.postgresql.postgresql_privs:
    db: mydb
    objs: TYPES
    privs: USAGE
    type: default_privs
    role: qarouser

I just had to deal with it in my own playbook and found your question while looking for it. Full disclosure: I'm not a DBA nor do I play one on TV. I have not stayed in any fancy hotel that somehow makes me qualified.

Collectives™ on Stack Overflow

How to create user in postgres using ansible having only read only (SELECT) access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related