Tập tành học PostgreSQL tutorial trên AWS

/ 14 March 2023 / 0 Comments

Share Everywhere

Table of contents

#1. Connecting to an AWS DB Instance Running the PostgreSQL Database Engine using psql:

You can use a local instance of the psql command line utility to connect to a PostgreSQL DB instance. You need either PostgreSQL or the psql client installed on your client computer.

Use one of the following formats to connect to a PostgreSQL DB instance on Amazon RDS. When you connect, you’re prompted for a password. For batch jobs or scripts, use the –no-password option:

psql \\\\
   --host=<DB instance endpoint> \\\\
   --port=<port> \\\\
   --username=<master user name> \\\\
   --password \\\\
   --dbname=<database name>

For example, the following command connects to a database called mypgdb on a PostgreSQL DB instance called mypostgresql using fictitious credentials:

psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb

Notes:

In the case that you do not use RDS, and want to login to psql console: The PostgreSQL installation creates a “UNIX USER” called postgres, who is ALSO the “Default PostgreSQL’s SUPERUSER”. To run psql using “UNIX USER” postgres, you need to invoke:

sudo -u postgres psql

The PostgreSQL’s prompt is in the format of databaseName=# for superuser or databaseName=> for regular user. In this case, the current database is also called postgres, which is the same as the username.
The prompt changes to -# (superuser) or -> (regular user) for command continuation.
Type help to see the help menu.
Type \\\\qto quit.
Type \\\\? to see all the psql commands.
You need to end a SQL statement with a semicolon (;) or \g (for GO) to execute the statement. For example, in “SELECT version();”.
Comments: In a SQL script or command, an end-of-line comment begins with –; a block comment is enclosed by /* and */

#2. Working with database:

2.1. Basic commands:

A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

List all databases: List all databases via \l (or \list), or \l+ for more details:

Tập tành học PostgreSQL tutorial trên AWS

Help on SQL command syntax:

\\\\h <command>

For example:

Create a new database called mytest:

CREATE DATABASE mytest;

By default, the owner of the database is the current login user.

Connect to mytest database via \c (or \connect):

\\\\c mytest

You can display all tables (aka relations) via \dt or \dt+ for more details.

We will be using an enumeration (TYPE) in our table, let’s define it:

CREATE TYPE cat_enum AS ENUM ('coffee', 'tea');

Display all types via /dT or /dT+ for more details:

/dT+

Create a new table:

CREATE TABLE IF NOT EXISTS cafe (
  id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  category cat_enum NOT NULL,   -- Use the enum type defined earlier
  name VARCHAR(50) NOT NULL,    -- Variable-length string of up to 50 characters
  price NUMERIC(5,2) NOT NULL,  -- 5 digits total, with 2 decimal places
  last_update DATE              -- 'YYYY-MM-DD'
);

Display all tables in the current database, via \dt or \dt+ for more details:

\\\\dt+

Display details of a particular table:

\\\\d+ cafe

Insert rows:

INSERT INTO cafe (category, name, price) VALUES
  ('coffee', 'Espresso', 3.19),
  ('coffee', 'Cappuccino', 3.29),
  ('coffee', 'Caffe Latte', 3.39),
  ('coffee', 'Caffe Mocha', 3.49),
  ('coffee', 'Brewed Coffee', 3.59),
  ('tea', 'Green Tea', 2.99),
  ('tea', 'Wulong Tea', 2.89);

The output shows the OID (Object Identification Number) and the rows affected. — Query (SELECT):

SELECT * FROM cafe;

Update:

UPDATE cafe SET price = price * 1.1 WHERE category = 'tea';

Delete:

DELETE FROM cafe WHERE id = 6;

Notes:

+) Take note that there are two sets of commands:

psql commands: such as \\\\c, \\\\l, \\\\dt.
SQL commands: such as CREATE DATABASE, CREATE TABLE and SELECT. SQL command KEYWORDS are not case sensitive. I show them in uppercase for clarity. PostgreSQL automatically converts all names (identifiers) to lowercase (even you type in uppercase or mixed case). To use uppercase or mixed case, you need to double-quote the names.

+) You need to end your SQL commands with a semi-colon (;) or \g. If you forget to enter a semi-colon, the command-prompt changes to “dbname-#” to indicate continuation (A SQL command can span many lines). You can enter the semi-colon on the new line.

More on psql commands:

+) \\\\?: show all psql commands.

+) \\\\h sql-command: show syntax on SQL command.

+) \\\\c dbname [username]: Connect to database, with an optional username (or \connect).

+) Display Commands: You can append + to show more details.

\\\\l: List all database (or \list).
\\\\d: Display all tables, indexes, views, and sequences.
\\\\dt: Display all tables.
\\\\di: Display all indexes.
\\\\dv: Display all views.
\\\\ds: Display all sequences.
\\\\dT: Display all types.
\\\\dS: Display all system tables.
\\\\du: Display all users.
\\\\x auto|on|off: Toggle|On|Off expanded output mode.

2.2. Using Utility “createuser” and “createdb”:

As an example, let’s create a PostgreSQL user called testuser, and a new database called testdb owned by testuser.

Create a new PostgreSQL user called testuser, allow user to login, but NOT creating databases:

sudo -u postgres createuser --login --pwprompt testuser

Create a new database called testdb, owned by testuser:

sudo -u postgres createdb --owner=testuser testdb

Tailor the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf to allow non-default user testuser to login to PostgreSQL server, by adding the following entry:

# TYPE  DATABASE    USER        ADDRESS          METHOD
local   testdb      testuser                     md5

Restart PostgreSQL server:

sudo service postgresql restart

psql -U testuser testdb

Note: If you encounter the error: psql: FATAL: Peer authentication failed for user "testuser" You can fix by:

Locate to your pg_hba.conf file, and find this line:

local   all             postgres                                peer

change it to:

local   all             postgres                                md5

and then restart PostgreSQL server.

List all databases (\list or \l):

\\\\list

#3. PostgreSQL administration:

3.1. Default Superuser “postgres”:

During installation, a “UNIX USER” (who cannot login to the system interactively) called postgres is created. You can verify by checking the entry in /etc/passwd and /etc/shadow:

sudo less /etc/passwd | grep postgres

and:

$ sudo less /etc/shadow | grep postgres

The user postgres belongs to the following groups:

groups postgres

An entry is also defined in PostgreSQL’s HBA (Host-Based Authentication) configuration file for this user:

sudo less /var/lib/pgsql/data/pg_hba.conf

3.2. Authentication Methods:

PostgreSQL supports a number of authentication methods. The commonly-used are:

ident, peer: identical (or fully match) to the OS account, with an optional name mapping defined in pg_ident.conf file. ident is applicable to TCP/IP; while peer for “local” connection.
md5: require md5-hashed password (most common).
password: require clear-text password.
trust: no password, as long as meeting the IP, user, and database criteria defined in the HBA.
reject: reject login immediately.
others, such as GSSAPI, SSPI, Kerberos, LDAP, RADIUS, Certificate, PAM.

There are two ways to login PostgreSQL:

1/ By running the “psql” command as a UNIX user which is also configured as PostgreSQL user using so-called IDENT/PEER authentication, e.g., “sudo -u postgres psql”.

2/ Via TCP/IP connection using PostgreSQL’s own managed username/password (using so-called MD5 authentication).

3.3. Set a Password for User postgres:

To set a password, login to PostgreSQL server using postgres via psql and issue command “\password username”, as follows:

sudo -u postgres psql

Change password for current user “postgres”:

\\\\password postgres

Display the user table:

SELECT * FROM pg_user;

To test the password login, you need to change the the authentication method from “peer” to “md5” in pg_hba.conf. Restart the server, and login via sudo -u postgres sql. The system will prompt you for the password.

3.4. Add your UNIX user as PostgreSQL user:

Using user “postgres” (which is a UNIX user as well as PostgreSQL user) to run psql involves switching user (via sudo -u <username> or su - <username>). You can simply the process by configuring your current UNIX userID as PostgreSQL user, as follows:

Switch to default superuser “postgres”, — run utility “createuser” to create a superuser same name as current login. — “$USER” is an environment variable denoting the current login user.

Command:

sudo -u postgres createuser --superuser $USER

Create the default database which shall be the same as the username:

sudo -u postgres createdb $USER

Now, you can invoke “psql” from your user account

psql

3.5. Group and User:

3.5.1. Create role:

The latest PostgreSQL treats both group and user as “role”. Some roles can login (i.e. user), some roles have member of other roles (i.e., group).

Roles are different from traditional Unix-style permissions in that there is no distinction between users and groups. Roles can be manipulated to resemble both of these conventions, but they are also more flexible.

For instance, roles can be members of other roles, allowing them to take on the permission characteristics of previously defined roles. Roles can also own objects and control access to those object for other roles.

You should use CREATE ROLE to create both users and groups (CREATE USER and CREATE GROUP are meant for compatibility).

Create a login user role:

CREATE ROLE user1 LOGIN PASSWORD 'xxxx' CREATEDB VALID UNTIL 'infinity';

Create a login superuser role:

CREATE ROLE user2 LOGIN PASSWORD 'xxxx' SUPERUSER VALID UNTIL 'YYYY-MM-DD';

Create a group role:

CREATE ROLE group1 INHERIT;

Add a user (or group) to this group:

GRANT group1 TO user1;

3.5.2. Define Privileges Upon Role Creation:

We can do this by specifying the permissions we want after the main create clause:

CREATE ROLE <role_name> WITH <optional_permissions>;

If we want to get to this state without specifying the “login” attribute with every role creation, we can actually use the following command instead of the “CREATE ROLE” command:

CREATE USER <role_name>;

The only difference between the two commands is that “CREATE USER” automatically gives the role login privileges.

3.5.3. Use Group Roles & alter ownership in PostgreSQL:

3.5.3.1. Use group roles:

Any member of a group role can act as the group role they are a member of by using the “set role” command.

Since the “postgres” user we are logged into currently has superuser privileges, we can use “set role” even though we are not a member of that group:

SET ROLE group1;

Now, any tables that are created are owned by the group1 role:

CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);

We can check the table ownership by issuing this command:

\\\\d

As you can see, the new table (and the sequence associated with the serial data type) is owned by the “group1” role.

We can get back to our original role permissions with the following command:

RESET ROLE;

If we give a user the “inherit” property with the “alter role” command, that user will automatically have all of the privileges of the roles they belong to without using the “set role” command:

ALTER ROLE dev INHERIT;

Now dev will have every permission of the roles it is a member of.

3.5.3.2. Alter the ownership:

You can delete a role using the following syntax:

DROP ROLE <role_name>;

or to avoid not found error:

DROP ROLE IF EXISTS <role_name>;

Now we try to remove a group role (or any role) with the “drop role” command:

DROP ROLE group1;

This will give you an error, because we created a table that is owned by “group1”. We can solve this problem by transferring ownership to a different role, the syntax is:

ALTER TABLE <table_name> OWNER TO <role>;

and in this case:

ALTER TABLE hello OWNER TO demo_role;

If we check, we can see that “group1” no longer owns any of the tables:

\\\\d

We can now drop the “group1” role successfully by issuing the command again:

DROP ROLE group1;

This will destroy the group1 role. The former members of “group1” (dev role) is not removed.

Note:

+) By default, the owner of the database is the current login user. If you want to alter the owner of a database:

ALTER DATABASE <database_name> OWNER TO <role>;

+) Alter schema’s owner:

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators.To access an object of a schema, you qualify its name with the schema name as a prefix:

schema_name.object_name

For example, you may have sales schema that has staff table and the public schema which also has the staff table. When you refer to the staff table you must qualify it as follows:

public.staff

If you want to alter the ownership of a schema, use the command:

ALTER SCHEMA <schema_name> OWNER TO <role>;

Important note:

You must own the schema to use ALTER SCHEMA. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database. (Note that superusers have all these privileges automatically.)

3.5.4. Change Privileges of Roles in PostgreSQL:

To change the attributes of an already created role, we use the “ALTER ROLE” command.

This command allows us to define privilege changes without having to delete and recreate users.

ALTER ROLE <role_name> WITH <attribute_options>;

For example:

3.5.5. Log In as a Different User in PostgreSQL:

psql -U <user_name> -d <database_name> -h 127.0.0.1 -W

and then enter the password for the user_name that you have created.

3.5.6. Grant Permissions in PostgreSQL:

When a database or table is created, usually only the role that created it (not including roles with superuser status) has permission to modify it. We can alter this behavior by granting permissions to other roles.

We can grant permissions using the “GRANT” command. The general syntax:

GRANT <permission_type> ON <table_name> TO <role_name>;

For example:

Create a simple table

CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);

We can see the result with:

\\\\d

We can now grant some privileges to the new “demo” table to “testuser”. Give the user “UPDATE” privileges with the following command:

GRANT UPDATE ON demo TO testuser;

We can grant full permissions to a user by substituting the permission type with the word “all”:

GRANT ALL ON demo TO testuser;

If we want to specify permissions for every user on the system, we can use the word “public” instead of a specific user:

GRANT INSERT ON demo TO PUBLIC;

To view the grant table, use the following command:

\\\\z

3.5.7 Remove Permissions in PostgreSQL:

You can remove permissions using the “REVOKE” command. The revoke command uses almost the same syntax as grant:

REVOKE <permission_type> ON <table_name> FROM <user_name>;

Again, we can use the same shorthand words (all and public) to make the command easier:

REVOKE INSERT ON demo FROM PUBLIC;

Tập tành học PostgreSQL tutorial trên AWS