Spielereien
Kanu
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
/ home / computer / postgresql / postgresql-for-mysql-admins / .
O/S user postgresql CLI tools Creating a user
Unix-domainsockets TCP/IP
Client authentication: The pg_hba.conf File, see PostgreSQL documentation (Host Based Authentication)
$datadir/pg_hba.conf
Read on:
View:
postgres=# select * from pg_hba_file_rules;
rule_number | file_name | line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------------------------------------+-------------+-------+---------------+------------+-----------+-----------------------------------------+---------------+---------+-------
1 | /etc/postgresql/16/main/pg_hba.conf | 118 | local | {all} | {postgres} | | | peer | |
2 | /etc/postgresql/16/main/pg_hba.conf | 121 | local | {all} | {oli} | | | trust | |
3 | /etc/postgresql/16/main/pg_hba.conf | 124 | local | {all} | {all} | | | peer | |
4 | /etc/postgresql/16/main/pg_hba.conf | 126 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
5 | /etc/postgresql/16/main/pg_hba.conf | 128 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
6 | /etc/postgresql/16/main/pg_hba.conf | 131 | local | {replication} | {all} | | | peer | |
7 | /etc/postgresql/16/main/pg_hba.conf | 132 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
8 | /etc/postgresql/16/main/pg_hba.conf | 133 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
(8 rows)
$ createuser -e app
$ psql
postgres=# CREATE USER app;
postgres=# \du
postgres=# \du+
postgres=# SELECT * FROM pg_user;
postgres=# SELECT rolname FROM pg_roles WHERE rolsuper = true;
rolname
----------
postgres
postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
-----------------------------+----------+------------+---------------+-------------+-------------+----------------
postgres | t | t | t | t | t | t
pg_database_owner | f | t | f | f | f | f
pg_read_all_data | f | t | f | f | f | f
pg_write_all_data | f | t | f | f | f | f
pg_monitor | f | t | f | f | f | f
pg_read_all_settings | f | t | f | f | f | f
pg_read_all_stats | f | t | f | f | f | f
pg_stat_scan_tables | f | t | f | f | f | f
pg_read_server_files | f | t | f | f | f | f
pg_write_server_files | f | t | f | f | f | f
pg_execute_server_program | f | t | f | f | f | f
pg_signal_backend | f | t | f | f | f | f
pg_checkpoint | f | t | f | f | f | f
pg_use_reserved_connections | f | t | f | f | f | f
pg_create_subscription | f | t | f | f | f | f
oli | f | t | f | f | t | f
postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
WHERE rolcanlogin = true;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
----------+----------+------------+---------------+-------------+-------------+----------------
postgres | t | t | t | t | t | t
oli | f | t | f | f | t | f
postgres=# SELECT * FROM information_schema.role_table_grants where grantee NOT IN ('postgres', 'PUBLIC');
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+-------------------+---------------+--------------+----------------------------+----------------+--------------+----------------
postgres | pg_monitor | postgres | public | pg_buffercache | SELECT | NO | YES
postgres | pg_read_all_stats | postgres | pg_catalog | pg_backend_memory_contexts | SELECT | NO | YES
postgres | pg_read_all_stats | postgres | pg_catalog | pg_shmem_allocations | SELECT | NO | YES
postgres=# SELECT * FROM pg_tables where tableowner NOT IN ('postgres');
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
postgres=# SELECT r.usename AS grantor,
e.usename AS grantee,
nspname,
privilege_type,
is_grantable
FROM pg_namespace
JOIN LATERAL (SELECT *
FROM aclexplode(nspacl) AS x) a
ON true
JOIN pg_user e
ON a.grantee = e.usesysid
JOIN pg_user r
ON a.grantor = r.usesysid;
grantor | grantee | nspname | privilege_type | is_grantable
----------+----------+--------------------+----------------+--------------
postgres | postgres | pg_catalog | USAGE | f
postgres | postgres | pg_catalog | CREATE | f
postgres | postgres | information_schema | USAGE | f
postgres | postgres | information_schema | CREATE | f
postgres=# SELECT*
FROM information_schema.role_schema_grants
WHERE grantee = 'username';
postgres=# SELECT datname, datacl
FROM pg_database;
https://stackoverflow.com/questions/40759177/postgresql-show-all-the-privileges-for-a-concrete-user
postgres=# ALTER USER oli WITH PASSWORD 'secret';
ALTER ROLE
CREATE USER is an alias to CREATE ROLE
https://www.postgresql.org/docs/current/sql-createuser.html
https://phoenixnap.com/kb/postgres-create-user
https://www.postgresql.org/docs/current/user-manag.html
https://www.strongdm.com/blog/postgres-create-user
https://www.strongdm.com/blog/postgres-list-users
VACUUM See also here
Equivalent to MySQL Error Log, General Log, Audit Log?, …
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
2025-07-01 15:15:06.409 CEST [23470] LOG: starting PostgreSQL 16.9 (Ubuntu 16.9-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-07-01 15:15:06.409 CEST [23470] LOG: listening on IPv6 address "::1", port 5432
2025-07-01 15:15:06.409 CEST [23470] LOG: listening on IPv4 address "127.0.0.1", port 5432
2025-07-01 15:15:06.413 CEST [23470] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-07-01 15:15:06.445 CEST [23473] LOG: database system was shut down at 2025-07-01 15:14:54 CEST
2025-07-01 15:15:06.498 CEST [23470] LOG: database system is ready to accept connections
...
2025-07-01 15:15:18.004 CEST [23470] LOG: received fast shutdown request
2025-07-01 15:15:18.009 CEST [23470] LOG: aborting any active transactions
2025-07-01 15:15:18.011 CEST [23470] LOG: background worker "logical replication launcher" (PID 23476) exited with exit code 1
2025-07-01 15:15:18.011 CEST [23471] LOG: shutting down
2025-07-01 15:15:18.015 CEST [23471] LOG: checkpoint starting: shutdown immediate
2025-07-01 15:15:18.077 CEST [23471] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.005 s, sync=0.002 s, total=0.067 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=4/8A753888, redo lsn=4/8A753888
2025-07-01 15:15:18.081 CEST [23470] LOG: database system is shut down
postgres=# SHOW data_directory;
data_directory
-----------------------------
/var/lib/postgresql/16/main
and
postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE 'log\_%' OR name = 'logging_collector';
name | setting
-----------------------------------+--------------------------------
log_autovacuum_min_duration | 600000
log_checkpoints | on
log_connections | off
log_destination | stderr
log_directory | log
log_disconnections | off
log_duration | off
log_error_verbosity | default
log_executor_stats | off
log_file_mode | 0600
log_filename | postgresql-%Y-%m-%d_%H%M%S.log
log_hostname | off
log_line_prefix | %m [%p] %q%u@%d
log_lock_waits | off
log_min_duration_sample | -1
log_min_duration_statement | -1
log_min_error_statement | error
log_min_messages | warning
log_parameter_max_length | -1
log_parameter_max_length_on_error | 0
log_parser_stats | off
log_planner_stats | off
log_recovery_conflict_waits | off
log_replication_commands | off
log_rotation_age | 1440
log_rotation_size | 10240
log_startup_progress_interval | 10000
log_statement | none
log_statement_sample_rate | 1
log_statement_stats | off
log_temp_files | -1
log_timezone | Europe/Zurich
log_transaction_sample_rate | 0
log_truncate_on_rotation | off
logging_collector | off
(35 rows)
log_statement: none,ddl, mod,all
log_hostname
SELECT set_config(’log_statement’, ‘all’, false);
ALTER SYSTEM SET log_statement = ‘all’; ALTER SYSTEM SET log_hostname = true; SELECT pg_reload_conf();
ALTER SYSTEM RESET log_statement;
ALTER DATABASE current_database() SET log_statement = ‘all’;
SELECT name, setting, context, source, boot_val, reset_val, sourcefile, pending_restart from pg_settings WHERE NAME LIKE ’log%’ limit 20;
https://www.postgresql.org/docs/current/runtime-config-logging.html https://www.atlassian.com/data/admin/how-to-log-queries-in-postgresql
DATABASESCHEMA