How to secure your PostgreSQL database
- Linh Le
Follow these steps to enable SSL connections and properly configure user privileges to keep your database secure
Another month, another database security disaster. It seems database security breaches are becoming more common as bad actors realize that databases contain lots of, well, data.
How does this keep happening? Usually, the answer is poorly executed database security protocols. How can we stop it from happening?
The good news is that it’s preventable. Databases do not need to be at risk, as long as you take the necessary precautions. Usually the answer is configuration. Don’t just use the out-of-box settings. Do use iptables to secure access. (As far as can be determined in the recent Exactis breach, had the setup made use of iptables or a similar feature, then the breach would have been prevented.)
In this article, we’ll look at steps you can take to secure PostgreSQL against intrusions and attacks. First we’ll look at implementing SSL connections and certificate-based authentication in PostgreSQL. Then we’ll examine how to create users and groups in PostgreSQL that provide the minimum appropriate level of database access.
As a tried-and-true open source database, PostgreSQL is used in many enterprise and start-up database deployments. There is certainly lots of data in PostgreSQL to protect.
Configure secure connections in PostgreSQL
PostgreSQL allows you to enable encryption of data over the wire through SSL. PostgreSQL uses SSL connections to encrypt the communication between client and server. In order to use SSL, you must have OpenSSL installed both on your client and server.
Steps to encrypt connections using SSL
To proceed further, you must create the server and client certification files that are signed by a Certification Authority.
1. Create a certificate signing request (CSR) and a key file.
1 2 | <span class="pln">$ openssl genrsa </span><span class="pun">-</span><span class="kwd">out</span><span class="pln"> rootCA</span><span class="pun">.</span><span class="pln">key </span><span class="lit">1024</span> <span class="pln">$ openssl req </span><span class="pun">-</span><span class="pln">x509 </span><span class="pun">-</span><span class="kwd">new</span> <span class="pun">-</span><span class="pln">key rootCA</span><span class="pun">.</span><span class="pln">key </span><span class="pun">-</span><span class="pln">days </span><span class="lit">365</span> <span class="pun">-</span><span class="kwd">out</span><span class="pln"> rootCA</span><span class="pun">.</span><span class="pln">crt </span><span class="pun">-</span><span class="pln">subj </span><span class="str">'/C=XX/L=Default City/O=Default Company Ltd/CN=root'</span> |
2. Create a server certificate signed by the root certificate authority.
1 2 3 | <span class="pln">$ openssl genrsa </span><span class="pun">-</span><span class="kwd">out</span><span class="pln"> server</span><span class="pun">.</span><span class="pln">key </span><span class="lit">1024</span> <span class="pln">$ openssl req </span><span class="pun">-</span><span class="kwd">new</span> <span class="pun">-</span><span class="pln">key server</span><span class="pun">.</span><span class="pln">key </span><span class="pun">-</span><span class="kwd">out</span><span class="pln"> server</span><span class="pun">.</span><span class="pln">csr </span><span class="pun">-</span><span class="pln">subj </span><span class="str">'/C=XX/L=Default City/O=Default Company Ltd/CN=pgservername'</span> <span class="pln">$ openssl x509 </span><span class="pun">-</span><span class="pln">req </span><span class="pun">-</span><span class="kwd">in</span><span class="pln"> server</span><span class="pun">.</span><span class="pln">csr </span><span class="pun">-</span><span class="pln">CA rootCA</span><span class="pun">.</span><span class="pln">crt </span><span class="pun">-</span><span class="typ">CAkey</span><span class="pln"> rootCA</span><span class="pun">.</span><span class="pln">key </span><span class="pun">-</span><span class="typ">CAcreateserial</span> <span class="pun">-</span><span class="kwd">out</span><span class="pln"> server</span><span class="pun">.</span><span class="pln">crt </span><span class="pun">-</span><span class="pln">days </span><span class="lit">365</span> |
(Note that CN
in the second line above should be the server name for server validation.)
3. Create a client certificate signed by the root certificate authority.
1 2 3 | <span class="pln">$ openssl genrsa </span><span class="pun">-</span><span class="kwd">out</span><span class="pln"> postgresql</span><span class="pun">.</span><span class="pln">key </span><span class="lit">1024</span> <span class="pln">$ openssl req </span><span class="pun">-</span><span class="kwd">new</span> <span class="pun">-</span><span class="pln">key postgresql</span><span class="pun">.</span><span class="pln">key </span><span class="pun">-</span><span class="kwd">out</span><span class="pln"> postgresql</span><span class="pun">.</span><span class="pln">csr </span><span class="pun">-</span><span class="pln">subj </span><span class="str">'/C=XX/L=Default City/O=Default Company Ltd/CN=postgres'</span> <span class="pln">$ openssl x509 </span><span class="pun">-</span><span class="pln">req </span><span class="pun">-</span><span class="kwd">in</span><span class="pln"> postgresql</span><span class="pun">.</span><span class="pln">csr </span><span class="pun">-</span><span class="pln">CA rootCA</span><span class="pun">.</span><span class="pln">crt </span><span class="pun">-</span><span class="typ">CAkey</span><span class="pln"> rootCA</span><span class="pun">.</span><span class="pln">key </span><span class="pun">-</span><span class="typ">CAcreateserial</span> <span class="pun">-</span><span class="kwd">out</span><span class="pln"> postgresql</span><span class="pun">.</span><span class="pln">crt </span><span class="pun">-</span><span class="pln">days </span><span class="lit">365</span> |
4. Copy the server certification file (server.crt), server private key (server.key), and root certification authority (rootCA.crt) to either the PostgreSQL data directory or a secured location accessible by PostgreSQL superuser (postgres). Once copied, allow read-only privileges to PostgreSQL users.
1 2 | <span class="pln">$ cd $PGDATA</span> <span class="pln">$ chmod </span><span class="lit">0400</span><span class="pln"> server</span><span class="pun">.</span><span class="pln">crt server</span><span class="pun">.</span><span class="pln">key rootCA</span><span class="pun">.</span><span class="pln">crt</span> |
5. Set the appropriate parameters in PostgreSQL to enable SSL mode.
1 2 3 4 5 | <span class="pln">$ psql</span> <span class="pln">ALTER SYSTEM SET ssl TO </span><span class="str">'ON'</span><span class="pun">;</span> <span class="pln">ALTER SYSTEM SET ssl_ca_file TO </span><span class="str">'root.crt'</span><span class="pun">;</span> <span class="pln">ALTER SYSTEM SET ssl_cert_file TO </span><span class="str">'server.crt'</span><span class="pun">;</span> <span class="pln">ALTER SYSTEM SET ssl_key_file TO </span><span class="str">'server.key'</span><span class="pun">;</span> |
6. OpenSSL supports a wide range of ciphers. You will want to choose the cipher that suits your organizational standards. Once you have chosen the cipher you wish to use, set the following parameter in PostgreSQL.
1 2 | <span class="pln">psql</span> <span class="pln">ALTER SYSTEM SET ssl_ciphers TO </span><span class="str">'your_desired_cipher'</span><span class="pun">;</span> <span class="pun">(=></span> <span class="typ">Change</span><span class="pln"> requires </span><span class="typ">Server</span> <span class="typ">Reload</span><span class="pun">)</span> |
Default cipher is: HIGH:MEDIUM:+3DES:!aNULL
7. You may either reload or restart PostgreSQL server to get these parameters into effect.
1 | <span class="pln">$ psql </span><span class="pun">-</span><span class="pln">c </span><span class="str">"select pg_reload_conf()"</span> |
or
1 | <span class="pln">$ pg_ctl </span><span class="pun">-</span><span class="pln">D $PGDATA restart </span><span class="pun">-</span><span class="pln">mf</span> |
8. To enable encryption using SSL, you must modify host to hostssl in the pg_hba.conf file. For example, to enable SSL for local connections, replace host with hostssl for local connections.
1 | <span class="pln">$ vim $PGDATA</span><span class="pun">/</span><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">conf</span> |
1 2 3 4 5 6 7 | <span class="pun">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>></span><span class="pln"> </span> <span class="com"># IPv4 local connections:</span> <span class="com"># host all all 127.0.0.1/32 trust</span> <span class="pln">hostssl all all </span><span class="lit">127.0</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">/</span><span class="lit">32</span><span class="pln"> trust</span> <span class="pun">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>></span><span class="pln"> </span> |
9. You may add similar entries to enable encryption of connections from your remote applications or clients.
1 | <span class="pln">$ vim $PGDATA</span><span class="pun">/</span><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">conf</span> |
1 2 3 4 5 6 7 8 9 | <span class="pun">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>></span><span class="pln"> </span> <span class="com"># IPv4 local connections:</span> <span class="com"># host all all 127.0.0.1/32 trust</span> <span class="pln">hostssl all all </span><span class="lit">127.0</span><span class="pun">.</span><span class="lit">0.1</span><span class="pun">/</span><span class="lit">32</span><span class="pln"> trust</span> <span class="pln">hostssl all all app_server_1</span><span class="pun">/</span><span class="lit">32</span><span class="pln"> md5</span> <span class="pln">hostssl all all app_server_2</span><span class="pun">/</span><span class="lit">32</span><span class="pln"> md5</span> <span class="pun"><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<</span><span class="pln"> </span> |
10. Once you have made the changes to pg_hba.conf file, you must perform a SIGHUP or reload.
1 | <span class="pln">$ psql </span><span class="pun">-</span><span class="pln">c </span><span class="str">"select pg_reload_conf()"</span> |
11. Test your local connections for SSL. This is how it looks before and after enabling SSL for local connections.
Before
1 2 3 4 5 6 | <span class="pun">=======</span> <span class="pln">$ psql </span><span class="pun">-</span><span class="pln">h localhost</span> <span class="pln">psql </span><span class="pun">(</span><span class="lit">10.4</span><span class="pun">)</span> <span class="typ">Type</span> <span class="str">"help"</span> <span class="kwd">for</span><span class="pln"> help</span><span class="pun">.</span> <span class="pln">postgres</span><span class="pun">=#</span> |
After
1 2 3 4 5 6 7 | <span class="pun">=======</span> <span class="pln">$ psql </span><span class="pun">-</span><span class="pln">h localhost</span> <span class="pln">psql </span><span class="pun">(</span><span class="lit">10.4</span><span class="pun">)</span> <span class="pln">SSL connection </span><span class="pun">(</span><span class="pln">protocol</span><span class="pun">:</span> <span class="typ">TLSv1</span><span class="pun">.</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> cipher</span><span class="pun">:</span><span class="pln"> ECDHE</span><span class="pun">-</span><span class="pln">RSA</span><span class="pun">-</span><span class="pln">AES256</span><span class="pun">-</span><span class="pln">GCM</span><span class="pun">-</span><span class="pln">SHA384</span><span class="pun">,</span><span class="pln"> bits</span><span class="pun">:</span> <span class="lit">256</span><span class="pun">,</span><span class="pln"> compression</span><span class="pun">:</span><span class="pln"> off</span><span class="pun">)</span> <span class="typ">Type</span> <span class="str">"help"</span> <span class="kwd">for</span><span class="pln"> help</span><span class="pun">.</span> <span class="pln">postgres</span><span class="pun">=#</span> |
If you use hostssl for your remote application connections, the communication to the database server is automatically encrypted.
Steps to enable client certificate authentication
12. A client certificate can be used for user identity by the client or application server. It is especially useful for authentication. You can now copy the client certificates generated in Step 3 (above) to your remote application server. Once copied, set appropriate privileges. For example, the IP of my application server for this test is 192.168.0.13.
1 | <span class="pln">$ scp postgresql</span><span class="pun">.</span><span class="pln">crt postgresql</span><span class="pun">.</span><span class="pln">key rootCA</span><span class="pun">.</span><span class="pln">crt postgres@192</span><span class="pun">.</span><span class="lit">168.0</span><span class="pun">.</span><span class="lit">13</span><span class="pun">:</span><span class="str">/var/</span><span class="pln">lib</span><span class="pun">/</span><span class="pln">pgsql</span> |
Once copied to the application server, give read-only privileges to the appropriate OS user in the application server.
1 | <span class="pln">$ chmod </span><span class="lit">0400</span><span class="pln"> postgresql</span><span class="pun">.</span><span class="pln">crt postgresql</span><span class="pun">.</span><span class="pln">key rootCA</span><span class="pun">.</span><span class="pln">crt</span> |
13. In order to use client certificate authentication, add the following entry to the pg_hba.conf of your remote PostgreSQL server.
1 | <span class="pln">$ vi $PGDATA</span><span class="pun">/</span><span class="pln">pg_hba</span><span class="pun">.</span><span class="pln">conf</span> |
1 2 3 4 5 | <span class="pun">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>></span><span class="pln"> </span> <span class="pln">hostssl all all </span><span class="lit">192.168</span><span class="pun">.</span><span class="lit">0.13</span><span class="pun">/</span><span class="lit">32</span><span class="pln"> cert clientcert</span><span class="pun">=</span><span class="lit">1</span> <span class="pun"><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<</span><span class="pln"> </span> |
Here, 192.168.0.13 is the application server’s IP address. You may wish to have multiple entries this way.
Observe the difference between Step 9 and Step 13. The pg_hba.conf entry in Step 9 would enable communication over encryption, with connections encrypted automatically. The pg_hba.conf entry in Step 13 goes further. It would also force the client certification authentication to ensure that the communication is happening between trusted parties.
Give a SIGHUP or reload to put the changes to pg_hba.conf into effect.
1 | <span class="pln">$ pg_ctl </span><span class="pun">-</span><span class="pln">D $PGDATA reload</span> |
14. Validate the remote connection using the psql client.
1 2 3 4 5 | <span class="pln">$ psql </span><span class="str">"port=5432 host=192.168.0.12 user=postgres sslcert=/var/lib/pgsql/postgresql.crt sslkey=/var/lib/pgsql/postgresql.key sslrootcert=/var/lib/pgsql/rootCA.crt sslmode=require"</span> <span class="pln">psql </span><span class="pun">(</span><span class="lit">10.4</span><span class="pun">)</span> <span class="pln">SSL connection </span><span class="pun">(</span><span class="pln">protocol</span><span class="pun">:</span> <span class="typ">TLSv1</span><span class="pun">.</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> cipher</span><span class="pun">:</span><span class="pln"> ECDHE</span><span class="pun">-</span><span class="pln">RSA</span><span class="pun">-</span><span class="pln">DES</span><span class="pun">-</span><span class="pln">CBC3</span><span class="pun">-</span><span class="pln">SHA</span><span class="pun">,</span><span class="pln"> bits</span><span class="pun">:</span><span class="pln"> </span><span class="lit">168</span><span class="pun">,</span><span class="pln"> compression</span><span class="pun">:</span><span class="pln"> off</span><span class="pun">)</span> <span class="typ">Type</span><span class="pln"> </span><span class="str">"help"</span><span class="pln"> </span><span class="kwd">for</span><span class="pln"> help</span><span class="pun">.</span> <span class="pln">postgres</span><span class="pun">=#</span> |
You should see the connections happening over SSL.
Create secure user and group roles in PostgreSQL
PostgreSQL allows you to enable encryption of data over the wire through SSL. However, it is also important to understand how security hardening and user management works in PostgreSQL. A user in PostgreSQL must have appropriate privileges that don’t give too much access. The steps below should help you understand how PostgreSQL implements user management. They also present some best practices.
What is a PostgreSQL user?
A PostgreSQL user is a role that has CONNECT privilege. Both CREATE USER
and CREATE ROLE
work well to create a PostgreSQL user. However, the user must have a LOGIN role. The login role is assigned to a user when you use any of the following three approaches:
1 | <span class="pln">CREATE USER percuser WITH ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> |
or
1 | <span class="pln">CREATE ROLE percuser WITH ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> |
or
1 2 | <span class="pln">CREATE ROLE percuser</span><span class="pun">;</span> <span class="pln">ALTER ROLE percuser WITH LOGIN ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> |
When you use CREATE USER
with any of the above, PostgreSQL automatically modifies the syntax internally with the following one:
1 2 3 | <span class="pln">CREATE ROLE percuser</span> <span class="pln">WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS</span> <span class="pln">ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> |
In the above syntax automatically modified by PostgreSQL, we see that it is not granting the ROLES that are needed to manage PostgreSQL.
After creating the user or role you may use the following query to see if the user can login (has the CONNECT privilege):
1 2 3 4 5 | <span class="pln">postgres</span><span class="pun">=#</span> <span class="kwd">select</span><span class="pln"> rolcanlogin </span><span class="kwd">from</span><span class="pln"> pg_roles </span><span class="kwd">where</span><span class="pln"> rolname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'percuser'</span><span class="pun">;</span> <span class="pln">rolcanlogin</span> <span class="pun">-------------</span> <span class="pln">t</span> <span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span> |
This returns a boolean that tells whether the user can log in or not. For example, when you use the following syntax to create a ROLE you see that the role does not have CONNECT privilege.
1 2 3 4 5 6 7 | <span class="pln">postgres</span><span class="pun">=#</span><span class="pln"> CREATE ROLE percrole</span><span class="pun">;</span> <span class="pln">CREATE ROLE</span> <span class="pln">postgres</span><span class="pun">=#</span> <span class="kwd">select</span><span class="pln"> rolcanlogin </span><span class="kwd">from</span><span class="pln"> pg_roles </span><span class="kwd">where</span><span class="pln"> rolname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'percrole'</span><span class="pun">;</span> <span class="pln">rolcanlogin</span> <span class="pun">-------------</span> <span class="pln">f</span> <span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span> |
Please note: Always avoid creating any users or roles with prefix pg_%.
Difference between CREATE USER and CREATE ROLE in PostgreSQL
When you create a user using CREATE USER
, the user automatically gets a LOGIN role:
1 2 3 4 5 6 7 | <span class="pln">postgres</span><span class="pun">=#</span><span class="pln"> CREATE USER percuser WITH ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> <span class="pln">CREATE ROLE</span> <span class="pln">postgres</span><span class="pun">=#</span> <span class="kwd">select</span><span class="pln"> rolcanlogin </span><span class="kwd">from</span><span class="pln"> pg_roles </span><span class="kwd">where</span><span class="pln"> rolname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'percuser'</span><span class="pun">;</span> <span class="pln">rolcanlogin</span> <span class="pun">-------------</span> <span class="pln">t</span> <span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span> |
However, when you create a role you must explicitly add the LOGIN role to allow the user to log in:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <span class="pln">postgres</span><span class="pun">=#</span><span class="pln"> CREATE ROLE percuser WITH ENCRYPTED PASSWORD </span><span class="str">'secret'</span><span class="pun">;</span> <span class="pln">CREATE ROLE</span> <span class="pln">postgres</span><span class="pun">=#</span> <span class="kwd">select</span><span class="pln"> rolcanlogin </span><span class="kwd">from</span><span class="pln"> pg_roles </span><span class="kwd">where</span><span class="pln"> rolname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'percuser'</span><span class="pun">;</span> <span class="pln">rolcanlogin</span> <span class="pun">-------------</span> <span class="pln">f</span> <span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span> <span class="pln">postgres</span><span class="pun">=#</span><span class="pln"> ALTER ROLE percuser WITH LOGIN</span><span class="pun">;</span> <span class="pln">ALTER ROLE</span> <span class="pln">postgres</span><span class="pun">=#</span> <span class="kwd">select</span><span class="pln"> rolcanlogin </span><span class="kwd">from</span><span class="pln"> pg_roles </span><span class="kwd">where</span><span class="pln"> rolname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'percuser'</span><span class="pun">;</span> <span class="pln">rolcanlogin</span> <span class="pun">-------------</span> <span class="pln">t</span> <span class="pun">(</span><span class="lit">1</span><span class="pln"> row</span><span class="pun">)</span> |
What is a group or a group role in PostgreSQL?
In PostgreSQL, a role can INHERIT another role. This means one role can get access using the privileges of another role. This can be achieved using GRANT or INHERIT keywords.
It is always recommended to provide access privileges to users by using GROUP ROLES.
For example, consider an organization with:
- 1 database (percona)
- 2 schemas (scottand tiger)
Let’s say we then need to create 10 individual users and 10 application users observing the following requirements:
- Five of the application users must have read-only access to the tables of the schema scott and read-write access to tables of schema tiger.
- The other five application users must have read-write access to the tables of schema scott and read-only access to tables of schema tiger.
- All 10 individual user accounts can have only read-only access to the tables of schemas scott and tiger.
In this situation, you can create four group roles like the following:
scott_readonly:
1 2 | <span class="pln">CREATE ROLE scott_readonly</span><span class="pun">;</span> <span class="pln">GRANT USAGE</span><span class="pun">,</span><span class="pln"> SELECT ON ALL TABLES IN SCHEMA scott TO scott_readonly</span><span class="pun">;</span> |
scott_readwrite:
1 2 | <span class="pln">CREATE ROLE scott_readwrite</span><span class="pun">;</span> <span class="pln">GRANT USAGE</span><span class="pun">,</span><span class="pln"> SELECT</span><span class="pun">,</span><span class="pln"> INSERT</span><span class="pun">,</span><span class="pln"> UPDATE</span><span class="pun">,</span><span class="pln"> DELETE ON ALL TABLES IN SCHEMA scott TO scott_readwrite</span><span class="pun">;</span> |
tiger_readonly:
1 2 | <span class="pln">CREATE ROLE tiger_readonly</span><span class="pun">;</span> <span class="pln">GRANT USAGE</span><span class="pun">,</span><span class="pln"> SELECT ON ALL TABLES IN SCHEMA tiger TO tiger_readonly</span><span class="pun">;</span> |
tiger_readwrite:
1 2 | <span class="pln">CREATE ROLE tiger_readwrite</span><span class="pun">;</span> <span class="pln">GRANT USAGE</span><span class="pun">,</span><span class="pln"> SELECT</span><span class="pun">,</span><span class="pln"> INSERT</span><span class="pun">,</span><span class="pln"> UPDATE</span><span class="pun">,</span><span class="pln"> DELETE ON ALL TABLES IN SCHEMA tiger TO tiger_readwrite</span><span class="pun">;</span> |
Now, you can GRANT
these group roles to the users based on the above three requirements.
1 2 3 | <span class="pln">GRANT scott_readonly</span><span class="pun">,</span><span class="pln">tiger_readonly TO user1</span><span class="pun">;</span> <span class="pln">GRANT scott_readonly</span><span class="pun">,</span><span class="pln">tiger_readwrite TO appuser1</span><span class="pun">;</span> <span class="pln">GRANT scott_readwrite</span><span class="pun">,</span><span class="pln">tiger_readonly TO appuser2</span><span class="pun">;</span> |
By assigning group roles to these users, you can segregate which privileges are allowed to which user.
Reference: https://www.postgresql.org/docs/10/static/sql-createrole.html
Are users and roles global to the entire instance?
Yes, users and roles in PostgreSQL are global to the entire instance. One user can be used to access any database. Similarly, a group role in PostgreSQL can be granted privileges of multiple schemas/objects in multiple databases.
Source : https://www.infoworld.com