Operations grimoire/PostgreSQL: Difference between revisions
(→Howto) |
(→Grant privileges to an user: GRANT ALL PRIVILEGES ON ALL TABLES through Salt) |
||
Line 40: | Line 40: | ||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO acme; | GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO acme; | ||
This set of privileges can be handled directly by Salt in cluster privileges definition: | |||
privileges: | |||
- database: <database name> | |||
scope: table | |||
schema: public | |||
tables: | |||
- ALL | |||
privileges: | |||
- SELECT | |||
See {{D|3252}} for an example. | |||
Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them: | Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them: |
Revision as of 20:14, 17 December 2023
PostgreSQL is available as a standalone role and can also be enabled on devserver.
Applications on the Docker PaaS can use our PostgreSQL infrastructure (Airflow, datasources) or have their own container (Sentry, with custom wal2json extension). This resource documents our own db- servers.
Howto
Open a console
Use peer authentication from the postgres user: sudo -u postgres psql
Create a new database or user
It's a simple two steps process:
- Edit the relevant pillar file, for example pillar/dbserver/cluster-A.sls in rOPS
- Deploy the change:
salt db-A-001 state.apply roles/dbserver-pgsql
You generally need:
- an user block in dbserver_postgresql.users
- block title is the username
- a password needs to be set in Flow, key is under ops/secrets/
- a database block
- a connection triplet db, user, ips (use /32 for an unique IP)
By default, external connections are NOT enabled for any user and database, an entry for pg_hba.conf MUST be added in connections part.
Servers don't have a public ICANN IP, so you can only connect from other Nasqueron servers.
Grant privileges to an user
Some applications don't set privileges, even if they own the database. That's for example the case of Orbeon Forms DDL.
To determine if acme has already the rights on the database:
SELECT grantor, grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'acme';
If not:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO acme;
This set of privileges can be handled directly by Salt in cluster privileges definition:
privileges: - database: <database name> scope: table schema: public tables: - ALL privileges: - SELECT
See D3252 for an example.
Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them:
SELECT 'GRANT USAGE ON SEQUENCE ' || sequence_schema || '.' || sequence_name || ' TO acme;' FROM information_schema.sequences WHERE sequence_schema NOT LIKE 'pg_%' AND sequence_schema != 'information_schema';
Clusters
Letters can be discontinuous: for example, B will be a MySQL cluster.
Cluster A
A is the general cluster, for Nasqueron services. It currently has one server, db-A-001.
Database | Managed by | Description |
---|---|---|
airflow | Nasqueron Ops | Workflows runner, used by datasources |
fantoir | Datasources | PostgreSQL version of FANTOIR for geocoding |