What is the method of establishing read-only users in PostgreSQL database

This article introduces the relevant knowledge of "what is the method of establishing read-only users of PostgreSQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1) create a read-only user

Create user user_reader password 'user_reader'

Specify the search path for read-only users

Alter user user_reader set search_path='user'

2) Authorization:

Authorized database logged in by postgres user

Grant select on all tables in schema schema-user to user_reader

3) modify pg_hba.conf to allow login and reload the configuration file

4) Log in to the database with a read-only user and execute select * from table directly.

5) if you don't say step 5, you will definitely come back for me.

In the place authorized in step 2, it is not possible to grant access only to the table, but also to the schema.

Grant usage on schema schema_user to user_reader; well, you can access it normally now. Got it.

However, it should have the same defects as oracle. If the new table is added, it will not be accessible.

Need to re-execute

Grant select on all tables in schema schema-user to user_reader

This sentence, or authorization for individual tables. In the future, if the newly added table automatically authorizes read-only users, then the following sentence is more important.

Alter default privileges in schema schema_name grant select on tables to schema_reader

The execution of this sql needs to be executed with table's ower, not by the superuser. Otherwise, using read-only users to query will report insufficient permissions and cannot be queried.

This is the end of the content of "what is the method of establishing read-only users of PostgreSQL database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!