DEV Community: Victoria Santana

giving and taking permissions on postgres

Victoria Santana — Mon, 15 Feb 2021 21:35:50 +0000

Intro

I'm writing a short and handy guide to anyone in a jr position - especially if you are a junior sysadmin. I often find myself having to search for a specific command to do something basic. I'm fully aware there are tons of other tutorials/posts, but I hope you find this one clear and helpful.

Privileges

Postgres offers 12 types of privileges, they are

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE

Commands

This are some examples based on what I use most in my day-to-day as an infrastructure analyst.

To give all privileges in a database for some user
GRANT ALL PRIVILEGES IN your_database TO your_user;
To let an user login

GRANT CONNECT ON DATABASE your_database TO your_user;

To become a superuser

ALTER USER your_user WITH SUPERUSER;

To let a user create databases

ALTER USER your_user CREATEDB;

Giving permissions in a schema

GRANT USAGE ON SCHEMA schema_name TO your_user;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA schema_name TO username;

Giving permissions in a specific table

GRANT { SELECT | INSERT | UPDATE | REFERENCES } ON TABLE table_name TO your_user

This one is for giving permissions in all schemas of a database (there are other ways to do this)
SELECT format('GRANT USAGE ON SCHEMA %I TO readonly_group;', schema_name) FROM information_schema.schemata \gexec
This one gives permissions on all tables in all schemas (again, there are other ways to do this)
SELECT format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO readonly_group;', schema_name) FROM information_schema.schemata \gexec

Taking privileges

First of all I'll show you how to remove privilege from a superuser

ALTER USER username WITH NOSUPERUSER;

The command REVOKE is used with similar syntax as GRANT, only difference is that it revokes privileges.

Examples

Removing user from role/group
REVOKE role FROM user; or ALTER GROUP group DROP USER user;
Removing all privileges from a user

REVOKE ALL PRIVILEGES ON table FROM user;

Conclusion

This is an alive document and I'm open to criticism, i'll probably update this with new findings.

References

Here is a full guide with all PostgreSQL Commands (a.k.a documentation) - https://www.postgresql.org/docs/13/sql-commands.html

How to get grafana password kube-stack-prometheus

Victoria Santana — Tue, 26 Jan 2021 17:07:56 +0000

Intro

The helm repo kube-stack-prometheus (formerly prometheus-operator) comes with a grafana deployment embedded. In order to log in Grafana first you need to get the credentials (admin/admin didn't work for me).

Discovering the password 🔍

The user is admin and the password can be found with the following command kubectl get secret grafana -o jsonpath="{.data.admin-password}" | base64 --decode ; echo

I found the command looking into some github issues and I took me some time, so I hope this is useful to someone else.

Reference

GitHub Thread

creating a local monitoring environment with helm

Victoria Santana — Mon, 25 Jan 2021 20:56:22 +0000

Introduction

In this post, that is actually documentation for myself, I'll guide you thru the deployment of a monitoring environment in your local machine, this can be used for study and prototype. The project will use prometheus to monitor and grafana to plot graphs.

Getting your machine ready

I'm using Ubuntu 20.04.1 LTS as my OS

First you'll need to install a tool to run Kubernetes locally (kind, minikube, k3s or other that you like; Personally I use kind)

The steps are the following

$wget https://github.com/kubernetes-sigs/kind/releases/download/v0.8.1/kind-linux-amd64
$chmod +x kind-linux-amd64
$mv kind-linux-amd64 /usr/local/bin/kind
$kind version

and then install Helm

sudo snap install helm --classic

for other ways of installing helm please refer to Helm Documentation

Kind

Now that we have our tools installed we need to create a kubernetes cluster

kind create cluster --name

If you don't put the flag --name the cluster name will be "kind"

Helm

Helm is a package manager for Kubernetes

To Add the prometheus charts repo use the command:

helm repo add prometheus-community https://prometheus-community.github.io/helm-charts

We'll install two helm charts

kube-prometheus-stack

As per github This chart contains a collection of Kubernetes manifests, Grafana dashboards, and Prometheus rules combined with documentation and scripts to provide easy to operate end-to-end Kubernetes cluster monitoring with Prometheus using the Prometheus Operator.

helm install prometheus-community/kube-prometheus-stack --generate-name

pushgateway

Pushgateway is a tool that pushes metrics that cannot be scraped

helm install prometheus-community/prometheus-pushgateway --generate-name

Testing and Tweaking

Run kubectl get services to see the services running and some information about them. A detail when deploying prometheus localy is to change the type of the following services kube-prometheus-stack-1611-operator kube-prometheus-stack-1611575778-grafana from ClusterIP to NodePort (if you are deploying it in a cloud environment you shall change to LoadBalancer). More information about Kubernetes services can be found here

To edit the services use the following command

kubectl edit svc your-service-goes-here

this command will open a vim type editor and allow you to edit information about the service

Now all you have to do is expose the ports using the port-forward command, the following example allows me to access alert-manager via browser

kubectl port-forward svc/alertmanager-operated 9093

The syntax is kubectl port-forward TYPE/NAME PORT.

And that's all for today 🤓

References

Kube Prometheus Stack
Helm Documentation
Kind Quick Start
Pushgateway
Port Forward
Kubectl cheat sheet