How to create a PostgreSQL Read-Only user for Satellite 6

Environment

• Red Hat Satellite 6

Issue

• How do I create a PostgreSQL Read-Only user so that the DBA can view tables/schemas, etc?

Resolution

Unsupported Procedure: The following information has been provided by Red Hat, but is outside the scope of the posted Service Level Agreements and supported procedures. The information is provided as-is and any configuration settings or installed applications made from the information in this article could make the Operating System unsupported by Red Hat Global Support Services. The intent of this article is to provide information to accomplish the system's needs.

Follow the commands and steps to create a read-only postgres user.

1. Connect to PostgreSQL DB with the following command.

[root@satellite ~]# su - postgres
Last login: Fri Nov  9 12:46:24 IST 2018 on pts/0

2. Create user/password and grant permission to foreman db:

bash-4.2$ psql -c "CREATE ROLE testuser WITH LOGIN ENCRYPTED PASSWORD 'redhat';" -d foreman
CREATE ROLE

3. Grant connect to database:

bash-4.2$ psql -c "GRANT CONNECT ON DATABASE foreman TO testuser;"
GRANT

4. Grant usage on public schema:

bash-4.2$ psql -c "GRANT USAGE ON SCHEMA public TO testuser;"
GRANT

5. Create file to grant additional privileges:

bash-4.2$ psql -c "SELECT 'GRANT SELECT ON ' || relname || ' TO testuser;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v');" --pset=tuples_only=on foreman > grant.sql

6. Use file to grant privileges:

bash-4.2$ psql -f grant.sql foreman

7. Connect with newly created user:

bash-4.2$ psql --host=localhost --dbname=foreman --username=testuser
Password for user testuser: 
psql (9.2.24)

foreman=> select * from architectures;
 id |  name  |         created_at         |         updated_at         
----+--------+----------------------------+----------------------------
  1 | x86_64 | 2018-08-07 16:18:36.393141 | 2018-08-07 16:18:36.393141
  2 | i386   | 2018-08-07 16:18:36.399202 | 2018-08-07 16:18:36.399202
(2 rows)

foreman=> SELECT * FROM user_roles;
 id | owner_id | role_id | owner_type 
----+----------+---------+------------
  1 |        1 |      14 | User
  2 |        2 |      14 | User
  3 |        3 |      14 | User
  4 |        4 |      14 | User
  7 |        7 |      14 | User
  8 |        8 |      14 | User
(6 rows)

Diagnostic Steps

• Check whether the user have read-only access or not.

Try to add a value:
foreman=> INSERT INTO user_roles(id, owner_id, role_id, owner_type) VALUES (17, 12, 9, User);
ERROR:  permission denied for relation user_roles

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.