Обсуждение: Minimum privilege for Backup and replication

Dear All Postgresql Users,
I am new to pgsql. I have an installation on my Ubuntu 18.04.

Now, my question is -
What is the Minimum Privilege of a pgsql Backup or Replication user?

I know how to do it for mysql but not pgsql.?? I begin to switch to pgsql 
v11 from mysql v5.7 after knowing it many nice features.?? Mysql seems 
very old-fashioned.?? Oracle and IBM DB2 are just too $$$ to me.

Thanks for your opinions.

Timmy

On Thu, Oct 10, 2019 at 1:26 PM Timmy Siu <timmy.siu@aol.com> wrote:
> Now, my question is -
> What is the Minimum Privilege of a pgsql Backup or Replication user?

It is not clear what your question really is. If you are thinking
about a "database user with backup grant", there is no thing like that
in PostgreSQL. If you asking what you need for replication, your user
must have "with replication" flag (create role/alter role).

Hope this helps.
Luca

Greetings,

* Timmy Siu (timmy.siu@aol.com) wrote:
> Now, my question is -
> What is the Minimum Privilege of a pgsql Backup or Replication user?

To perform a file-level backup of PostgreSQL, your OS user will need
read access to all of the files in the data directory (you can use group
privileges for that as of v11, as I recall), and then it'll need to be
able to connect to PG as a user and be able to execute the
pg_start_backup() and pg_stop_backup() functions (you can grant that
access by issuing GRANT commands).  Depending on your file-level backup
solution (please do NOT roll your own...), you might also need to grant
access to functions like pg_switch_wal() too.  Check the documentation
for the backup solution.

We do not (yet, anyway..  maybe in v13) have a way to GRANT read access
to all tables in a database to be able to perform a database-wide export
of the dat (using pg_dump).

For replication, you need to create a user with the 'replication' role
attribute and configure pg_hba.conf to allow the client to connect to
the replication database.

Thanks,

Stephen