DB/Postgres PostgreSQL - Revision history

Pio2pio: /* Cli client */

2022-04-07T11:11:22Z

Cli client

← Older revision		Revision as of 13:11, 7 April 2022
Line 13:		Line 13:
	export POSTGRES_USER=postgres		export POSTGRES_USER=postgres
	export POSTGRES_PASSWORD=mypassword		export POSTGRES_PASSWORD=mypassword

			# Interactive login version 12+
			PGPASSWORD=mypassword psql -h postgresql.default.svc.cluster.local -U myuser mydatabase

	psql -h localhost -p 5432 -U postgres postgres		psql -h localhost -p 5432 -U postgres postgres

2022-03-21T09:12:45Z

Permissions

← Older revision		Revision as of 11:12, 21 March 2022
Line 74:		Line 74:
	= Permissions =		= Permissions =
	<source lang=sql>		<source lang=sql>
	# Grant privileges on a database to a user		-- Grant privileges on a database to a user
	GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;		GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

	# Grant permissions on a table		-- Grant permissions on a table
	# In PostgreSQL 12 and later, it is possible to grant all privileges of a table in a database to a role/user/account.		-- In PostgreSQL 12 and later, it is possible to grant all privileges of a table in a database to a role/user/account.
	GRANT ALL ON table_name TO role_name;		GRANT ALL ON table_name TO role_name;

	# Grant to all tables in the database		-- Grant to all tables in the database
	GRANT ALL ON ALL TABLES TO role_name;		GRANT ALL ON ALL TABLES TO role_name;

	# Grant it to all tables of a schema in the database		-- Grant it to all tables of a schema in the database
	GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;		GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;
	</source>		</source>
Line 90:		Line 90:
	== Public schema ==		== Public schema ==
	<source lang=sql>		<source lang=sql>
	# Grant all permissions on the schema		-- Grant all permissions on the schema
	GRANT ALL ON SCHEMA public TO myuser;		GRANT ALL ON SCHEMA public TO myuser;

	# Change the schema owner		-- Change the schema owner
	ALTER SCHEMA public OWNER TO myuser;		ALTER SCHEMA public OWNER TO myuser;
	</source>		</source>

2022-03-21T09:12:19Z

Public schema

@@ Line 72: / Line 72: @@
 </source>
-= Public schema =
+= Permissions =
 <source lang=sql>
 GRANT ALL ON SCHEMA public TO myuser;
 </source>

2022-03-21T08:40:03Z

Operational queries

← Older revision		Revision as of 10:40, 21 March 2022
Line 61:		Line 61:
	public		public
	(1 row)		(1 row)
			</source>


			Execute psql via kubectl exec
			<source lang=bash>
			# Connection info
			kubectl -n postgres exec -it sts/postgres -- psql postgresql://postgres@localhost:5432/${DB} -c "SELECT * FROM pg_stat_activity;"
			kubectl -n postgres exec -it sts/postgres -- psql postgresql://postgres@localhost:5432/${DB} -c "\c"
			You are now connected to database "database1" as user "postgres".
	</source>		</source>

2022-03-18T11:38:11Z

Operational queries

← Older revision		Revision as of 13:38, 18 March 2022
Line 61:		Line 61:
	public		public
	(1 row)		(1 row)
			</source>

			= Public schema =
			<source lang=sql>
			GRANT ALL ON SCHEMA public TO myuser;
	</source>		</source>

2022-03-18T11:26:37Z

Cli client

← Older revision		Revision as of 13:26, 18 March 2022
Line 1:		Line 1:
	* [https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ Managing PostgreSQL users and roles] 04 MAR 2019, AWS Blog		* [https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/ Managing PostgreSQL users and roles] 04 MAR 2019, AWS Blog

			= Building blocks =
			Postgresql cluster > databases > Schemas > Objects

	= Cli client =		= Cli client =

2022-03-18T11:22:59Z

Operational queries

← Older revision		Revision as of 13:22, 18 March 2022
Line 48:		Line 48:

	(2 rows)		(2 rows)
			</source>


			Postgresql schema search path. First it will use the current schema (often it's public). If the schema name matches the USER$, then an object (table,view,etc.) will be sources/created in that schema.
			<source lang=sql>
			SELECT current_schema();
			current_schema
			----------------
			public
			(1 row)
	</source>		</source>

2022-03-11T15:44:34Z

Pio2pio moved page Postgres PostgreSQL to DB/Postgres PostgreSQL without leaving a redirect

← Older revision	Revision as of 17:44, 11 March 2022
(No difference)

2021-11-18T13:33:22Z

Operational queries

← Older revision		Revision as of 15:33, 18 November 2021
Line 41:		Line 41:
	SELECT * FROM pg_stat_activity;		SELECT * FROM pg_stat_activity;
	SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity;		SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity;

	usename \| application_name \| client_addr \| backend_start \| state		usename \| application_name \| client_addr \| backend_start \| state
	----------+------------------+--------------+-------------------------------+--------		----------+------------------+--------------+-------------------------------+--------

2021-11-18T13:33:12Z

@@ Line 37: / Line 37: @@
 -- Reset user password, no need to flush or log out
 ALTER USER user_name WITH PASSWORD 'new_password';
 </source>