PostgreSQL - Tipps und Tricks - Versionsgeschichte

Stefan: /* Create "Read-only User" */

2020-11-09T08:34:49Z

Create "Read-only User"

← Nächstältere Version		Version vom 9. November 2020, 08:34 Uhr
Zeile 29:		Zeile 29:
	=== Create "Read-only User" ===		=== Create "Read-only User" ===

	~~Show~~ 'SELECT' ~~grants~~:		Generate 'SELECT' grant commands from a catalog query:

	SELECT 'GRANT SELECT ON ' \|\| relname \|\| ' TO xxx;'		SELECT 'GRANT SELECT ON ' \|\| relname \|\| ' TO xxx;'

Stefan: /* Create "Read-only User" */

2020-11-09T07:50:36Z

Create "Read-only User"

← Nächstältere Version		Version vom 9. November 2020, 07:50 Uhr
Zeile 47:		Zeile 47:
	REVOKE CREATE ON SCHEMA public FROM public;		REVOKE CREATE ON SCHEMA public FROM public;
	REVOKE USAGE ON SCHEMA public FROM public;		REVOKE USAGE ON SCHEMA public FROM public;

	# Create read-only user		# Create read-only user
	CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';		CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
	GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;		GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
	GRANT USAGE ON SCHEMA public TO readonlyuser; -- omit USAGE if needed		GRANT USAGE ON SCHEMA public TO readonlyuser; -- omit USAGE if needed

	# Restore USAGE/CREATE to default user		# Restore USAGE/CREATE to default user
	GRANT USAGE ON SCHEMA public TO public;		GRANT USAGE ON SCHEMA public TO public;

Stefan: /* Create "Read-only User" */

2020-11-09T07:50:23Z

Create "Read-only User"

← Nächstältere Version		Version vom 9. November 2020, 07:50 Uhr
Zeile 29:		Zeile 29:
	=== Create "Read-only User" ===		=== Create "Read-only User" ===

	~~From: [http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql]~~		Show 'SELECT' grants:

	~~If you only grant CONNECT to a database, the user can connect but has no other privileges.~~
	~~In PostgreSQL 8.x you have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually. So something like:~~

	~~GRANT CONNECT ON DATABASE mydb TO xxx;~~
	~~-- This assumes you're actually connected to mydb..~~
	~~GRANT USAGE ON SCHEMA public TO xxx;~~
	~~GRANT SELECT ON mytable TO xxx;~~

	~~Granting~~ 'SELECT' ~~to each table/view individually can be a bore. Something like this can help~~:

	SELECT 'GRANT SELECT ON ' \|\| relname \|\| ' TO xxx;'		SELECT 'GRANT SELECT ON ' \|\| relname \|\| ' TO xxx;'
Zeile 45:		Zeile 35:
	WHERE nspname = 'public' AND relkind IN ('r', 'v')		WHERE nspname = 'public' AND relkind IN ('r', 'v')

	~~This should output~~ the ~~relevant~~ GRANT ~~commands to~~ GRANT ~~SELECT on all~~ tables and ~~views in public~~, ~~for copy~~-~~n-paste~~.		Good to know:
			* If you only grant CONNECT to a database, the user can connect but has no other privileges.
	~~PostgreSQL 9:~~		* PG makes a distinction between GRANT SELECT and GRANT USAGE for security reasons (poking metadata about tables).
			* PG knows a schema PUBLIC and also a 'default role' (which define 'DEFAULT PRIVILEGES') also called PUBLIC.
	In PostgreSQL 9.0 ~~its easier. You~~ still need to grant USAGE permissions on schemas, but you can grant permissions on all tables, views etc. in the schema using a single command rather than having to generate them:		* PG sets (and 'inherits') security 'flags' at the time of the commands (e.g. GRANT). This means, 1. when a 'parent-flag' changes this change is not propagated ('inherited') and 2. A "GRANT SELECT ON ALL TABLES IN SCHEMA public TO ourtable;" only affects tables that have already been created.
			* Since PostgreSQL 9.0 you still need to grant USAGE permissions on schemas, but you can grant permissions on all tables, views etc. in the schema using a single command rather than having to generate them:
	~~GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;~~

	~~This only affects~~ tables ~~that have already been created, so it's the equivalent of the generated commands above. More powerfully, you can automatically have default roles assigned to new objects in future:~~		Read-Only user (with variants):

			# Prevent default users from creating tables
			REVOKE CREATE ON SCHEMA public FROM public;
			REVOKE USAGE ON SCHEMA public FROM public;

	~~ALTER DEFAULT PRIVILEGES~~ IN SCHEMA public		# Create read-only user
	GRANT ~~SELECT~~ ON ~~TABLES~~ TO ~~osmgis~~;		CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
			GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
			GRANT USAGE ON SCHEMA public TO readonlyuser; -- omit USAGE if needed

	~~Note that by~~ default ~~this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing~~ user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.		# Restore USAGE/CREATE to default user
			GRANT USAGE ON SCHEMA public TO public;
			GRANT CREATE ON SCHEMA public TO public;

			(Source: note that not all answers are uptodate or correct: https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql )

	=== Daten laden/importieren ===		=== Daten laden/importieren ===

Stefan: /* Foreign Data Wrappers */

2019-11-09T19:53:43Z

Foreign Data Wrappers

← Nächstältere Version		Version vom 9. November 2019, 19:53 Uhr
Zeile 83:		Zeile 83:
	* SQL/MED [http://wiki.postgresql.org/wiki/SQL/MED#Foreign_data_wrappers]		* SQL/MED [http://wiki.postgresql.org/wiki/SQL/MED#Foreign_data_wrappers]

	Linking open and remote (PostgreSQL) read-only databases easily! Uses postgres_fdw the SQL/MED implementation (Foreign Data Wrapper).		<b>Linking open and remote (PostgreSQL) read-only databases easily! Uses postgres_fdw the SQL/MED implementation (Foreign Data Wrapper).</b>

	Caveats:		Caveats:

Stefan: /* Foreign Data Wrappers */

2019-11-09T19:51:24Z

Foreign Data Wrappers

@@ Zeile 77: / Zeile 77: @@
 === Foreign Data Wrappers ===
-Ab Version 9.1 werden Foreign Data Wrappers (FDW) und ein Teil des SQL/MED-Standards unterstützt.
+Ab Version 9.1 werden Foreign Data Wrappers (FDW) und ein Teil des SQL/MED-Standards unterstützt. file_fdw ist eine offizielle Extension (contrib module): [http://www.postgresql.org/docs/9.1/static/file-fdw.html].
 Siehe auch PostgreSQL Wiki:
 * Foreign_data_wrappers [http://wiki.postgresql.org/wiki/Foreign_data_wrappers#file_fdw]
 * SQL/MED [http://wiki.postgresql.org/wiki/SQL/MED#Foreign_data_wrappers]
 == PostgreSQL SQL ==

Stefan: /* Weitere Tipps... */ Data Generator

2018-03-05T11:04:06Z

Weitere Tipps...: Data Generator

← Nächstältere Version		Version vom 5. März 2018, 11:04 Uhr
Zeile 195:		Zeile 195:

	== Weitere Tipps... ==		== Weitere Tipps... ==

			=== Data Generator ===

			Schritt 1: Demo / Tests:
			select
			g as no,
			random() as rand_01_float,
			(random() * 2147483647)::int as rand_uint,
			trunc(random() * 6 + 1) as range_1_6_int, -- >=1 and <=6
			md5(random()::text) as char32
			from generate_series(1,20) g
			order by 4;

			Generate series directly with dates. No need to use ints or timestamps:
			select
			datetime::date -- only date part of datetime
			from generate_series('2018-03-01'::date,'2018-03-05'::date,'1 day'::interval) datetime;

			Step 2: Generate table which fits' to schema:
			create table foo ( ... )

			Generate data and insert it into table:
			insert into foo (id, remarks)
			values (generate_series(1,3), 'b '\|\|md5(random()::text));

			select * from foo order by 1;

	=== Convert text to number ===		=== Convert text to number ===

Stefan: /* PostgreSQL SQL */

2017-02-11T10:38:04Z

PostgreSQL SQL

← Nächstältere Version		Version vom 11. Februar 2017, 10:38 Uhr
Zeile 94:		Zeile 94:
	Finding duplicate values in one or more columns:		Finding duplicate values in one or more columns:
	<pre>		<pre>
	SELECT * FROM (		SELECT *
	~~SELECT~~ row_number() ~~over~~ ( ~~partition~~ BY email),		FROM (
	name,		SELECT
	email		row_number() OVER (PARTITION BY email),
	FROM dedup ) t		name,
			email
			FROM dedup) t
	WHERE t.row_number < 2;		WHERE t.row_number < 2;
	</pre>		</pre>
Zeile 104:		Zeile 106:
	Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):		Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):
	SELECT *		SELECT *
	FROM sometable		FROM sometable
	WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION'		WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION'

	Problem mit Datenfelder (for < 8.3):		Problem mit Datenfelder (for < 8.3):

Stefan am 10. Februar 2017 um 23:38 Uhr

2017-02-10T23:38:02Z

@@ Zeile 91: / Zeile 91: @@
 Version:
    select version();
 Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):
@@ Zeile 98: / Zeile 108: @@
 Problem mit Datenfelder (for < 8.3):
      EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN
      AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...

Stefan: /* PostgreSQL SQL */

2014-01-19T23:41:20Z

PostgreSQL SQL

@@ Zeile 107: / Zeile 107: @@
      EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN
      AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...
 == hstore ==

Stefan: /* SQL MERGE/UPSERT Command */

2013-05-21T14:54:20Z

SQL MERGE/UPSERT Command

← Nächstältere Version		Version vom 21. Mai 2013, 14:54 Uhr
Zeile 196:		Zeile 196:
	First line does not check datetype, so I'd prefer one of the other two.		First line does not check datetype, so I'd prefer one of the other two.

	=== SQL MERGE~~/UPSERT~~ Command ===		=== SQL MERGE Command ===

	The [http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE PostgreSQL documentation] provides a solution for an MERGE/INSERT function. 'UPSERT' is the PostgreSQL slang for ANSI SQL 'Merge' and means UPDATE-SELECT. But this solution proposal ~~hast~~ concurrency issues which have been avoided by this proposal (by Depesz's Blog [http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/]).		The [http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE PostgreSQL documentation] provides a solution for an MERGE/INSERT function. 'UPSERT' is the PostgreSQL slang for ANSI SQL 'Merge' and means UPDATE-SELECT. But this solution proposal has concurrency issues which have been avoided by this proposal (by Depesz's Blog [http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/]). See also http://wiki.postgresql.org/wiki/SQL_MERGE

	PL/pgSQL-Funktion für ein MERGE/UPSERT mit einem Attribut ('in_whatever'):		PL/pgSQL-Funktion für ein MERGE/UPSERT mit einem Attribut ('in_whatever'):