DB/Postgres PostgreSQL

• Managing PostgreSQL users and roles 04 MAR 2019, AWS Blog

Building blocks

Postgresql cluster > databases > Schemas > Objects

Cli client

# Install
apt-get install -yq postgresql-client

# Connect
export POSTGRES_DB=postgres
export POSTGRES_USER=postgres
export POSTGRES_PASSWORD=mypassword

# Interactive login version 12+
PGPASSWORD=mypassword psql -h postgresql.default.svc.cluster.local -U myuser mydatabase

psql -h localhost -p 5432 -U postgres postgres
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

postgres=#
  \l  -- list all databases
  \dt -- list all the tables in the current database
  \c mydatabase   -- switch database
  \d <table_name> -- describe a table
  \g -- execute previous command
  \? -- help
  \h DROP TABLE -- help about particular comamnd
  \timing -- execution times of queries

# Show version
postgres=# SELECT version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

Operational queries

-- Reset user password, no need to flush or log out
ALTER USER user_name WITH PASSWORD 'new_password';

-- Show connections to database
SELECT * FROM pg_stat_activity;
SELECT usename,application_name,client_addr,backend_start,state FROM pg_stat_activity;

 usename  | application_name | client_addr  |         backend_start         | state  
----------+------------------+--------------+-------------------------------+--------
 postgres |                  |              | 2021-11-18 13:21:45.376108+00 | 
 postgres | psql             | 10.10.11.111 | 2021-11-18 13:28:56.659556+00 | active

(2 rows)

Postgresql schema search path. First it will use the current schema (often it's public). If the schema name matches the USER$, then an object (table,view,etc.) will be sources/created in that schema.

SELECT current_schema();
 current_schema 
----------------
 public
(1 row)

Execute psql via kubectl exec

# Connection info
kubectl -n postgres exec -it sts/postgres -- psql postgresql://postgres@localhost:5432/${DB} -c "SELECT * FROM pg_stat_activity;"
kubectl -n postgres exec -it sts/postgres -- psql postgresql://postgres@localhost:5432/${DB} -c "\c"
You are now connected to database "database1" as user "postgres".

Permissions

-- Grant privileges on a database to a user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

-- Grant permissions on a table
-- In PostgreSQL 12 and later, it is possible to grant all privileges of a table in a database to a role/user/account.
GRANT ALL ON table_name TO role_name;

-- Grant to all tables in the database
GRANT ALL ON ALL TABLES TO role_name;

-- Grant it to all tables of a schema in the database
GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;

Public schema

-- Grant all permissions on the schema
GRANT ALL ON SCHEMA public TO myuser;

-- Change the schema owner
ALTER SCHEMA public OWNER TO myuser;