Programming/PostgreSQL/Setup - Revision history

Brodriguez: Correct typo

2020-11-20T04:41:41Z

Correct typo

← Older revision		Revision as of 04:41, 20 November 2020
Line 16:		Line 16:

	== User Management ==		== User Management ==
	{{ note \| Unless otherwise specified, all commands from this section ~~assumes~~ you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}		{{ note \| Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}

	'''Roles''' in PostreSQL are the equivalent of MySQL's '''Users'''.		'''Roles''' in PostreSQL are the equivalent of MySQL's '''Users'''.

Brodriguez: Clean up page to remove unnecessary text

2020-11-20T04:36:29Z

Clean up page to remove unnecessary text

← Older revision		Revision as of 04:36, 20 November 2020
Line 1:		Line 1:
	== Installation ==		== Installation ==
			=== Windows ===
			Download and install from: https://www.postgresql.org/download/windows/

	=== Arch Linux ===		=== Arch Linux ===
	Basic installation can be performed with:		Basic installation can be performed with:
Line 13:		Line 16:

	== User Management ==		== User Management ==
			{{ note \| Unless otherwise specified, all commands from this section assumes you're in the [[PostgreSQL#Basics \| PostgreSQL shell]]. }}

	'''Roles''' in PostreSQL are the equivalent of MySQL's '''Users'''.		'''Roles''' in PostreSQL are the equivalent of MySQL's '''Users'''.

Line 20:		Line 25:

	=== List All Users ===		=== List All Users ===
	~~From the PostgreSQL shell:~~
	\du		\du

	=== Create User ===		=== Create User ===
			==== Via PostgreSQL Shell ====
			First, invoke a PostgreSQL shell that has PostgreSQL '''role''' privileges:
			CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

	==== Via Linux Shell ====		==== Via Linux Shell ====
	First, invoke a system user that has PostgreSQL '''role''' privileges.		First, invoke a system user that has PostgreSQL '''role''' privileges.
Line 30:		Line 38:
	createuser --interactive		createuser --interactive

	Alternatively, create a ~~minimal~~ user and enable permissions with flags via:		Alternatively, create a user and enable permissions with flags via:
	createuser <flags> <user_name>		createuser <flags> <user_name>

	~~==== Via PostgreSQL Shell ====~~		See https://www.postgresql.org/docs/current/app-createuser.html for possible flags.
	~~First, invoke a PostgreSQL shell that has PostgreSQL '''role''' privileges~~:
	~~CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';~~

	=== Remove User ===		=== Remove User ===
	~~From the PostgreSQL shell:~~
	DROP OWNED BY <user_name>;		DROP OWNED BY <user_name>;
	DROP USER <user_name>;		DROP USER <user_name>;

	=== Change User Password ===		=== Change User Password ===
	~~From the PostgreSQL shell:~~
	ALTER USER <user_name> WITH ENCRYPTED PASSWORD '<password>';		ALTER USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

	=== Change User Permissions ===		=== Change User Permissions ===
	~~From the PostgreSQL shell:~~
	ALTER ROLE <user_name> WITH <options>;		ALTER ROLE <user_name> WITH <options>;

	For all current available user permissions, see https://www.postgresql.org/docs/current/sql-createrole.html		For all current available user permissions, see https://www.postgresql.org/docs/current/sql-createrole.html

Brodriguez: Move basic "access shell" commands to PostgreSQL page

2020-11-20T04:29:45Z

Move basic "access shell" commands to PostgreSQL page

← Older revision		Revision as of 04:29, 20 November 2020
Line 10:		Line 10:
	Basic installation can be performed with:		Basic installation can be performed with:
	sudo apt install postgresql postgresql-contrib		sudo apt install postgresql postgresql-contrib


	~~== Basics ==~~
	~~To access the default PostgreSQL user, use:~~
	~~sudo -i -u postgres~~

	~~This is required for some commands, such as the [[PostGreSQL#Creating Users \| createuser ]] linux terminal command.~~

	~~=== The PostgreSQL Shell ===~~
	~~{{ Note \| The {{ ic \| sudo -u postgres}} command can be used to invoke any postgresql shell command you want, without having to enter the PostgreSQL shell.}}~~

	~~To access the PostgreSQL shell for the first time, invoke the {{ ic \|postgres}} user:~~
	~~sudo -u postgres psql~~

	~~Exit the shell with:~~
	\q

Brodriguez: Correct systemctl commands

2020-11-20T04:22:14Z

Correct systemctl commands

← Older revision		Revision as of 04:22, 20 November 2020
Line 4:		Line 4:
	sudo pacman -Syu postgresql		sudo pacman -Syu postgresql
	sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data		sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
	sudo start ~~systemctl~~ postgresql.service		sudo systemctl start postgresql.service
	sudo enable ~~systemctl~~ postgresql.service		sudo systemctl enable postgresql.service

	=== Ubuntu ===		=== Ubuntu ===

Brodriguez: Brodriguez moved page PostgreSQL/Setup to Programming/PostgreSQL/Setup

2020-11-03T00:28:08Z

Brodriguez moved page PostgreSQL/Setup to Programming/PostgreSQL/Setup

← Older revision

Revision as of 00:28, 3 November 2020

Brodriguez: Expand page

2020-11-02T23:55:37Z

Expand page

← Older revision		Revision as of 23:55, 2 November 2020
Line 2:		Line 2:
	=== Arch Linux ===		=== Arch Linux ===
	Basic installation can be performed with:		Basic installation can be performed with:
	sudo pacman -Syu		sudo pacman -Syu postgresql
			sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
			sudo start systemctl postgresql.service
			sudo enable systemctl postgresql.service

	=== Ubuntu ===		=== Ubuntu ===
Line 9:		Line 12:


	== ~~PostgreSQL Shell~~ ==		== Basics ==
	~~{{ Note \| The {{ ic \|~~ sudo -u postgres~~}} command can be used to invoke any postgresql shell command you want, without having to enter the PostgreSQL shell}}.~~		To access the default PostgreSQL user, use:
			sudo -i -u postgres

	To access the PostgreSQL for the first time, invoke the {{ ic \|postgres}} user:		This is required for some commands, such as the [[PostGreSQL#Creating Users \| createuser ]] linux terminal command.

			=== The PostgreSQL Shell ===
			{{ Note \| The {{ ic \| sudo -u postgres}} command can be used to invoke any postgresql shell command you want, without having to enter the PostgreSQL shell.}}

			To access the PostgreSQL shell for the first time, invoke the {{ ic \|postgres}} user:
	sudo -u postgres psql		sudo -u postgres psql


	Exit the shell with:		Exit the shell with:
Line 20:		Line 28:


	== ~~Creating Roles~~ ==		== User Management ==
	'''Roles''' in PostreSQL are the equivalent of ~~other~~ MySQL's '''Users'''.		'''Roles''' in PostreSQL are the equivalent of MySQL's '''Users'''.

			However, documentation online (and even PostgreSQL's shell commands) seem inconsistent regarding which name it uses.

			Due to habit, the rest of this wiki will probably refer to '''roles''' as '''users'''.

			=== List All Users ===
			From the PostgreSQL shell:
			\du

			=== Create User ===
			==== Via Linux Shell ====
			First, invoke a system user that has PostgreSQL '''role''' privileges.

	~~To create~~ a new user via an interactive GUI:		Create a new user via an interactive GUI:
	createuser --interactive		createuser --interactive

	Alternatively, create a minimal user and enable permissions with flags via:		Alternatively, create a minimal user and enable permissions with flags via:
	createuser <flags> <user_name>		createuser <flags> <user_name>

			==== Via PostgreSQL Shell ====
			First, invoke a PostgreSQL shell that has PostgreSQL '''role''' privileges:
			CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

			=== Remove User ===
			From the PostgreSQL shell:
			DROP OWNED BY <user_name>;
			DROP USER <user_name>;

			=== Change User Password ===
			From the PostgreSQL shell:
			ALTER USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

			=== Change User Permissions ===
			From the PostgreSQL shell:
			ALTER ROLE <user_name> WITH <options>;

			For all current available user permissions, see https://www.postgresql.org/docs/current/sql-createrole.html

Brodriguez: Create page

2020-11-02T23:07:13Z

Create page

New page

== Installation ==
=== Arch Linux ===
Basic installation can be performed with:
sudo pacman -Syu

=== Ubuntu ===
Basic installation can be performed with:
sudo apt install postgresql postgresql-contrib

== PostgreSQL Shell ==
{{ Note | The {{ ic | sudo -u postgres}} command can be used to invoke any postgresql shell command you want, without having to enter the PostgreSQL shell}}.

To access the PostgreSQL for the first time, invoke the {{ ic |postgres}} user:
sudo -u postgres psql

Exit the shell with:
\q

== Creating Roles ==
'''Roles''' in PostreSQL are the equivalent of other MySQL's '''Users'''.

To create a new user via an interactive GUI:
createuser --interactive

Alternatively, create a minimal user and enable permissions with flags via:
createuser <flags> <user_name>