Need to create a postgres user for survey_display to replace webuser

[ryantanaka]

Issue posted in response to #3

• create a survey_display user with appropriate permissions to be used in the config.toml file of survey_display
• update the config.toml file to reflect the change
• test survey_display to ensure the web application is working

[ryantanaka]

Notes:

1. obtain current permission information of webuser so that they can be replicated onto the survey_display account

All tables used by survey_display are owned by sepgroup_nonmsg:

kiosk=> \dt
                 List of relations
 Schema |      Name       | Type  |      Owner      
--------+-----------------+-------+-----------------
 public | building        | table | sepgroup_nonmsg
 public | deployed_url    | table | sepgroup_nonmsg
 public | kiosk_survey    | table | sepgroup_nonmsg
 public | option          | table | sepgroup_nonmsg
 public | project         | table | sepgroup_nonmsg
 public | question        | table | sepgroup_nonmsg
 public | response        | table | sepgroup_nonmsg
 public | survey_info     | table | sepgroup_nonmsg
 public | survey_question | table | sepgroup_nonmsg
 public | user            | table | webuser
 public | users           | table | webuser
(11 rows)

Members of sepgroup_nonmsg as listed in grolist can be viewed by this query:

SELECT *
FROM pg_group
WHERE groname='sepgroup_nonmsg';

To view human readable usernames use:

SELECT usename 
FROM pg_user,
    (SELECT grolist 
    FROM pg_group
    WHERE groname='sepgroup_nonmsg')
    AS groups
    WHERE usesysid = ANY(grolist);            

User information omitted here, but can be viewed by running the queries.

webuser belongs to none of the groups, which means it must have been explicitly granted permissions to the kiosk tables. Additionally, running \du webuser shows that the user belongs to no roles.

To confirm granted privileges, use:

SELECT * 
FROM information_schema.role_table_grants
WHERE grantee='webuser';

kiosk=> select * from information_schema.role_table_grants where grantee='webuser';
     grantor     | grantee | table_catalog | table_schema |   table_name    | privilege_type | is_grantable | with_hierarchy 
-----------------+---------+---------------+--------------+-----------------+----------------+--------------+----------------
 sepgroup_nonmsg | webuser | kiosk         | public       | building        | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | building        | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | building        | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | kiosk_survey    | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | kiosk_survey    | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | kiosk_survey    | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | option          | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | option          | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | option          | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | question        | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | question        | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | question        | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_question | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_question | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_question | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_info     | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_info     | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | survey_info     | UPDATE         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | deployed_url    | INSERT         | NO           | NO
 sepgroup_nonmsg | webuser | kiosk         | public       | deployed_url    | SELECT         | NO           | YES
 sepgroup_nonmsg | webuser | kiosk         | public       | deployed_url    | UPDATE         | NO           | NO
(21 rows)

Another way of viewing access privileges:
\z <tablename>

webuser has been granted permissions to 7 of the 10 tables (not including user). users is owned by webuser which means that there are 2 tables where it hasn't been explicitly granted permissions: response and project

\z response and \z project can confirm this:

kiosk=> \z response
                              Access privileges
 Schema |   Name   | Type  | Access privileges | Column privileges | Policies 
--------+----------+-------+-------------------+-------------------+----------
 public | response | table |                   |                   | 
(1 row)

kiosk=> \z project
                              Access privileges
 Schema |  Name   | Type  | Access privileges | Column privileges | Policies 
--------+---------+-------+-------------------+-------------------+----------
 public | project | table |                   |                   | 
(1 row)

What does this mean?
Taken from the Postgres docs,

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request. Similarly, entries are shown in “Column access privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)

Access Privilege (taken from Postgres docs ) are of the form:

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

\dp shows all the access privileges for a database

The behavior of the database when access privileges are blank is described in the docs:

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the specified request.

webuser is not given permissions to access response and project because the query survey_display executes only reads from survey_question, kiosk_survey, option, question, and deployed_url with the following query:

# This is the primary `inner-join` used to collect the goods.
CMD = """
    SELECT
    "public".kiosk_survey.url,
    "public".kiosk_survey.survey_info_id,
    "public".survey_question.question_id,
    "public".survey_question.question_position,
    "public".question.question_text,
    "public"."option".option_id,
    "public"."option"."text",
    "public".kiosk_survey.deployed_url_id,
    "public".deployed_url.is_kioski
    FROM
    "public".kiosk_survey
    INNER JOIN "public".survey_question ON "public".kiosk_survey.survey_info_id = "public".survey_question.survey_info_id
    INNER JOIN "public".question ON "public".survey_question.question_id = "public".question.question_id
    INNER JOIN "public"."option" ON "public"."option".question_id = "public".question.question_id
    INNER JOIN "public".deployed_url ON "public".kiosk_survey.deployed_url_id = "public".deployed_url.deployed_url_id
    """

webuser is only used to read from the database. var-www is the process that is running survey_display, and therefore is the process that writes responses to a csv. scrape-util is then run as root and inputs the data into the database.

2. create survey_display account with password and permissions

Currently reading into how users, groups, and roles are managed. We don't have any kind of protocol set up as of yet and so the naive way to create the survey_display user would be to just create it and mimic the permissions that were given to webuser by sepgroup_nonmsg, however moving forward it seems like this would not be the appropriate way to do things. Another issue is that sepgroup_nonmsg owns the tables, and therefore I would have to grant survey_display permissions via sepgroup_nonmsg but since we are trying to move away from this one "super role", I'm taking some time to understand roles, users, and groups better.

Right now I'm looking through this guide for a high level understanding.

3. update the config.toml file in the survey_display project

4. test survey_display with the updated account

additional notes

• need to discuss with Eileen protocol for managing users and groups on the database
• proposed method
  • every database has a role ie. dbname_group
  • users that need to access that database become a member of that dbname_group
    • if we do it this way, then when a user types \du, we have a logical idea of a user's access patterns
    • no need to worry about individual specific permissions because that can be confusing to follow
  • a main ERDL group will be a member of all database groups

THIS IS NOT DONE YET