By Andomar

2014-08-02 16:59:47 8 Comments

When I create a user in Postgres,

create user test with password 'test';

The user ends up being able to connect to every database in the instance. The user can also select from any table:

# psql -h localhost testdb test
Password for user test: *****
psql (8.4.20, server 9.1.13)
WARNING: psql version 8.4, server version 9.1.
         Some psql features might not work.
Type "help" for help.

testdb=> select * from testtable;
(1 row)

The \l database listing shows that testdb has empty Access privileges. So why can a newly created user connect to that database?

The newly created "test" user can also select data from tables. Why can he do so without a grant of select privileges?

I'd like to create a user that can only connect to one database and only read from certain views. How can I prevent a user from reading directly from tables?


@Erwin Brandstetter 2014-08-04 15:48:25

Also addressing the question in the comments.

Role public

Per documentation:

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles.

Bold emphasis mine. Membership in public cannot be revoked (or granted). You can only revoke privileges from public. In your case, to block out all roles without explicit privileges, like @Robert already provided:


Role postgres

The database role postgres is a superuser by default. You can take that away from postgres - but that would be unwise: everybody (including some client programs) expects postgres to be a superuser. You can also create more superusers (careful with that!). Superusers don't need privileges. The manual once more:

It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary.

As to your question:

Is it true that if \l or \dn+ displays empty access privileges, that is the same as postgres=UC/postgres, =UC/postgres

Not exactly.

  • Privileges are granted by the owner or a superuser (or a role that was granted the privilege to do so). postgres=UC/postgres would mean postgres granted it, which is not accurate for empty privileges, but the effect is the same in a default installation.

  • However, public gets no privileges for new schemas and only CONNECT and TEMP (can create temporary tables) privileges for databases by default, not the CREATE privilege (cannot create schemas). That would be something like =UT/?? according to the list of possible privileges - the manual once more:

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

You can change default privileges. I wrote more about that in the related answer mentioned in the comments:

But those default privileges only apply objects created after defaults were changed. So what does an empty ACL entry mean? The manual on the system catalog pg_default_acl:

Note that when an ACL entry in another catalog is null, it is taken to represent the hard-wired default privileges for its object, not whatever might be in pg_default_acl at the moment.

Bold emphasis mine.

@Robert Gannon 2014-08-02 17:57:21

To address the question of why test can SELECT from tables without a GRANT, it is because you have not explicitly run REVOKE for the ability to connect to your database(s) from PUBLIC, and then explicitly run GRANT for your test user:

GRANT connect ON DATABASE testdb TO test;

Once that is done, you would REVOKE SELECT for your user within that database your tables and other items you wish to prevent the user from accessing, and then explicitly GRANT SELECT for the views.

Note that user here could also be role, which multiple users could then be added to.

@Andomar 2014-08-02 21:03:53

Thanks. I won't be able to revoke privileges from public as that would affect other users. Is there a way to deny rights to a particular user, in effect overruling the grant from public?

@Andomar 2014-08-03 07:28:09

After revoke connect on database testdb from test, the test user can still connect to testdb. It seems you can't overrule grants from public then?

@Craig Ringer 2014-08-03 13:02:09

@Andomar REVOKE means "take away a grant". You'd want a DENY rule, i.e. "explicitly disallow what would otherwise be allowed", but PostgreSQL doesn't have them. So you must REVOKE from public then grant the rights back to the users/groups who require it.

@Robert Gannon 2014-08-03 19:16:50

Even if you do not know all the users that may need to connect to a DB, if you can enforce that they be added to a role granted connect other than public, that should not prove difficult.

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] Created user can access all databases in PostgreSQL without any grants

1 Answered Questions

1 Answered Questions

[SOLVED] Adding new user in Postgres

3 Answered Questions

[SOLVED] Why is a new user allowed to create a table?

1 Answered Questions

[SOLVED] password confusion in postgres sql

1 Answered Questions

[SOLVED] When are privileges listed in \l and when not?

2 Answered Questions

[SOLVED] How do I give [email protected] permission to grant privileges in MySQL?

  • 2015-04-09 16:10:12
  • user1075722
  • 9984 View
  • 2 Score
  • 2 Answer
  • Tags:   mysql permissions

1 Answered Questions

[SOLVED] Why is a new user able to create tables in PostgreSQL?

Sponsored Content