Programming/PostgreSQL/Setup: Difference between revisions

From Dev Wiki
Jump to navigation Jump to search
m (Brodriguez moved page PostgreSQL/Setup to Programming/PostgreSQL/Setup)
m (Correct typo)
 
(3 intermediate revisions by the same user not shown)
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:
  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 ===
Line 12: Line 15:




== Basics ==
== User Management ==
To access the default PostgreSQL user, use:
{{ note | Unless otherwise specified, all commands from this section assume you're in the [[PostgreSQL#Basics | PostgreSQL shell]]. }}
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
 


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


Line 36: 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 46: 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

Latest revision as of 04:41, 20 November 2020

Installation

Windows

Download and install from: https://www.postgresql.org/download/windows/

Arch Linux

Basic installation can be performed with: 

sudo pacman -Syu postgresql
sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service

Ubuntu

Basic installation can be performed with: 

sudo apt install postgresql postgresql-contrib


User Management

Note: Unless otherwise specified, all commands from this section assume you're in the PostgreSQL shell.

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

\du

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

First, invoke a system user that has PostgreSQL role privileges.

Create a new user via an interactive GUI: 

createuser --interactive

Alternatively, create a user and enable permissions with flags via: 

createuser <flags> <user_name>

See https://www.postgresql.org/docs/current/app-createuser.html for possible flags.

Remove User

DROP OWNED BY <user_name>;
DROP USER <user_name>;

Change User Password

ALTER USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

Change User Permissions

ALTER ROLE <user_name> WITH <options>;

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

Retrieved from "https://wiki.brandon-rodriguez.com/index.php?title=Programming/PostgreSQL/Setup&oldid=513"