Azure – How to create a read-only user in Azure PostgreSQL

azurepostgresql

How can I create a read-only user in Azure PostgreSQL?

On my local postgres installation, I can run these commands to create a read-only user:

  CREATE USER spqr1 WITH ENCRYPTED PASSWORD 'password';
  REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
  CREATE DATABASE mydb;
  REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
  GRANT CONNECT ON DATABASE mydb TO spqr1;
  \connect mydb
  REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;
  GRANT USAGE ON SCHEMA public TO spqr1;

When I run these commands on an Azure PostgreSQL database, the new user spqr1 has privileges that I don't want: they can connect to the postgres database, and create tables in mydb. On my local machine, the new user doesn't have these privileges.

It looks to me like there is a default user azure_superadmin who has granted PUBLIC the privilege of connecting to the postgres database, and the privilege of creating tables in any public schema. My admin account (meadmin) didn't grant those privileges, and can't remove them. Is this what's going on, and if so then how should I create a read-only account?

Here is the trimmed output from \l:

   Name    |      Owner      |      Access privileges          
-----------+-----------------+-------------------------------------
 mydb      | meadmin         | =T/meadmin                         +
           |                 | meadmin=CTc/meadmin                +
           |                 | spqr1=c/meadmin
 postgres  | azure_superuser | =Tc/azure_superuser                +
           |                 | azure_superuser=CTc/azure_superuser
 template0 | azure_superuser | =c/azure_superuser                 +
           |                 | azure_superuser=CTc/azure_superuser
 template1 | azure_superuser | =c/azure_superuser                 +
           |                 | azure_superuser=CTc/azure_superuser

And here is the trimmed output from \dn+, after I have connected to mydb:

  Name  |      Owner      |         Access privileges        
--------+-----------------+------------------------------------
 public | azure_superuser | azure_superuser=UC/azure_superuser+
        |                 | =UC/azure_superuser

Best Answer

I was having a lot of trouble with this too but I finally got it working. Here's what I did:

  1. Create new role, readonly

    CREATE ROLE readonly WITH LOGIN PASSWORD 'password';

  2. Grant connect on database

    GRANT CONNECT ON DATABASE databasename to readonly;

  3. Connect to [databasename] on local database cluster

    \c databasename

  4. Grant Usage to schemas

    GRANT USAGE ON SCHEMA schema_name TO readonly;

  5. Grant SELECT access to all current and future tables

    ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema_name TO readonly;

Note that as far as I know, you won't be able to create a read only user for future schemas that are created as well. I think you will have to grant the select privileges again if a new schema is created. Also note that the one thing that really tripped me up was that the ALTER DEFAULT PRIVILEGES and GRANT SELECT ON ALL... statements only worked when I was logged in as the owner of those schemas. When I was not logged in as an owner of those schemas when I ran those commands there were no error messages or anything but then the readonly user still didn't have sufficient access. Hope this helps!

Related Topic