Postgresql - Revision history

T1t0: /* Administration */

2026-10-02T21:44:05Z

Administration

@@ Line 239: / Line 239: @@
 postgres@host:~$ psql
 postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser_pass' SUPERUSER;
 </pre>
 === Reserve connections to specific users ===
 <pre>
 postgres@host:~$ psql
-postgres=# ALTER SYSTEM SET reserved_connections = 3;
+postgres=# ALTER SYSTEM SET reserved_connections = 5;
 postgres=# GRANT pg_use_reserved_connections TO haproxy;
 postgres=# \q

T1t0 at 21:31, 2 August 2026

2026-08-02T21:31:14Z

Show changes

T1t0: /* Administration */

2026-08-02T21:23:14Z

Administration

← Older revision		Revision as of 21:23, 2 August 2026
Line 271:		Line 271:
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# CREATE ROLE db_user LOGIN PASSWORD 'db_pass';		postgres=# CREATE ROLE db_user LOGIN PASSWORD 'db_pass';
			postgres=# \q
	</pre>		</pre>

Line 277:		Line 278:
	<pre>		<pre>
	root@host:~# su - postgres		root@host:~# su - postgres
	~~postgres@host:~$ psql -U postgres~~
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser_pass' SUPERUSER;		postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser_pass' SUPERUSER;
			postgres=# \q
			</pre>

			=== Reserve connections to specific users ===
			<pre>
			root@host:~# su - postgres
			postgres@host:~$ psql -U postgres postgres
			postgres=# ALTER SYSTEM SET reserved_connections = 3;
			postgres=# GRANT pg_use_reserved_connections TO haproxy;
			postgres=# \q
			postgres@host:~$ pg_ctl restart
	</pre>		</pre>

Line 286:		Line 297:
	On modern postgresql, you can create an user with read access to all databases:		On modern postgresql, you can create an user with read access to all databases:
	<pre>		<pre>
	CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';		root@host:~# su - postgres
			postgres@host:~$ psql -U postgres postgres
			postgres=# CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';
			postgres=# \q
	</pre>		</pre>

Line 298:		Line 312:
	from https://www.postgresql.org/docs/18/sql-alterdefaultprivileges.html		from https://www.postgresql.org/docs/18/sql-alterdefaultprivileges.html
	<pre>		<pre>
	GRANT SELECT ON LARGE OBJECTS TO backup;		root@host:~# su - postgres
			postgres@host:~$ psql -U postgres postgres
			postgres=# GRANT SELECT ON LARGE OBJECTS TO backup;
			postgres=# \q
	</pre>		</pre>

Line 315:		Line 332:
	database_to_query=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;		database_to_query=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
	database_to_query=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;		database_to_query=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
	postgres~~=# quit~~=# \q		postgres=# \q
	</pre>		</pre>

Line 324:		Line 341:
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;		postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;
			postgres=# \q
	</pre>		</pre>

T1t0: /* Show connections */

2026-08-02T21:17:30Z

Show connections

← Older revision		Revision as of 21:17, 2 August 2026
Line 144:		Line 144:
	SELECT usename, backend_type, count() FROM pg_stat_activity GROUP BY usename, backend_type ORDER BY count() DESC) q1,(		SELECT usename, backend_type, count() FROM pg_stat_activity GROUP BY usename, backend_type ORDER BY count() DESC) q1,(
	SELECT setting::int global_reserved_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,(		SELECT setting::int global_reserved_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,(
	SELECT count(*) global_used FROM pg_stat_activity) q3,(		SELECT setting::int global_reserved_for_other FROM pg_settings WHERE name='reserved_connections') q3,(
	SELECT setting::int global_usable FROM pg_settings WHERE name='max_connections') q4;		SELECT count(*) global_used FROM pg_stat_activity) q4,(
			SELECT setting::int global_usable FROM pg_settings WHERE name='max_connections') q5;
	</pre>		</pre>

T1t0: /* Show connections */

2026-08-01T16:12:36Z

Show connections

← Older revision		Revision as of 16:12, 1 August 2026
Line 134:		Line 134:
	== Show connections ==		== Show connections ==

	* ~~show connection info~~		* count connections by user
	<pre>		<pre>
	SELECT * FROM		SELECT usename, count(*) AS count FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;
	~~(SELECT~~ count(*) ~~used~~ FROM pg_stat_activity~~) q1,~~
	~~(SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,~~
	~~(SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') q3~~;
	</pre>		</pre>

	* ~~count connections by user~~		* show connection info
	<pre>		<pre>
	SELECT usename, count(*) ~~AS count~~ FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;		SELECT * FROM(
			SELECT usename, backend_type, count() FROM pg_stat_activity GROUP BY usename, backend_type ORDER BY count() DESC) q1,(
			SELECT setting::int global_reserved_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,(
			SELECT count(*) global_used FROM pg_stat_activity) q3,(
			SELECT setting::int global_usable FROM pg_settings WHERE name='max_connections') q4;
	</pre>		</pre>

T1t0: /* Create a backup user */

2026-07-10T12:52:15Z

Create a backup user

@@ Line 287: / Line 287: @@
 </pre>
-Please note: this will (still?) not works for large objects, see https://www.postgresql.org/message-id/r5a3aqlrrqen2snktdmx5tjeoakp3hmbektlqmeqhij3fqqez4%40zmx3bdscipny
+Please note: this will (still?) not works for large objects,
 === Create readonly user ===

T1t0: /* Create a backup user */

2026-07-10T12:27:33Z

Create a backup user

← Older revision		Revision as of 12:27, 10 July 2026
Line 286:		Line 286:
	CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';		CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';
	</pre>		</pre>

			Please note: this will (still?) not works for large objects, see https://www.postgresql.org/message-id/r5a3aqlrrqen2snktdmx5tjeoakp3hmbektlqmeqhij3fqqez4%40zmx3bdscipny

	=== Create readonly user ===		=== Create readonly user ===

T1t0 at 14:01, 8 June 2026

2026-06-08T14:01:41Z

@@ Line 245: / Line 245: @@
 postgres@host:~$ psql -U postgres -d name_db
 postgres=# SELECT pg_terminate_backend(PID);
 postgres=# \q
 postgres@host:~$ exit

T1t0: /* System query */

2025-12-09T16:15:14Z

System query

@@ Line 50: / Line 50: @@
 postgres@host:~$ psql
 postgres=# SELECT usename FROM pg_user; # or \dg
 postgres=# \q
 </pre>

T1t0: /* Create user */

2025-11-16T20:55:02Z

Create user

← Older revision		Revision as of 20:55, 16 November 2025
Line 255:		Line 255:
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres@host:~$ psql -U postgres		postgres@host:~$ psql -U postgres
	postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD '~~superuser~~' SUPERUSER;		postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser_pass' SUPERUSER;
			</pre>

			=== Create a backup user ===

			On modern postgresql, you can create an user with read access to all databases:
			<pre>
			CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';
	</pre>		</pre>

Line 263:		Line 270:
	root@host:~# su - postgres		root@host:~# su - postgres
	postgres@host:~$ psql -U postgres postgres		postgres@host:~$ psql -U postgres postgres
	postgres=# CREATE ROLE readonly_user LOGIN PASSWORD ~~XXX~~;		postgres=# CREATE ROLE readonly_user LOGIN PASSWORD 'readonly_pass';
	postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;		postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;
	postgres=# \q		postgres=# \q