postgresql

examples.txt

# Querying Schema Privileges
sampledb=# dn+ 
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description      
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)
- the default-created schema named "public", described as "standard public schema", and owned by the role "postgres"
-- ownership, unless specified otherwise, is set to the role which creates the schema.
- third column listing the access privileges

The format of the privilege information provides three items: 
  1. the privilege grantee
  2. the privileges
  3. privilege grantor in the format "grantee=privileges/grantor" 
     that is, to the left of the equality sign is the role receiving the privilege(s), 
     immediately to the right of the equality sign is a group of letters specifying 
     the particular privilege(s), and lastly following the slash the role which granted 
     to privilege(s). 
     There may be multiple such privilege information specifications, listed separated 
     by a plus sign since privileges are additive.
     
There are two possible privileges which may be granted separately: 
  - U for "USAGE"  -> required for a role to have the ability to lookup database objects 
                      such as tables and views contained in the schema
  - C for "CREATE" -> allows for a role to create database objects in the schema

  Thus to interpret the privilege listing above, the first specification tells us that the postgres 
  user was granted the usage and create privileges by itself on the public schema.

  Notice that for the second specification above, an empty string appears to the left of the equal sign. 
  This is how privileges granted to all users, by means of the PUBLIC key word mentioned earlier, is denoted.

  - =UC/postgres -> granting usage and create privileges on the public schema to all users

# modifying schema
REVOKE [ GRANT OPTION FOR ]
  { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
  ON SCHEMA schema_name [, ...]
  FROM { [ GROUP ] role_name | PUBLIC } [, ...]
  [ CASCADE | RESTRICT ]



### Delete sessions
SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

-------
psql -U postgres -h localhost

postgres=# create database read_write;
CREATE DATABASE
postgres=#\c read_write;

postgres=# CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    grade FLOAT NOT NULL
);

read_only=# \d+
                             List of relations
 Schema |      Name       |   Type   |  Owner   |    Size    | Description 
--------+-----------------+----------+----------+------------+-------------
 public | students        | table    | postgres | 8192 bytes | 
 public | students_id_seq | sequence | postgres | 8192 bytes | 

INSERT INTO students (name, age, grade) VALUES ('John Smith', 18, 4.0),('Jane Doe', 17, 3.8);

--------------------------------------------------------------------------------------------------------

postgres=# create database read_only;
CREATE DATABASE
postgres=# \c read_only

postgres=# CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    grade FLOAT NOT NULL
);
postgres=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
(1 row)

INSERT INTO students (name, age, grade) VALUES ('Smith', 18, 4.0),('Doe', 17, 3.8);

--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
create user ro_user WITH PASSWORD 'rouser';
GRANT CONNECT ON DATABASE read_only TO ro_user;
GRANT USAGE ON SCHEMA public TO ro_user;

 \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | ro_user=U/pg_database_owner            | 


GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user;

### Test
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------


psql -U ro_user -h localhost -b read_only
Password for user ro_user: 
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 15.1 (Debian 15.1-1.pgdg110+1))
WARNING: psql major version 12, server major version 15.
         Some psql features might not work.
Type "help" for help.

read_only=> \d
               List of relations
 Schema |      Name       |   Type   |  Owner   
--------+-----------------+----------+----------
 public | students        | table    | postgres
 public | students_id_seq | sequence | postgres
(2 rows)

read_only=> \dt students
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | students | table | postgres
(1 row)

read_only=> \d students
                                   Table "public.students"
 Column |         Type          | Collation | Nullable |               Default                
--------+-----------------------+-----------+----------+--------------------------------------
 id     | integer               |           | not null | nextval('students_id_seq'::regclass)
 name   | character varying(50) |           | not null | 
 age    | integer               |           | not null | 
 grade  | double precision      |           | not null | 
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)

read_only=> select * from students;
 id |    name    | age | grade 
----+------------+-----+-------
  1 | John Smith |  18 |     4
  2 | Jane Doe   |  17 |   3.8
(2 rows)

read_only=> INSERT INTO students (name, age, grade) VALUES ('Smith', 18, 4.1)
read_only-> ;
ERROR:  permission denied for table students
STATEMENT:  INSERT INTO students (name, age, grade) VALUES ('Smith', 18, 4.1)
;
read_only=> CREATE TABLE deleteme (
read_only(>     id SERIAL PRIMARY KEY,
read_only(>     name VARCHAR(50) NOT NULL
read_only(> );
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE deleteme (
                     ^
STATEMENT:  CREATE TABLE deleteme (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);


## Copy database on same cluster
# This just copies a whole DB on the file level.

CREATE DATABASE master_db_copy TEMPLATE master_db;

# database size
SELECT pg_size_pretty(pg_database_size(current_database()));

```
# Query Activity: You can monitor the activity in the pg_stat_activity system view. 
# A VACUUM operation is considered a query, so you can look for the specific VACUUM 
# query and its state in this view. When the state changes to something other 
# than "active," it indicates that the VACUUM operation has completed.

SELECT * FROM pg_stat_activity WHERE query ILIKE 'VACUUM%';

Check pg_stat_progress_vacuum: If you're using PostgreSQL 13 or later, you can query 
the pg_stat_progress_vacuum system view to get information about the progress of 
active vacuum operations.

SELECT * FROM pg_stat_progress_vacuum;


```

# Vacuum
```
# database size
SELECT pg_size_pretty(pg_database_size(current_database())) AS size;

# Dead tuples
SELECT
relname AS TableName
,n_live_tup AS LiveTuples
,n_dead_tup AS DeadTuples
,last_autovacuum AS Autovacuum
,last_autoanalyze AS Autoanalyze
FROM pg_stat_user_tables;

# Last autovacumm
SELECT relname, last_autovacuum,last_autoanalyze FROM pg_stat_user_tables;


```