I would like to use an external DB client to run some queries against the analytics tables.
What steps should I use on the PG host tp create a read-only PG user so I can query the analytics data externally?
Since I can log into the PG server as root and become the PG user (same user that running the postmaster process I assume I should be able to issue some sequence of CREATE USER, GRANT PRIVILEGES commands to create the user.
Since you have access to the PG server, you should as superuser (apigee i would think) be able to do the create user and grant privileges steps.
Yes, I did that. Here;s what I did:
1. create the DB user
[root@myedge ~]# sudo su - apigee
[apigee@myedge ~]$ which createuser
/usr/bin/createuser
[apigee@myedge ~]$ createuser myuser -P
Enter password for new role:
Enter it again:
2. Grante the DB user Perms
[apigee@myedge ~]$ /opt/apigee4/bin/psql.sh
psql (9.3.10)
Type "help" for help.
apigee=# GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO myuser;
GRANT
apigee=# GRANT USAGE ON SCHEMA analytics TO myuser;
GRANT
great! I assume this user is now able to perform the appropriate queries against the schema?