Client

  • apt install postgresql-client
  • psql 
    1
2
3

    export PGPASSWORD=PASSWORD
  
psql -h HOST -U USER -d DATABASE [-f SQL_FILE]

Users & Roles

Due to [REF]

Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

Create

1
2
3
4
5
6
7
8
9
10
11

create role ROLE 
    [with
        [superuser]
        [createdb]
        [login password 'PASS']
    ]

create database DB 
    [with 
        [owner = ROLE]
    ]

Meta Data

List user and roles

[REF]

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc

Session

[REF]

1
2
3
4
5
6
7
8
9
10
11
12

-- List Sessions
select *
from pg_stat_activity

-- Kill Session - pg_terminate_backend()
select pg_terminate_backend(pid) 
from pg_stat_activity
where 
  pid = '18765'
  usename = 'USER'
  datname = 'DB_NAME'
  client_addr = 'CLIENT_IP'

BackUp